我有一张表 table1,结构如下
userid sum date
1 3000 2008-11-21
1 5000 2008-11-19
2 4000 2008-11-17
2 6000 2008-11-19
3 1000 2008-10-12
3 200 2008-11-02
我现在想实现这样的查询结果
userid sum date
1 3000 2008-11-21
2 6000 2008-11-19
3 200 2008-11-02就是userid来分组,把每个userid 对应的date最大的记录筛选出来
请教一下语句应该怎么写。
userid sum date
1 3000 2008-11-21
1 5000 2008-11-19
2 4000 2008-11-17
2 6000 2008-11-19
3 1000 2008-10-12
3 200 2008-11-02
我现在想实现这样的查询结果
userid sum date
1 3000 2008-11-21
2 6000 2008-11-19
3 200 2008-11-02就是userid来分组,把每个userid 对应的date最大的记录筛选出来
请教一下语句应该怎么写。
INSERT @TB
SELECT 1, 3000, '2008-11-21' UNION ALL
SELECT 1, 5000, '2008-11-19' UNION ALL
SELECT 2, 4000, '2008-11-17' UNION ALL
SELECT 2, 6000, '2008-11-19' UNION ALL
SELECT 3, 1000, '2008-10-12' UNION ALL
SELECT 3, 200, '2008-11-02'
SELECT * FROM @TB AS A WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE userid=A.userid AND DATE>A.DATE)
/*
userid sum date
----------- ----------- ------------------------------------------------------
1 3000 2008-11-21 00:00:00
2 6000 2008-11-19 00:00:00
3 200 2008-11-02 00:00:00
*/
DECLARE @TB TABLE(userid INT, sum INT, date SMALLDATETIME)
INSERT @TB
SELECT 1, 3000, '2008-11-21' UNION ALL
SELECT 1, 5000, '2008-11-19' UNION ALL
SELECT 2, 4000, '2008-11-17' UNION ALL
SELECT 2, 6000, '2008-11-19' UNION ALL
SELECT 3, 1000, '2008-10-12' UNION ALL
SELECT 3, 200, '2008-11-02'
SELECT A.* FROM @TB AS A JOIN (
SELECT userid,MAX(DATE) AS DATE FROM @TB GROUP BY userid
) B ON A.userid=B.userid and a.date=b.date
/*
userid sum date
----------- ----------- ------------------------------------------------------
3 200 2008-11-02 00:00:00
2 6000 2008-11-19 00:00:00
1 3000 2008-11-21 00:00:00
*/
WHERE NOT EXISTS(SELECT 1 FROM tb b WHERE B.userid=A.userid AND A.DATE<B.DATE)
INSERT @TB
SELECT 1, 3000, '2008-11-21' UNION ALL
SELECT 1, 5000, '2008-11-19' UNION ALL
SELECT 2, 4000, '2008-11-17' UNION ALL
SELECT 2, 6000, '2008-11-19' UNION ALL
SELECT 3, 1000, '2008-10-12' UNION ALL
SELECT 3, 200, '2008-11-02'
SELECT A.* FROM @TB A INNER JOIN(SELECT USERID, MAX(DATE)DATE FROM @TB GROUP BY USERID) B ON A.DATE=B.DATE AND A.USERID=B.USERID
ORDER BY A.USERID ASC
/*userid sum date
----------- ----------- ------------------------------------------------------
1 3000 2008-11-21 00:00:00
2 6000 2008-11-19 00:00:00
3 200 2008-11-02 00:00:00
*/
--drop table #t
go
create table test(userid int,sum int,date datetime)
insert into test select 1 , 3000 , ' 2008-11-21'
insert into test select 1 , 5000 , ' 2008-11-19'
insert into test select 2 , 4000 , ' 2008-11-17'
insert into test select 2 , 6000 , '2008-11-19'
insert into test select 3 , 1000 , '2008-10-12'
insert into test select 3 , 200 , ' 2008-11-02'
select *
from test
where not exists (select 1 from test t where t.userid=test.userid and t.date>test.date)
from table1,
(select max(convert(dateTime,date,101)) as Max_Time,userid as userid
group by userid
) as AA
where AA.userid = table1.userid
and AA.Max_Time= table1.date
order by AA.userid
(爱新觉罗.毓华 2007-10-23于浙江杭州)
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七,如果整行数据有重复,所有的列都相同。
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决,生成一个自增列,先对val取最大或最小,然后再通过自增列来取数据。
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
goselect * , px = identity(int,1,1) into tmp from tbselect m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)drop table tb,tmp/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数,不需要使用临时表。
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
goselect m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)drop table tb/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值(2 行受影响)
*/
SELECT userid,MAX(SUM) SUM FROM TABLE1 GROUP BY userid ) B ON A.USERID=B.USERID AND A.SUM=B.SUM
ORDER BY a.userid
INSERT #A
SELECT 1, 3000, '2008-11-21' UNION ALL
SELECT 1, 5000, '2008-11-19' UNION ALL
SELECT 2, 4000, '2008-11-17' UNION ALL
SELECT 2, 6000, '2008-11-19' UNION ALL
SELECT 3, 1000, '2008-10-12' UNION ALL
SELECT 3, 200, '2008-11-02'SELECT * FROM #A A
WHERE (SELECT COUNT(*) FROM #A WHERE ID=A.ID AND S_DATE>A.S_DATE)<1DROP TABLE #A/**(6개 행 적용됨)ID AMT S_DATE
----------- ----------- ----------
1 3000 2008-11-21
2 6000 2008-11-19
3 200 2008-11-02(3개 행 적용됨)
就会出现这样结果:
userid sum date
----------- ----------- -----------------------
1 3000 2008-11-21 00:00:00
1 2000 2008-11-21 00:00:00
2 6000 2008-11-19 00:00:00
3 200 2008-11-02 00:00:00(4 行受影响)