select t.* from t,(select NAME,DT=min(DT) from t group by NAME) a where t.NAME=a.NAME and t.DT=a.DT
SELECT A.* FROM 表 A JOIN (SELECT NAME,MIN(DT) AS DT FROM 表 GROUP BY NAME)B ON A.NAME=B.NAME AND A.DT=B.DT
或者: select * from 表 a where dt in(select dt=min(dt) from 表 where name=a.name)
以上查询有一定的缺陷 如: BB 2005-01-24 800 BB 2005-01-24 2200 所以主键最好使用integer型,由小到大自动总加 这样就可以如下 select * from 表 where id in( select id=min(id) from 表 group by name)
或者: select * from t a where not exists(select * from t where NAME=a.NAME and DT<a.DT)
select t.* from t,(select NAME,min(DT) from t group by NAME) a where t.NAME=a.NAME and t.DT=a.DT
select * from 表 a where dt = (select top 1 dt from 表 where name=a.name order by dt)
select t.* from t,(select NAME,DT=min(DT) from t group by NAME) a where t.NAME=a.NAME and t.DT=a.DT
select * from 表 where dt in(select dt=min(dt) from 表 group by name)
select * from t a where not exists(select * from t where NAME=a.NAME and DT<a.DT)
CREATE TABLE TEST(NAME NVARCHAR(2), DT DATETIME, MM INT ) INSERT INTO TEST SELECT 'AA','2005-01-21','1200' UNION SELECT 'BB','2005-01-23','800' UNION SELECT 'BB','2005-01-24','2200' UNION SELECT 'AA','2005-01-26','1000' UNION SELECT 'AA','2005-01-19','500' UNION SELECT 'BB','2005-01-17','300' UNION SELECT 'DD','2005-01-17','300' SELECT t1.* FROM TEST T1 INNER JOIN (SELECT [NAME],MIN(DT)AS DT FROM TEST GROUP BY [NAME]) AS T2 ON T1.[NAME]=T2.[NAME] AND T1.DT=T2.DT drop table test
如果存在名称相同,日期也相同的数据,又想只取出其中一笔,那么用 select * from ( select NAME,DT ,max(money) as money from tb1 group by NAME,DT ) t1 (select name,min(dt) as dt from tb1 group by name) t2 where t1.name = t2.name and t1.dt = t2.dt
风云的select * from t a where not exists(select * from t where NAME=a.NAME and DT<a.DT) 正解~ 感谢各位
from t,(select NAME,DT=min(DT) from t group by NAME) a
where t.NAME=a.NAME and t.DT=a.DT
(SELECT NAME,MIN(DT) AS DT FROM 表 GROUP BY NAME)B
ON A.NAME=B.NAME AND A.DT=B.DT
select * from 表 a where dt in(select dt=min(dt) from 表 where name=a.name)
BB 2005-01-24 800
BB 2005-01-24 2200
所以主键最好使用integer型,由小到大自动总加
这样就可以如下
select * from 表 where id in(
select id=min(id) from 表 group by name)
select * from t a
where not exists(select * from t where NAME=a.NAME and DT<a.DT)
from t,(select NAME,min(DT) from t group by NAME) a
where t.NAME=a.NAME and t.DT=a.DT
from t,(select NAME,DT=min(DT) from t group by NAME) a
where t.NAME=a.NAME and t.DT=a.DT
where not exists(select * from t where NAME=a.NAME and DT<a.DT)
DT DATETIME,
MM INT
)
INSERT INTO TEST SELECT 'AA','2005-01-21','1200' UNION
SELECT 'BB','2005-01-23','800' UNION
SELECT 'BB','2005-01-24','2200' UNION
SELECT 'AA','2005-01-26','1000' UNION
SELECT 'AA','2005-01-19','500' UNION
SELECT 'BB','2005-01-17','300' UNION
SELECT 'DD','2005-01-17','300' SELECT t1.* FROM TEST T1 INNER JOIN (SELECT [NAME],MIN(DT)AS DT FROM TEST GROUP BY [NAME]) AS T2 ON T1.[NAME]=T2.[NAME] AND T1.DT=T2.DT
drop table test
select *
from ( select NAME,DT ,max(money) as money
from tb1
group by NAME,DT
) t1
(select name,min(dt) as dt from tb1 group by name) t2
where t1.name = t2.name
and t1.dt = t2.dt
where not exists(select * from t where NAME=a.NAME and DT<a.DT)
正解~ 感谢各位