MySQL 一条语句中一张表的多个结果

Question

我尝试在两个表中使用多重选择查询:

表1 -> 信息选项卡:

  theType   |          theTime    | theCount 
------------+---------------------+----------
 WithPinIt  | 2017-04-01 13:00:00 | 45
 WithPinIt  | 2017-04-01 13:00:00 | 100
 WithMinIt  | 2017-04-01 13:00:00 | 75
 WithQinIt  | 2017-05-01 13:00:00 | 45
 WithMinIt  | 2017-06-01 13:00:00 | 55

表2 -> defsTab:

   theId  |   theDefs   |   theType
----------+-------------+-------------
1         |  defQs      |  WithQinIt 
1         |  defOs      |  WithOinIt 
1         |  defJs      |  WithJinIt 

我想要得到的是这样的结果:

          theTime    |  P  |  M 
---------------------+-----+-----
2017-04-01 13:00:00  | 145 | 
2017-04-01 13:00:00  |     | 75 
2017-06-01 13:00:00  |     | 55

到目前为止我使用了这个查询:

SELECT   
    theTime,   
    (SELECT SUM(theCount) AS pSum FROM infoTab WHERE theType NOT IN (SELECT content FROM defsTab) AND theType LIKE '%P%' GROUP BY theTime) AS Ps,   
    (SELECT SUM(theCount) AS cSum FROM infoTab WHERE theType NOT IN (SELECT content FROM defsTab) AND theType LIKE '%C%' GROUP BY theTime) AS Cs   
FROM infoTab 

但它不起作用......有什么帮助吗？

Answer 1

如果你使用 CASE 应该可以工作

select
theTime,
sum(
CASE 
WHEN theType like '%P%' THEN theCount
ELSE 0
END) as P,
sum(
CASE 
WHEN theType like '%C%' THEN theCount
ELSE 0
END) as C
from infoTab
where theType NOT IN (SELECT content FROM defsTab)
group by theTime;