mysql - JOIN SQL 查询结果为一个

Question

我想在一个查询中进行多个选择，我的查询在没有最后一个 SELECT 的情况下工作，但是我有一个错误，即 Operand should contain 1 column有人可以告诉我如何纠正吗？

---

SELECT (
 SELECT COUNT(*) FROM mytable WHERE TYPE = 'MSS' AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) 
) as TOTAL_MSS,
( 
SELECT COUNT(*) FROM mytable WHERE TYPE = 'MSS' AND S = 0 AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())
) as MSS_S,
(
 SELECT COUNT(*) FROM mytable WHERE TYPE = 'MSS' AND S != 0 AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())
) as MSS_F,
(
SELECT COUNT(*) as Nb ,OR as TOP FROM mytable WHERE TYPE = 'MSS'  AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) GROUP BY OR ORDER BY Nb DESC LIMIT 1
)

---

如果我分别执行它们，我会得到

TOTAL_MSS   MSS_S   MSS_F
99          12      87

---

Nb  TOP
78  j.r@domain.com

Answer 1

试试这个查询

SELECT 
   * 
FROM 
(SELECT 
   1 as rId,
   count(*) as TOTAL_MSS,
   sum(if(S = 0, 1, 0)) as MSS_S,
   sum(if(S <> 0, 1, 0)) as MSS_F
FROM 
   mytable
WHERE
   TYPE = 'MSS' AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())) a
INNER JOIN
   (SELECT 
       1 as rId,
       COUNT(*) as Nb, 
       OR as TOP 
   FROM 
       mytable 
   WHERE 
       TYPE = 'MSS'  AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) 
   GROUP BY 
       OR 
   ORDER BY 
       Nb DESC LIMIT 1)b
 ON 
   a.rID = b.rID;

希望这有助于...