如何合并两个不同地点和特殊条件的选择
如何合并两个不同地点和特殊条件的选择
提问于 2015-11-17 18:23:35
我有这样的桌子：
date|status|value日期为日期，待决状态为1，确认为2，值为订单值。
我想要三栏：
date|#status pending|#status pending+confirmed数据示例：
+------------+-----------------+-----------------+
| date | status | value |
+------------+-----------------+-----------------+
| 2015-11-17 | 1 | 89|
| 2015-11-16 | 1 | 6 |
| 2015-11-16 | 2 | 16 |
| 2015-11-16 | 2 | 26 |
| 2015-11-15 | 2 | 26 |
| 2015-11-14 | 2 | 24 |
+------------+-----------------+-----------------+我想要的例子：
+------------+-----------------+-----------------+
| date | confirmed |confirmed+pending|
+------------+-----------------+-----------------+
| 2015-11-17 | 0 | 1 |
| 2015-11-16 | 2 | 3 |
| 2015-11-15 | 1 | 1 |
| 2015-11-14 | 1 | 1 |
+------------+-----------------+-----------------+我想做的是：
SELECT array1.DATE
,array1.confirmed
,array2.total
FROM (
SELECT DATE (DATE) AS DATE
,count(value) AS confirmed
FROM Orders
WHERE STATUS = '2'
GROUP BY DATE (DATE) DESC limit 5
) AS array1
INNER JOIN (
SELECT DATE (DATE) AS DATE
,count(value) AS total
FROM Orders
GROUP BY DATE (DATE) DESC limit 5
) AS array2但我每次得到4个结果与重复确认的价值和不同的总交易。
如果我试着分开，我可以得到两个正确的信息：
只列出过去5天的已确认订单：
SELECT array1.DATE
,array1.confirmed
,array2.total
FROM (
SELECT DATE (DATE) AS DATE
,count(valor) AS confirmed
FROM Orders
WHERE STATUS = '2'
GROUP BY DATE (DATE) DESC limit 5;
)将列出过去5天内所有订单的总数：
SELECT DATE (DATE) AS DATE
,count(valor) AS total
FROM Orders
GROUP BY DATE (DATE) DESC limit 5我注意到至少有一个大问题：
有时我们会有一天的许多未确认的订单和零确认，所以可能内部连接将失败。
回答 3
Stack Overflow用户
回答已采纳
发布于 2015-11-17 18:37:31
您可以使用CASE WHEN，以获得预期的输出，您已经给出。
SELECT `date`,
(SUM(CASE WHEN `status`=1 THEN 1 ELSE 0 END)) AS Confirmed,
(SUM(CASE WHEN `status`=1 OR `status`=2 THEN 1 ELSE 0 END)) AS Confirmed_Pending
FROM
table_name
GROUP BY DATE(`date`) DESC 希望这能有所帮助。
Stack Overflow用户
发布于 2015-11-17 18:35:25
您在ON中缺少了一个INNER JOIN子句。或者，因为在您的例子中，您加入的列在两边是相同的，所以可以使用USING。
SELECT array1.DATE
,array1.confirmed
,array2.total
FROM (
SELECT DATE (DATE) AS DATE
,count(value) AS confirmed
FROM Orders
WHERE STATUS = '2'
GROUP BY DATE (DATE) DESC limit 5
) AS array1
INNER JOIN (
SELECT DATE (DATE) AS DATE
,count(value) AS total
FROM Orders
GROUP BY DATE (DATE) DESC limit 5
) AS array2
USING (DATE)Stack Overflow用户
发布于 2015-11-17 18:39:44
一种更简单的方法可以是使用case表达式来计算状态是否需要计数，并将count函数应用于此：
SELECT DATE (`date`) AS `date`,
COUNT(CASE status WHEN 2 THEN 1 END) AS `confirmed`,
COUNT(CASE WHEN status IN (1, 2) THEN 1 END) AS `pending and confirmed`,
FROM orders
GROUP BY DATE (`date`) DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33764297
复制相关文章
相似问题
腾讯云开发者
