组合2个SQL语句
组合2个SQL语句
提问于 2012-11-23 23:55:18
我需要组合以下两个select语句，以便第二个语句的结果在第一个SELECT语句的每一行中显示为一列。
SELECT MEM.Id,
EN.artistName,
EN.dateAdded,
EN.voteStatus,
ES.enterNextRound,
ES.notified,
ES.voted,
GR.genre,
ES.entrantId AS bandID
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
WHERE MEM.Id = @memberId
AND ES.roundId = 2
SELECT COUNT(enterNextRound)
FROM recEntrantStatus
WHERE enterNextRound = 1
AND roundId = 2
AND entrantId = ES.entrantId其中'ES.entrantId‘取自第一次select中访问的当前行。
如果有任何建议，我们将非常感谢。
谢谢
回答 3
Stack Overflow用户
回答已采纳
发布于 2012-11-23 23:58:21
您可以使用OUTER APPLY
SELECT MEM.Id,
EN.artistName,
EN.dateAdded,
EN.voteStatus,
ES.enterNextRound,
ES.notified,
ES.voted,
GR.genre,
ES.entrantId AS bandID,
src.CountEnterNextRound
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
OUTER APPLY
(
SELECT COUNT(enterNextRound) CountEnterNextRound
FROM recEntrantStatus
WHERE enterNextRound = 1
AND roundId = 2
AND entrantId = @memberId
) src
WHERE MEM.Id = @memberId
AND ES.roundId = 2根据您的编辑，您是否尝试过：
SELECT MEM.Id,
EN.artistName,
EN.dateAdded,
EN.voteStatus,
ES.enterNextRound,
ES.notified,
ES.voted,
GR.genre,
ES.entrantId AS bandID,
(SELECT COUNT(enterNextRound)
FROM recEntrantStatus
WHERE enterNextRound = 1
AND roundId = 2
AND entrantId = ES.entrantId) CountEnterNextRound
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
WHERE MEM.Id = @memberId
AND ES.roundId = 2或者甚至是：
SELECT MEM.Id,
EN.artistName,
EN.dateAdded,
EN.voteStatus,
ES.enterNextRound,
ES.notified,
ES.voted,
GR.genre,
ES.entrantId AS bandID,
src.CountEnterNextRound
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
LEFT JOIN
(
SELECT COUNT(enterNextRound) CountEnterNextRound, entrantId
FROM recEntrantStatus
WHERE enterNextRound = 1
AND roundId = 2
GROUP BY entrantId
) src
ON ES.entrantId = src.entrantId
WHERE MEM.Id = @memberId
AND ES.roundId = 2;Stack Overflow用户
发布于 2012-11-23 23:57:02
将其包装在子查询中并使用CROSS JOIN
SELECT MEM.Id,
EN.artistName,
EN.dateAdded,
EN.voteStatus,
ES.enterNextRound,
ES.notified,
ES.voted,
GR.genre,
ES.entrantId AS bandID,
s.totalCount
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
CROSS JOIN (
SELECT COUNT(enterNextRound) totalCount
FROM recEntrantStatus
WHERE enterNextRound = 1
AND roundId = 2
AND entrantId = @memberId
) s
WHERE MEM.Id = @memberId
AND ES.roundId = 2Stack Overflow用户
发布于 2012-11-23 23:57:19
怎么样
SELECT *
FROM
(
SELECT MEM.Id, EN.artistName, EN.dateAdded, EN.voteStatus, ES.enterNextRound,
ES.notified, ES.voted, GR.genre, ES.entrantId AS bandID
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
WHERE MEM.Id = @memberId
AND ES.roundId = 2
) x
,
(
SELECT COUNT(enterNextRound) as Total
FROM recEntrantStatus
WHERE enterNextRound = 1
AND roundId = 2
AND entrantId = @memberId
) y当你从两个没有连接的表中做的时候，你得到了一个像交叉连接一样的笛卡尔乘积。所以它应该是相同的执行时间。
这是一种隐式的编写方式。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13532328
复制相关文章
相似问题
腾讯云开发者
