原有sql查出关联表数据，例如：
select id,a1,a2,a3,b1,b2,b3 from t1 inner join t2 on t1.id=t2.id
(表t1和t2为主从表，通过a1和b1关联)
查出结果是：
id a1 a2 a3 b1 b2 b3
1 a aa aaa 1 11 111
1 a aa aaa 2 22 222
2 b bb bbb 3 33 333
2 b bb bbb 4 44 444
3 c cc ccc 5 55 555
3 c cc ccc 6 66 666
我想再写个sql把原有sql嵌套进去查出下面的效果：
id a1 a2 a3 b1 b2 b3
1 a aa aaa 1 11 111
2 22 222
2 b bb bbb 3 33 333
4 44 444
3 c cc ccc 5 55 555
6 66 666
也就是把重复数据去掉，多谢了!!!
------解决方案--------------------
----------------------------
-- Author :谭磊
-- Date :2013-02-19 12:36:39
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
-- Jun 28 2012 08:36:30
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
--
----------------------------
--> 测试数据：#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([id] int,[a1] varchar(1),[a2] varchar(2),[a3] varchar(3),[b1] int,[b2] int,[b3] int)
insert #TA
select 1,'a','aa','aaa',1,11,111 union all
select 1,'a','aa','aaa',2,22,222 union all
select 2,'b','bb','bbb',3,33,333 union all
select 2,'b','bb','bbb',4,44,444 union all
select 3,'c','cc','ccc',5,55,555 union all
select 3,'c','cc','ccc',6,66,666
--------------开始查询--------------------------
select
Case when Row_Number()over(PARTITION by ID,a1,a2,a3 Order by b1) = 1 then cast(ID AS varchar(10)) else ''end ID,
Case when Row_Number()over(PARTITION by ID,a1,a2,a3 Order by b1) = 1 then cast(a1 AS varchar(10)) else ''end a1,