--重复记录
UID Code SKU Startdate EndDate
--------------------------------------------------
1 A 001 2008-4-1 2008-4-30
2 A 001 2008-4-1 2009-4-30
3 A 002 2008-4-1 2009-4-30 4 B 002 2008-4-1 2009-4-30
5 B 002 2008-4-1 2008-6-30
6 B 002 2008-4-1 2009-4-30
7 B 003 2008-4-1 2009-4-30
8 B 003 2008-5-1 2009-4-30
9 B 003 2008-6-1 2009-4-30 10 C 001 2008-4-1 2009-4-30
11 C 001 2008-4-1 2009-4-30 12 D 001 2008-4-1 2009-4-10 13 E 005 2008-4-2 2009-4-10
14 E 005 2008-4-1 2009-4-30--查询条件
按Code分组,在有效日期范围内 不重复的SKU记录
排序规则:开始时间最晚,结束时间最晚,追加时间最晚(UID 是主键)
ORDER BY [StartDate] DESC, [EndDate] ASC, [UID] DESC--查询结果
Code SKU Startdate EndDate
-----------------------------------
A 001 2008-4-1 2008-4-1
A 002 2008-4-1 2009-4-1
B 002 2008-5-1 2008-6-1
B 003 2008-6-1 2009-4-1
C 001 2008-4-1 2009-4-1
E 005 2008-4-1 2009-4-30--SQL???
UID Code SKU Startdate EndDate
--------------------------------------------------
1 A 001 2008-4-1 2008-4-30
2 A 001 2008-4-1 2009-4-30
3 A 002 2008-4-1 2009-4-30 4 B 002 2008-4-1 2009-4-30
5 B 002 2008-4-1 2008-6-30
6 B 002 2008-4-1 2009-4-30
7 B 003 2008-4-1 2009-4-30
8 B 003 2008-5-1 2009-4-30
9 B 003 2008-6-1 2009-4-30 10 C 001 2008-4-1 2009-4-30
11 C 001 2008-4-1 2009-4-30 12 D 001 2008-4-1 2009-4-10 13 E 005 2008-4-2 2009-4-10
14 E 005 2008-4-1 2009-4-30--查询条件
按Code分组,在有效日期范围内 不重复的SKU记录
排序规则:开始时间最晚,结束时间最晚,追加时间最晚(UID 是主键)
ORDER BY [StartDate] DESC, [EndDate] ASC, [UID] DESC--查询结果
Code SKU Startdate EndDate
-----------------------------------
A 001 2008-4-1 2008-4-1
A 002 2008-4-1 2009-4-1
B 002 2008-5-1 2008-6-1
B 003 2008-6-1 2009-4-1
C 001 2008-4-1 2009-4-1
E 005 2008-4-1 2009-4-30--SQL???
开始时间最晚,结束时间最早,追加时间最晚(UID 是主键)
12 D 001 2008-4-1 2008-4-10 13 E 005 2008-4-2 2008-4-10
14 E 005 2008-4-1 2008-4-30
Code SKU Startdate EndDate
-----------------------------------
A 001 2008-4-1 2008-4-1
A 002 2008-4-1 2009-4-1
B 002 2008-5-1 2008-6-1
B 003 2008-6-1 2009-4-1
C 001 2008-4-1 2009-4-1
E 005 2008-4-1 2009-4-30
这个结果怎么来的?怎么EndDate一个都对不上啦?
结束日期 都改成 30号查询结果Code SKU Startdate EndDate
---------------------------------------
A 001 2008-4-1 2008-4-30
A 002 2008-4-1 2009-4-30
B 002 2008-5-1 2008-6-30
B 003 2008-6-1 2009-4-30
C 001 2008-4-1 2009-4-30
E 005 2008-4-1 2009-4-30
from table
group by code, SKU请问追加时间是什么意思,ID 大就追加晚吗?但是你给的结果不是这样的啊。
这个5-1是哪儿来的?
declare @t table(UID int,Code varchar(10),SKU varchar(10),Startdate datetime,EndDate datetime)
insert into @t select
1, 'A','001','2008-4-1','2008-4-30' union select
2 , 'A','001','2008-4-1','2009-4-30' union select
3 , 'A','002','2008-4-1','2009-4-30' union select
4 , 'B','002','2008-4-1','2009-4-30' union select
5 , 'B','002','2008-4-1','2008-6-30' union select
6 , 'B','002','2008-4-1','2009-4-30' union select
7 , 'B','003','2008-4-1','2009-4-30' union select
8 ,'B','003','2008-5-1','2009-4-30' union select
9 ,'B','003','2008-6-1','2009-4-30' union select
10 , 'C','001','2008-4-1','2009-4-30' union select
11 , 'C','001','2008-4-1','2009-4-30' union select
12 , 'D','001','2008-4-1','2009-4-10' union select
13 , 'E','005','2008-4-2','2009-4-10' union select
14 , 'E','005','2008-4-1','2009-4-30'
/*--查询结果
Code SKU Startdate EndDate
--------------------------------------
A 001 2008-4-1 2008-4-30
A 002 2008-4-1 2009-4-30
B 002 2008-5-1 2008-6-30
B 003 2008-6-1 2009-4-30
C 001 2008-4-1 2009-4-30
E 005 2008-4-1 2009-4-30 */select px = identity(int,1,1),* into #
from (select top 100 percent * from @t ORDER BY [StartDate] DESC, [EndDate] ASC, [UID] DESC ) a
select code,sku,startdate,enddate
from # a
where not exists(select 1 from # where code = a.code and sku = a.sku and uid > a.uid)
and code in(select code from # group by code having count(1) > 1)
order by code ,sku
drop table #
/*
code sku startdate enddate
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
A 001 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
A 002 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
B 002 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
B 003 2008-06-01 00:00:00.000 2009-04-30 00:00:00.000
C 001 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
E 005 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000(所影响的行数为 6 行)
*/
13 E 005 2008-4-2 2008-4-10
14 E 005 2008-4-1 2008-4-30
开始时间最晚,结束时间最早
返回的应该是
13 E 005 2008-4-2 2008-4-10 啊
再或者
13 E 005 2008-4-2 2008-4-30
14 E 005 2008-4-1 2008-4-10
根据你的规则你选什么?
insert into @t select
1, 'A','001','2008-4-1','2008-4-30' union select
2 , 'A','001','2008-4-1','2009-4-30' union select
3 , 'A','002','2008-4-1','2009-4-30' union select
4 , 'B','002','2008-4-1','2009-4-30' union select
5 , 'B','002','2008-4-1','2008-6-30' union select
6 , 'B','002','2008-4-1','2009-4-30' union select
7 , 'B','003','2008-4-1','2009-4-30' union select
8 ,'B','003','2008-5-1','2009-4-30' union select
9 ,'B','003','2008-6-1','2009-4-30' union select
10 , 'C','001','2008-4-1','2009-4-30' union select
11 , 'C','001','2008-4-1','2009-4-30' union select
12 , 'D','001','2008-4-1','2009-4-10' union select
13 , 'E','005','2008-4-2','2009-4-10' union select
14 , 'E','005','2008-4-1','2009-4-30'
/*--查询结果
Code SKU Startdate EndDate
--------------------------------------
A 001 2008-4-1 2008-4-30
A 002 2008-4-1 2009-4-30
B 002 2008-5-1 2008-6-30
B 003 2008-6-1 2009-4-30
C 001 2008-4-1 2009-4-30
E 005 2008-4-1 2009-4-30 */select px = identity(int,1,1),* into #
from (select top 100 percent * from @t ORDER BY [StartDate] DESC, [EndDate] ASC, [UID] DESC ) a
select code,sku,startdate,enddate
from # a
where not exists(select 1 from # where code = a.code and sku = a.sku and uid > a.uid)
and code in(select code from # group by code having count(1) > 1)
order by code ,sku
drop table #
/*
code sku startdate enddate
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
A 001 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
A 002 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
B 002 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
B 003 2008-06-01 00:00:00.000 2009-04-30 00:00:00.000
C 001 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
E 005 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000(所影响的行数为 6 行)
*/
insert into tb values( 1 , 'A','001','2008-4-1','2008-4-30')
insert into tb values( 2 , 'A','001','2008-4-1','2009-4-30')
insert into tb values( 3 , 'A','002','2008-4-1','2009-4-30')
insert into tb values( 4 , 'B','002','2008-4-1','2009-4-30')
insert into tb values( 5 , 'B','002','2008-4-1','2008-6-30')
insert into tb values( 6 , 'B','002','2008-4-1','2009-4-30')
insert into tb values( 7 , 'B','003','2008-4-1','2009-4-30')
insert into tb values( 8 , 'B','003','2008-5-1','2009-4-30')
insert into tb values( 9 , 'B','003','2008-6-1','2009-4-30')
insert into tb values( 10, 'C','001','2008-4-1','2009-4-30')
insert into tb values( 11, 'C','001','2008-4-1','2009-4-30')
insert into tb values( 12, 'D','001','2008-4-1','2009-4-10')
insert into tb values( 13, 'E','005','2008-4-2','2009-4-10')
insert into tb values( 14, 'E','005','2008-4-1','2009-4-30')
goselect Code,SKU,Startdate = max(Startdate), EndDate = max(EndDate) from tb group by Code,SKU order by Code,SKUdrop table tb/*
Code SKU Startdate EndDate
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
A 001 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
A 002 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
B 002 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
B 003 2008-06-01 00:00:00.000 2009-04-30 00:00:00.000
C 001 2008-04-01 00:00:00.000 2009-04-30 00:00:00.000
D 001 2008-04-01 00:00:00.000 2009-04-10 00:00:00.000
E 005 2008-04-02 00:00:00.000 2009-04-30 00:00:00.000(所影响的行数为 7 行)
*/
排序规则:开始时间最晚,结束时间最早,追加时间最晚(UID 是主键)
另外,查询结果的结束日期都是 30号,
仔细看我问的,我没有问你规划具体是什么,而是你的规则是指什么。
你的规则是指过滤条件的选择,还是指排序。如果仅是指排序,一个group by就解决了,没必要问邹健。如果是这样,没有这个疑问,我也懒得回贴。