求一SQL语句，大家来看看啦。解决思路

求一SQL语句，大家来看看啦。。。
表结构

SQL code

CREATE TABLE [dbo].[Schedule](    [ID] [int] IDENTITY(1,1) NOT NULL,    [SchedueDate] [varchar](50) NULL,    [Area] [varchar](50) NULL,    [TotalAM] [int] NULL CONSTRAINT [DF_Schedule_TotalAM]  DEFAULT ((0)),    [FactAM] [int] NULL CONSTRAINT [DF_Schedule_FactAM]  DEFAULT ((0)),    [TotalPM] [int] NULL CONSTRAINT [DF_Schedule_TotalPM]  DEFAULT ((0)),    [FactPM] [int] NULL CONSTRAINT [DF_Schedule_FactPM]  DEFAULT ((0)), CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED (    [ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFF

数据：

SQL code

ID     SchedueDate   Area   TotalAM   FactAM   TotalPM   FactPM1      2012-06-07    香港      12       12        12       122      2012-06-07    九龍      12       12        12       123      2012-06-08    香港      12       12        12       124      2012-06-08    九龍      12       12        12       12

我想要的查询结果：
  2012-06-07 2012-06-08
  Total 24 24
香港 FactAM 12 12
  FactPM 12 12
  Total 24 24
九龍 FactAM 12 12
  FactPM 12 12
其中，Total为表中TotalAM与TotalPM的和
请问各位大牛，像这种查询结果可以实现吗？如果可以，怎么可以实现呢？
求Sql

[解决办法]

SQL code

select Area,'FactAM' as col,    sum(case when scheduedate='2012-06-07' then TotalAm else 0 end) as [2012-06-07],    sum(case when scheduedate='2012-06-08' then TotalAm else 0 end) as [2012-06-08]from [Schedule] group by Areaunion allselect Area,'FactPM',    sum(case when scheduedate='2012-06-07' then FactAM else 0 end),    sum(case when scheduedate='2012-06-08' then FactAM else 0 end)from [Schedule] group by Areaunion allselect     Area,'Total',sum(FactAM),sum(FactPM)from [Schedule] group by Area order by 1 desc,2 desc/*Area                           col    2012-06-07  2012-06-08------------------------------ ------ ----------- -----------香港                             Total  24          24香港                             FactPM 12          12香港                             FactAM 12          12九龍                             Total  24          24九龍                             FactPM 12          12九龍                             FactAM 12          12*/
[解决办法]
给你写了第一个，后面两个一样的方法实现
declare @sql varchar(200)
select @sql=ISNULL(@sql+',','')+'sum(case when SchedueDate='''+LTRIM(SchedueDate)+''' then TotalPM end) as ['+LTRIM(SchedueDate)+']'
from [Schedule] group by SchedueDate
exec('select '+@sql+' from [Schedule] ')
[解决办法]
探讨


引用:

SQL code

select Area,'FactAM' as col,
sum(case when scheduedate='2012-06-07' then TotalAm else 0 end) as [2012-06-07],
sum(case when scheduedate='2012-06-08' then TotalAm else 0 ……

[解决办法]
探讨


引用:

引用:

引用:

SQL code

select Area,'FactAM' as col,
sum(case when scheduedate='2012-06-07' then TotalAm else 0 end) as [2012-06-07],
sum(case when scheduedate='……
 

 

查看更多 下一篇