select t1.name,t1.price,t1.date from tablename t1,(select name,max(date) from tablename group by name )t2 where t1.name=t2.name and t1.date=t2.date
Select A.* from TableName A Inner Join (Select name,Max([date]) As [date] from TableName Group By name) B On A.name=B.name And A.[date]=B.[date]
不好意思,看错了 楼上的有误create table t1( [name] char(5), price dec (14,2), [date] varchar(12) )insert into t1 select 'A', 0.25, '2005-06-25' union all select 'A', 0.21, '2005-06-30' union all select 'A', 0.32, '2005-06-30' union all select 'B', 0.30, '2005-07-01' union all select 'B', 0.34, '2005-08-01' union all select 'C', 0.32, '2005-05-01' --select * from t1Select a.name,min(a.price) price,a.date from t1 A Inner Join (Select name,Max([date]) As [date] from t1 Group By name) B On A.name=B.name And A.[date]=B.[date]GROUP BY a.name,a.date ORDER BY A.NAME----- name price date ----- ---------------- ------------ A .21 2005-06-30 B .34 2005-08-01 C .32 2005-05-01(所影响的行数为 3 行)
--楼上没一个对的。 --测试环境 create table #T(name varchar(2),price decimal(4,2),date datetime) insert into #T select 'A',0.25,'2005-6-25' union all select 'A',0.21,'2005-6-30' union all select 'A',0.32,'2005-6-30' union all select 'B',0.3,'2005-7-1' union all select 'B',0.34,'2005-8-1' union all select 'C',0.32,'2005-5-1' --SQL查询 select a.name, price=min(price),date=max(date) from #T a where (a.date)= (select top 1 (date) from #T where name = a.name order by date desc) group by name --结果 name price date ---- ------ ------------------------------------------------------ A .21 2005-06-30 00:00:00.000 B .34 2005-08-01 00:00:00.000 C .32 2005-05-01 00:00:00.000(所影响的行数为 3 行) --删除环境 drop table #T
from tablename t1,(select name,max(date)
from tablename
group by name
)t2
where t1.name=t2.name and t1.date=t2.date
Inner Join
(Select name,Max([date]) As [date] from TableName Group By name) B
On A.name=B.name And A.[date]=B.[date]
[name] char(5),
price dec (14,2),
[date] varchar(12) )insert into t1
select 'A', 0.25, '2005-06-25' union all
select 'A', 0.21, '2005-06-30' union all
select 'A', 0.32, '2005-06-30' union all
select 'B', 0.30, '2005-07-01' union all
select 'B', 0.34, '2005-08-01' union all
select 'C', 0.32, '2005-05-01'
--select * from t1Select a.name,min(a.price) price,a.date from t1 A
Inner Join
(Select name,Max([date]) As [date] from t1 Group By name) B
On A.name=B.name And A.[date]=B.[date]GROUP BY a.name,a.date ORDER BY A.NAME-----
name price date
----- ---------------- ------------
A .21 2005-06-30
B .34 2005-08-01
C .32 2005-05-01(所影响的行数为 3 行)
--测试环境
create table #T(name varchar(2),price decimal(4,2),date datetime)
insert into #T select 'A',0.25,'2005-6-25'
union all select 'A',0.21,'2005-6-30'
union all select 'A',0.32,'2005-6-30'
union all select 'B',0.3,'2005-7-1'
union all select 'B',0.34,'2005-8-1'
union all select 'C',0.32,'2005-5-1'
--SQL查询
select a.name,
price=min(price),date=max(date)
from #T a
where (a.date)=
(select top 1 (date) from #T where name = a.name order by date desc)
group by name
--结果
name price date
---- ------ ------------------------------------------------------
A .21 2005-06-30 00:00:00.000
B .34 2005-08-01 00:00:00.000
C .32 2005-05-01 00:00:00.000(所影响的行数为 3 行)
--删除环境
drop table #T