一个简单的SQL 查询问题（UP 给分！）

[NB他叔NC]

有如下两表
table1
RNo CId_one CId_Two CId_Three .....
1 .............
2 ..............
table2
CId CName .....
1.............
2.............
3.............
现在想查询 （RNo,CName_one,CName_two,CName_three。。）

如何写SQL 呢？？

[xiaomaoque]

这个我会呀，呵呵
select rno,cname_one,cname_two,cname_tree from table1,table2

[brook_huang]

两个表之间怎么关联呀

[sealeeship]

用join ....on.....

[奇遇]

◎_@

[ilfsm]

主　　题： MSSQL中,有位网友email问我的问题,下面是我给出的方案,大家也来看一看吧.(zhuzhichao)
作　　者： zhuzhichao (炎龙骑士团—索尔—破龙击)
等　　级：
信 誉 值： 108
所属论坛： 数据库基础
问题点数： 300
回复次数： 60
发表时间： 2002-1-30 10:03:24



问题描述:
有表Table2,其结构及内容如下:
Table2:

id name1 name2 name3 ……(列有很多)
1 aa bb cc ……
2 ii jj kk ……
3 xx yy zz ……
…………………………………………

有表Table1,其结构如下:
Table1:
id variable

现在想把Table2中的除id以外的字段名(注意,是字段名)填入Table1中的id,
从Table2中提取某一行(先假设是id为1的那一行吧)中与每个字段名对应的内容填入Table1的variable.

以id为1的那行为例,就是要达到如下的效果:
Table1:
id variable
name1 aa
name2 bb
name3 cc
……………….


我的解决方案:

declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量
declare @sql nvarchar(4000) -- 動態sql
declare @insertSql varchar(4000) -- 插入臨時表的sql
set @vVariable = ' '
if object_id('tempdb.dbo.#temp1') is not null
drop table #temp1
select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid

if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2
create table #temp2
(
id int identity,
variable varchar(10)
)
else
truncate table #temp2

select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id =
object_id('Table2') and name <> 'id' order by colid
set @vVariable = right(@vVariable,len(@vVariable) - 6)
set @sql = 'select @var = '+@vVariable+' from Table2 where id = 1'
exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@var

set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'
exec (@insertSql) -- 根據變量中的逗號分格插入臨時表

insert into Table1(id,variable)
select name,variable from #temp1,#temp2
where #temp1.id = #temp2.id

select * from Table1

[NB他叔NC]

现在把问题重新说明以下：
有如下两表
table1
RNo CId_one CId_Two CId_Three .....
1 .............
2 ..............
table2
CId CName .....
1.............
2.............
3.............

其中table1的 CId_one CId_Two CId_Three为外键；来自Table2;
现在想查询 （RNo,CName_one,CName_two,CName_three）
也就是将ID号（CId_one CId_Two CId_Three）转换为Cname1,Cname2,Cname3
如何写SQL呢？？

[liliang24]

select RNo,CName_one,CName_two,CName_three
from table1
left join table2
on table1.cID_one=table2.cID
left join table2
on table1.cID_two=table2.cID
left join table2
on talbe1.cID-three=table2.cID


You also can put the table1 in cursor and fetch cursor for table2

[SE1]

两个办法：
1、
select RNo
,(select CName from table2 where table1.CID_one=table2.CID) as CName_one
,(select CName from table2 where table1.CID_two=table2.CID) as CName_two
,(select CName from table2 where table1.CID_three=table2.CID) as CName_three
from table1
2、
select t0.RNo,t1.cName as CName_one,t2.cName as CName_two,t3.cName as CName_three
from table1 t0
inner join
table2 t1 on t0.cID_one=t1.cid
inner join
table2 t2 on t0.cID_two=t2.cid
inner join
table2 t3 on t0.cID_three=t3.cid

[奇遇]

说清楚问题，好办事，是不是想要这个？
SELECT a.rno AS rno, b.cname AS cname_one, c.cname AS cname_two,d.cname as cname_three
FROM tab1 a INNER JOIN
tab2 b ON a.cid_one = b.cid INNER JOIN
tab2 c ON a.cid_two = c.cid INNER JOIN
tab2 d ON a.cid_three = d.cid

http://www.csdn.net/expert/topic/1085/1085046.xml?temp=.8072321

[Yang_]

很多也是有限而且已知的，否则这样的表结构很难查询，只能：

select RNo,t1.cname as CName_one,t2.cname as CName_two,t2.cname as CName_three,...
from table1 a
table2 t1,
table2 t2,
table2 t3,
...
where a.Cid_One=t1.Cid
and a.Cid_Two=t2.Cid
and a.CId_Three=t3.Cid
...