从两个表中选出曾在各表内同组出现的记录（附有测试数据）
tb1是会员表（会员ID不重复），tb2是活动记录表（会员ID可重复），请教一SQL语句，可筛选出在同一“注册日期”注册，且在同一“活动日期”参加过同一“活动项目”的全部会员ID。
测试数据如下：
CREATE TABLE tb1([会员ID] int,[注册日期] datetime)
INSERT INTO tb1 VALUES(1,'2013-1-1')
INSERT INTO tb1 VALUES(2,'2013-1-1')
INSERT INTO tb1 VALUES(3,'2013-1-1')
INSERT INTO tb1 VALUES(4,'2013-1-1')
INSERT INTO tb1 VALUES(5,'2013-1-1')
INSERT INTO tb1 VALUES(6,'2013-1-1')
INSERT INTO tb1 VALUES(7,'2013-1-1')
INSERT INTO tb1 VALUES(8,'2013-1-1')
INSERT INTO tb1 VALUES(9,'2013-1-1')
INSERT INTO tb1 VALUES(10,'2013-1-1')
INSERT INTO tb1 VALUES(11,'2013-1-5')
INSERT INTO tb1 VALUES(12,'2013-1-5')
INSERT INTO tb1 VALUES(13,'2013-1-5')
INSERT INTO tb1 VALUES(14,'2013-1-5')
INSERT INTO tb1 VALUES(15,'2013-1-5')
INSERT INTO tb1 VALUES(16,'2013-1-5')
INSERT INTO tb1 VALUES(17,'2013-1-8')
INSERT INTO tb1 VALUES(18,'2013-1-8')
INSERT INTO tb1 VALUES(19,'2013-1-8')
INSERT INTO tb1 VALUES(20,'2013-1-8')
INSERT INTO tb1 VALUES(21,'2013-1-8')
INSERT INTO tb1 VALUES(22,'2013-1-12')
INSERT INTO tb1 VALUES(23,'2013-1-12')
INSERT INTO tb1 VALUES(24,'2013-1-12')
INSERT INTO tb1 VALUES(25,'2013-1-12')
INSERT INTO tb1 VALUES(26,'2013-1-25')
INSERT INTO tb1 VALUES(27,'2013-1-25')
INSERT INTO tb1 VALUES(28,'2013-1-25')
INSERT INTO tb1 VALUES(29,'2013-1-25')
INSERT INTO tb1 VALUES(30,'2013-1-25')
CREATE TABLE tb2([活动日期] datetime,[活动项目] VARCHAR(10),[会员ID] int)
INSERT INTO tb2 VALUES('2013-5-1','羽毛球',4)
INSERT INTO tb2 VALUES('2013-5-1','羽毛球',16)
INSERT INTO tb2 VALUES('2013-5-1','羽毛球',18)
INSERT INTO tb2 VALUES('2013-5-1','羽毛球',21)
INSERT INTO tb2 VALUES('2013-5-1','羽毛球',22)
INSERT INTO tb2 VALUES('2013-5-1','篮球',3)
INSERT INTO tb2 VALUES('2013-5-1','篮球',7)
INSERT INTO tb2 VALUES('2013-5-1','篮球',8)
INSERT INTO tb2 VALUES('2013-5-1','篮球',9)
INSERT INTO tb2 VALUES('2013-5-1','游泳',12)
INSERT INTO tb2 VALUES('2013-5-1','游泳',15)
INSERT INTO tb2 VALUES('2013-5-1','游泳',17)
INSERT INTO tb2 VALUES('2013-5-8','羽毛球',1)
INSERT INTO tb2 VALUES('2013-5-8','羽毛球',4)
INSERT INTO tb2 VALUES('2013-5-8','羽毛球',16)
INSERT INTO tb2 VALUES('2013-5-8','羽毛球',17)
INSERT INTO tb2 VALUES('2013-5-8','羽毛球',18)
INSERT INTO tb2 VALUES('2013-5-8','羽毛球',22)
INSERT INTO tb2 VALUES('2013-5-8','游泳',12)
INSERT INTO tb2 VALUES('2013-5-8','游泳',19)
INSERT INTO tb2 VALUES('2013-5-8','游泳',23)
INSERT INTO tb2 VALUES('2013-5-10','篮球',3)
INSERT INTO tb2 VALUES('2013-5-10','篮球',8)
INSERT INTO tb2 VALUES('2013-5-10','篮球',11)
INSERT INTO tb2 VALUES('2013-5-10','篮球',22)
INSERT INTO tb2 VALUES('2013-5-10','篮球',23)
INSERT INTO tb2 VALUES('2013-5-10','游泳',26)
INSERT INTO tb2 VALUES('2013-5-10','游泳',27)
INSERT INTO tb2 VALUES('2013-5-10','游泳',29)
INSERT INTO tb2 VALUES('2013-5-10','游泳',30)
分组 查询
[解决办法]
--楼主看下是你想要的结果吗？
;WITH CTE AS
(
SELECT A.会员ID, A.注册日期, B.活动日期, B.活动项目
FROM TB1 A
INNER JOIN TB2 b
ON A.会员ID = B.会员ID
)
SELECT
A.注册日期, A.活动日期, A.活动项目, 同时活动的会员数=COUNT(DISTINCT A.会员ID),
会员列表=STUFF((SELECT ','+RTRIM(M.会员ID) FROM CTE M WHERE M.注册日期=A.注册日期 AND M.活动日期=A.活动日期 AND M.活动项目=A.活动项目 FOR XML PATH('')),1,1,'')
FROM CTE A
GROUP BY A.注册日期, A.活动日期, A.活动项目
HAVING COUNT(DISTINCT A.会员ID) > 1
/*
注册日期活动日期活动项目同时活动的会员数会员列表
2013-01-01 00:00:00.0002013-05-01 00:00:00.000篮球43,7,8,9
2013-01-01 00:00:00.0002013-05-08 00:00:00.000羽毛球21,4
2013-01-01 00:00:00.0002013-05-10 00:00:00.000篮球23,8
2013-01-05 00:00:00.0002013-05-01 00:00:00.000游泳212,15
2013-01-08 00:00:00.0002013-05-01 00:00:00.000羽毛球218,21
2013-01-08 00:00:00.0002013-05-08 00:00:00.000羽毛球217,18
2013-01-12 00:00:00.0002013-05-10 00:00:00.000篮球222,23
2013-01-25 00:00:00.0002013-05-10 00:00:00.000游泳426,27,29,30
*/
--或者是这样？
;WITH CTE AS
(
SELECT A.会员ID, A.注册日期, B.活动日期, B.活动项目
FROM TB1 A
INNER JOIN TB2 b
ON A.会员ID = B.会员ID
)
SELECT * --如果只要会员ID,可以把*改成： DISTINCT A.会员ID
FROM CTE A
WHERE EXISTS
(
SELECT 1
FROM CTE M
WHERE M.注册日期=A.注册日期 AND M.活动日期=A.活动日期 AND M.活动项目=A.活动项目
AND M.会员ID <> A.会员ID
)
create function dbo.fn_会员ID(@注册日期 as datetime,@活动日期 datetime,@活动项目 varchar(50))
returns varchar(500)
as
begin
declare @name varchar(500)
set @name=''
select @name=@name+cast(a.会员ID as varchar)+','
from tb1 a
inner join tb2 b on a.会员ID=b.会员ID
where a.注册日期=@注册日期 and b.活动日期=@活动日期 and b.活动项目=@活动项目
if isnull(@name,'')<>''
set @name=left(@name,len(@name)-1)
return (@name)
end
select a.注册日期,b.活动日期,b.活动项目,count(*) as 次数,dbo.fn_会员ID(a.注册日期,b.活动日期,b.活动项目) as 会员ID_List
from tb1 a
inner join tb2 b on a.会员ID=b.会员ID
group by a.注册日期,b.活动日期,b.活动项目
having count(*)>1
/*
注册日期活动日期活动项目次数会员ID_List
2013-01-01 00:00:00.0002013-05-01 00:00:00.000篮球43,7,8,9
2013-01-01 00:00:00.0002013-05-08 00:00:00.000羽毛球21,4
2013-01-01 00:00:00.0002013-05-10 00:00:00.000篮球23,8
2013-01-05 00:00:00.0002013-05-01 00:00:00.000游泳212,15
2013-01-08 00:00:00.0002013-05-01 00:00:00.000羽毛球218,21
2013-01-08 00:00:00.0002013-05-08 00:00:00.000羽毛球217,18
2013-01-12 00:00:00.0002013-05-10 00:00:00.000篮球222,23
2013-01-25 00:00:00.0002013-05-10 00:00:00.000游泳426,27,29,30
*/
select * from tb1 a
where exists
(select 1
from (select * from tb2 b where b.会员ID=a.会员ID) c
inner join
(select e.*
from tb1 d
inner join tb2 e on d.会员ID=e.会员ID
where d.会员ID<>a.会员ID and d.注册日期=a.注册日期) f
on c.活动日期=f.活动日期 and c.活动项目=f.活动项目)
with t1 as (
SELECT [会员ID],[活动日期],
(select [注册日期] from tb1 where tb1.[会员ID]=tb2.[会员ID]) as [注册日期],
value = stuff((SELECT ',' + [活动项目]
FROM tb2 AS t
WHERE t .[会员ID] = tb2.[会员ID]
and t.[活动日期]=tb2.[活动日期]
order by [活动项目]
FOR xml path('' )), 1, 1, '')
FROM tb2
GROUP BY [会员ID],[活动日期])
SELECT value as [活动项目],[活动日期],[注册日期],
[会员] = stuff((SELECT ',' + convert(nvarchar(10),[会员ID])
FROM t1 AS t
WHERE t .value = t1.value
and t.[活动日期]=t1.[活动日期]
and t.[注册日期]=t1.[注册日期]
order by [会员ID]
FOR xml path('')), 1, 1, '')
FROM t1
GROUP BY value,[活动日期],[注册日期]
having count(1) >1
