IP A B iA iB uA uB
1 a b 20 30 40 50
1 a b 22 32 null null下面那两条后面的字段为什么是空的?
1 a b 20 30 40 50
1 a b 22 32 null null下面那两条后面的字段为什么是空的?
解决方案 »
- 数据类型 varchar 和 varchar 在 modulo 运算符中不兼容。
- 关于计算数据库平均值的问题
- sqlserver2008 X86可以安装在WIN2008 X64的OS上吗?
- SQL SERVER 字符转日期函数
- |zyciis| 麻烦大家帮我分析一下,这两个对表的锁定是不是相同 谢谢 急
- 请问:我在打开网页时报错,原因是“该帐户的密码必须更改。”,想改用户密码策略,sql 又报错!
- 再求查询几个并列条件的SQL语句写法?(再次发贴,多多给分)
- 求这个存储过程怎么写?(循环)
- telnet 外网不通
- 触发器问题
- 求一段Transact-SQL语句
- 为什么局域网内的SQL Server 2005 无法通过机器名称访问
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, 结果是对的, 但就是不明白原理, 换成我的表后,还是不能提到我的结果