请教一个SQL查询问题
请教一个SQL查询问题,查询table_2,table_3,在table_1中user出现的数量
table_1
------------------------
id user
1 A
2 B
3 C
4 D
table_2
-------------------------
id user_work user_game
1 A A
2 A A
3 A B
4 B C
5 C C
table_3
-------------------------
id user_other
1 A
2 B
3 C
4 B
5 C
-----------------------------------------
需要的结果：
table_4
--------------------------
user user_work user_game user_other
A 3 2 1
B 1 1 2
C 1 2 2
D 0 0 0
[解决办法]
declare @a table(id int, [user] varchar(10))
insert @a select 1 , 'A '
union all select 2 , 'B '
union all select 3, 'C '
union all select 4, 'D '
declare @b table(ID INT,user_work varchar(100),user_game varchar(10))
insert @b select 1, 'A ', 'A '
union all select 2, 'A ', 'A '
union all select 3, 'A ', 'B '
union all select 4, 'B ', 'C '
union all select 5, 'C ', 'C '
DECLARE @C TABLE(id INT, user_other VARCHAR(10))
INSERT @C SELECT 1 , 'A '
union all select 2 , 'B '
union all select 3 , 'C '
union all select 4 , 'B '
union all select 5 , 'C '
SELECT ID,USER_WORK=(SELECT COUNT(1) FROM @B WHERE USER_WORK=A.[USER]),
USER_GAME=(SELECT COUNT(1) FROM @B WHERE USER_GAME=A.[USER]),
USER_OTHER=(SELECT COUNT(1) FROM @C WHERE USER_OTHER=A.[USER])
FROM @A A
