Select A.UID, Count(B.UID) As 登陆次数 From 用户表 A Inner Join 表2 B On A.UID = B.UID Group By A.UID
如果還要查詢Name之類的信息,在後面加,eg:Select A.UID, A.Name Count(B.UID) As 登陆次数 From 用户表 A Inner Join 表2 B On A.UID = B.UID Group By A.UID, A.Name
Create Table 用户表 (UID Int, Name Varchar(10), Address Varchar(50)) Insert 用户表 Select 1, 'AA', 'AAAAA' Union All Select 2, 'BB', 'BBBBB' Union All Select 3, 'CC', 'CCCCC'Create Table 表2 (UID Int, AddDate DateTime) Insert 表2 Select 1, '2007-01-03' Union All Select 1, '2007-01-08' Union All Select 2, '2007-01-06' Union All Select 3, '2007-01-03' Union All Select 3, '2007-02-03' Union All Select 3, '2007-03-03' GO Select A.UID, A.Name, A.Address, Count(B.UID) As 登陆次数 From 用户表 A Inner Join 表2 B On A.UID = B.UID Group By A.UID, A.Name, A.Address GO Drop Table 用户表, 表2 --Result /* UID Name Address 登陆次数 1 AA AAAAA 2 2 BB BBBBB 1 3 CC CCCCC 3 */
Select B.UID, Count(A.UID) As 登陆次数 From 表2 A left Join 表1 B On A.UID = B.UID Group By A.UID
A.UID,
Count(B.UID) As 登陆次数
From
用户表 A
Inner Join
表2 B
On A.UID = B.UID
Group By A.UID
A.UID,
A.Name
Count(B.UID) As 登陆次数
From
用户表 A
Inner Join
表2 B
On A.UID = B.UID
Group By A.UID, A.Name
(UID Int,
Name Varchar(10),
Address Varchar(50))
Insert 用户表 Select 1, 'AA', 'AAAAA'
Union All Select 2, 'BB', 'BBBBB'
Union All Select 3, 'CC', 'CCCCC'Create Table 表2
(UID Int,
AddDate DateTime)
Insert 表2 Select 1, '2007-01-03'
Union All Select 1, '2007-01-08'
Union All Select 2, '2007-01-06'
Union All Select 3, '2007-01-03'
Union All Select 3, '2007-02-03'
Union All Select 3, '2007-03-03'
GO
Select
A.UID,
A.Name,
A.Address,
Count(B.UID) As 登陆次数
From
用户表 A
Inner Join
表2 B
On A.UID = B.UID
Group By A.UID, A.Name, A.Address
GO
Drop Table 用户表, 表2
--Result
/*
UID Name Address 登陆次数
1 AA AAAAA 2
2 BB BBBBB 1
3 CC CCCCC 3
*/
B.UID,
Count(A.UID) As 登陆次数
From
表2 A
left Join
表1 B
On A.UID = B.UID
Group By A.UID