征集一句复杂的SQL语句
表Meeting
ID,Name,StartDate,EndDate
表Attendee(与会人员住宿登记,InDate入住日期,OutDate退房日期,可以中途退房或入住)
ID,Name,InDate,OutDate,Meeting_ID
现在要列出每个会议每天的参加人数(暂定所有会议均为5天)
最好是列出:Meeting_ID,Meeting_Name,Attendee1(第1天人数),Attendee2(第2天人数)...Attendee5(第5天人数)
其次是列出:
Meeting_ID1,Meeting_Name1,Attendee(第1天人数)
Meeting_ID1,Meeting_Name1,Attendee(第2天人数)
Meeting_ID1,Meeting_Name1,Attendee(第3天人数)
Meeting_ID1,Meeting_Name1,Attendee(第4天人数)
Meeting_ID1,Meeting_Name1,Attendee(第5天人数)
Meeting_ID2,Meeting_Name2,Attendee(第1天人数)
Meeting_ID2,Meeting_Name2,Attendee(第2天人数)
Meeting_ID2,Meeting_Name2,Attendee(第3天人数)
Meeting_ID2,Meeting_Name2,Attendee(第4天人数)
Meeting_ID2,Meeting_Name2,Attendee(第5天人数)
......
如果天数不定又该如何？
[解决办法]
--最好是列出:M.Meeting_ID,M.Meeting_Name,Attendee1(第1天人数),Attendee2(第2天人数)...Attendee5(第5天人数) select M.Meeting_ID, M.Meeting_Name, Attendee1=count(A1.Meeting_ID), Attendee2=count(A2.Meeting_ID), Attendee3=count(A3.Meeting_ID), Attendee4=count(A4.Meeting_ID), Attendee5=count(A5.Meeting_ID) from Meeting M left join Attendee A1 on M.Meeting_ID=A1.Meeting_ID and M1.StartDate between A1.InDate and A1.OutDateleft join Attendee A2 on M.Meeting_ID=A2.Meeting_ID and dateadd(day,1,M1.StartDate) between A2.InDate and A2.OutDateleft join Attendee A3 on M.Meeting_ID=A3.Meeting_ID and dateadd(day,2,M1.StartDate) between A3.InDate and A3.OutDateleft join Attendee A4 on M.Meeting_ID=A4.Meeting_ID and dateadd(day,3,M1.StartDate) between A4.InDate and A4.OutDateleft join Attendee A5 on M.Meeting_ID=A5.Meeting_ID and dateadd(day,4,M1.StartDate) between A5.InDate and A5.OutDategroup by M.Meeting_ID, M.Meeting_Name
[解决办法]
似乎忽悠了一个问题，入住/与会之间有联系，但不是必然联系。
与会但不入住，或反之，都有可能。
[解决办法]
--每个会议第一天人数
select a.meeting_id,rtrim(b.name)+'1' meeting_name,count(*) as attendee
from attendee a,metting b
where a.meeting_id=b.id and b.startdate between a.indate and a.outdate
group by a.metting_id,rtrim(b.name)+'1'
同理每个会议第2-5天的人数可依此推
如第2天,将+'1'改为+'2',将a.startdate加1
再将5个查询结果用union all加起来却可
[解决办法]
每个 meeting 5天的情况：
select ID, Name,
(select Count(distinct ID)
from Attendee
where Meeting_ID = A.ID and Indate < A.StartDate
and OutDate > A.StartDate) as Attendee1,
(select Count(distinct ID)
from Attendee
where Meeting_ID = A.ID and Indate < A.StartDate + 1
and OutDate > A.StartDate + 1) as Attendee2,
(select Count(distinct ID)
from Attendee
where Meeting_ID = A.ID and Indate < A.StartDate + 2
and OutDate > A.StartDate + 2) as Attendee3,
(select Count(distinct ID)
from Attendee
where Meeting_ID = A.ID and Indate < A.StartDate + 3
and OutDate > A.StartDate + 3) as Attendee4,
(select Count(distinct ID)
from Attendee
where Meeting_ID = A.ID and Indate < A.StartDate + 4
and OutDate > A.StartDate + 4) as Attendee5
from Meeting
[解决办法]
对任意天
创建一临时表，用while 语句
各天人数的查询语句用动态生成
用insert into 临时表 exec(动态sql语句）
将结果插入到临时表中
再对临时表查询.
[解决办法]
--不知道这是不是你要的样式和结果--创建表CREATE TABLE MEETING(ID INT,NAME VARCHAR(20),STARTDATE VARCHAR(20),ENDDATE VARCHAR(20))INSERT INTO MEETINGSELECT 1,'十六大','2007-10-01','2007-10-05'UNION ALL SELECT 2,'十七大','2007-10-15','2007-10-20'CREATE TABLE ATTENDEE(ID INT,NAME VARCHAR(20),INDATE VARCHAR(20),OUTDATE VARCHAR(20),MEETING_ID INT)INSERT INTO ATTENDEESELECT 1,'张大','2007-10-01','2007-10-03',1UNION ALL SELECT 2,'张二','2007-10-02','2007-10-05',1UNION ALL SELECT 3,'张三','2007-10-03','2007-10-03',1UNION ALL SELECT 4,'张四','2007-10-01','2007-10-05',1UNION ALL SELECT 5,'张五','2007-10-04','2007-10-05',1UNION ALL SELECT 6,'张六','2007-10-03','2007-10-05',1UNION ALL SELECT 7,'王大','2007-10-02','2007-10-05',1UNION ALL SELECT 8,'王二','2007-10-15','2007-10-15',2UNION ALL SELECT 9,'王三','2007-10-17','2007-10-20',2UNION ALL SELECT 10,'王田','2007-10-15','2007-10-20',2UNION ALL SELECT 11,'王五','2007-10-19','2007-10-20',2UNION ALL SELECT 12,'李云','2007-10-18','2007-10-19',2UNION ALL SELECT 13,'马五','2007-10-15','2007-10-17',2UNION ALL SELECT 14,'李玉','2007-10-16','2007-10-16',2UNION ALL SELECT 15,'刘云','2007-10-16','2007-10-20',2--插入临时表select bh=identity( int,0,1) ,* into #tbl from attendee--查询select tb.meeting_id ,ta.name,tb.sj,count(*) '人数' from(select a.*, convert(varchar(10),dateadd(day,b.bh,a.indate),120)sj from attendee a,#tbl b where datediff(day,dateadd(day,b.bh,a.indate),a.outdate)>=0 )tb inner join meeting ta on ta.id=tb.meeting_id and tb.sj between ta.startdate and ta.enddate group by tb.sj,tb.meeting_id,ta.name--删除drop table #tbl,meeting,attendee
