总行数

Question

我有一个问题，我创建了一个有点复杂的存储过程，这是代码：

SELECT TOP (25) 
    [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2], 
    [Project1].[ICP_PRENOM] AS [ICP_PRENOM], 
    [Project1].[IDC_NOSOC] AS [IDC_NOSOC], 
    [Project1].[C3] AS [C3], 
    [Project1].[C4] AS [C4], 
    [Project1].[C5] AS [C5], 
    [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
    [Project1].[ADC_VILLE] AS [ADC_VILLE], 
    [Project1].[Libelle] AS [Libelle], 
    [Project1].[Libelle1] AS [Libelle1]
    FROM ( SELECT [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], [Project1].[IDC_NOSOC] AS [IDC_NOSOC], [Project1].[ICP_PRENOM] AS [ICP_PRENOM], [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], [Project1].[ADC_VILLE] AS [ADC_VILLE], [Project1].[Libelle] AS [Libelle], [Project1].[Libelle1] AS [Libelle1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], row_number() OVER (ORDER BY [Project1].[C1] ASC) AS [row_number]
        FROM ( SELECT 
            [Extent1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
            [Extent1].[IDC_NOSOC] AS [IDC_NOSOC], 
            [Extent2].[ICP_PRENOM] AS [ICP_PRENOM], 
            [Extent4].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
            [Extent4].[ADC_VILLE] AS [ADC_VILLE], 
            [Extent5].[Libelle] AS [Libelle], 
            [Extent6].[Libelle] AS [Libelle1], 
            LTRIM(RTRIM([Extent1].[IDC_NOM])) AS [C1], 
            LTRIM(RTRIM([Extent2].[ICP_NMNAISS])) AS [C2], 
             CAST( [Extent2].[ICP_DTNAISS] AS datetime2) AS [C3], 
             CAST( [Extent3].[ICR_DTCREA_ENTREPRISE] AS datetime2) AS [C4], 
            CASE WHEN ([Extent1].[IDC_CDPORTEFEUILLE] IS NULL) THEN N'''' ELSE  CAST( [Extent1].[IDC_CDPORTEFEUILLE] AS nvarchar(max)) END AS [C5]
            FROM      [mdw].[IDENTITE_CONTACT] AS [Extent1]
            LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PHYSIQUE] AS [Extent2] ON [Extent1].[IDC_IDCONTACT] = [Extent2].[ICP_IDCONTACT]
            LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PROFESSIONNEL] AS [Extent3] ON [Extent1].[IDC_IDCONTACT] = [Extent3].[ICR_IDCONTACT]
            LEFT OUTER JOIN [mdw].[ADRESSE_CONTACT] AS [Extent4] ON [Extent1].[IDC_IDCONTACT] = [Extent4].[ADC_IDADR]
            LEFT OUTER JOIN [mdw].[Ref_Profil_Contact] AS [Extent5] ON [Extent1].[IDC_CDPROFIL_CONTACT] = [Extent5].[Cod]
            LEFT OUTER JOIN [mdw].[Ref_Statut] AS [Extent6] ON [Extent1].[IDC_CDSTATUTSOC] = [Extent6].[Cod]
            WHERE ( NOT ((9975 = [Extent1].[IDC_CDPORTEFEUILLE]) AND ([Extent1].[IDC_CDPORTEFEUILLE] IS NOT NULL))) AND ( NOT ((9976 = [Extent1].[IDC_CDPORTEFEUILLE]) AND ([Extent1].[IDC_CDPORTEFEUILLE] IS NOT NULL))) AND ([Extent1].[IDC_CDPROFIL_CONTACT] = 'P')
        )  AS [Project1]
    )  AS [Project1]
    WHERE [Project1].[row_number] > 0
    ORDER BY [Project1].[C1] ASC'

我还想返回总行数，而不是仅有的25行，我不知道将count(*)放在哪里。错误总是存在的。你能帮帮我吗？

提前感谢

Answer 1

您可以添加

COUNT(1) OVER () AS records_count, 

你的问题。

对我来说，它工作得很好：

SELECT [id]
  ,[p_value]
  ,COUNT(1) OVER () AS records_count
FROM [dbo].[tb_temp]

返回：

​

​

对于前(N)个案例：

​

​

针对性能查询进行了优化：

WITH QueryCTE AS 
(

  SELECT 
    [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2], 
    [Project1].[ICP_PRENOM] AS [ICP_PRENOM], 
    [Project1].[IDC_NOSOC] AS [IDC_NOSOC], 
    [Project1].[C3] AS [C3], 
    [Project1].[C4] AS [C4], 
    [Project1].[C5] AS [C5], 
    [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
    [Project1].[ADC_VILLE] AS [ADC_VILLE], 
    [Project1].[Libelle] AS [Libelle], 
    [Project1].[Libelle1] AS [Libelle1]
    FROM ( SELECT [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], [Project1].[IDC_NOSOC] AS [IDC_NOSOC], [Project1].[ICP_PRENOM] AS [ICP_PRENOM], [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], [Project1].[ADC_VILLE] AS [ADC_VILLE], [Project1].[Libelle] AS [Libelle], [Project1].[Libelle1] AS [Libelle1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], row_number() OVER (ORDER BY [Project1].[C1] ASC) AS [row_number]
        FROM ( SELECT 
            [Extent1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
            [Extent1].[IDC_NOSOC] AS [IDC_NOSOC], 
            [Extent2].[ICP_PRENOM] AS [ICP_PRENOM], 
            [Extent4].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
            [Extent4].[ADC_VILLE] AS [ADC_VILLE], 
            [Extent5].[Libelle] AS [Libelle], 
            [Extent6].[Libelle] AS [Libelle1], 
            LTRIM(RTRIM([Extent1].[IDC_NOM])) AS [C1], 
            LTRIM(RTRIM([Extent2].[ICP_NMNAISS])) AS [C2], 
             CAST( [Extent2].[ICP_DTNAISS] AS datetime2) AS [C3], 
             CAST( [Extent3].[ICR_DTCREA_ENTREPRISE] AS datetime2) AS [C4], 
            CASE WHEN ([Extent1].[IDC_CDPORTEFEUILLE] IS NULL) THEN N'''' ELSE  CAST( [Extent1].[IDC_CDPORTEFEUILLE] AS nvarchar(max)) END AS [C5]
            FROM      [mdw].[IDENTITE_CONTACT] AS [Extent1]
            LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PHYSIQUE] AS [Extent2] ON [Extent1].[IDC_IDCONTACT] = [Extent2].[ICP_IDCONTACT]
            LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PROFESSIONNEL] AS [Extent3] ON [Extent1].[IDC_IDCONTACT] = [Extent3].[ICR_IDCONTACT]
            LEFT OUTER JOIN [mdw].[ADRESSE_CONTACT] AS [Extent4] ON [Extent1].[IDC_IDCONTACT] = [Extent4].[ADC_IDADR]
            LEFT OUTER JOIN [mdw].[Ref_Profil_Contact] AS [Extent5] ON [Extent1].[IDC_CDPROFIL_CONTACT] = [Extent5].[Cod]
            LEFT OUTER JOIN [mdw].[Ref_Statut] AS [Extent6] ON [Extent1].[IDC_CDSTATUTSOC] = [Extent6].[Cod]
            WHERE ( NOT ((9975 = [Extent1].[IDC_CDPORTEFEUILLE]) AND ([Extent1].[IDC_CDPORTEFEUILLE] IS NOT NULL))) AND ( NOT ((9976 = [Extent1].[IDC_CDPORTEFEUILLE]) AND ([Extent1].[IDC_CDPORTEFEUILLE] IS NOT NULL))) AND ([Extent1].[IDC_CDPROFIL_CONTACT] = 'P')
        )  AS [Project1]
    )  AS [Project1]
    WHERE [Project1].[row_number] > 0
)

