在连接表中使用最高子句参数

Question

我加入了一些表以收集一些信息，我正在尝试在SQL Server 2008 R2中使用TOP子句。这是我的代码：

    DECLARE @num INT
    SELECT 
        TOP (@num) installTicketItem.[id] AS ItemID, it.[id], it.[usr], it.[openDate],it.[closeDate],it.[saleId],it.[ticketType],it.[ticketDesc],it.[agent],
        CASE WHEN(resultId = 3 AND usr= it.usr) THEN 1 ELSE 0 END AS fault,
        CASE WHEN EXISTS(SELECT * FROM installTicket WHERE ticketType= 'modem' AND usr= it.usr AND closeDate IS Null) AND ticketType <> 'modem' THEN 1 ELSE 0 END AS hasModem, 
        CASE WHEN ((payment IS NULL) AND (installer IS NOT NULL)) THEN 1 ELSE 0 END AS notPaid 
    FROM installTicket it 
        JOIN (SELECT ticketId, MAX(id) AS maxID, 
        SET @num = COUNT(DISTINCT ticketId) FROM installTicketItem GROUP BY ticketId) AS iti ON iti.ticketId = it.id
        JOIN operator ON agent = username
        JOIN installPolicy ON installPolicy.agent = parentagent
        JOIN installManage ON installPolicy.panelUsr = installManage.panelUsr
        JOIN installTicketItem ON it.id = installTicketItem.ticketId
        JOIN installResult ON installResult.id = installTicketItem.resultId 
    WHERE   
        it.closeDate IS NULL AND ticketType = 'install' AND managerUsr = 'adminPanel2' AND done = 1
    ORDER BY 
        ItemID DESC, id DESC

我想在嵌套选择中设置@num参数。

有什么方法可以做到吗？我很感激，如果有人可以帮助我。

Answer 1

hi基于您的问题，您应该使用此查询为

DECLARE @num INT
        Set @num = 
        ( 
            SELECT 
                COUNT(DISTINCT iti.ticketId) 
            FROM 
                installTicketItem  iti join  installTicket it ON iti.ticketId = it.id 
            WHERE 
                it.closeDate IS NULL AND ticketType = 'install' AND managerUsr = 'adminPanel2' AND done = 1
            GROUP BY 
                ticketId
        )
        SELECT 
            TOP (@num) installTicketItem.[id] AS ItemID, it.[id], it.[usr], it.[openDate],it.[closeDate],it.[saleId],it.[ticketType],it.[ticketDesc],it.[agent],
            CASE WHEN(resultId = 3 AND usr= it.usr) THEN 1 ELSE 0 END AS fault,
            CASE WHEN EXISTS(SELECT * FROM installTicket WHERE ticketType= 'modem' AND usr= it.usr AND closeDate IS Null) AND ticketType <> 'modem' THEN 1 ELSE 0 END AS hasModem, 
            CASE WHEN ((payment IS NULL) AND (installer IS NOT NULL)) THEN 1 ELSE 0 END AS notPaid 
        FROM installTicket it 
            JOIN (SELECT ticketId, MAX(id) AS maxID
            --, SET @num = COUNT(DISTINCT ticketId) 
            FROM installTicketItem GROUP BY ticketId) AS iti ON iti.ticketId = it.id
            JOIN operator ON agent = username
            JOIN installPolicy ON installPolicy.agent = parentagent
            JOIN installManage ON installPolicy.panelUsr = installManage.panelUsr
            JOIN installTicketItem ON it.id = installTicketItem.ticketId
            JOIN installResult ON installResult.id = installTicketItem.resultId 
        WHERE   
            it.closeDate IS NULL AND ticketType = 'install' AND managerUsr = 'adminPanel2' AND done = 1
        ORDER BY 
            ItemID DESC, id DESC

Answer 2

您可以使用row_number参数化top n

    DECLARE @num INT
--needs work, what are you doing here?
--SELECT  @num = COUNT(DISTINCT ticketId) FROM installTicketItem GROUP BY ticketId) AS iti ON iti.ticketId = it.id;  
SELECT  @num = 99;   --99 as example
    SELECT 
        installTicketItem.[id] AS ItemID, it.[id], it.[usr], it.[openDate],it.[closeDate],it.[saleId],it.[ticketType],it.[ticketDesc],it.[agent],
        CASE WHEN(resultId = 3 AND usr= it.usr) THEN 1 ELSE 0 END AS fault,
        CASE WHEN EXISTS(SELECT * FROM installTicket WHERE ticketType= 'modem' AND usr= it.usr AND closeDate IS Null) AND ticketType <> 'modem' THEN 1 ELSE 0 END AS hasModem, 
        CASE WHEN ((payment IS NULL) AND (installer IS NOT NULL)) THEN 1 ELSE 0 END AS notPaid 
    FROM installTicket it 
        JOIN (SELECT ticketId, MAX(id) AS maxID    --needs work, what doing here?
        JOIN operator ON agent = username
        JOIN installPolicy ON installPolicy.agent = parentagent
        JOIN installManage ON installPolicy.panelUsr = installManage.panelUsr
        JOIN installTicketItem ON it.id = installTicketItem.ticketId
        JOIN installResult ON installResult.id = installTicketItem.resultId 
    WHERE   
        it.closeDate IS NULL AND ticketType = 'install' AND managerUsr = 'adminPanel2' AND done = 1
        and ROW_NUMBER() OVER (order by ItemID DESC, id DESC) <= @num   -- use row_number to get top 99
    ORDER BY 
        ItemID DESC, id DESC

Answer 3

我已经重新布置了查询以使其更有意义，必须在试图在

中运行它的查询之外完成一个变量的集合。

Declare @num Int;
Select @num = Count(DISTINCT ticketId) From installTicketItem; 
Select Top ( @num )
        [installTicketItem].[id] As [ItemID]
      , [it].[id]
      , [it].[usr]
      , [it].[openDate]
      , [it].[closeDate]
      , [it].[saleId]
      , [it].[ticketType]
      , [it].[ticketDesc]
      , [it].[agent]
      , Case When ( [resultId] = 3
                    And [usr] = [it].[usr]
                  ) Then 1
             Else 0
        End As [fault]
      , Case When Exists ( Select   *
                           From     [installTicket]
                           Where    [ticketType] = 'modem'
                                    And [usr] = [it].[usr]
                                    And [closeDate] Is Null )
                  And [ticketType] <> 'modem' Then 1
             Else 0
        End As [hasModem]
      , Case When ( ( [payment] Is Null )
                    And ( [installer] Is Not Null )
                  ) Then 1
             Else 0
        End As [notPaid]
From    [installTicket] [it]
        Join ( Select   [ticketId]
                      , Max([id]) As [maxID]
               From     [installTicketItem]
               Group By [ticketId]
             ) As [iti]
            On [iti].[ticketId] = [it].[id]
        Join [operator]
            On [agent] = [username]
        Join [installPolicy]
            On [installPolicy].[agent] = [parentagent]
        Join [installManage]
            On [installPolicy].[panelUsr] = [installManage].[panelUsr]
        Join [installTicketItem]
            On [it].[id] = [installTicketItem].[ticketId]
        Join [installResult]
            On [installResult].[id] = [installTicketItem].[resultId]
Where   [it].[closeDate] Is Null
        And [ticketType] = 'install'
        And [managerUsr] = 'adminPanel2'
        And [done] = 1
Order By [ItemID] Desc
      , [id] Desc;

此外，您应该定义要使用的连接类型