假如有2张表test1(id,name) test2(id,name1)
test1 数据
1 a
2 a
3 b
test2数据
1 d
2 e
3 c
test1 test2通过id,关联,
我想要这样结果
test1里面的 name不重复,出来结果只是
1 a,
2 bselect distinct a.name,a.id
from test1 a join test2 b on a.id=b.id
这样好像不可以
test1 数据
1 a
2 a
3 b
test2数据
1 d
2 e
3 c
test1 test2通过id,关联,
我想要这样结果
test1里面的 name不重复,出来结果只是
1 a,
2 bselect distinct a.name,a.id
from test1 a join test2 b on a.id=b.id
这样好像不可以
INSERT INTO @test1
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'b'DECLARE @test2 TABLE(id INT,name NVARCHAR(10))
INSERT INTO @test2
SELECT 1,'d' UNION ALL
SELECT 2,'e' UNION ALL
SELECT 3,'c'
SELECT b.id,b.name,c.name
FROM (SELECT * FROM @test1 a WHERE NOT EXISTS(SELECT 1 FROM @test1 WHERE name=a.name AND id<a.id)) b
LEFT JOIN @test2 c
ON b.id=c.id
id name name
----------- ---------- ----------
1 a d
3 b c
--是这样吗?
(2 row(s) affected)
DECLARE @test1 TABLE(id INT,name NVARCHAR(10))
INSERT INTO @test1
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'b'DECLARE @test2 TABLE(id INT,name NVARCHAR(10))
INSERT INTO @test2
SELECT 1,'d' UNION ALL
SELECT 2,'e' UNION ALL
SELECT 3,'c'select ROW_NUMBER() OVER(ORDER BY bb.name) AS id,bb.*from
(select distinct a.name from @test1 a join @test2 b on a.id=b.id )bb/*
(3 row(s) affected)
id name
-------------------- ----------
1 a
2 b
*/
distinct(去掉重复)
distinct(去掉重复)
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html