select fdt,fvalue1=(select fvalue from 表 where fdt=a.fdt and ftype=min(a.ftype) ), fvalue2=(select fvalue from 表 where fdt=a.fdt and ftype>min(a.ftype) ) from 表 a group by fdt
Create table A (fType int,fValue int,fDT datetime) insert into A select 3011, 20,'2004-1-1' union all select 3012,300,'2004-1-1' union all select 3011,25,'2004-1-2' union all select 3012 ,305,'2004-1-2' ----------------實現語句------------- select fdt,fvalue1=(select fvalue from A where fdt=b.fdt and ftype=min(b.ftype) ), fvalue2=(select fvalue from A where fdt=b.fdt and ftype>min(b.ftype) ) from A b group by fdt ---------------------結果----------------------- 2004-1-1 20 300 2004-1-2 25 305
--我也想了一种方法,测试通过. select a.fdt,a.fvalue,b.fvalue from (select * from a b where ftype =(select top 1 ftype from a where fdt=b.fdt order by fvalue)) a join (select * from a b where ftype =(select top 1 ftype from a where fdt=b.fdt order by fvalue desc)) b on a.fdt=b.fdt--测试结果 /* fdt fvalue fvalue ------------------------------------------------------ ----------- ----------- 2004-01-01 00:00:00.000 20 300 2004-01-02 00:00:00.000 25 305(所影响的行数为 2 行) */
fvalue2=(select fvalue from 表 where fdt=a.fdt and ftype>min(a.ftype) ) from 表 a group by fdt
insert into A
select 3011, 20,'2004-1-1' union all
select 3012,300,'2004-1-1' union all
select 3011,25,'2004-1-2' union all
select 3012 ,305,'2004-1-2'
----------------實現語句-------------
select fdt,fvalue1=(select fvalue from A where fdt=b.fdt and ftype=min(b.ftype) ),
fvalue2=(select fvalue from A where fdt=b.fdt and ftype>min(b.ftype) ) from A b group by fdt
---------------------結果-----------------------
2004-1-1 20 300
2004-1-2 25 305
select a.fdt,a.fvalue,b.fvalue
from
(select * from a b where ftype =(select top 1 ftype from a where fdt=b.fdt order by fvalue)) a
join
(select * from a b where ftype =(select top 1 ftype from a where fdt=b.fdt order by fvalue desc)) b
on a.fdt=b.fdt--测试结果
/*
fdt fvalue fvalue
------------------------------------------------------ ----------- -----------
2004-01-01 00:00:00.000 20 300
2004-01-02 00:00:00.000 25 305(所影响的行数为 2 行)
*/