三表之连要取如下结果, 如何写SQL语句
表1 IP A B 表2 IP iA iB 表3 IP uA uB
1 a b 1 20 30 1 40 50
2 c d 1 22 32 2 41 51
3 e f 3 42 52
-----------------------------
要求结果如下:
IP A B iA iB uA uB
1 a b 20 30 40 50
1 a b 22 32 null null
2 c d null null 41 51
3 e f null null 42 52
==========================================================
请高手支持... 谢谢 !
[解决办法]
表1 IP A B 表2 IP iA iB 表3 IP uA uB
1 a b 1 20 30 1 40 50
2 c d 1 22 32 2 41 51
3 e f 3 42 52
-----------------------------
要求结果如下:
IP A B iA iB uA uB
1 a b 20 30 40 50
1 a b 22 32 null null
2 c d null null 41 51
3 e f null null 42 52
==========================================================
请高手支持... 谢谢 !
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
我不时高手... 抱歉 !
declare @T1 table(IP varchar(2000),A varchar(2000),B varchar(2000))
insert @T1
select '1 ', 'a ', 'b ' union all
select '2 ', 'c ', 'd ' union all
select '3 ', 'e ', 'f '
declare @T2 table(IP varchar(2000),iA varchar(2000),iB varchar(2000))
insert @T2
select '1 ', '20 ', '30 ' union all
select '1 ', '22 ', '32 ' union all
select '3 ', '42 ', '52 '
declare @T3 table(IP varchar(2000),uA varchar(2000),uB varchar(2000))
insert @T3
select '1 ', '40 ', '50 ' union all
select '2 ', '41 ', '51 '
select * from (select a.*, b.uA, b.uB from (select a.*, b.iA, b.iB from @T1 a left join @T2 b on a.IP = b.IP and b.IP <> 3) a join (select * from @T3 union all select 1, null, null union all select * from @T2 where IP = 3) b on a.IP = b.IP) a where (IP = 1 and iB = 30 and uA is not null) or (IP = 1 and iB = 32 and uA is null) or IP <> 1 order by IP, uA desc
/*
得到结果如下:
IPABiAiBuAuB
1ab20304050
1ab2232NULLNULL
2cdNULLNULL4151
3efNULLNULL4252
*/
[解决办法]
----创建测试数据
declare @t1 table(IP int,A varchar(10),B varchar(10))
insert @t1
select 1, 'a ', 'b ' union all
select 2, 'c ', 'd ' union all
select 3, 'e ', 'f '
declare @t2 table(IP int,iA int,iB int)
insert @t2
select 1,20,30 union all
select 1,22,32
declare @t3 table(IP int,uA int,uB int)
insert @t3
select 1,40,50 union all
select 2,41,51 union all
select 3,42,52
----查询
SELECT a.*, b.iA,b.iB,
uA = case when b.iA is null or b.iA = (select top 1 iA from @t2 where IP = c.IP) then c.uA else NULL end ,
uB = case when b.iA is null or b.iA = (select top 1 iA from @t2 where IP = c.IP) then c.uB else NULL end
FROM @t1 as a
LEFT JOIN @t2 as b ON a.IP = b.IP
LEFT JOIN @t3 as c ON a.IP = c.IP
/*结果
IP A B iA iB uA uB
-----------------------------------------------------
1 a b 20 30 40 50
1 a b 22 32 null null
2 c d null null 41 51
3 e f null null 42 52
*/
[解决办法]
借樓上數據一用
declare @t1 table(IP int,A varchar(10),B varchar(10))
insert @t1
select 1, 'a ', 'b ' union all
select 2, 'c ', 'd ' union all
select 3, 'e ', 'f '
declare @t2 table(IP int,iA int,iB int)
insert @t2
select 1,20,30 union all
select 1,22,32
declare @t3 table(IP int,uA int,uB int)
insert @t3
select 1,40,50 union all
select 2,41,51 union all
select 3,42,52
--select * from @t1
--select * from @t2
--select * from @t3
select id=identity(int,1,1),
a.*,
b.ia,b.ib,
c.ua,c.ub
into #t
from @t1 a
left join @t2 bon a.ip=b.ip
left join @t3 c on a.ip=c.ip
select
t1.ip,t1.a,t1.b,
t1.ia,t1.ib,
ua=case when exists(select 1 from #t where t1.ip=ip and t1.id <id)then null else ua end,
ub=case when exists(select 1 from #t where t1.ip=ip and t1.id <id)then null else ub end
from #t t1
ip a b ia ib ua ub
----------- ---------- ---------- ----------- ----------- ----------- -----------
1 a b 22 32 40 50
1 a b 20 30 NULL NULL
2 c d NULL NULL 41 51
3 e f NULL NULL 42 52
(4 row(s) affected)
[解决办法]
--看數據，表2和表3中似乎沒有主鍵，需要借助下臨時表
--創建測試環境
Declare @T1 Table(IP Int, A Varchar(10), B Varchar(10))
Insert @T1 Select 1, 'a ', 'b '
Union All Select 2, 'c ', 'd '
Union All Select 3, 'e ', 'f '
Union All Select 4, 'g ', 'h '
Declare @T2 Table(IP Int, iA Varchar(10), iB Varchar(10))
Insert @T2 Select 1, '20 ', '30 '
Union All Select 1, '22 ', '32 '
Union All Select 3, '3a ', '3b '
Declare @T3 Table(IP Int, uA Varchar(10), uB Varchar(10))
Insert @T3 Select 1, '40 ', '50 '
Union All Select 2, '41 ', '51 '
Union All Select 3, '42 ', '52 '
Union All Select 3, '43 ', '53 '
--測試
Select ID = Identity(Int, 1, 1), * Into #T2 From @T2
Select ID = Identity(Int, 1, 1), * Into #T3 From @T3
Select A.IP, A.A, A.B, B.iA, B.iB, C.uA, C.uB
From (Select OrderID = (Select Count(ID) From #T2 Where IP = T2.IP And ID <= T2.ID), * From #T2 T2) B
Full Join
(Select OrderID = (Select Count(ID) From #T3 Where IP = T3.IP And ID <= T3.ID), * From #T3 T3) C
On B.IP = C.IP And B.OrderID = C.OrderID
Inner Join @T1 A
On A.IP = IsNull(B.IP, C.IP)
Order By
A.IP
Drop Table #T2, #T3
--結果
/*
IPABiAiBuAuB
1ab20304050
1ab2232NULLNULL
2cdNULLNULL4151
3ef3a3b4252
3efNULLNULL4353
*/
