临时表的替代方案

Question

我需要的是（通过临时表或视图获得的）这个：

numcasos  valcount1  valcount2  
--------  ---------  -----------
       0          2            1
       1          1            2
       2     (NULL)       (NULL)
       3     (NULL)            1
       4          1       (NULL)
       5     (NULL)       (NULL)
       6     (NULL)       (NULL)
       7     (NULL)       (NULL)
       8     (NULL)       (NULL)
       9     (NULL)       (NULL)
      10     (NULL)       (NULL)

这是一个示例，因为我需要它来处理300+个valcounts。MySQL:

CREATE TEMPORARY TABLE sumnum1
SELECT numos.num1 AS num1,
 COUNT(*)   AS valcount1      
FROM `numos` 
GROUP BY numos.num1  ; 

CREATE TEMPORARY TABLE sumnum2
SELECT numos.num2 AS num2,
 COUNT(*)   AS valcount2      
FROM `numos` 
GROUP BY numos.num2  ; 



SELECT casos.`numcasos` AS numcasos, 
sumnum1.valcount1 AS valcount1,
sumnum2.valcount2 AS valcount2

FROM ( casos
LEFT JOIN sumnum1
ON (casos.`numcasos`= sumnum1.num1)
LEFT JOIN sumnum2
ON (casos.numcasos = sumnum2.num2))

我希望能够使用子查询得到与此相同的结果，但是我收到了如下错误信息：

Subquery returns more than 1 row

在MySQL中是否可能实现？

- oportatil

---

这是可能的。公布失败的查询也是有意义的。 - full.stack.ex

Answer 1

尝试类似以下的方法：

SELECT casos.numcasos AS numcasos, 
sumnum1.valcount1 AS valcount1,
sumnum2.valcount2 AS valcount2
FROM casos
LEFT JOIN
(
    SELECT numos.num1 AS num1, COUNT(*) AS valcount1
    FROM numos GROUP BY numos.num1 
)sumnum1  ON (casos.`numcasos`= sumnum1.num1)
LEFT JOIN 
(
   SELECT numos.num2 AS num2, COUNT(*) AS valcount2
   FROM numos GROUP BY numos.num2  
)sumnum2 ON (casos.numcasos = sumnum2.num2))

- Marc

---

这正是我所需要的，运行得非常完美。@Marc，抱歉，我没有足够的声望来投票支持你的答案。 - oportatil