表a:
ip name
1 bear
2 lime
3 gear
4 peer
表b:
source ip destination ip
1 2
1 3
2 1
2 3
3 4
............
希望达到的是这样的表C:
source ip (s)name destination ip (d)name
1 bear 2 lime
1 bear 3 gear
2 bear 1 bear
2 lime 3 gear
3 gear 4 peer
...........请高手指教,跪谢!
ip name
1 bear
2 lime
3 gear
4 peer
表b:
source ip destination ip
1 2
1 3
2 1
2 3
3 4
............
希望达到的是这样的表C:
source ip (s)name destination ip (d)name
1 bear 2 lime
1 bear 3 gear
2 bear 1 bear
2 lime 3 gear
3 gear 4 peer
...........请高手指教,跪谢!
解决方案 »
- MSSQL2000,如何利用数据库日志还原到前天的数据呢?
- 如何写一个返回求和的函数?
- 这个SQL我怎么错了,应该怎么写?
- 问一个很幼稚的数据COPY问题
- MSSQL2000对文件的操作,求高手!
- 可以对装在WIN98上的SQLSERVER个人版用PB编程吗?
- 如何迅速删除sql server中的几十万条数据
- 在access中datediff函数问题,容易回答,快来抢分。
- sql server 的datetime类型 如何 like 'date%' ,比如数据库 datetime 为 '2001-01-01 09:09:09' 如何找出 日期为'2001-01-01'的纪录
- 这两个语句,怎么组合在一起?
- 如何将string类型数据插入到SQL中Varchar(255)中!
- 有没有高人做过SQL Server的数据订阅
insert @ta select 1 ,'bear'
insert @ta select 2 ,'lime'
insert @ta select 3 ,'gear'
insert @ta select 4 ,'peer'declare @tb table([source ip] int, [destination ip] int)
insert @tb select 1 ,2
insert @tb select 1 ,3
insert @tb select 2 ,1
insert @tb select 2 ,3
insert @tb select 3 ,4select b.[source ip],a.name,b.[destination ip],c.name from @ta a,@ta c,@tb b where a.ip=b.[source ip] and c.ip=b.[destination ip]
source ip name destination ip name
----------- ---------- -------------- ----------
2 lime 1 bear
1 bear 2 lime
1 bear 3 gear
2 lime 3 gear
3 gear 4 peer(所影响的行数为 5 行)
join a c on b.destinationip=c.ip
insert a select 1,'bear'
union all select 2,'lime'
union all select 3,'gear'
union all select 4,'peer'create table b(source int,ip1 varchar(20),destination int,ip2 varchar(20))
insert b select 1,null,2,null
union all select 1,null,3,null
union all select 2,null,1,null
union all select 2,null,3,null
union all select 3,null,4,nullselect z.source,x.name,z.destination,y.name from a x,a y,b z where x.ip=z.source and y.ip=z.destination
还有表a和b都是非常大的表手动逐个输入数据比较困难。
insert @ta select 1,'bear'
insert @ta select 2,'lime'
insert @ta select 3,'gear'
insert @ta select 4,'peer'declare @tb table([source ip] int, [destination ip] int)
insert @tb select 1,2
insert @tb select 1,3
insert @tb select 2,1
insert @tb select 2,3
insert @tb select 3,4select b.[source ip],a.name,b.[destination ip],c.name into 新表 from @ta a,@ta c,@tb b where a.ip=b.[source ip] and c.ip=b.[destination ip]如果要生成表已经存在,可以只添加数据,使用下面的语句:
insert into 新表 select b.[source ip],a.name,b.[destination ip],c.name from @ta a,@ta c,@tb b where a.ip=b.[source ip] and c.ip=b.[destination ip]
create table #a([ip] int,[name] char(5))
insert #a select 1, 'bear'
insert #a select 2, 'lime'
insert #a select 3, 'gear'
insert #a select 4, 'peer'
insert #b select 1, 2
insert #b select 1, 3
insert #b select 2, 1
insert #b select 2, 3
insert #b select 3, 4
select #b.[source ip] as [source ip],(select #a.[name] from #a where #b.[source ip]=#a.[ip]) as [source name],
#b.[destination ip] as [destination ip],(select #a.[name] from #a where #b.[destination ip]=#a.[ip]) as [destination name]
into [#你要的表]
from #b
select * from [#你要的表]~~~
source ip source name destination ip destination na
1 bear 2 lime
1 bear 3 gear
2 lime 1 bear
2 lime 3 gear
3 gear 4 peer