这是网友的问题,我当时立马给出了自己的解决方案,但是没有想到中间有点小问题,发现后经过自己仔细调试,完全得到正确结果后,那个网友已经结帖了。我的代码遂成为鸡肋,食之无味,弃之可惜。但是我觉得我的代码确实还是挺经典的,所以整理了一下,供各位网友欣赏。
问题:
假设环境如下:
表 1 : ID , NAME , QQ , PHONE ,
表中数据: 1 秦云 10102800 13500000
2 在路上 10378 13600000
3 LEO 10000 13900000
表 2 : ID , NAME , 上机时间,管理员,
表中数据: 1 秦云 2004-1-1 李大伟
2 秦云 2005-1-1 马化腾
3 在路上 2005-1-1 马化腾
4 秦云 2005-1-1 李大伟
5 在路上 2005-1-1 李大伟
实现目的:从表 1 中取所有人员列表,从表 2 中取上机次数和管理员 .
上机人员名单 上机次数 管理员(上这几次机的每个管理员都列出来)
秦云 3 李大伟,马化腾,李大伟
在路上 2 马化腾,李大伟
LEO 0
如果不算管理员那一列的话 , 我是这样写的。
SELECT 表 1.NAME AS 姓名 , COUNT( 表 2.ID) AS 上机次数
FROM 表 1 LEFT OUTER JOIN
表 2 ON 表 1.NAME = 表 2.NAME
GROUP BY 表 1. 名称
解答:
** 测试用例 **
create table 表 1( --drop table 表 1
ID int,
NAME varchar(10),
QQ varchar(10),
PHONE varchar(20)
)
insert into 表 1 values(1 ,' 秦云 ' ,'10102800' ,'13500000')
insert into 表 1 values(2 ,' 在路上 ' ,'10378' ,'13600000')
insert into 表 1 values(3 ,'LEO' ,'10000' ,'13900000')
create table 表 2( --drop table 表 2
ID int,
NAME varchar(10) ,
上机时间 datetime,
管理员 varchar(10)
)
insert into 表 2 values(1,' 秦云 ' ,cast(' 2004-1-1 ' as datetime),' 李大伟 ')
insert into 表 2 values(2,' 秦云 ' ,cast(' 2005-1-1 ' as datetime),' 马化腾 ')
insert into 表 2 values (3,' 在路上 ' ,cast(' 2005-1-1 ' as datetime),' 马化腾 ')
insert into 表 2 values(4,' 秦云 ' ,cast(' 2005-1-1 ' as datetime),' 李大伟 ')
insert into 表 2 values(5,' 在路上 ' ,cast(' 2005-1-1 ' as datetime),' 李大伟 ')
** 程序部分 **
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 管理员 from 表 2 where 表 2.NAME = @name )
open myCur
fetch next from myCur into @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 into @tempStr
end
close myCur
deallocate myCur
return @nameStr
end
select 表 2.NAME as 姓名 , count(ID) as 上机次数 , dbo.GetNameStr( 表 2.NAME) as 管理员
from 表 2
where 表 2.NAME in ( select 表 1.NAME from 表 1 )
group by 表 2.NAME
** 测试结果: **
姓名 上机次数 管理员
--------------------------------------------------------------
秦云 3 李大伟 , 马化腾 , 李大伟
在路上 2 马化腾 , 李大伟