表T1,字段:orderid,suborderid,orderdate,amount
求一个sql,找出每个orderid按suborderid和orderdate升序的并且amount>0的,第1笔记录值为:
orderid,suborderid,orderdate,amount
1 , 1 , 2010/01/01,0
1 , 2 , 2010/01/02,200
1 , 3 , 2010/01/03,100
2 , 1 , 2010/01/01,100
2 , 2 , 2010/01/02,000
2 , 3 , 2010/01/03,200 结果应该为
1 , 2 , 2010/01/02,200
2 , 1 , 2010/01/01,100
求一个sql,找出每个orderid按suborderid和orderdate升序的并且amount>0的,第1笔记录值为:
orderid,suborderid,orderdate,amount
1 , 1 , 2010/01/01,0
1 , 2 , 2010/01/02,200
1 , 3 , 2010/01/03,100
2 , 1 , 2010/01/01,100
2 , 2 , 2010/01/02,000
2 , 3 , 2010/01/03,200 结果应该为
1 , 2 , 2010/01/02,200
2 , 1 , 2010/01/01,100
if object_id('[tb]') is not null drop table [tb]
create table [tb]([orderid] int,[suborderid] int,[orderdate] datetime,[amount] varchar(3))
insert [tb]
select 1,1,'2010/01/01','0' union all
select 1,2,'2010/01/02','200' union all
select 1,3,'2010/01/03','100' union all
select 2,1,'2010/01/01','100' union all
select 2,2,'2010/01/02','000' union all
select 2,3,'2010/01/03','200'select * from [tb] t
where [suborderid] =
(select top 1 [suborderid] from [tb] where [orderid]=t.[orderid] and amount > 0 order by suborderid,orderdate )
---------------------------
1 2 2010-01-02 00:00:00.000 200
2 1 2010-01-01 00:00:00.000 100
--> Author : js_szy
--> Target : ★★★
--> Date : 2010-01-06 14:08:41
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (orderid int,suborderid int,orderdate datetime,amount varchar(3))
insert into @tb
select 1,1,'2010/01/01','0' union all
select 1,2,'2010/01/02','200' union all
select 1,3,'2010/01/03','100' union all
select 2,1,'2010/01/01','100' union all
select 2,2,'2010/01/02','000' union all
select 2,3,'2010/01/03','200'select * from @tb t
where (select count(*) from @tb where orderid=t.orderid and suborderid<t.suborderid and orderdate<t.orderdate)<=1
and amount>0
orderid suborderid orderdate amount
----------- ----------- ----------------------- ------
1 2 2010-01-02 00:00:00.000 200
2 1 2010-01-01 00:00:00.000 100
where [suborderid] =
(select top 1 [suborderid]
from [tb]
where [orderid]=T.[orderid] and amount > 0
order by suborderid,orderdate )
create table [tb]([orderid] int,[suborderid] int,[orderdate] datetime,[amount] varchar(3))
insert [tb]
select 1,1,'2010/01/01','0' union all
select 1,2,'2010/01/02','200' union all
select 1,3,'2010/01/03','100' union all
select 2,1,'2010/01/01','100' union all
select 2,2,'2010/01/02','000' union all
select 2,3,'2010/01/03','200'select * from [tb] t
where [suborderid] =
(select top 1 [suborderid] from [tb] where [orderid]=t.[orderid] and amount > 0 order by suborderid,orderdate )
---------------------------
1 2 2010-01-02 00:00:00.000 200
2 1 2010-01-01 00:00:00.000 100
create table [tb]([orderid] int,[suborderid] int,[orderdate] datetime,[amount] varchar(3))
insert [tb]
select 1,1,'2010/01/01','0' union all
select 1,2,'2010/01/02','200' union all
select 1,3,'2010/01/03','100' union all
select 2,1,'2010/01/01','100' union all
select 2,2,'2010/01/02','000' union all
select 2,3,'2010/01/03','200'
select t.*
from tb t
where t.amount>0 and
not exists(select 1 from tb where t.[orderid]=tb.[orderid]
and t.[suborderid]>tb.[suborderid] and amount>0)orderid suborderid orderdate amount
----------- ----------- ----------------------- ------
1 2 2010-01-02 00:00:00.000 200
2 1 2010-01-01 00:00:00.000 100(2 row(s) affected)
(
SELECT orderid,MIN(suborderid) AS suborderid
FROM T1
WHERE amount>0
GROUP BY orderid
ORDER BY suborderid ASC,orderdate
) T ON (T1.orderid=T.orderid AND T1.suborderid=T.suborderid)