希望有人能帮我做这件事,我真的被它蒙蔽了双眼。
我有一个存储用户选择的产品的表,在该表中我存储ordertime、pickedup和pickeuptime。因此,structur是index(PRIMARY INT)、ordernr(INT),ordertime(TIMESTAMP)、producted(INT)、pickedup(TINYINT)和pickeduptime(TIMESTAMP)。
现在我想得到一个图表的结果集,上面写着,日期,订购产品的数量和按日期挑选、分组和订购的产品的数量。我现在的问题是,我得到了几乎正确的数字,但pickedup被计入了订单,我似乎无法理解这一点。
SELECT (case when pickedup = '1' then DATE(order_detail.pickeduptime) else DATE(order_detail.ordertime) end) AS x,COUNT(productid) AS y,SUM(case when pickedup = '1' then 1 else 0 end) AS z FROM order_detail WHERE productid = '127' AND YEAR(`ordertime`) = YEAR(NOW()) GROUP BY x ORDER BY x asc
任何帮助都将不胜感激。:-)
更新:
1 index int(11) AUTO_INCREMENT Primary
2 ordernr int(11)
3 productid int(11)
4 price int(11)
5 ordertime timestamp CURRENT_TIMESTAMP
6 pickedup tinyint(4) 0
7 pickeduptime timestamp 0000-00-00 00:00:00
桌子看起来怎么样。
可能有帮助的第一件事是正确格式化SQL:
SELECT
(CASE
WHEN pickedup = '1' THEN DATE(pickeduptime)
ELSE DATE(ordertime)
END) AS x,
COUNT(productid) AS y,
SUM(CASE
WHEN pickedup = '1' THEN 1
ELSE 0
END) AS z
FROM
order_detail
WHERE
productid = '127' AND
YEAR(ordertime) = YEAR(NOW())
GROUP BY x
ORDER BY x ASC
现在我需要弄清楚你想做什么。这是真正的问题开始的地方。目前尚不清楚是否重复使用表中的行进行拾取。对不起,我帮不了你。不过,您的SQL非常奇怪。对于这样简单的事情,您不需要使用两个CASE
命令。
我也无法理解您的SQL,按两列中的日期分组?
仔细阅读后,我想我开始明白你想要什么了。您想了解每个日期订购和提货的产品数量。我不会尝试使用一个SQL命令,而是使用两个。两行,两个SQL命令。:-)
首先是一个简单的问题:按日期订购、分组和排序的产品数量:
SELECT
DATE(ordertime) as orderdate,
COUNT(productid) AS quantity
FROM
order_detail
WHERE
productid = '127' AND
YEAR(ordertime) = YEAR(NOW())
GROUP BY orderdate
ORDER BY orderdate ASC
现在,尚未提货的订购产品:
SELECT
DATE(ordertime) as orderdate,
COUNT(productid) AS quantity
FROM
order_detail
WHERE
productid = '127' AND
YEAR(ordertime) = YEAR(NOW()) AND
pickedup != '1'
GROUP BY orderdate
ORDER BY orderdate ASC
现在,已经提货的产品仍在订单日期排序:
SELECT
DATE(ordertime) as orderdate,
COUNT(productid) AS quantity
FROM
order_detail
WHERE
productid = '127' AND
YEAR(ordertime) = YEAR(NOW()) AND
pickedup = '1'
GROUP BY orderdate
ORDER BY orderdate ASC
以下是按提货日期排序的相同信息:
SELECT
DATE(pickeduptime) as pickedupdate,
COUNT(productid) AS quantity
FROM
order_detail
WHERE
productid = '127' AND
YEAR(ordertime) = YEAR(NOW()) AND
pickedup = '1'
GROUP BY pickedupdate
ORDER BY pickedupdate ASC
现在,您可以使用其中任何一个在图形中绘制一条线。
如果你坚持一个查询,你可以使用这个:
SELECT
DATE(ordertime) as orderdate,
SUM(pickedup) AS pickedup_quantity,
SUM(1-pickedup) AS not_pickedup_quantity
FROM
order_detail
WHERE
productid = '127' AND
YEAR(ordertime) = YEAR(NOW())
GROUP BY orderdate
ORDER BY orderdate ASC
请注意,所有内容都必须按一个日期排序。
不确定你到底有什么问题,但我认为你有一个语法错误:忘记在"END"后面放一个"CASE",就像MySQL参考中一样