Title SQL Server Classic Cases of Custom Functions and Cursor Applications Select from Goodlines BLOG Keyword SQL Server Classic Cases of Custom Functions and Cursor Applications This is a netizen. I immediately gave my own solution. But I didn't think of a little problem in the middle. After discovered it, I was carefully debugged. After I get the correct result, the netizen has already filed. 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. Question: 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, Passenger, Administrator, Table China 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 achieved the purpose : Take all the list of people from Table 1, take the number of times and the number of people from Table 2. Have the number of people lists the number of administrators (listed on 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 not, I don't think about the manager, I wrote this.
SELECT table 1.Name As Name, Count (Table 2.id) AS number of times from the number of people 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 ',' 1000 ',' 13900000 ') Create Table Table 2 (--Drop Table Table 2 ID INT, Name Varchar (10), Plus Time 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), 'Mahuateng') Insert Into Table 2 Values (3, 'On the Road", Cast (' 2005 -1-1 'as datetime,' Mahuateng ') 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 of 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 Else Begin Set @namestr = @NameStr ',' @Tempstr End Set @flag = @flag
