SELECT t1.id,t1.name,a.alias FROM t1 left OUTER JOIN (select id , min(alias) as alias from t2 group by id) a ON t1.id=a.id
这个得到的1 surf ice 2 chen NULLice 是 (1,'water'); (1,'ice'); 两条之中最小的 也就是得到 ice如果你要是得到第一条的话 在sql server 中可以这样做 --增加序列行 ALTER TABLE t2 ADD index_c int IDENTITY(1,1) --查询 SELECT t1.id,t1.name,a.alias FROM t1 left OUTER JOIN (select id , alias from t2 b where not exists (select 1 from t2 where index_c <b.index_c and id = b.id) ) a ON t1.id=a.id/* 结果 1 surf water 2 chen NULL */ --删除序列行 ALTER TABLE t2 DROP COLUMN index_c
left OUTER JOIN
(select id , min(alias) as alias from t2 group by id) a
ON t1.id=a.id
2 chen NULLice 是 (1,'water'); (1,'ice'); 两条之中最小的
也就是得到 ice如果你要是得到第一条的话
在sql server 中可以这样做 --增加序列行
ALTER TABLE t2 ADD index_c int IDENTITY(1,1)
--查询
SELECT t1.id,t1.name,a.alias FROM t1
left OUTER JOIN
(select id , alias from t2 b where not exists (select 1 from t2 where index_c <b.index_c and id = b.id) ) a
ON t1.id=a.id/* 结果
1 surf water
2 chen NULL
*/
--删除序列行
ALTER TABLE t2 DROP COLUMN index_c