这条SQL语句怎么写?
表名: TableName
字段:
UserID year month shuju TF
1 2007 4 5 1
2 2007 6 6.2 0
5 2006 6 2 1
1 2006 7 3 1
2 2007 7 42 0
2 2007 5 4 1
1 2007 5 2.2 1
3 2007 6 5 0
要求:把每个UserID取出TF为1,日期Year,Month最大的数数据shuju出来.
每个用户最多有一条.
结果要象这样.
UserID year month shuju TF
1 2007 5 2.2 1
2 2007 5 4 1
5 2006 6 2 1
[解决办法]
create table tb(UserID int,[year] varchar(10),[month] varchar(10),shuju decimal(18,2),TF int)
insert into tb values(1, '2007 ', '4 ', 5 , 1)
insert into tb values(2, '2007 ', '6 ', 6.2, 0 )
insert into tb values(5, '2006 ', '6 ', 2 , 1)
insert into tb values(1, '2006 ', '7 ', 3 , 1)
insert into tb values(2, '2007 ', '7 ', 42 , 0)
insert into tb values(2, '2007 ', '5 ', 4 , 1)
insert into tb values(1, '2007 ', '5 ', 2.2, 1)
insert into tb values(3, '2007 ', '6 ', 5 , 0)
go
select tb.* from tb,
(
select userid , max(yearmonth) yearmonth from
(
select userid , [year] + [month] yearmonth , shuju, tf from tb where tf = 1
) m
group by userid
) n
where tb.userid = n.userid and tb.[year] = left(n.yearmonth,4) and tb.[month] = substring(n.yearmonth,5,10)
drop table tb
/*
UserID year month shuju TF
----------- ---------- ---------- -------------------- -----------
1 2007 5 2.20 1
2 2007 5 4.00 1
5 2006 6 2.00 1
（所影响的行数为 3 行）
*/
[解决办法]
select * from table as a where TF = 1 and
not exists(select 1 from table where TF = 1 and UserID = a.UserID and year + month > a.year + a.month)
[解决办法]
Select a.* from TableName as a Where TF=1 and not exists
(Select * from TableName Where TF=1 and UserID=a.UserID and
Convert(datetime,ltrim([year])+ '- '+ltrim([month])+ '-01 ')
> Convert(datetime,ltrim(a.[year])+ '- '+ltrim(a.[month])+ '-01 ')
)
[解决办法]
declare @t table(
UserID int,
[year] int,
[month] int,
shuju decimal(10, 1),
TF int)
insert @t select 1, 2007, 4, 5, 1
union all select 2, 2007, 6, 6.2, 0
union all select 5, 2006, 6, 2, 1
union all select 1, 2006, 7, 3, 1
union all select 2, 2007, 7, 42, 0
union all select 2, 2007, 5, 4, 1
union all select 1, 2007, 5, 2.2, 1
union all select 3, 2007, 6, 5, 0
select * from @t a
where TF = 1
and not exists (select 1 from @t where TF = 1 and UserID = a.UserID and
cast(ltrim([year]) + '- ' + ltrim([month]) + '-1 ' as datetime) >
cast(ltrim(a.[year]) + '- ' + ltrim(a.[month]) + '-1 ' as datetime))
order by UserID
/*
UserID year month shuju TF
----------- ----------- ----------- ------------ -----------
1 2007 5 2.2 1
2 2007 5 4.0 1
5 2006 6 2.0 1
（所影响的行数为 3 行）
*/
[解决办法]
select * from t a
where TF = 1
and not exists (select 1 from t where TF = 1 and UserID = a.UserID and
cast(ltrim([year]) + '- ' + ltrim([month]) + '-1 ' as datetime) >
cast(ltrim(a.[year]) + '- ' + ltrim(a.[month]) + '-1 ' as datetime))
order by UserID
[解决办法]
sp4（1）
Select * From tb As A
Where TF = 1
And Not Exists
(
Select 1 From TB Where TF=1 And UserID=A.UserID And [year]+[month]> A.[year]+A.[month]
)
好像有一点儿 问题 ，如果插入下面两条计录的话，问题就出现了 。
insert into tb values(3, '2007 ', '06 ', 5 , 1)
insert into tb values(3, '2007 ', '4 ', 5 , 1)
用 sp4的方法 查出来的数据 就为：
5200662.001
2200754.001
1200752.201
3200745.001
那么很显然，最后一条数据是错误的 ，稍微改一下 就行了 。
select * from tb as a where not Exists (select 1 from tb where UserID = a.UserID and Convert(datetime,[year]+ '- '+[month]+ '-01 ')> Convert(datetime,a.[year]+ '- '+a.[month]+ '-01 ') and TF=1) and TF=1
go
[解决办法]
select * from tb a
where TF= '1 ' and
CAST ( a.UserId AS varchar(10))+ '_ '+a.year+ '_ '+a.month in (
select top 1 CAST ( b.UserId AS varchar(10))+ '_ '+b.year+ '_ '+b.month
from tb b where a. UserId=b. UserId and b.TF=a.TF
order by b.UserId, b.year+b.month desc)
order by a.UserId, a.year desc
--------------------------------------------
果:
1200752.201
2200754.001
5200662.001
[解决办法]
---创建测试环境
declare @t table(UserID int,[year] int,[month] int,shuju decimal(18,2),TF int)
insert into @t select 1,2007,4,5,1
union all select 2,2007,6,6.2,0
union all select 5,2006,6,2,1
union all select 1,2006,7,3,1
union all select 2,2007,7,42,0
union all select 2,2007,5,4,1
union all select 1,2007,5,2.2,1
union all select 3,2007,6,5,0
---查看测试环境
select * from @t
---查询结果
select * from @t as a where not exists
(select 1 from @t where TF = 1 and UserID = a.UserID and
year + month > a.year + a.month) and TF = 1 order by userid
[解决办法]
---创建测试环境
declare @t table(UserID int,[year] varchar(4),[month] varchar(2),shuju decimal(18,2),TF int)
insert into @t select 1,2007,4,5,1
union all select 2,2007,6,6.2,0
union all select 5,2006,6,2,1
union all select 1,2006,7,3,1
union all select 2,2007,7,42,0
union all select 2,2007,5,4,1
union all select 1,2007,5,2.2,1
union all select 3,2007,6,5,0
insert into @t values(3, '2007 ', '06 ', 5 , 1)
insert into @t values(3, '2007 ', '4 ', 5 , 1)
---查看测试环境
select * from @t order by year,month
---查询结果
select *
from @t a
where TF = 1 and
not exists (
select 1
from @t
where TF = 1 and
UserID = a.UserID and
[year]*100+[month] > a.[year]*100+a.[month])
/*结果
userid year month shuju TF
------ ---- ----- ----- --
5 2006 6 2.00 1
2 2007 5 4.00 1
1 2007 5 2.20 1
3 2007 06 5.00 1
*/
[解决办法]
楼上有错啊，呵呵
select max(year*100+month) from TableName z where a.UserID = z.UserID
只在UserID相当的集合中找，而最大的值可能是TF=0的项，这样就无法和前面TF=1的记录匹配了。
select * from tablename tn
where TF=1
and year*12+month=(select max(year*12+month) from table
where UserID=tn.UserID
and TF=1);
这样写好象有点罗嗦哦，定义视图~~
creat view tv as
select * from tablename
where TF=1;
select * from tv tvx
where year*12+month=(select max(year*12+month) from tv
where UserID=tvx.ID);
drop view tv;