组合MySQL查询

Question

我正在努力寻找组合这两个查询的方法……我尝试过子查询、连接、联合，但都没有成功:(

查询A

SELECT DATE(crtdtime) AS cntldate, sd_class, COUNT(crtdtime) AS created
FROM master
WHERE DATE(crtdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

它会产生

+------------+----------+---------+
| cntldate   | sd_class | created |
+------------+----------+---------+
| 2011-11-16 | CUST     |    2226 |
| 2011-11-16 | NET      |     238 |
+------------+----------+---------+

查询B

SELECT DATE(rstdtime) AS cntldate, sd_class, COUNT(rstdtime) AS restored
FROM master
WHERE DATE(rstdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

它会产生

+------------+----------+----------+
| cntldate   | sd_class | restored |
+------------+----------+----------+
| 2011-11-16 | CUST     |     2315 |
| 2011-11-16 | NET      |      221 |
+------------+----------+----------+

但希望最终结果是..。

+------------+----------+---------+----------+
| cntldate   | sd_class | created | restored |
+------------+----------+---------+----------+
| 2011-11-16 | CUST     |    2226 |     2315 |
| 2011-11-16 | NET      |     238 |      221 |
+------------+----------+---------+----------+

会非常感谢任何人的帮助。

谢谢。

Answer 1

使用

SELECT C.cntldate, C.sd_class, C.created, R.restored
FROM
(
SELECT DATE(crtdtime) AS cntldate, sd_class, COUNT(crtdtime) AS created
FROM master
GROUP BY cntldate, sd_class
) C
INNER JOIN
(
SELECT DATE(rstdtime) AS cntldate, sd_class, COUNT(rstdtime) AS restored
FROM master
GROUP BY cntldate, sd_class
) R
ON R.cntldate = C.cntldate AND R.sd_class = C.sd_class
WHERE C.cntldate = '2011-11-16' AND R.cntldate = '2011-11-16'

Answer 2

一种可能的方法

SELECT DATE(crtdtime) AS cntldate, 
  sd_class, 
  sum( DATE(crtdtime) = '2011-11-16') AS created
  sum( DATE(rstdtime) = '2011-11-16') AS restored
FROM master
WHERE DATE(crtdtime) = '2011-11-16' or DATE(rstdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

Answer 3

怎么样

select q1.cntldate, q1.sd_class, q1.created, q2.restored 
   from  (
   SELECT DATE(crtdtime) AS cntldate, sd_class, COUNT(crtdtime) AS created
      FROM master
      WHERE DATE(crtdtime) = '2011-11-16'
      GROUP BY cntldate, sd_class
   )  q1
   inner join (
      SELECT DATE(rstdtime) AS cntldate, sd_class, COUNT(rstdtime) AS restored
         FROM master
         WHERE DATE(rstdtime) = '2011-11-16'
         GROUP BY cntldate, sd_class
  ) q2
      on q1.cntldate=q2.cntldate and q1.sdclass = q2.sdclass

不过，这是Postgres语法，所以可能需要使用它才能与MySql一起运行