有时候回答有时候蹭分,不对之处,大家多多包涵!!顺一个问题吧:
A表:
id name
1 aspx
2 psp
3 jspB表:
guid cid gid
1111 1 2
2222 2 3要求用一个SQL语句得到如下guid cname gname
1111 aspx psp
2222 psp jsp答对者另开帖,重重有赏-_-!!!
A表:
id name
1 aspx
2 psp
3 jspB表:
guid cid gid
1111 1 2
2222 2 3要求用一个SQL语句得到如下guid cname gname
1111 aspx psp
2222 psp jsp答对者另开帖,重重有赏-_-!!!
(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 #TMPBSELECT 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.IDDROP TABLE #TMPA
DROP TABLE #TMPB
2222 PSP JSP
jf
也可
join tableA t2 on t1.cid=t2.id
join tableA t3 on t1.gid=t3.id;
楼住给分吧...
请问lz现在包养了两只猩猩有何感想
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 行受影响)
...............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
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