一个分类统计的问题，有点复杂

Question

--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+','+人员 from 表 where 部门 = @department
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go--输出结果
/*
部门  人员
----  --------------
1     张三,李四,王五
2     赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go

Answer 1

---用函数
create function f(@dt datetime,@fn varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+姓名 from t_就餐记录 where 就餐日期=@dt and 饭店名称=@fn
set @str=stuff(@str,1,1,'')
return @str
endgo 
select 就餐日期,饭店名称,吃饭人员=dbo.f(就餐日期)
from t_就餐记录
group by 就餐日期,饭店名称

Answer 2

create table t_就餐记录(姓名 varchar(20),就餐日期 datetime ,饭店名称 varchar(50) , id int)
insert t_就餐记录
select '张三','2007-01-01','百福楼',  1
union select '李四','2007-01-01','百福楼',  2
union select '张三','2007-05-01','梅园',  3
union select '刘德华','2007-05-01','梅园',  4
union select '刘德华','2007-06-01','幼儿园',  5
union select '张三','2007-06-01','幼儿园',  6
union select '周润发','2007-06-01','幼儿园',  7go ---用函数
create function f(@dt datetime,@fn varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+姓名 from t_就餐记录 where 就餐日期=@dt and 饭店名称=@fn
set @str=stuff(@str,1,1,'')
return @str
endgo 
select 就餐日期,饭店名称,吃饭人员=dbo.f(就餐日期,饭店名称)
from t_就餐记录
group by 就餐日期,饭店名称drop table t_就餐记录
drop function dbo.f/*    结果 
就餐日期                                                   饭店名称                                               吃饭人员                                                                                                                                                                                                                                                             
------------------------------------------------------ -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2007-01-01 00:00:00.000                                百福楼                                                李四,张三
2007-05-01 00:00:00.000                                梅园                                                 刘德华,张三
2007-06-01 00:00:00.000                                幼儿园                                                刘德华,张三,周润发(3 row(s) affected)*/

Answer 3

create table test(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20),id int)
insert into test select '张三 ','2007-01-01','百福楼',1
insert into test select '李四 ','2007-01-01','百福楼',2
insert into test select '张三 ','2007-05-01','梅园  ',3
insert into test select '刘德华','2007-05-01','梅园  ',4
insert into test select '刘德华','2007-06-01','幼儿园',5
insert into test select '张三 ','2007-06-01','幼儿园',6
insert into test select '周润发','2007-06-01','幼儿园',7
goselect
    distinct a.*
from
    test a,test b
where
    a.姓名=b.姓名 and a.饭店名称<>b.饭店名称 and a.就餐日期<>b.就餐日期
    and
    exists(select
               1
           from
               test c
           where
               c.姓名<>a.姓名 and c.饭店名称=a.饭店名称 and 就餐日期=a.就餐日期
               and
               exists(select 
                          1
                      from
                          test
                      where
                          姓名=c.姓名 and 饭店名称=b.饭店名称 and 就餐日期=b.就餐日期))
order by
    a.就餐日期,a.饭店名称,a.姓名
go/*
姓名                   就餐日期           饭店名称             id          
-------------------- -------------------- -------------------- ----------- 
刘德华                  2007-05-01        梅园                 4
张三                   2007-05-01        梅园                 3
刘德华                  2007-06-01        幼儿园               5
张三                   2007-06-01        幼儿园               6
*/drop table test
go

Answer 4

