sql - SQL - 一个 SELECT 中的 TOP 和 COUNT

Question

我尝试将这两个语句合并为一个，但我所有的尝试都失败了!是否可以合并它们？

-- Is there a open answer?
SELECT CASE COUNT(tbl_Communication.pk_Communication) WHEN 0 
       THEN 0 ELSE 1 END AS hasAnsweredCom
FROM   tbl_Communication 
JOIN   tbl_CommunicationElements ON tbl_CommunicationElements.pk_Communication = tbl_Communication.pk_Communication
WHERE  tbl_Communication.pk_Ticket = @pk_Ticket
  AND tbl_Communication.isClosed = 0 
  AND tbl_Communication.pk_CommunicationType = (SELECT pk_CommunicationType
                                                FROM   tbl_CommunicationType
                                                WHERE  name = 'query')

-- Get the answer text
SELECT TOP 1 tbl_Communication.subject AS hasAnsweredComStepName
FROM   tbl_Communication 
JOIN   tbl_CommunicationElements ON tbl_CommunicationElements.pk_Communication = tbl_Communication.pk_Communication
WHERE  tbl_Communication.pk_Ticket = @pk_Ticket 
  AND tbl_Communication.isClosed = 0 
  AND tbl_Communication.pk_CommunicationType = (SELECT pk_CommunicationType
                                                FROM   tbl_CommunicationType
                                                WHERE  name = 'query')
ORDER BY tbl_Communication.pk_Communication

Answer 1

右连接技巧。

SELECT TOP 1
    CASE WHEN tbl_CommunicationElements.pk_Communication IS NULL THEN 0 ELSE 1 END hasAnsweredCom
    , tbl_Communication.subject AS hasAnsweredComStepName
FROM   tbl_Communication 
JOIN   tbl_CommunicationElements ON tbl_CommunicationElements.pk_Communication = tbl_Communication.pk_Communication
RIGHT JOIN (VALUES(1)) AS Ext(x) ON (
  tbl_Communication.pk_Ticket = @pk_Ticket 
  AND tbl_Communication.isClosed = 0 
  AND tbl_Communication.pk_CommunicationType = (SELECT pk_CommunicationType
                                                FROM   tbl_CommunicationType
                                                WHERE  name = 'query')
)