Table a
Id room
1 room a
2 rooms b
3 room c
Table b
Id id room host name of the host state
1 1 host a 1
2 host b 1
3 1 host c 2
4 2 host d 0
5 2 host e 1
How to write SQL statements can see host summary status of each room how many respectively?
Room state 0 1 2
Room a 0 2 1
Room 1 1 0 b
Computer room c 0 0 0
CodePudding user response:
The CREATE TABLE # A
(
ID INT,
Computer room VARCHAR (20)
)
The CREATE TABLE # B
(
ID INT,
Room ID INT,
Host name VARCHAR (20),
The host state INT
)
# INSERT INTO A VALUES (1, A ' 'room)
# INSERT INTO A VALUES (2, 'room B)
# INSERT INTO A VALUES (3, 'room C)
INSERT INTO # B VALUES (1, 1, 'host A', 1)
INSERT INTO # B VALUES (2, 1, 'host B, 1)
INSERT INTO # B VALUES (3, 1, 'host C, 2)
INSERT INTO # B VALUES (4, 2, 'host D, 0)
INSERT INTO # B VALUES (5, 2, 'host E', 1)
SELECT * FROM (
Computer room, SELECT a. b. host name, b. host state FROM # ON Anderson, A LEFT JOIN # B B D=b. room ID) X
The PIVOT
(
COUNT (host name) FOR the host state IN ([0], [1], [2])
) XX
DROP TABLE # A, # B