22,209
社区成员
发帖
与我相关
我的任务
分享
where 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)
)
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]))
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 all
select 2,2011,'01','张三','男' union all
select 3,2011,'11','李四','男' union all
select 4,2001,'06','李四','男' union all
select 5,2011,'11','王五','男'
select * /* into #t */ from [User] a
where 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 王五 男
select * into #t from User a
where 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)
)
--楼主意思是不是就是相同性别和名字的取最新时间的插入临时表嘛,是的话如下:
select * into #t from User a
where not exists
(select 1 from User where name=a.name and sex=a.sex and year>a.year)
--如果还是要传变量自行改下好了
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,'张三','男'
go
select * 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 行受影响)
*/
go
drop table [user]
order by year desc
select top 1 * into #t from User
where name like '%'+@name+'%' and sex like '%' +@sex+'%'
--这样?不过为什么要用like,而且前后都%?用不到索引诶,名字还能模糊的?
--然后你要的结果是2,3,5。为什么又要传@name和@sex?那不是会唯一确定一个的?