========================
表 A:用户表USER_ID USER_NAME
1 David
2 Tommy
3 Mike
4 Mary========================
表 B:用户登录记录ITEM_ID USER_ID LOGIN_DATETIME
1 1 略
2 3 略
3 1 略
4 1 略
5 1 略
6 2 略
7 3 略
8 1 略
9 1 略========================我想要的sql是,可以查出这样的表C:========================
表 C:用户登录次数统计USER_ID USER_NAME USER_LOGIN_COUNT
1 David 6 // 在表B中,David有6行记录
2 Tommy 1
3 Mike 2
4 Mary 0 // 在表B中,没有Mary的记录。请指教,谢谢~!
表 A:用户表USER_ID USER_NAME
1 David
2 Tommy
3 Mike
4 Mary========================
表 B:用户登录记录ITEM_ID USER_ID LOGIN_DATETIME
1 1 略
2 3 略
3 1 略
4 1 略
5 1 略
6 2 略
7 3 略
8 1 略
9 1 略========================我想要的sql是,可以查出这样的表C:========================
表 C:用户登录次数统计USER_ID USER_NAME USER_LOGIN_COUNT
1 David 6 // 在表B中,David有6行记录
2 Tommy 1
3 Mike 2
4 Mary 0 // 在表B中,没有Mary的记录。请指教,谢谢~!
group by user_id,user_nameor select user_id,user_name,(select count(1) from b where user_id = a.user_id) from a
group by A.USER_ID,A.USER_NAME
FROM A LEFT JOIN B ON A.USER_ID=B.USER_ID
group by a.user_id,a.user_name
(select USER_ID , count(1) cnt from b group by USER_ID) t
on a.USER_ID = t.USER_ID order by a.USER_ID
FROM A LEFT JOIN B ON A.[USER_ID]=B.[USER_ID]GROUP BY A.[USER_ID],A.[USER_NAME]
select a.user_id,user_name,count(1) from a left join b where a.user_id = b.user_id
group by a.user_id,user_name
select user_id,user_name,count(1) from a left join b where a.user_id = b.user_id
group by a.user_id,a.user_name
insert into a values(1 , 'David')
insert into a values(2 , 'Tommy')
insert into a values(3 , 'Mike')
insert into a values(4 , 'Mary')
create table B(ITEM_ID int, [USER_ID] int, LOGIN_DATETIME varchar(10))
insert into b values(1 , 1 , '略')
insert into b values(2 , 3 , '略')
insert into b values(3 , 1 , '略')
insert into b values(4 , 1 , '略')
insert into b values(5 , 1 , '略')
insert into b values(6 , 2 , '略')
insert into b values(7 , 3 , '略')
insert into b values(8 , 1 , '略')
insert into b values(9 , 1 , '略')
go--方法一
select a.* , isnull(t.cnt,0) USER_LOGIN_COUNT from a left join
(select [USER_ID] , count(1) cnt from b group by [USER_ID]) t
on a.[USER_ID] = t.[USER_ID] order by a.[USER_ID]--方法二
select a.* , isnull((select count(1) from b where b.[USER_ID] = a.[USER_ID]),0) USER_LOGIN_COUNT from a order by a.[USER_ID]drop table a , b /*
USER_ID USER_NAME USER_LOGIN_COUNT
----------- ---------- ----------------
1 David 6
2 Tommy 1
3 Mike 2
4 Mary 0(所影响的行数为 4 行)
*/
FROM A LEFT JOIN B ON A.USER_ID=B.USER_ID