表名: 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
字段:
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
解决方案 »
- 有一个字母和数字组成的编号,如何取得下一个编号?
- 内连接与不用内连接会对性能有影响吗?
- 关于满足某种条件之后sql数据库自动退出或锁定数据库表的问题
- SQL 2005的竞争问题,谢谢
- exec 后支持的字符串长度
- 新手问题:查询管理器登陆时为什么只能选择连接 对话框中提供的服务器登陆sql,而不能用填入ip或者localhost进行登陆?
- SqlServer 查询遇到特殊字符 'ς',无法查询到记录
- SQL SERVER基本问题请教
- 网上考试系统怎么定义表结构????
- 请帮忙解决一下SQL SERVER触发器的问题
- 关于在SQL 2000中用自定义公式生成主键的问题(急)
- select 赋值的简单问题,在线等……
(Select * from TableName Where UserID=a.UserID and
Convert(datetime,ltrim([year])+'-'+ltrim([month])+'-01')
>Convert(datetime,ltrim(a.[year])+'-'+ltrim(a.[month])+'-01')
)
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)
goselect 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 行)
*/
not exists(select 1 from table where TF = 1 and UserID = a.UserID and year + month > a.year + a.month)
(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')
)
insert test 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,0select * from (select * from test where TF=1) a where not exists
(
select 1 from (select * from test where TF=1)b where b.UserID=a.UserID
and rtrim(b.year)+rtrim(b.month)>rtrim(a.year)+rtrim(a.month)
)
order by UserIDdrop table test
UserID year month shuju TF
----------- ----------- ----------- ------------ -----------
1 2007 5 2.2 1
2 2007 5 4.0 1
5 2006 6 2.0 1
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)----方法2:
SELECT a.* FROM TABLE as a
INNER JOIN (select UserID,MAX(year+month) as ym from table where TF = 1 group by UserID) as b
ON a.UserID = b.UserID and a.year + a.month = b.ym
WHERE a.TF = 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)
goselect tb.* from tb,
(
select userid , max(yearmonth) yearmonth from
(
select userid , cast([year] as varchar) + right('00' + cast([month] as varchar),2) 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] = cast(substring(n.yearmonth,5,len(n.yearmonth) - 4) as int)
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 行)
*/
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, 0select * 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 行)
*/
create table test(UserID int,year int,month int,shuju decimal(10,1),TF int)
insert test 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,0select * from (select * from test where TF=1) a where not exists
(
select 1 from (select * from test where TF=1)b where b.UserID=a.UserID
and convert(datetime,rtrim(b.year)+'-'+rtrim(b.month)+'-1',120)>
convert(datetime,rtrim(a.year)+'-'+rtrim(a.month)+'-1',120)
)
order by UserIDdrop table testUserID 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 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]
)
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
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的方法 查出来的数据 就为:
5 2006 6 2.00 1
2 2007 5 4.00 1
1 2007 5 2.20 1
3 2007 4 5.00 1
那么很显然,最后一条数据是错误的 ,稍微改一下 就行了 。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
--------------------------------------------
結果:
1 2007 5 2.20 1
2 2007 5 4.00 1
5 2006 6 2.00 1
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
where tb.shuju = b.mm and tb.userid=b.id
order by userid
修改下/*
---创建测试环境
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
union all select 1,2006,7,5,1 --加此行(相同的usrid,存在相同的year和month)
union all select 3,2007,5,5,1 --加此行
---查看测试环境
select * from @t
---查询结果
select * from @t as a where TF = 1
and not exists
(select 1 from @t where TF = 1 and UserID = a.UserID and year + month > a.year + a.month )
and not exists
(select 1 from @t where userid=a.userid and year + month>=a.year + a.month and shuju>a.shuju) 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 *
from table
where TF=1 and (userid,year+month) in
(select userid,max(year+month)
from table where TF=1
group by userid)
FROM tb
JOIN
( SELECT userid,MAX(CONVERT(DATETIME,([year])+'-'+([month])+'-01')) dt
FROM tb WHERE TF=1
GROUP BY userid
) tb2
ON tb.userid = tb2.userid
AND CONVERT(datetime,([year])+'-'+([month])+'-01') = tb2.dt
WHERE tf = 1
where year*100+month = (select max(year*100+month) from TableName z
where a.UserID = z.UserID)
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;
right join
(Select max([Year]+[Month]*12) num,userid from table1 where tf=1 group by userid) a
ON (b.tf=1 and (b.[Year]+b.[Month]*12) = a.num and b.userid=a.userid)
(
UserID int,
year int,
month int,
shuju numeric(3,1),
TF int
)
goinsert into #temp 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, 0goselect R.*
from(
select UserID,max(YearMonth) YearMonth
from(
select UserID,cast((cast(Year as varchar)+'-'+cast(month as varchar)+'-1')
as datetime) YearMonth
from #temp
where TF=1
) a
group by UserID
)L,#temp R
where L.UserID=R.UserID
and Year(L.YearMonth)=R.Year
and Month(L.YearMonth)=R.Month
order by R.UserIDgo
drop table #tempgo
你的那个不对。如果有一个月份大于9,就会造成结果不对select max(yearmonth) yearmonth
from(
select [year]+[month] yearmonth
from(
select '2007' as year,'7' as 'month' union all
select '2007','12'
)a
)b其最大值是20077而不是200712,这显然是不对的。
(5,2006,6,2,0)
修改下*/
---创建测试环境
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
union all select 1,2006,7,5,1 --加此行(相同的usrid,存在相同的year和month)
union all select 3,2007,5,5,1 --加此行
---查看测试环境
select * from @t
---查询
select * from @t as a where TF = 1
and not exists
(select 1 from @t where TF = 1 and UserID = a.UserID and year + month > a.year + a.month )
and not exists
(select 1 from @t where userid=a.userid and year + month>=a.year + a.month and shuju>a.shuju) order by userid
--结果
userid year month shuju TF
----------- ----------- ----------- -------------------- -----------
1 2006 7 5.00 1
3 2007 5 5.00 1
5 2006 6 2.00 1(所影响的行数为 3 行)
问题还把大家对年份和月份取最大值进行了讨论,我的本意是要取Year,Month是年月的最大值,不只是Year最大,要把年和月加到一起取最大的年月最大。
dawugui(潇洒老乌龟)第一种方法有问题,少了几条纪录。
bill024(咖啡熊)、WangZWang(先来)的可以
njwangchuan(川儿)、oftwKLC(学习者)的方法是直接year + month>=a.year + a.month这样就可能出问题,这样是对字符串的比较了,不是日期的比较。
像liufuyahong() 的方法就不行了,你用的是循环,几百万条纪录会耗时间的。
我想问问,当一个UserID存在相同的年而不同的月时你要得是什么结果??
create table t_table(UserID int,year int,month int,shuju decimal(10,1),TF int)
insert test 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,0select b.UserID ,b.year ,b.month ,b.shuju ,b.TF from (select userid,max(ym) as ym from (select *,year+month as ym from t_table where tf=1) as a group by userid)as c
join (select *,year+month as ym from t_table where tf=1) as b on c.userid=b.userid and b.ym=c.ym