升了两个星星 散分！！
有时候回答有时候蹭分，不对之处，大家多多包涵！！
顺一个问题吧：
A表：
id name
1 aspx
2 psp
3 jsp
B表:
guid cid gid
1111 1 2
2222 2 3
要求用一个SQL语句得到如下
guid cname gname
1111 aspx psp
2222 psp jsp
答对者另开帖，重重有赏-_-!!!
[解决办法]
哦，恭喜:)
[解决办法]
抢不到SF
BD也好
恭喜
[解决办法]
CREATE TABLE #TMPA
(ID int,
[NAME] varchar(20))
INSERT INTO #TMPA VALUES (1, 'ASPX ')
INSERT INTO #TMPA VALUES (2, 'PSP ')
INSERT INTO #TMPA VALUES (3, 'JSP ')
CREATE TABLE #TMPB
(GUID varchar(40),
CID int,
GID int)
INSERT INTO #TMPB VALUES ( '1111 ',1,2)
INSERT INTO #TMPB VALUES ( '2222 ',2,3)
SELECT * FROM #TMPA
SELECT * FROM #TMPB
SELECT GUID, C.[NAME] AS CID, G.[NAME] AS GID
FROM #TMPB
JOIN #TMPA C ON #TMPB.CID=C.ID
JOIN #TMPA G ON #TMPB.GID=G.ID
DROP TABLE #TMPA
DROP TABLE #TMPB
[解决办法]
恭喜
[解决办法]
运行结果
1111ASPXPSP
2222PSPJSP
[解决办法]
顶下,也接点分
[解决办法]
接分
[解决办法]
前排支持，努力升星中
[解决办法]
向ezhuyin(碧海蓝天) 学习
jf
[解决办法]
接点分
[解决办法]
jf
[解决办法]
select guid,(select name from A where id=B.cid) as cname,(select name from A where id=B.gid) as gname from b
[解决办法]
接分
[解决办法]
接分
[解决办法]
j100f
[解决办法]
select b.guid,(select name from a where a.id=b.cid) as CID, (select name from a where a.id=b.gid) as GID from b
[解决办法]
select b.guid,a1.name as cname ,a2.name as gname from b left join a a1 on b.cid=a1.id left join a a2 on b.gid=a2.id
也可
[解决办法]
恭喜
[解决办法]
恭喜，接分
[解决办法]
接。。。。。。分。。。。。。了。。。
[解决办法]
select t1.guid,t2.name cname,t3.name gname from tableB t1
join tableA t2 on t1.cid=t2.id
join tableA t3 on t1.gid=t3.id;
[解决办法]
忘了恭喜楼主了，呵呵，恭喜，接分！！
[解决办法]
接分啊
[解决办法]
....-.-
[解决办法]
恭喜
[解决办法]
恭喜 & 接分 !
[解决办法]
JF
[解决办法]
up
[解决办法]
jf
[解决办法]
JF...
来抢你的分来了。。。
[解决办法]
jf
[解决办法]
恭喜恭喜...
[解决办法]
恭喜
[解决办法]
select guid,(select name from T1 where id=T2.cid) as cname,(select name from T1 where id=T2.gid) as gname from T2
楼住给分吧...
[解决办法]
jf
[解决办法]
gxgx
请问lz现在包养了两只猩猩有何感想
[解决办法]
再接再厉 LZ 顶
[解决办法]
俩星星爽吗?
[解决办法]
两个行星需要多少时间
[解决办法]
jf
[解决办法]
看看能不能混点分用！
[解决办法]
接分
[解决办法]
gxgx, jf!
[解决办法]
先来接分，一会儿再考虑问题，呵呵
[解决办法]
JF
[解决办法]
up
[解决办法]
重开帖再回答
[解决办法]
select guid,(select name from A where id=B.cid) as cname,(select name from A where id=B.gid) as gname from b
[解决办法]
up
[解决办法]
select B.guid,A.name as cname ,C.name as gname from B left join A on B.cid=A.id left join a C on B.gid=C.id
[解决办法]
如果考虑B表比A表的数据多的情况下如
A表:ID NAME
----------- --------------------
1 ASPX
2 PSP
3 JSP
(3 行受影响)
B表:
GUID CID GID
---------------------------------------- ----------- -----------
1111 1 2
2222 2 3
3333 4 5
则
select B.guid,A.name as cname ,C.name as gname from B left join A on B.cid=A.id left join a C on B.gid=C.id
与
select guid,(select name from A where id=B.cid) as cname,(select name from A where id=B.gid) as gname from b
的结果为
guid cname gname
---------------------------------------- -------------------- --------------------
1111 ASPX PSP
2222 PSP JSP
3333 NULL NULL
(3 行受影响)
这个时候如果考虑不要上面的第三行的话,可以为
select B.guid,A.name as cname ,C.name as gname from B left join A on B.cid=A.id left join a C on B.gid=C.id where A.name is not null and C.name is not null
或者
select guid,(select name from A where id=B.cid) as cname,(select name from A where id=B.gid) as gname from b
where (select name from A where id=B.cid) is not null and (select name from A where id=B.gid) is not null
或者
.....
其实条件可以换成好多种方法加上的.
结果
guid cname gname
---------------------------------------- -------------------- --------------------
1111 ASPX PSP
2222 PSP JSP
(2 行受影响)
[解决办法]
学习.................接分
[解决办法]
jf~~~
[解决办法]
JF
[解决办法]
恭喜LZ，小弟我接分````
[解决办法]
不理,只负责接分
[解决办法]
o
[解决办法]
接分，学习。。
[解决办法]
接——————> 分
[解决办法]
HOHO
[解决办法]
赏是不要了，只要分。
[解决办法]
GX
[解决办法]
又转回来了,再顶一次
[解决办法]
……..............8888888888888888888888888o.
...............888::::888888888888888888888888o.
.............88::::::::88888888888888888888888888o.
...........o8:::::::::888::顶88888888888888888888888.
..........8888:8::::::8:::::::::顶88888888888888888888.
.........8888::::8888::::::::::::::::顶88888888888 '88
........8888:::888888888::::::::::::::::顶888888888 8
.......88.88888888888888888:::::::::::::顶88888888888
......88..888888888888888888:::::::::::顶88888888888888
......88.888888888888888888888::::::::顶88888888888888888
......8..8888888888888888888888::::::顶88888888888888888888
........8888888888888888::88888:::::顶8888888888888888888888
......88888888888888888::::8888::::顶8888888888888888 '8888
.....88888888888888888::::8888::::顶:8::***::888888888 888
....88888888888888888::::888:::::顶::::*EYE*::888888888 88
..888888888888888888::::88::::::顶:::::::::::::888888888 88
.8888888888888888888::::8::::::顶:::::::::::::顶888888888 88
888...8888888888::88::::8:::::顶:::8oo8::::::88888888888888 8
88...88888888888:::8:::::::::顶:::::::::::::88888888888888888
.8..8888888888888:::::::::::顶:: "8888888 ":::顶88888888888888888
..8888888888::888::::::::::顶::::: "8a8 "::::顶88888888888888888888
.888888888:::::88:::::::::顶88::::::::::::顶88888888888888888888888
8888888888:::::8:::::::::顶88888::::::::顶888888888888888888888888888
888888888:::::8:::::::::顶8888888ooooo顶8888888888888888888888888888888
888888.顶::::::::::::::顶8888888888::::::顶8888888888888888 '888888888
8888..顶:::::::::::::::顶88888888888::::::顶8888888888888888 88888888
.888..顶::::::::::::::顶8888888888888:::::::顶888888888888888 888888
..888.顶:::::::::::::顶88888888888888888::::::顶8888888888888 88888
...88.顶:::::::::::::8888:88888888888888888:::::顶顶888888888 8888
...88.顶:::::::::::8888顶::88888::888888888888:::::顶顶88888 888
...8...顶::::::::::8888顶:::8888:::::88888888888::::::顶顶88 888
.......8顶:::::::8888顶:::::888:::::::88:::8888888:::::::顶顶 88
......88顶:::::::8888顶::::::88:::::::::8:::::888888::::::::顶顶 88
.....8888顶:::::888顶:::::::::88::::::::::顶::::8888::::::::::::顶 8
....88888:顶::::888顶::::::::::8:::::::::::顶::::888::::::::::::::顶
...88.888:顶:::888:顶::::::::::8::::::::::::顶::888::::::::::::::::顶
...8.88888顶:::88::顶::::::::::::::::::::::::顶:88::::::::::::::::::顶
.....88888顶:::88::顶:::::::::::::::::::::::::顶88:::::::::::::::::::顶
.....88888顶:::88::顶:::::::::::::::::::::::::顶:8::::::::::::::::::::顶
....888888顶::::8::顶::::::::::::::8888::::::::顶8::::::::::::8888:::::顶
....88888..顶:::8::顶顶:::::::::::888888:::::::顶::::::::::::888888::::顶
....8888...顶顶:::::顶顶::::::::::888888:::::::顶顶::::::::::888888::::顶
....8888....顶:::::::顶顶::::::::::8888:::::::顶顶顶::::::::::8888::::顶 '
.....888....顶顶::::::顶顶顶:::::::::::::::::顶顶::顶::::::::::::::::顶 '
......88.....顶::::::::顶顶顶顶:::::::::::顶顶顶:::顶顶:::::::::::::顶 '
.......88....顶顶:::::::::顶顶顶顶顶顶顶顶顶顶:::::::顶顶顶顶顶顶顶顶 '
........88....顶顶::::::::::::顶顶顶顶顶顶顶::::::::::::顶顶顶顶顶o '
.........88...8顶顶::::::::::::::::::::::::::::::::::::::::顶
..........8...88顶顶::::::::::::::::::::::顶:::顶::::::::::顶
..............888顶顶::::::::::::::::::顶顶::::::顶顶::::::顶
.............88888顶顶:::::::::::::::顶顶顶:::::::顶顶:::::顶
.............888888顶顶:::::::::::::顶顶顶:::::::::顶顶:::顶
............88888888顶顶:::::::::::顶顶顶:::::::::::顶:::顶
...........88.8888888.顶::::::::顶顶顶::::::::::::::顶::顶
...........8..888888..顶::::::顶顶:::::::::::::::::顶::顶
..............888888..顶:::::顶:::::::::::::::::::顶::顶顶
.............888888...顶::::顶::::::::::::::::::::::::顶顶
.............888888...顶::::顶::::::::::::::::::::::::顶:顶
.............88888...顶::::::::::::::8::::::::::::::::顶::顶
............88888...顶:::::::::::::::88::::::::::::::::顶::顶
...........88888...顶::::::::::::::::8::::::::::::::::::顶::顶
..........88888...顶:::::顶:::::::::::::::::::::顶::::::::顶::顶
..........8888...顶:::::顶:::::::::::::::::::::::顶顶::::::顶::顶
.........8888...顶:::::顶:::::::::::::::::::::::顶顶顶:::::::顶:顶
........888....顶:::::顶顶::::::::::::::::::::::顶顶顶::::::::顶:顶
......8888....顶::::::顶顶:::::::::::::::::::::顶顶顶::::::::: 顶:顶
.....888......顶:::::顶:::::::::::::::::::::::顶顶::::::::::::::顶:顶
..8888.......顶::::::::::::::::::::::::::::::顶::::::::::::::::::顶:顶
.............顶::::::::::::::::::::::::::::::::::::::::::::::::::顶:顶
............顶::::::::顶:::::::::::::::::::::::::::::::::::::::::顶:顶
............顶::::::::顶:::::::::::::::::顶::::::::::::::::::::::顶:顶
...........顶::::::::::顶::::::::::::::顶::::::::::::::::::::::::顶:顶
...........顶:::::::::::顶8::::::::8:顶:::::::::::::::::::::::::顶:顶p
...........顶::::::::::::888hole888顶:::::::::::::::::::::::::::顶:顶o
...........顶:::::::::::::88888888顶:::::::::::::::::::::::::::顶:顶p
...........顶::::::::::::::888888顶::::::::::::::::::::::::::::顶:顶
...........顶:::::::::::::::88888顶:::::::::::::::::::::::::::顶:顶
...........顶:::::::::::::::::88顶::::::::::::::::::::::::::::顶顶
...........顶:::::::::::::::::::顶:::::::::::::::::::::::::::顶顶
...........顶:::::::::::::::::::顶::::::::::::::::::::::::::顶顶
............顶:::::::::::::::::顶::::::::::::::::::::::::::顶顶
............顶:::::::::::::::::顶::::::::::::::::::::::::::顶
.............顶:::::::::::::::顶::::::::::::::::::::::::::顶
.............顶:::::::::::::::顶:::::::::::::::::::::::::顶
..............顶:::::::::::::顶:::::::::::::::::::::::::顶
..............顶:::::::::::::顶:::::::::::::::::::::::顶
...............顶:::::::::::顶:::::::::::::::::::::::顶
...............顶:::::::::::顶::::::::::::::::::::::顶
................顶:::::::::顶::::::::::::::::::::::顶
................顶:::::::::顶:::::::::::::::::::::顶
.................顶:::::::顶:::::::::::::::::::::顶
..................顶::::::顶::::::::::::::::::::顶
..................顶:::::顶::::::::::::::::::::顶
...................顶:::顶::::::::::::::::::::顶
...................顶::顶::::::::::::::::::::顶
....................顶:顶:::::::::::::::::::顶
....................顶顶:::::::::::::::::::顶
....................顶顶::::::::::::::::::顶
....................顶顶:::::::::::::::::顶
....................顶顶:::::顶::::::::顶顶
....................顶顶::::顶顶:::::::顶顶
....................顶顶::::::顶顶:::::顶顶
.....................顶顶::::::::::::::顶顶
.....................顶顶::::顶:::::::::顶顶
......................顶顶::顶顶顶:::::::顶顶
......................顶顶::顶顶顶:::::::顶顶
......................顶顶:::顶顶::::::::::顶
.......................顶顶::顶顶::::::::::顶
.......................顶顶::顶顶:::::::::::顶
.......................顶顶:::顶::::::::::::顶
.......................顶顶:::::::::::::::::顶
.......................顶顶:::::::::::::::::顶
.......................顶顶::::::::::::::::::顶
.......................顶顶::::::::::::::::::顶
.......................顶顶::::::::::::::::::顶
........................顶顶:::::::::::::::::顶
........................顶顶:::::::::::::::::顶
........................顶顶:::::::::::::::::顶
........................顶顶:::::::::::::::::顶
........................顶顶:::::::::::::::::顶
.........................顶顶:::::::::::::::顶
.........................顶顶:::::::::::::::顶
.........................顶顶:::::::::::::::顶
.........................顶顶::::::::::::::顶
..........................顶顶:::::::::::::顶
..........................顶顶:::::::::::::顶
发个MM
[解决办法]
JF
[解决办法]
jf
[解决办法]
select z.guid,x.name,y.name from a x ,b z ,a y where x.id=z.cname and y.id=z.gname
[解决办法]
恭喜了! 接分
[解决办法]
SELECT dbo.B.guid, dbo.A.name AS cname, A_1.name AS gname
FROM dbo.A INNER JOIN
dbo.B ON dbo.A.id = dbo.B.cid INNER JOIN
dbo.A A_1 ON dbo.B.gid = A_1.id
[解决办法]
恭喜恭喜
------解决方案--------------------
jf
[解决办法]
恭喜，接分
[解决办法]
jf
[解决办法]
GX,JF
[解决办法]
gx
[解决办法]
jf
[解决办法]
恭喜了，纯粹接分
[解决办法]
jf
[解决办法]
接分
[解决办法]
gx
----------------------------------------------------
出售HP,IBM,SUN小型机,工作站,服务器 电话:010-51280889转1007,MSN:wanglinan1121@hotmail.com
[解决办法]
mark
[解决办法]
select guid,(select name from A where id=B.cid) as cname,(select name from A where id=B.gid) as gname from b
[解决办法]
恭喜！
[解决办法]
专业接分！！
[解决办法]
恭喜，jf
[解决办法]
恭喜恭喜,^_^
[解决办法]
select t0.guid,t1.name as canme,t2.name as gname from b t0 inner join a t1 on t0.cid = t1.id inner join a t2 on t0.gid = t2.id
[解决办法]
jf
[解决办法]
jf
[解决办法]
jf
[解决办法]
jf
[解决办法]
恭喜
[解决办法]
路过
[解决办法]
接分。
[解决办法]
select guid, b.name as cname,c.name as gname from A a left outer join B b on a.cid=b.id left outer join B c on a.gid=c.id
[解决办法]
jf
[解决办法]
gx
[解决办法]
占楼~
[解决办法]
100层~