create table t_就餐记录(姓名 varchar(20),就餐日期 datetime ,饭店名称 varchar(50) , id int)
insert t_就餐记录
select '张三','2007-01-01','百福楼',  1
union select '李四','2007-01-01','百福楼',  2
union select '张三','2007-05-01','梅园',  3
union select '刘德华','2007-05-01','梅园',  4
union select '刘德华','2007-06-01','幼儿园',  5
union select '张三','2007-06-01','幼儿园',  6
union select '周润发','2007-06-01','幼儿园',  7go ---用函数
create function f(@dt datetime,@fn varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+姓名 from t_就餐记录 where 就餐日期=@dt and 饭店名称=@fn
set @str=stuff(@str,1,1,'')
return @str
endgo ---借用一下临时表
select 就餐日期,饭店名称,吃饭人员=dbo.f(就餐日期,饭店名称) into #
from t_就餐记录 t
group by 就餐日期,饭店名称select * from # t where (select count(1) from # where charindex(t.吃饭人员,吃饭人员)>0 or charindex(吃饭人员,t.吃饭人员)>0)>=2
drop table #
drop table t_就餐记录
drop function dbo.f/*    结果 
就餐日期       饭店名称   吃饭人员                                                                                                                                                                                                                                                             
----------------------- --------- -----------------
2007-05-01 00:00:00.000 梅园      刘德华,张三
2007-06-01 00:00:00.000 幼儿园    刘德华,张三,周润发(3 row(s) affected)*/

Answer 5

呵呵，大家热情很高，刚才的话是对libin_ftsafe(子陌红尘：TS for Banking Card)朋友说的，to:leo_lesley(leo) ,统计的是一起吃过两次饭以上的人员，不是每次饭局的人数

Answer 6

create table test(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20),id int)
insert into test select '张三 ','2007-01-01','百福楼',1
insert into test select '李四 ','2007-01-01','百福楼',2
insert into test select '张三 ','2007-05-01','梅园  ',3
insert into test select '刘德华','2007-05-01','梅园  ',4
insert into test select '刘德华','2007-06-01','幼儿园',5
insert into test select '张三 ','2007-06-01','幼儿园',6
insert into test select '周润发','2007-06-01','幼儿园',7
gocreate function f_str(@date varchar(20),@restaurant varchar(20))
returns varchar(200)
as
begin
    declare @ret varchar(200)
    set @ret=''
    
    declare @t table(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20))
    
    insert into @t
    select 
        distinct a.姓名,a.就餐日期,a.饭店名称
    from
        test a,test b
    where
        a.姓名=b.姓名 and a.饭店名称<>b.饭店名称 and a.就餐日期<>b.就餐日期
        and
        a.饭店名称=@restaurant and a.就餐日期=@date
        and
        exists(select
                   1
               from
                   test c
               where
                   c.姓名<>a.姓名 and c.饭店名称=a.饭店名称 and 就餐日期=a.就餐日期
                   and
                   exists(select 
                              1
                          from
                              test
                          where
                              姓名=c.姓名 and 饭店名称=b.饭店名称 and 就餐日期=b.就餐日期))
    
    select @ret=@ret+','+姓名 from @t    set @ret=stuff(@ret,1,1,'')    return @ret
end
goselect
    a.就餐日期,a.饭店名称,dbo.f_str(a.就餐日期,a.饭店名称) as 姓名
from
    test a,test b
where
    a.姓名=b.姓名 and a.饭店名称<>b.饭店名称 and a.就餐日期<>b.就餐日期
    and
    exists(select
               1
           from
               test c
           where
               c.姓名<>a.姓名 and c.饭店名称=a.饭店名称 and 就餐日期=a.就餐日期
               and
               exists(select 
                          1
                      from
                          test
                      where
                          姓名=c.姓名 and 饭店名称=b.饭店名称 and 就餐日期=b.就餐日期))
group by
    a.就餐日期,a.饭店名称
go/*
就餐日期             饭店名称             姓名
-------------------- -------------------- --------------------
2007-05-01           梅园                 刘德华,张三
2007-06-01           幼儿园               刘德华,张三
*/drop function f_str
drop table test
go

Answer 7

create table test(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20),id int)
insert into test select '张三 ','2007-01-01','百福楼',1
insert into test select '李四 ','2007-01-01','百福楼',2
insert into test select '张三 ','2007-05-01','梅园  ',3
insert into test select '刘德华','2007-05-01','梅园  ',4
insert into test select '刘德华','2007-06-01','幼儿园',5
insert into test select '张三 ','2007-06-01','幼儿园',6
insert into test select '周润发','2007-06-01','幼儿园',7
gocreate function f_str(@date varchar(20),@restaurant varchar(20))
returns varchar(200)
as
begin
    declare @ret varchar(200)
    set @ret=''
    
    declare @t table(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20))
    
    insert into @t
    select 
        distinct a.姓名,a.就餐日期,a.饭店名称
    from
        test a,test b
    where
        a.姓名=b.姓名 and a.饭店名称<>b.饭店名称 and a.就餐日期<>b.就餐日期
        and
        a.饭店名称=@restaurant and a.就餐日期=@date
        and
        exists(select
                   1
               from
                   test c
               where
                   c.姓名<>a.姓名 and c.饭店名称=a.饭店名称 and 就餐日期=a.就餐日期
                   and
                   exists(select 
                              1
                          from
                              test
                          where
                              姓名=c.姓名 and 饭店名称=b.饭店名称 and 就餐日期=b.就餐日期))
    
    select @ret=@ret+','+姓名 from @t    set @ret=stuff(@ret,1,1,'')    return @ret
