表:CREATE TABLE [dbo].[YK_Meeting](
[MainID] [char](11) NOT NULL,
[ApplicationDate] [varchar](50) NULL,
[Room] [nchar](10) NULL,
[ScheduledDate] [varchar](50) NULL,
[Applicant] [nvarchar](20) NULL,
[Participant] [nvarchar](200) NULL,
[Description] [nvarchar](200) NULL,
[s08300900] [int] NULL,
[s09000930] [int] NULL,
[s09301000] [int] NULL,
[s10001030] [int] NULL,
[s10301100] [int] NULL,
[s11001130] [int] NULL,
[s11301200] [int] NULL,
[s12001230] [int] NULL,
[s12301300] [int] NULL,
[s13001330] [int] NULL,
[s13301400] [int] NULL,
[s14001430] [int] NULL,
[s14301500] [int] NULL,
[s15001530] [int] NULL,
[s15301600] [int] NULL,
[s16001630] [int] NULL,
[s16301700] [int] NULL,
[s17001730] [int] NULL,
CONSTRAINT [PK_YK_Meeting] PRIMARY KEY CLUSTERED
(
[MainID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
表中数据:MainID Room ScheduledDate s08300900 s09000930 s09301000YKM00000106 小会议室 2014-04-14 0 0 0
YKM00000107 大会议室 2014-04-14 1 0 0
YKM00000108 大会议室 2014-04-14 0 1 0
YKM00000109 培训室 2014-04-14 1 0 0
YKM00000110 大会议室 2014-04-15 0 0 0表中数据每一行代表一个会议预定。
room:表示会议室名称
ScheduledDate:预定会议时间
s08300900:如果是1表示预定8:30-9:00,默认为0
s09000930:如果是1表示预定9:00-9:30,默认为0
s09301000:如果是1表示预定9:30-10:00,默认为0现在表中数据表示:
2014-4-14日小会议室预定,但时间不确定。大会议室8:30-9:30已预定。培训室8:30-9:00已预定。
2014-4-15日大会议室预定,但时间不确定。
目的:可以通过sql查出某天(ScheduledDate字段)的预定各个会议室情况。原来我使用:
select yk.Room,SUM(yk.[s08300900]) as [s08300900],SUM(yk.[s09000930]) as [s09000930],SUM(yk.[s09301000]) as [s09301000] from yk_meeting yk group by yk.Room,yk.ScheduledDate having yk.ScheduledDate='2014-04-14'但所有可以预定的会议室是(小会议室、大会议室、培训室、1号洽谈室、2号洽谈室),用上面的sql只能查出当天有预定的会议室情况。我希望得出的数据是:Room s08300900 s09000930 s09301000
大会议室 1 1 0
培训室 1 0 0
小会议室 0 0 0
1号洽谈室 0 0 0
2号洽谈室 0 0 0请帮忙写个sql吧,多谢勒!!
[MainID] [char](11) NOT NULL,
[ApplicationDate] [varchar](50) NULL,
[Room] [nchar](10) NULL,
[ScheduledDate] [varchar](50) NULL,
[Applicant] [nvarchar](20) NULL,
[Participant] [nvarchar](200) NULL,
[Description] [nvarchar](200) NULL,
[s08300900] [int] NULL,
[s09000930] [int] NULL,
[s09301000] [int] NULL,
[s10001030] [int] NULL,
[s10301100] [int] NULL,
[s11001130] [int] NULL,
[s11301200] [int] NULL,
[s12001230] [int] NULL,
[s12301300] [int] NULL,
[s13001330] [int] NULL,
[s13301400] [int] NULL,
[s14001430] [int] NULL,
[s14301500] [int] NULL,
[s15001530] [int] NULL,
[s15301600] [int] NULL,
[s16001630] [int] NULL,
[s16301700] [int] NULL,
[s17001730] [int] NULL,
CONSTRAINT [PK_YK_Meeting] PRIMARY KEY CLUSTERED
(
[MainID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
表中数据:MainID Room ScheduledDate s08300900 s09000930 s09301000YKM00000106 小会议室 2014-04-14 0 0 0
YKM00000107 大会议室 2014-04-14 1 0 0
YKM00000108 大会议室 2014-04-14 0 1 0
YKM00000109 培训室 2014-04-14 1 0 0
YKM00000110 大会议室 2014-04-15 0 0 0表中数据每一行代表一个会议预定。
room:表示会议室名称
ScheduledDate:预定会议时间
s08300900:如果是1表示预定8:30-9:00,默认为0
s09000930:如果是1表示预定9:00-9:30,默认为0
s09301000:如果是1表示预定9:30-10:00,默认为0现在表中数据表示:
2014-4-14日小会议室预定,但时间不确定。大会议室8:30-9:30已预定。培训室8:30-9:00已预定。
2014-4-15日大会议室预定,但时间不确定。
目的:可以通过sql查出某天(ScheduledDate字段)的预定各个会议室情况。原来我使用:
select yk.Room,SUM(yk.[s08300900]) as [s08300900],SUM(yk.[s09000930]) as [s09000930],SUM(yk.[s09301000]) as [s09301000] from yk_meeting yk group by yk.Room,yk.ScheduledDate having yk.ScheduledDate='2014-04-14'但所有可以预定的会议室是(小会议室、大会议室、培训室、1号洽谈室、2号洽谈室),用上面的sql只能查出当天有预定的会议室情况。我希望得出的数据是:Room s08300900 s09000930 s09301000
大会议室 1 1 0
培训室 1 0 0
小会议室 0 0 0
1号洽谈室 0 0 0
2号洽谈室 0 0 0请帮忙写个sql吧,多谢勒!!
--假设表a存有所有room信息
with a(room) as(
select '大会议室' union all
select '培训室 union all
select '小会议室' union all
select '1号洽谈室union all
select '2号洽谈室union all
)
select a.room , b.*
from a left join (select yk.Room,SUM(yk.[s08300900]) as [s08300900],SUM(yk.[s09000930]) as [s09000930],SUM(yk.[s09301000]) as [s09301000] from yk_meeting yk group by yk.Room,yk.ScheduledDate having yk.ScheduledDate='2014-04-14') as b on a.room = b.room[/code]
with a(room) as(
select '大会议室' union all
select '培训室' union all
select '小会议室' union all
select '1号洽谈室'union all
select '2号洽谈室'
)select a.room , b.*
from a left join
(select yk.Room,SUM(yk.[s08300900]) as [s08300900],SUM(yk.[s09000930]) as [s09000930],SUM(yk.[s09301000]) as [s09301000] from yk_meeting yk group by yk.Room,yk.ScheduledDate having yk.ScheduledDate='2014-04-14')
as b on a.room = b.room
試試這個,剛沒檢查語法
大会议室 大会议室 1 1 0
培训室 培训室 1 0 0
小会议室 小会议室 0 0 0
1号洽谈室 NULL NULL NULL NULL
2号洽谈室 NULL NULL NULL NULL我需要的是:
Room s08300900 s09000930 s09301000
大会议室 1 1 0
培训室 1 0 0
小会议室 0 0 0
1号洽谈室 0 0 0
2号洽谈室 0 0 0
insert into yktext
select 'YKM00000106', '小会议室', '2014-04-14', 0, 0, 0 union all
select 'YKM00000107', '大会议室', '2014-04-14', 1, 0, 0 union all
select 'YKM00000108', '大会议室', '2014-04-14', 0, 1, 0 union all
select 'YKM00000109', '培训室', '2014-04-14', 1, 0, 0 union all
select 'YKM00000110', '大会议室', '2014-04-15', 0, 0, 0 select *
from yktext
select a.room,isnull(sum(b.s08300900),0) s08300900 ,isnull(sum(b.s09000930),0) s09000930 ,isnull(sum(b.s09301000),0) s09301000
from (select distinct room
from yktext) a left join (select *
from yktext
where yktext.secheduleddate='2014-04-14' ) b on a.room =b.room
group by a.room