请教一条SQL，有难度

[asdf008]

假设表如下
NAME DT MONEY
AA 2005-01-21 1200
BB 2005-01-23 800
BB 2005-01-24 2200
AA 2005-01-26 1000
要求用一条SQL语句查询出:
AA 2005-01-21 1200
BB 2005-01-23 800
就是每种NAME日期最早的那条记录，各位帮忙啊，分不够再加
表的ID是UUID生成的char(32)

[Softlee81307]

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

[masong_1978]

select * from 表 where dt in(select dt=min(dt) from 表 group by name)

[lishengyu]

select * from t a
where not exists(select * from t where NAME=a.NAME and DT<a.DT)

[remote_peng]

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

[hillhx]

如果存在名称相同，日期也相同的数据，又想只取出其中一笔，那么用


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

[asdf008]

风云的select * from t a
where not exists(select * from t where NAME=a.NAME and DT<a.DT)
正解～ 感谢各位

[wk9388]

子莫红尘 的应该没问题，很简短

[daijingjie2002]

select * from 表 where dt in(
select dt=min(dt) from 表 group by name)

[pbsql]

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

[skyboy0720]

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

[pbsql]

或者：
select * from 表 a where dt in(select dt=min(dt) from 表 where name=a.name)

[daijingjie2002]

以上查询有一定的缺陷 如:
BB 2005-01-24 800
BB 2005-01-24 2200
所以主键最好使用integer型，由小到大自动总加
这样就可以如下
select * from 表 where id in(
select id=min(id) from 表 group by name)

[pbsql]

或者：
select * from t a
where not exists(select * from t where NAME=a.NAME and DT<a.DT)

[asdf008]

因为表数据非常多，要选一种效率最高的，dt是一个datetime字段，我先看看各位的SQL再给分，谢谢各位

[wang_jzh]

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)