Name Num1 Date1 Date2 Add
张三 1002 2011-5-6 2010-4-1 广东广州XXXXXXX
张三 1002 2011-5-6 2011-5-1 广东广州XXXXXXX
李四 1003 2012-1-5 2011-6-5 广东珠海XXXXXXX
李四 1003 2012-2-5 2011-5-5 广东珠海XXXXXXX
结果:
张三 1002 2011-5-6 2011-5-1 广东广州XXXXXXX
李四 1003 2012-2-5 2011-5-5 广东珠海XXXXXXX
就是Date1相同时,取Date2最后的日期;DATE1不同时,取DATE1最后的日期。
请问怎么用一条SQL语句查询出来?执行效率要好一点的哦~
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2012-04-01 11:19:39
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[TB]') IS NOT NULL
DROP TABLE [TB]
go
CREATE TABLE [TB] (Name VARCHAR(4),Num1 INT,Date1 DATETIME,Date2 DATETIME,Addi VARCHAR(15))
INSERT INTO [TB]
SELECT '张三',1002,'2011-5-6','2010-4-1','广东广州XXXXXXX' UNION ALL
SELECT '张三',1002,'2011-5-6','2011-5-1','广东广州XXXXXXX' UNION ALL
SELECT '李四',1003,'2012-1-5','2011-6-5','广东珠海XXXXXXX' UNION ALL
SELECT '李四',1003,'2012-2-5','2011-5-5','广东珠海XXXXXXX'select *
from
(
select row=row_number() over(partition by name order by date1 desc,date2 desc),* from tb
) K
where row=1
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Name] varchar(4),[Num1] int,[Date1] datetime,[Date2] datetime,[Add] varchar(15))
insert [tb]
select '张三',1002,'2011-5-6','2010-4-1','广东广州XXXXXXX' union all
select '张三',1002,'2011-5-6','2011-5-1','广东广州XXXXXXX' union all
select '李四',1003,'2012-1-5','2011-6-5','广东珠海XXXXXXX' union all
select '李四',1003,'2012-2-5','2011-5-5','广东珠海XXXXXXX'
--------------开始查询--------------------------select * from [tb] t where not exists(
select 1 from tb where [Name]=t.[Name]
and [Num1]=t.[Num1]
and (
([Date1]=t.[Date1] and [Date2]>t.[Date2])
or [Date1]>t.[Date1]
)
)
----------------结果----------------------------
/*
Name Num1 Date1 Date2 Add
---- ----------- ----------------------- ----------------------- ---------------
张三 1002 2011-05-06 00:00:00.000 2011-05-01 00:00:00.000 广东广州XXXXXXX
李四 1003 2012-02-05 00:00:00.000 2011-05-05 00:00:00.000 广东珠海XXXXXXX(2 行受影响)
*/
create table [#tb]([Name] varchar(4),[Num1] int,[Date1] datetime,[Date2] datetime,[Add] varchar(15))
insert [#tb]
select '张三',1002,'2011-5-6','2010-4-1','广东广州XXXXXXX' union all
select '张三',1002,'2011-5-6','2011-5-1','广东广州XXXXXXX' union all
select '李四',1003,'2012-1-5','2011-6-5','广东珠海XXXXXXX' union all
select '李四',1003,'2012-2-5','2011-5-5','广东珠海XXXXXXX' union all
select '李三',1004,'2012-2-5','2011-5-5','广东珠海XXXXXXX' union all
select '李三',1004,'2012-2-5','2011-5-5','广东珠海XXXXXXX'
NAME和NUM相同的只显示一条