您的位置：程序门 -> ms-sql server -> 疑难问题

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

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

一个分类统计的问题，有点复杂[已结贴,结贴人:day90]

发表于：2007-09-18 09:47:14 楼主

现有表 t_就餐记录
字段如下：
姓名就餐日期饭店名称 id（自增列)
张三 2007-01-01 百福楼 1
李四 2007-01-01 百福楼 2
张三 2007-05-01 梅园 3
刘德华 2007-05-01 梅园 4
刘德华 2007-06-01 幼儿园 5
张三 2007-06-01 幼儿园 6
周润发 2007-06-01 幼儿园 7

想统计出来，在一起吃过两次饭以上的人员（两人，或两人以上）

统计出来的格式大概是这个样式
就餐日期饭店名称吃饭人员
2007-05-01 梅园刘德华，张三
2007-06-01 幼儿园刘德华，张三

或者其他格式也可以，只要能看出是是一起吃过两次饭以上的人员就可以了

先谢谢了

发表于：2007-09-18 09:48:161楼得分:0

--生成测试数据
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

发表于：2007-09-18 09:50:252楼得分:0

理解错了

发表于：2007-09-18 09:51:183楼得分:0

---用函数
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
end

go

select 就餐日期,饭店名称,吃饭人员=dbo.f（就餐日期)
from t_就餐记录
group by 就餐日期,饭店名称

发表于：2007-09-18 09:54:554楼得分:10

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 ', '幼儿园 ', 7

go

---用函数
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
end

go

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)

*/

发表于：2007-09-18 09:55:535楼得分:5

学习!

发表于：2007-09-18 09:56:136楼得分:0

发表于：2007-09-18 10:13:257楼得分:0

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 ', '幼儿园 ', 7

go

---用函数
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
end

go

---借用一下临时表
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)

*/

发表于：2007-09-18 10:16:168楼得分:0

谢谢楼上的，不知能否将结果更进一步，明确把一起吃过两次饭以上的人员组合起来，这样如果数据很多的话就不会产生混淆了。
非常感谢

发表于：2007-09-18 10:18:099楼得分:0

呵呵，大家热情很高，刚才的话是对libin_ftsafe（子陌红尘：ts for banking card)朋友说的，

to:leo_lesley（leo) ,统计的是一起吃过两次饭以上的人员，不是每次饭局的人数

发表于：2007-09-18 10:31:3610楼得分:0

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

create 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
go

select
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

发表于：2007-09-18 10:33:4711楼得分:80

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

create 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
go

select
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

发表于：2007-09-18 10:34:3212楼得分:5

---统计的是一起吃过两次饭以上的人员
---借用楼上的数据
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 行）
*/

发表于：2007-09-18 10:35:2813楼得分:0

有了子陌红尘的查询结果， tb_linagci
刘德华 2007-05-01 梅园 4
张三 2007-05-01 梅园 3
刘德华 2007-06-01 幼儿园 5
张三 2007-06-01 幼儿园 6

如果根据一个人的姓名，找出跟他一块吃过两次饭的人员，如根据刘德华找出上面的结果

语句是不是该这样写
select * from tb_linagci
where 姓名= '刘德华 ' and （应该是根据刘德华的就餐日期和饭店名称对表进行比对 )

这个语句该怎么写？
再次感谢

发表于：2007-09-18 10:36:1514楼得分:0

---抱歉想错了,不对

发表于：2007-09-18 10:38:0015楼得分:0

楼上各位，非常感谢

发表于：2007-09-18 10:38:0316楼得分:0

如果根据一个人的姓名，找出跟他一块吃过两次饭的人员，如根据刘德华找出上面的结果

语句是不是该这样写
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
go

select
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

发表于：2007-09-18 10:41:2717楼得分:0

子陌红尘，你真是我的偶像！赞一个！

其他资讯