假设有两张表A 和B
A表
GameID GameName
1 aaa
2 bbb
3 ccc
.. ...B表
RoomID GameID RoomName
1 2 ddd
2 2 ggg
3 3 fff
4 1 hhh
5 3 ttt
.. .. ...用slq 语句 查询B表中所有的信息,把GameID转换成A表的GameName 我记得是用存储过程吗???
A表
GameID GameName
1 aaa
2 bbb
3 ccc
.. ...B表
RoomID GameID RoomName
1 2 ddd
2 2 ggg
3 3 fff
4 1 hhh
5 3 ttt
.. .. ...用slq 语句 查询B表中所有的信息,把GameID转换成A表的GameName 我记得是用存储过程吗???
(
select 1,'aaa'
union all select 2,'bbb'
union all select 3,'ccc'
),
tb(RoomID, GameID,RoomName) as
(
select 1,2,'ddd'
union all select 2,2,'ggg'
union all select 3,3,'fff'
union all select 4,1,'hhh'
union all select 5,3,'ttt'
)
select RoomID,a.GameName,b.RoomName
from tb b
left join ta a on b.GameID=a.GameID/*
RoomID GameName RoomName
-------------------------------
1 bbb ddd
2 bbb ggg
3 ccc fff
4 aaa hhh
5 ccc ttt
*/
create table #ta(GameID int,GameName varchar(50))
insert into #ta
select 1,'aaa'
union all select 2,'bbb'
union all select 3,'ccc'create table #tb(RoomID int, GameID int,RoomName varchar(50))
insert into #tb
select 1,2,'ddd'
union all select 2,2,'ggg'
union all select 3,3,'fff'
union all select 4,1,'hhh'
union all select 5,3,'ttt'select RoomID,a.GameName,b.RoomName
from #tb b
left join #ta a on b.GameID=a.GameID
--------------------------------------------------------------RoomID GameName RoomName
----------- -------------------------------------------------- --------------------------------------------------
1 bbb ddd
2 bbb ggg
3 ccc fff
4 aaa hhh
5 ccc ttt(5 行受影响)
假设数据库有一百万条数据
tb(RoomID, GameID,RoomName)
as(
select 1,2,'ddd'
union all select 2,2,'ggg'
union all select 3,3,'fff'
union all select 4,1,'hhh'
。。
。
)你这里还要写一百完次吗?并且我也不知道RoomID GameID RoomName 是什么 还能这样写吗??
from tb b
left join ta a on b.GameID=a.GameID 这段
exec sp_addlinkedserver '链接名称',' ','SQLOLEDB','192.168.xx.xxx\SQLEXPRESS2008R2'
exec sp_addlinkedsrvlogin '链接名称','false',NULL,'sa','xxxxxxx'--查询
select * from 链接名称.数据库名.dbo.表名