如何获得各个项目

问题描述：

的计数，我有以下2个表分别命名为ROOM和会话。如何获得各个项目

ROOM 

BUILDINGNO | ROOMNO | ROOMCAPACITY 
-----------+--------+------------- 
B1   | R1  | 121 
B1   | R2  | 253 
B2   | R1  | 275 
B2   | R2  | 126 
B2   | R3  | 124 
B3   | R1  | 256  
B3   | R2  | 324 
B4   | R1  | 431 
B4   | R2  | 345 
B4   | R3  | 231 


SESSION 

SESSIONID | BUILDINGNO | ROOMNO | SPEAKERID 
----------+------------+--------+---------- 
SS01  | B1   | R1  | S1 
SS02  | B2   | R1  | S2 
SS03  | B1   | R2  | S2 
SS04  | B4   | R2  | S4 
SS05  | B3   | R2  | S5 
SS06  | B3   | R1  | S5 
SS07  | B4   | R2  | S3 
SS08  | B1   | R2  | S2 
SS09  | B2   | R3  | S4 
SS10  | B4   | R1  | S3

我希望能够显示位于每个房间的会话数量。理想情况下，会显示一个这样的表格。

BUILDINGNO | ROOMNO | NUMBEROFSESSIONS 
-----------+--------+----------------- 
B1   | R1  | 1 
B1   | R2  | 2 
B2   | R1  | 1 
B2   | R2  | 0 
B2   | R3  | 1 
B3   | R1  | 1 
B3   | R2  | 1 
B4   | R1  | 1 
B4   | R2  | 2 
B4   | R3  | 0

我知道我需要在某处使用COUNT和JOIN函数......但我至少有点卡住了。如果任何人都可以点我在正确的方向或解释这对我来说将不胜感激:)

SELECT BUILDINGNUMBER, ROOMNO FROM ROOM ... COUNT? ... JOIN?

还有什么可以让我显示/隐藏行，其中会话= 0的号码吗？

你的表数据不被标准化。 –

对不起，我或许应该补充约束ROOM_PK PRIMARY KEY（BUILDINGNO，ROOMNO） – james

@詹姆斯那不是正常化。 – Ravi

答

如果你只在每个房间内的会话数有兴趣的话，我相信你有足够的通知在SESSION表中。

SELECT BUILDINGNO, ROOMNO, COUNT(*)FROM SESSION 
GROUP BY BUILDINGNO, ROOMNO;

Here is a sql fiddle:

答

你可以使用count（不同的SessionID）

SELECT ROOM.BUILDINGNO , ROOM.ROOMNO, count(distinct SESSION.sessionid) 
from ROOM 
left join SESSION on ROOM.ROOMNO = SESSION.ROOMNO 
    and ROOM.BUILDINGNO = SESSION.BUILDINGNO 
group by ROOM.BUILDINGNUMBER, ROOM.ROOMNO

和隐藏的房间与resul = 0，你可以使用具有

SELECT ROOM.BUILDINGNO , ROOM.ROOMNO, count(distinct SESSION.sessionid) 
from ROOM 
left join SESSION on ROOM.ROOMNO = SESSION.ROOMNO 
    and ROOM.BUILDINGNO = SESSION.BUILDINGNO 
group by ROOM.BUILDINGNUMBER, ROOM.ROOMNO 
HAVING count(distinct SESSION.sessionid) >0

，但你必须在表中的会话足够的信息

SELECT SESSION.BUILDINGNO , SESSION.ROOMNO, count(distinct SESSION.sessionid) 
SESSION 
group by SESSION.BUILDINGNO , SESSION.ROOMNO 
HAVING count(distinct SESSION.sessionid) >0

'SELECT ROOM.BUILDINGNO，ROOM.ROOMNO COUNT（DISTINCT SESSION.SESSIONID）从室温LEFT加入会话 ON ROOM.ROOMNO = SESSION.ROOMNO AND ROOM.BUILDINGNO = SESSION.BUILDINGNO GROUP BY ROOM.BUILDINGNO，ROOM .ROOMNO;' 谢谢！ :)这给了我想要的。我将如何隐藏计数为0的行？ – james

更好地解释你对此有何评论..是不懂 – scaisEdge

可以使用具有..答案更新 – scaisEdge

答

SELECT r.BUILDINGNO, r.ROOMNO, count(1) QQ 
FROM ROOM r, SESS s 
WHERE 
r.ROOMNO = s.ROOMNO 
AND r.BUILDINGNO = s.BUILDINGNO 
GROUP BY r.BUILDINGNO, r.ROOMNO 
ORDER by r.BUILDINGNO;

[DEMO] [1] http://sqlfiddle.com/#!4/eb290/6

如何获得各个项目

相关推荐