如果一定是三个:select GroupNo,max(case when not exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates<a.Dates) then Data else null end) as Data1,
max(case when exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates<a.Dates) and exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates>a.Dates) then Data else null end) as Data2,
max(case when not exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates>a.Dates) then Data else null end) as Data3,
min(Dates) as Dates
from DataTable a
group by GroupNo
max(case when exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates<a.Dates) and exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates>a.Dates) then Data else null end) as Data2,
max(case when not exists (select 1 from DataTable where GroupNo=a.GroupNo and Dates>a.Dates) then Data else null end) as Data3,
min(Dates) as Dates
from DataTable a
group by GroupNo
from (select GroupNo,min(Data) Data1 from table group by GroupNo)a,
(select GroupNo,max(Data) Data2 from table group by GroupNo)b,
(select table.GroupNo,Data from table ,(select GroupNo,min(Data) Data2 from table group by GroupNo) c ,( select GroupNo,max(Data) Data2 from table group by GroupNo) d where table.GroupNo=c.GroupNo and table.GroupNo=d.GroupNo
and data<>data1 and data<>data2) f,
(select GroupNo,Dates from table ,(select GroupNo,min(Data) Data1 from table group by GroupNo) g where table.GroupNo=g.GroupNo and data1=data) j
where a.GroupNo=b.GroupNo and a.GroupNo=f.GroupNo and a.GroupNo=j.GroupNo
insert into ta values(1,1.1,'2003-01-01')
insert into ta values(1,1.2,'2003-01-02')
insert into ta values(1,1.3,'2003-01-03')
insert into ta values(2,1.1,'2003-02-01')
insert into ta values(2,1.2,'2003-02-02')
insert into ta values(2,1.3,'2003-02-03')select groupno,
sum(case data when 1.1 then data else 0 end) as data1,
sum(case data when 1.2 then data else 0 end) as data2,
sum(case data when 1.3 then data else 0 end) as data3,
min(dates) as dates
from ta
group by groupno
服务器: 消息 130,级别 15,状态 1,行 1
不能对包含聚合或子查询的表达式执行聚合函数。
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'and' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'then' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'then' 附近有语法错误。是什么原因?谢谢!
服务器: 消息 156,级别 15,状态 1,行 2
在关键字 'table' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 3
在关键字 'table' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 4
在关键字 'table' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 4
在关键字 'table' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'table' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'table' 附近有语法错误。
什么原因?谢谢!
goinsert into #ta values(1,1.1,'2003-01-01')
insert into #ta values(1,1.2,'2003-01-02')
insert into #ta values(1,1.3,'2003-01-03')
insert into #ta values(2,2.1,'2003-02-01')
insert into #ta values(2,2.2,'2003-02-02')
insert into #ta values(2,2.3,'2003-02-03')
goselect GroupNo,min(data1),min(data2),min(data3),min(dates)
from (
select GroupNo,
case when not exists (select 1 from #ta where GroupNo=a.GroupNo and Dates<a.Dates) then Data else null end as Data1,
case when exists (select 1 from #ta where GroupNo=a.GroupNo and Dates<a.Dates) and exists (select 1 from #ta where GroupNo=a.GroupNo and Dates>a.Dates) then Data else null end as Data2,
case when not exists (select 1 from #ta where GroupNo=a.GroupNo and Dates>a.Dates) then Data else null end as Data3,
Dates as Dates
from #ta a
) x
group by groupno
有个警告:警告: 聚合或其它 SET 操作消除了空值。
把语句中的null改成你的data字段不可能达到的一个大数,就可以消除警告。select GroupNo,min(data1),min(data2),min(data3),min(dates)
from (
select GroupNo,
case when not exists (select 1 from #ta where GroupNo=a.GroupNo and Dates<a.Dates) then Data else 100000000 end as Data1,
case when exists (select 1 from #ta where GroupNo=a.GroupNo and Dates<a.Dates) and exists (select 1 from #ta where GroupNo=a.GroupNo and Dates>a.Dates) then Data else 10000000 end as Data2,
case when not exists (select 1 from #ta where GroupNo=a.GroupNo and Dates>a.Dates) then Data else 10000000 end as Data3,
Dates as Dates
from #ta a
) x
group by groupno
谢谢,问题已经解决,现在又有一个问题(会不会烦了?呵呵):insert into #ta values(1,1.1,'2003-01-01')
insert into #ta values(1,1.2,'2003-01-02')
insert into #ta values(1,1.3,'2003-01-03')
insert into #ta values(2,2.1,'2003-02-01')
insert into #ta values(2,2.2,'2003-02-02')
insert into #ta values(2,2.3,'2003-02-03')这几句是不是必须的?如果数据很多,从1.1、2.1、3.1N.1,是不是也要都这么写?
谢谢!
insert datatable values( 1 , 1.1 ,'2003/1/1')
insert datatable values( 1 ,1.2 ,'2003/1/2')
insert datatable values( 1 ,1.3 ,'2003/1/3')
insert datatable values( 2 ,2.1 ,'2003/2/1')
insert datatable values( 2 ,2.2 ,'2003/2/2')
insert datatable values( 2 ,2.3, '2003/2/3')
select groupno,sum(case when id=1 then data end) ,min(dates) dates,sum(case when id=2 then data end) ,min(dates) dates,sum(case when id=3 then data end) ,min(dates) dates from (select *,(select sum(1) from datatable where dates<=tem.dates and groupno=tem.groupno) id from datatable tem) tem2 group by groupnogo
drop table datatable
现在又有一个新问题(真是贪得无厌,不好意思):
上面的例子,每组数据个数是固定的(Datat1、Datat2、Datat3),如果多个数据表,每个数据表的组数据是不同的,分别是2、3、4个,那么这段语句如何自动适应?谢谢!
我给大伙鞠躬啦!
,sum(case when id=2 then data end)
,sum(case when id=3 then data end)
,sum(case when id=4 then data end)
...
,sum(case when id=N then data end)
,min(dates) dates
from (select *,(select sum(1) from datatable where dates<=tem.dates and groupno=tem.groupno) id from datatable tem) tem2
group by groupno
create table #datatable(GroupNo int,Data numeric(8,1),Dates datetime)
insert #datatable values( 1 , 1.1 ,'2003/1/1')
insert #datatable values( 1 ,1.2 ,'2003/1/2')
insert #datatable values( 1 ,1.3 ,'2003/1/3')
insert #datatable values( 2 ,2.1 ,'2003/2/1')
insert #datatable values( 2 ,2.2 ,'2003/2/2')
insert #datatable values( 2 ,2.3, '2003/2/3')
select groupno,
sum(case when id=1 then data end) data1,
min(dates) dates,
sum(case when id=2 then data end) data2,
min(dates) dates,
sum(case when id=3 then data end) data3,
min(dates) dates
from (select *,(select sum(1) from #datatable where dates<=tem.dates and groupno=tem.groupno) id
from #datatable tem) tem2 group by groupnogo
drop table #datatable
insert into #ta values(1,1.1,'2003-01-01')
insert into #ta values(1,1.2,'2003-01-02')
insert into #ta values(1,1.3,'2003-01-03')
insert into #ta values(2,2.1,'2003-02-01')
insert into #ta values(2,2.2,'2003-02-02')
insert into #ta values(2,2.3,'2003-02-03')select a.GroupNo,a.data1,b.data2,f.data,j.dates
from (select GroupNo,min(Data) Data1 from #ta group by GroupNo)a,
(select GroupNo,max(Data) Data2 from #ta group by GroupNo)b, (select #ta.GroupNo,Data from #ta ,(select GroupNo,min(Data) Data1
from #ta group by #ta.GroupNo) c ,
( select GroupNo,max(Data) Data2 from #ta group by GroupNo) d
where #ta.GroupNo=c.GroupNo and #ta.GroupNo=d.GroupNo
and data<>c.data1 and data<>d.data2) f,
(select #ta.GroupNo,Dates from #ta ,(select GroupNo,min(Data) Data1
from #ta group by #ta.GroupNo) g where #ta.GroupNo=g.GroupNo and g.data1=data) j
where a.GroupNo=b.GroupNo and a.GroupNo=f.GroupNo and a.GroupNo=j.GroupNodrop table #ta
GroupNo data1 data2 data dates
----------- ----- ----- ----
1 1.1 1.3 1.2 2003-01-01 00:00:00.000
2 2.1 2.3 2.2 2003-02-01 00:00:00.000