如何在一个表中的单个查询上执行多个子查询？

debugcn 发表于 Dev

弗朗西斯科·伊格纳西奥·霍约斯·阿米霍

我有两个表：BUILDING和APARTMENT。

建筑物具有ID_BUILDING，BUILDING_NAME（这仅是此问题的关键）

公寓有ID_BUILDING，N_APARTMENTS，TOTAL_ROOMS。

我需要做这样的查询：

| BUILDING NAME | TOTAL APARTMENTS | TOTAL APARTMENTS WITH 1 BEDROOM | TOTAL APARTMENTS WITH 2 BEDROOMS |
|---------------|------------------|---------------------------------|----------------------------------|
| BUILDING A    | 31               | 8                               | 0                                 
|_______________________________________________________________________________________________________|
| BUILDING B    | 20               | 14                              | 11                               
|________________________________________________________________________________________________________
| BUILDING C    | 41               | 90                              | 5                                
|________________________________________________________________________________________________________

卧室的数量可以在1到5之间。

为此，我附带了以下查询：

SELECT E.BUILDING_NAME as "name", COUNT(D.N_APARTMENTS) "TOTAL APARTMENTS", (SELECT COUNT(D1.TOTAL_ROOMS) FROM APARTMENT D1 WHERE D1.TOTAL_ROOMS = 1)
FROM BUILDING E
JOIN APARTMENT D 
ON E.ID_BUILDING = D.ID_BUILDING    
GROUP BY E.E.BUILDING_NAME
ORDER BY E.BUILDING_NAME;

不幸的是，这包括所有带1个房间的公寓，不包括BUILDING_NAME：

| BUILDING NAME | TOTAL APARTMENTS | TOTAL APARTMENTS WITH 1 BEDROOM |
|---------------|------------------|---------------------------------|
| BUILDING A    | 31               | 122                             |
| BUILDING B    | 20               | 122                             |
| BUILDING C    | 41               | 122                             |

我确实在这里和这里尝试了答案，但是它们并不是完全相同的问题。

我认为解决方案可能是使用多个联接（或内部联接），但是我找不到正确的答案。

提前致谢。

卢卡斯·索兹达（Lukasz Szozda）

使用条件聚合：

SELECT E.BUILDING_NAME as "name", 
 COUNT(D.N_APARTMENTS) "TOTAL APARTMENTS", 
 SUM(CASE WHEN D.TOTAL_ROOMS = 1 THEN 1 ELSE 0 END) AS "TOTAL APARTMENTS WITH 1 BEDROOM"
FROM BUILDING E
JOIN APARTMENT D 
ON E.ID_BUILDING = D.ID_BUILDING    
GROUP BY E.E.BUILDING_NAME
ORDER BY E.BUILDING_NAME;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。