SELECT T.* FROM TB t WHERE 入库时间 = (select max( 入库时间) from tb where 编码 = t.编码)
select top 2 * from db order by 入库时间 desc
SELECT * FROM TB A WHERE NOT EXISTS(SELECT 1 FROM TB WHERE 编码=A.编码 AND 入库时间>A.入库时间 )
create table tb(编码 int, 入库时间 datetime, 金额 int) insert into tb values(101 , '2008/01/12' , 120 ) insert into tb values(101 , '2008/01/13' , 130 ) insert into tb values(101 , '2008/02/18' , 150 ) insert into tb values(102 , '2008/01/13' , 170 ) insert into tb values(102 , '2008/02/15' , 120 ) insert into tb values(102 , '2008/03/17' , 190 )SELECT T.* FROM TB t WHERE 入库时间 = (select max( 入库时间) from tb where 编码 = t.编码) order by t.编码drop table tb/*编码 入库时间 金额 ----------- ------------------------------------------------------ ----------- 101 2008-02-18 00:00:00.000 150 102 2008-03-17 00:00:00.000 190(所影响的行数为 2 行)*/
就这几种方法,你自己选择.--一、按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
create table tb(编码 int, 入库时间 datetime, 金额 int) insert into tb values(101 , '2008/01/12' , 120 ) insert into tb values(101 , '2008/01/13' , 130 ) insert into tb values(101 , '2008/02/18' , 150 ) insert into tb values(102 , '2008/01/13' , 170 ) insert into tb values(102 , '2008/02/15' , 120 ) insert into tb values(102 , '2008/03/17' , 190 )select 编码,入库时间,金额 from( select 编码,入库时间,金额,row_number() over(partition by 编码 order by 入库时间 desc) as cn from tb) as b where cn = 1/*----------- 101 2008-02-18 00:00:00.000 150 102 2008-03-17 00:00:00.000 190
select t.* from tb as t where 编码=(select t.编码 from tb as t where 编码=t.编码)order by t.编码 desc
纠正一下 : 上一个 这个可以通过 select t.* from tb t where 入库时间 in(select top 2 入库时间 from tb order by 入库时间 desc)
select top 2 * from tb order by 入库时间 desc
select 编码,max(入库时间),max(金额) from 表名 group by 编码
三楼的显然错了赛 select top 2 * from db order by 入库时间 desc 这里有一个同名取大的问题的么
SELECT T.* FROM TB t WHERE 入库时间 = (select max( 入库时间) from tb where 编码 = t.编码)
insert into tb values(101 , '2008/01/12' , 120 )
insert into tb values(101 , '2008/01/13' , 130 )
insert into tb values(101 , '2008/02/18' , 150 )
insert into tb values(102 , '2008/01/13' , 170 )
insert into tb values(102 , '2008/02/15' , 120 )
insert into tb values(102 , '2008/03/17' , 190 )SELECT T.* FROM TB t WHERE 入库时间 = (select max( 入库时间) from tb where 编码 = t.编码) order by t.编码drop table tb/*编码 入库时间 金额
----------- ------------------------------------------------------ -----------
101 2008-02-18 00:00:00.000 150
102 2008-03-17 00:00:00.000 190(所影响的行数为 2 行)*/
--方法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
create table tb(编码 int, 入库时间 datetime, 金额 int)
insert into tb values(101 , '2008/01/12' , 120 )
insert into tb values(101 , '2008/01/13' , 130 )
insert into tb values(101 , '2008/02/18' , 150 )
insert into tb values(102 , '2008/01/13' , 170 )
insert into tb values(102 , '2008/02/15' , 120 )
insert into tb values(102 , '2008/03/17' , 190 )select 编码,入库时间,金额 from(
select 编码,入库时间,金额,row_number() over(partition by 编码 order by 入库时间 desc) as cn from tb)
as b where cn = 1/*-----------
101 2008-02-18 00:00:00.000 150
102 2008-03-17 00:00:00.000 190
select t.* from tb as t where 编码=(select t.编码 from tb as t where 编码=t.编码)order by t.编码 desc
上一个
这个可以通过
select t.* from tb t where 入库时间 in(select top 2 入库时间 from tb order by 入库时间 desc)
这里有一个同名取大的问题的么