多选与MySQL中的同一张表不起作用

问题描述：

我遇到了数据库的多选问题。我需要做一个选择，返回一些包含一些处理过的数据的表格，并且它需要在月份的某一天进行排序。要做到这一点，我使用多选择的问题的MySQL。这是我的代码：多选与MySQL中的同一张表不起作用

SELECT 
    (SELECT COUNT(*) FROM `table` WHERE `type`=1                    AS 'Total', 
    (SELECT COUNT(*) FROM `table` WHERE `type`=1 and `status` = 0 and `status_cancel` = 0          AS 'Open', 
    (SELECT COUNT(*) FROM `table` WHERE `type`=1 and `status_cancel` = 1              AS 'Cancel', 
    (SELECT COUNT(*) FROM `table` WHERE `type`=1 and `date_finish` is not null and `status_cancel` = 0       AS 'Finish', 
    (SELECT COUNT(*) FROM `table` WHERE `type`=1 and `result` >= 0 and `date_finish` is not null and `status_cancel` = 0  AS 'Win', 
    (SELECT COUNT(*) FROM `table` WHERE `type`=1 and `result` < 0 and `date_finish` is not null and `status_cancel` = 0   AS 'Loss'

现在，它的总的所有行返回我的表，但我不能这样做的回报是按天分组，帮助我，好吗！

的结果必须是这样的：

Result that i need

更多信息。表格的完整模式是什么？您正在寻找的输出是什么？给出一个示例输出。你的查询应该只返回一行，但看起来你不止一个。你在一个月的日子里是什么意思？ – kaineub

当询问SQL查询问题时，请为查询中引用的所有表运行“SHOW CREATE TABLE”，并将结果包含在您的问题中。这将给我们提供信息，所以我们不必猜测你的列，数据类型，索引等。 –

答

我觉得你说我怎么能按年月日时我的查询都达子查询。我会把它改成这种情况的陈述。尝试这样的事情

SELECT DATE(date_finish) as `date`, count(*) as 'Total', 
     SUM(CASE WHEN `status` = 0 AND `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Open', 
     SUM(CASE WHEN `status_cancel` = 1 THEN 1 ELSE 0 END) as 'Cancel', 
     SUM(CASE WHEN `date_finish` is not null and `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Finish', 
     SUM(CASE WHEN `result` >= 0 AND `date_finish` is not null AND `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Win', 
     SUM(CASE WHEN `result` < 0 AND `date_finish` is not null AND `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Loss' 
from `table` 
WHERE `type` = 1 
group by DATE(date_finish)

TEST：需要http://rextester.com/HCRIU11065

是的男人！非常感谢！那就是我寻找的东西！ –

多选与MySQL中的同一张表不起作用

相关推荐