end
goselect
    a.就餐日期,a.饭店名称,dbo.f_str(a.就餐日期,a.饭店名称) as 姓名
from
    test a
group by
    a.就餐日期,a.饭店名称
having
    charindex(',',dbo.f_str(a.就餐日期,a.饭店名称))>0  --调用了两次函数，效率也不高
go/*
就餐日期             饭店名称             姓名
-------------------- -------------------- --------------------
2007-05-01           梅园                 刘德华,张三
2007-06-01           幼儿园               刘德华,张三
*/drop function f_str
drop table test
go

Answer 8

---统计的是一起吃过两次饭以上的人员
---借用楼上的数据
create table test(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20),id int)
insert into test select '张三 ','2007-01-01','百福楼',1
insert into test select '李四 ','2007-01-01','百福楼',2
insert into test select '张三 ','2007-05-01','梅园  ',3
insert into test select '刘德华','2007-05-01','梅园  ',4
insert into test select '刘德华','2007-06-01','幼儿园',5
insert into test select '张三 ','2007-06-01','幼儿园',6
insert into test select '周润发','2007-06-01','幼儿园',7
Go---查询统计的是一起吃过两次饭以上的人员姓名
Select Distinct 姓名 From test As A Where Exists
        (Select 1 From test Where 姓名=A.姓名 And id<A.id Group By 姓名,饭店名称) 
---清除测试环境
Drop Table test---结果
/*
姓名                   
-------------------- 
刘德华
张三 （所影响的行数为 2 行）
*/

Answer 9

有了子陌红尘的查询结果， tb_linagci
刘德华                  2007-05-01        梅园                 4
张三                   2007-05-01        梅园                 3
刘德华                  2007-06-01        幼儿园               5
张三                   2007-06-01        幼儿园               6如果根据一个人的姓名，找出跟他一块吃过两次饭的人员，如根据刘德华找出上面的结果语句是不是该这样写
select * from tb_linagci
where 姓名='刘德华' and ( 应该是根据刘德华的就餐日期和饭店名称 对表进行比对 )这个语句该怎么写？
再次感谢

Answer 10

如果根据一个人的姓名，找出跟他一块吃过两次饭的人员，如根据刘德华找出上面的结果语句是不是该这样写
select * from tb_linagci
where 姓名='刘德华' and ( 应该是根据刘德华的就餐日期和饭店名称 对表进行比对 )这个语句该怎么写？
再次感谢——————————————————————————————————————————create table test(姓名 varchar(20),就餐日期 varchar(20),饭店名称 varchar(20),id int)
insert into test select '张三 ','2007-01-01','百福楼',1
insert into test select '李四 ','2007-01-01','百福楼',2
insert into test select '张三 ','2007-05-01','梅园  ',3
insert into test select '刘德华','2007-05-01','梅园  ',4
insert into test select '刘德华','2007-06-01','幼儿园',5
insert into test select '张三 ','2007-06-01','幼儿园',6
insert into test select '周润发','2007-06-01','幼儿园',7
goselect
    distinct a.*
from
    test a,test b
where
    a.姓名=b.姓名 and a.饭店名称<>b.饭店名称 and a.就餐日期<>b.就餐日期
    and
    exists(select
               1
           from
               test c
           where
               c.姓名='刘德华'                --此处加上限定条件即可
               and
               c.姓名<>a.姓名 and c.饭店名称=a.饭店名称 and 就餐日期=a.就餐日期
               and
               exists(select 
                          1
                      from
                          test
                      where
                          姓名=c.姓名 and 饭店名称=b.饭店名称 and 就餐日期=b.就餐日期))
order by
    a.就餐日期,a.饭店名称,a.姓名
go/*
姓名                   就餐日期           饭店名称             id          
-------------------- -------------------- -------------------- ----------- 
张三                   2007-05-01        梅园                 3
张三                   2007-06-01        幼儿园               6
*/drop table test
go