SELECT TOP (25) 
    *       
  FROM QueryCTE
CROSS APPLY (SELECT COUNT(1) AS record_count FROM QueryCTE) cnt_qry
  ORDER BY [C1] ASC

结果：

​

​

Answer 2

在这种情况下，您可以使用CTE来帮助您，以便使用带有和不带有TOP子句的查询。

类似于(相当丑陋，但应该可以工作)：

 WITH QueryCTE AS 
(
SELECT 
    [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2], 
    [Project1].[ICP_PRENOM] AS [ICP_PRENOM], 
    [Project1].[IDC_NOSOC] AS [IDC_NOSOC], 
    [Project1].[C3] AS [C3], 
    [Project1].[C4] AS [C4], 
    [Project1].[C5] AS [C5], 
    [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
    [Project1].[ADC_VILLE] AS [ADC_VILLE], 
    [Project1].[Libelle] AS [Libelle], 
    [Project1].[Libelle1] AS [Libelle1]
    FROM ( SELECT [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], [Project1].[IDC_NOSOC] AS [IDC_NOSOC], [Project1].[ICP_PRENOM] AS [ICP_PRENOM], [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], [Project1].[ADC_VILLE] AS [ADC_VILLE], [Project1].[Libelle] AS [Libelle], [Project1].[Libelle1] AS [Libelle1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], row_number() OVER (ORDER BY [Project1].[C1] ASC) AS [row_number]
        FROM ( SELECT 
            [Extent1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
            [Extent1].[IDC_NOSOC] AS [IDC_NOSOC], 
            [Extent2].[ICP_PRENOM] AS [ICP_PRENOM], 
            [Extent4].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
            [Extent4].[ADC_VILLE] AS [ADC_VILLE], 
            [Extent5].[Libelle] AS [Libelle], 
            [Extent6].[Libelle] AS [Libelle1], 
            LTRIM(RTRIM([Extent1].[IDC_NOM])) AS [C1], 
            LTRIM(RTRIM([Extent2].[ICP_NMNAISS])) AS [C2], 
             CAST( [Extent2].[ICP_DTNAISS] AS datetime2) AS [C3], 
             CAST( [Extent3].[ICR_DTCREA_ENTREPRISE] AS datetime2) AS [C4], 
            CASE WHEN ([Extent1].[IDC_CDPORTEFEUILLE] IS NULL) THEN N'''' ELSE  CAST( [Extent1].[IDC_CDPORTEFEUILLE] AS nvarchar(max)) END AS [C5]
            FROM      [mdw].[IDENTITE_CONTACT] AS [Extent1]
            LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PHYSIQUE] AS [Extent2] ON [Extent1].[IDC_IDCONTACT] = [Extent2].[ICP_IDCONTACT]
            LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PROFESSIONNEL] AS [Extent3] ON [Extent1].[IDC_IDCONTACT] = [Extent3].[ICR_IDCONTACT]
            LEFT OUTER JOIN [mdw].[ADRESSE_CONTACT] AS [Extent4] ON [Extent1].[IDC_IDCONTACT] = [Extent4].[ADC_IDADR]
            LEFT OUTER JOIN [mdw].[Ref_Profil_Contact] AS [Extent5] ON [Extent1].[IDC_CDPROFIL_CONTACT] = [Extent5].[Cod]
            LEFT OUTER JOIN [mdw].[Ref_Statut] AS [Extent6] ON [Extent1].[IDC_CDSTATUTSOC] = [Extent6].[Cod]
            WHERE ( NOT ((9975 = [Extent1].[IDC_CDPORTEFEUILLE]) AND ([Extent1].[IDC_CDPORTEFEUILLE] IS NOT NULL))) AND ( NOT ((9976 = [Extent1].[IDC_CDPORTEFEUILLE]) AND ([Extent1].[IDC_CDPORTEFEUILLE] IS NOT NULL))) AND ([Extent1].[IDC_CDPROFIL_CONTACT] = 'P')
        )  AS [Project1]
    )  AS [Project1]
    WHERE [Project1].[row_number] > 0

)
SELECT TOP (25) 
* 
,(SELECT COUNT(*) FROM QueryCTE) AS NBRows
FROM QueryCTE
ORDER BY [C1] ASC