SQL中原值 需要的结果
说明:
编号 日期 单价
11 20080608 20 1、编号以升序,日期以降序排序 编号 日期 单价
11 20080607 10 11 20080608 20
12 20080617 0 2、当单价不为零值时,只取同一编号中日期最大的值 12 20080616 220
12 20080616 220 13 20080617 230
12 20080613 130 14 20080618 240
12 20080612 60 15 20080609 90
12 20080609 30 16 20080610 100
13 20080618 0
13 20080617 230
13 20080615 150
13 20080614 140
13 20080614 200
13 20080611 110
13 20080610 40
13 20080607 70
14 20080618 240
14 20080616 160
14 20080615 210
14 20080613 190
14 20080612 120
14 20080611 50
14 20080608 80
15 20080609 90
16 20080610 100
insert into tb
select 11, '20080608', 20 union all
select 11, '20080607', 10 union all
select 12, '20080617', 0 union all
select 12, '20080616', 220 union all
select 12, '20080613', 130 union all
select 12, '20080612', 60 union all
select 12 , '20080609', 30 union all
select 13 , '20080618', 0 union all
select 13 , '20080617', 230---SQL 2005
with tab([编号], [单价])
as(
select [编号], [单价] = max([单价])
from tb
group by [编号]
--
)
select A.[编号], B.[日期], A.[单价]
from tab A left join tb B on A.[编号] = B.[编号] and A.[单价] = B.[单价]
order by A.[编号] asc, B.[日期] desc
--查询结果
编号 日期 单价
----------- ----------------------- -----------
11 2008-06-08 00:00:00.000 20
12 2008-06-16 00:00:00.000 220
13 2008-06-17 00:00:00.000 230(3 行受影响)
--SQL2005
with tab([编号], [单价])
as(
select [编号], [单价] = max([单价])
from tb
group by [编号]
)
select B.*
from tab A left join tb B on A.[编号] = B.[编号] and A.[单价] = B.[单价]
order by A.[编号] asc, B.[日期] desc
--SQL2000
select B.* from (
select [编号], [单价] = max([单价])
from tb
group by [编号]) a left join tb B on A.[编号] = B.[编号] and A.[单价] = B.[单价]