This is a question of netizens. I immediately gave my own solution, but I didn't expect the middle a little problem in the middle. After discovering it, after careful debugging, the netizen has already filed the post. My code becomes a chicken rib, the taste of food, and unfortunately. But I think my code is still quite a classic, so I have sorted it, and I have been appreciated by friends.
problem:
Suppose the environment is as follows:
Table 1: ID, Name, QQ, Phone,
Table data: 1 Qin Yun 10102800 13500000
2 on the road 10378 13600000
3 Leo 10000 13900000
Table 2: ID, Name, time, administrator,
Table data: 1 Qin Yun
2004-1-1
Li Dawei
2 Qin Yun
2005-1-1
Ma Huateng
3 on the road
2005-1-1
Ma Huateng
4 Qin Yun
2005-1-1
Li Dawei
5 on the road
2005-1-1
Li Dawei
DETAFT> From Table 1, take the list, take the number of times and administrators from Table 2.
Personnel listing number of personnel (listed in each administrator of these machines)
Qin Yun 3 Li Dawei, Ma Huateng, Li Dawei
On the road 2 Ma Huateng, Li Dawei
Leo 0
If you don't count the list of administrators, I wrote this.
SELECT table 1.Name as name, count (Table 2.ID) AS number of times
From table 1 Left outer Join
Table 2 ON table 1.name = Table 2.Name
Group by Table 1. Name
answer:
Test case
CREATE TABLE Table 1 (--Drop Table Table 1
ID INT,
Name varchar (10),
QQ varchar (10),
Phone varchar (20)
)
Insert INTO Table 1 Values (1, 'Qin Yun', '10102800', '13500000')
INSERT INTO Table 1 Values (2, 'On the Road ",' 10378 ',' 13600000 ')
INSERT INTO Table 1 Values (3, 'Leo', '10000', '13900000')
CREATE TABLE Table 2 (--Drop Table Table 2
ID INT,
Name varchar (10),
DateTime,
Administrator VARCHAR (10)
)
INSERT INTO Table 2 Values (1, 'Qin Yun ", Cast ('
2004-1-1
'as datetime,' Li Dawei ')
INSERT INTO Table 2 Values (2, 'Qin Yun ", CAST ('
2005-1-1
'as datetime),' Ma Huateng ')
INSERT INTO Table 2 Values (3, 'On the Road ", CAST ('
2005-1-1
'as datetime),' Ma Huateng ')
INSERT INTO Table 2 Values (4, 'Qin Yun ", Cast ('
2005-1-1
'as datetime,' Li Dawei ')
Insert Into Table 2 Values (5, 'on the road ", Cast ('
2005-1-1
'as datetime,' Li Dawei ')
Program part
Create function getNameStr (@name nvarchar (10))
Returns nvarchar (800)
AS
Begin
Declare @namestr nvarchar (800)
Declare @tempstr nvarchar (800)
Declare @flag int
Declare Mycur Cursor for (SELECT Administrator from Table 2 Where Table 2.Name = @Name)
Open mycur
Fetch next from mycur @Tempstr
Set @flag = 0
While @@ fetch_status = 0
Begin
IF @flag = 0
Begin
Set @namestr = @Tempstr
end
Else
Begin
Set @namestr = @NameStr ',' @Tempstr
end
Set @flag = @flag 1
Fetch next from mycur @Tempstr
end
Close mycur
Deallocate mycur
Return @namestr
end
SELECT Table 2.Name As Name, Count (ID) AS The number of times, DBo.getNameStr (Table 2.Name) AS Administrator
From Table 2
WHERE Table 2.Name in (SELECT Table 1.Name from Table 1)
Group by Table 2.Name
Test Results:
Name number
-------------------------------------------------- ----------------
Qin Yun 3 Li Dawei, Ma Huateng, Li Dawei
On the road 2 Ma Huateng, Li Dawei
