表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/* 得到结果如下: 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 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 @t3select id=identity(int,1,1), a.*, b.ia,b.ib, c.ua,c.ub into #t from @t1 a left join @t2 b on 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 endfrom #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)
非常感谢大家的支持, Sorry! 我忘记了一点: ---------------------------------------- 表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 4 g h ----------------------------------------------------------------- 要求结果如下: 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 ==========================================================
非常感谢大家的支持, Sorry! 我忘记了一点: ---------------------------------------- 表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 3a 3b 3 42 52 4 g h 3 43 53 ----------------------------------------------------------------- 要求结果如下: 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 3a 3b 42 52 4 e f null null 43 53==========================================================注意最后 IP 3 行 如果按上述各位的方法做,其如果为 1 a b 20 30 40 50 1 a b 22 32 NULL NULL 2 c d NULL NULL 41 51 3 e f 3 3 42 52 3 e f 3 3 43 53 4 h g NULL NULL NULL NULL 不是我想要的结果, 请重新帮我考虑完善点, 谢谢! 谢谢
非常感谢大家的支持, Sorry! 我忘记了一点: ---------------------------------------- 表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 3a 3b 3 42 52 4 g h 3 43 53 ----------------------------------------------------------------- 要求结果如下: 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 3a 3b 42 52 3 e f null null 43 53==========================================================注意最后 IP 3 行 如果按上述各位的方法做,其如果为 1 a b 20 30 40 50 1 a b 22 32 NULL NULL 2 c d NULL NULL 41 51 3 e f 3 3 42 52 3 e f 3 3 43 53 4 h g NULL NULL NULL NULL 不是我想要的结果, 请重新帮我考虑完善点, 谢谢! 谢谢 ==================== 晕哟, 应该以此为准, 文本框中写这个描述东西,就是不方便
create table tb1(IP int, A varchar(10), B varchar(10)) insert into tb1 values(1, 'a', 'b') insert into tb1 values(2, 'c', 'd') insert into tb1 values(3, 'e', 'f') insert into tb1 values(4, 'g', 'h') create table tb2(IP int, iA varchar(10), iB varchar(10)) insert into tb2 values(1, '20', '30') insert into tb2 values(1, '22', '32') insert into tb2 values(3, '3a', '3b') create table tb3(IP int, uA varchar(10), uB varchar(10)) insert into tb3 values(1, '40', '50') insert into tb3 values(2, '41', '51') insert into tb3 values(3, '42', '52') insert into tb3 values(3, '43', '53') goselect tb1.* , tb2.ia,tb2.ib,tb3.ua,tb3.ub from tb1 left join tb2 on tb1.ip = tb2.ip left join tb3 on tb1.ip = tb3.ipdrop table tb1,tb2,tb3/* IP A B ia ib ua ub ----------- ---------- ---------- ---------- ---------- ---------- ---------- 1 a b 20 30 40 50 1 a b 22 32 40 50 2 c d NULL NULL 41 51 3 e f 3a 3b 42 52 3 e f 3a 3b 43 53 4 g h NULL NULL NULL NULL(所影响的行数为 6 行)*/
--看數據,表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 @T3Select 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.IPDrop Table #T2, #T3 --結果 /* 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 3a 3b 42 52 3 e f NULL NULL 43 53 */
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/*
得到结果如下:
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 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
*/
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 @t3select id=identity(int,1,1),
a.*,
b.ia,b.ib,
c.ua,c.ub
into #t
from @t1 a
left join @t2 b on 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 endfrom #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)
----------------------------------------
表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
4 g h
-----------------------------------------------------------------
要求结果如下:
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 3a 3b 3 42 52
4 g h 3 43 53
-----------------------------------------------------------------
要求结果如下:
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 3a 3b 42 52
4 e f null null 43 53==========================================================注意最后 IP 3 行
如果按上述各位的方法做,其如果为
1 a b 20 30 40 50
1 a b 22 32 NULL NULL
2 c d NULL NULL 41 51
3 e f 3 3 42 52
3 e f 3 3 43 53
4 h g NULL NULL NULL NULL
不是我想要的结果,
请重新帮我考虑完善点, 谢谢! 谢谢
----------------------------------------
表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 3a 3b 3 42 52
4 g h 3 43 53
-----------------------------------------------------------------
要求结果如下:
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 3a 3b 42 52
3 e f null null 43 53==========================================================注意最后 IP 3 行
如果按上述各位的方法做,其如果为
1 a b 20 30 40 50
1 a b 22 32 NULL NULL
2 c d NULL NULL 41 51
3 e f 3 3 42 52
3 e f 3 3 43 53
4 h g NULL NULL NULL NULL
不是我想要的结果,
请重新帮我考虑完善点, 谢谢! 谢谢
====================
晕哟, 应该以此为准, 文本框中写这个描述东西,就是不方便
insert into tb1 values(1, 'a', 'b')
insert into tb1 values(2, 'c', 'd')
insert into tb1 values(3, 'e', 'f')
insert into tb1 values(4, 'g', 'h')
create table tb2(IP int, iA varchar(10), iB varchar(10))
insert into tb2 values(1, '20', '30')
insert into tb2 values(1, '22', '32')
insert into tb2 values(3, '3a', '3b')
create table tb3(IP int, uA varchar(10), uB varchar(10))
insert into tb3 values(1, '40', '50')
insert into tb3 values(2, '41', '51')
insert into tb3 values(3, '42', '52')
insert into tb3 values(3, '43', '53')
goselect tb1.* , tb2.ia,tb2.ib,tb3.ua,tb3.ub from tb1
left join tb2 on tb1.ip = tb2.ip
left join tb3 on tb1.ip = tb3.ipdrop table tb1,tb2,tb3/*
IP A B ia ib ua ub
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 a b 20 30 40 50
1 a b 22 32 40 50
2 c d NULL NULL 41 51
3 e f 3a 3b 42 52
3 e f 3a 3b 43 53
4 g h NULL NULL NULL NULL(所影响的行数为 6 行)*/
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 @T3Select 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.IPDrop Table #T2, #T3
--結果
/*
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 3a 3b 42 52
3 e f NULL NULL 43 53
*/
------------------
非常感谢,其实 表2 和 表3 都是有主键的, 表2 主键:IDb , 表3 主键是: IDc,
表1.IP->表2.IP, 表1.IP->表3.IP
======================================================
实际数据库,所有主键都不是数字,而是36位字符串,只是为简单求助而少写了点,希望大家理解! 在现实生活中 表1为业务表, 表2为收入 表3为支出, 客户出此结果,希望大家在考虑时,不要视主键为Int递增值,
非常感谢 paoluo, 结果是对的, 但就是不明白原理, 换成我的表后,还是不能提到我的结果