我有个表
User(Id,year,month,name,sex)
1,2001, 11,‘张三’,'男'
2,2011, 01,‘张三’,'男'
3,2011, 11,‘李四’,'男'
4,2001, 06,‘李四’,'男'
5,2011, 11,‘张三’,'男'
然后我要把这个表的数据插入临时表
这个临时表最后获取到的数据的id为2,3,5
条件是这样的，获取year和month最晚的数据，也就是时间最晚，
然后多个条件name like '%'+@name+'%' and sex like '%' +@sex+'%'
所以最后如果name是张三，sex是男的话，最后临时表的数据，就是user表里Id为2的数据了
------解决方案--------------------
- SQL code
create table [User](Id int,[year] int,[month] int,name nvarchar(10),sex varchar(10))insert into [User] select 1,2001, 11,'张三','男'insert into [User] select 2,2011, 01,'张三','男'insert into [User] select 3,2011, 11,'李四','男'insert into [User] select 4,2001, 06,'李四','男'insert into [User] select 5,2011, 11,'张三','男'goselect * from [user] a where not exists(select 1 from [user] where [year]>a.[year] or [year]=a.[year] and [month]>a.[month])/*Id year month name sex----------- ----------- ----------- ---------- ----------3 2011 11 李四 男5 2011 11 张三 男(2 行受影响)*/godrop table [user]
------解决方案--------------------
- SQL code
if object_id('[User]') is not null drop table [User]create table [User] (Id int,year int,month varchar(2),name varchar(4),sex varchar(2))insert into [User]select 1,2001,'11','张三','男' union allselect 2,2011,'01','张三','男' union allselect 3,2011,'11','李四','男' union allselect 4,2001,'06','李四','男' union allselect 5,2011,'11','王五','男'select * /* into #t */ from [User] awhere not exists(select 1 from [User] where name=a.name and sex=a.sex and ltrim(year)+ltrim(month)>ltrim(a.year)+ltrim(a.month))drop table [User]/*2 2011 01 张三 男3 2011 11 李四 男5 2011 11 王五 男
------解决方案--------------------
- SQL code
select * from [user] a where not exists(select 1 from [user] where name=a.name and sex=a.sex and [year]>a.[year] or (name=a.name and sex=a.sex and [year]=a.[year] and [month]>a.[month]))