获取SubQuery的计数

Question

我需要计算一些我无法控制的SQL查询的结果。我的想法是用一个包装器计数查询来包装内部SQL。外部SQL非常简单，下面是我得到的结果：

select count(*) from ( x ) as CountQuery

其中x是内部SQL所在的任何位置。这样做的问题是一些查询会崩溃，也就是如果否则会结束。

如何创建一个能够成功包装任何SQL的包装器SQL？

下面是我需要计算的一个内部SQL的示例：

IF null <> 'PIPELINE_STAGE'
BEGIN
WITH cnts AS
(
SELECT 
       o.[OPPORTUNITY_ID]
      ,[OPPORTUNITY_NAME]
      ,[OPPORTUNITY_DETAILS]
      ,[IMAGE_URL]
      ,OPPORTUNITY_VALUE
      ,[PROBABILITY]
      ,[BID_CURRENCY]
      ,[BID_AMOUNT]
      ,[BID_TYPE]
      ,[BID_DURATION]
      ,[FORECAST_CLOSE_DATE]
      ,o.[CATEGORY_ID]
      ,c.CATEGORY_NAME
      ,c.BACKGROUND_COLOR
      ,o.[PIPELINE_ID]
      ,o.[STAGE_ID]
      ,[OPPORTUNITY_STATE]
      ,[RESPONSIBLE_USER_ID]
      ,u.[First_Name]
      ,u.[Last_Name]
      ,o.[VISIBLE_TO]
      ,o.VISIBLE_TEAM_ID
      ,o.[DATE_CREATED_UTC]
      ,o.[DATE_UPDATED_UTC]
      ,o.OWNER_USER_ID
      ,o.IMPORT_ID
      ,Follow_id
       ,ROW_NUMBER() OVER( ORDER BY CASE WHEN @sortOrder = 'OPPORTUNITY_NAME' THEN OPPORTUNITY_NAME END,
  CASE WHEN @sortOrder = 'RESPONSIBLE_USER' THEN ISNULL(u.[FIRST_Name], 'zz') END,
  CASE WHEN @sortOrder = 'FORECAST_CLOSE_DATE' THEN Forecast_Close_Date END,
  CASE WHEN @sortOrder = 'DATE_CREATED' THEN o.[DATE_CREATED_UTC]  END,
  CASE WHEN @sortOrder = 'FORECAST_CLOSE_DATE_DESC' THEN Forecast_Close_Date END DESC,
  CASE WHEN @sortOrder = 'DATE_CREATED_DESC' THEN o.[DATE_CREATED_UTC]  END DESC
       ) as intRow, 
     COUNT(o.INSTANCE_ID) OVER() AS Count
  FROM [Insightly].[dbo].[Opportunity] o with (nolock)
  LEFT JOIN [Insightly].[dbo].[Reference.Category] c with (nolock)
  on o.CATEGORY_ID = c.CATEGORY_ID AND c.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User] u WITH (NOLOCK)
  ON u.USER_ID = o.RESPONSIBLE_USER_ID AND u.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User.Follow] uf WITH (NOLOCK)
  ON (o.OPPORTUNITY_ID = uf.OPPORTUNITY_ID AND uf.USER_ID = @currentUserId AND uf.INSTANCE_ID = @instanceId)
WHERE o.INSTANCE_ID = @instanceId AND TEMPORARY = 0 
AND (@userId IS NULL OR [RESPONSIBLE_USER_ID] =@userId) AND (@categoryId IS NULL OR c.CATEGORY_ID = @categoryId)
AND (@importId IS NULL OR o.IMPORT_ID = @importId)
AND OPPORTUNITY_STATE in @opportunityState)
SELECT * FROM cnts
WHERE intRow BETWEEN @skip AND @take
END
ELSE
BEGIN
WITH cnts AS
(
SELECT 
       o.[OPPORTUNITY_ID]
      ,[OPPORTUNITY_NAME]
      ,[OPPORTUNITY_DETAILS]
      ,[IMAGE_URL]
      ,OPPORTUNITY_VALUE
      ,[PROBABILITY]
      ,[BID_CURRENCY]
      ,[BID_AMOUNT]
      ,[BID_TYPE]
      ,[BID_DURATION]
      ,[FORECAST_CLOSE_DATE]
      ,o.[CATEGORY_ID]
      ,c.CATEGORY_NAME
      ,c.BACKGROUND_COLOR
      ,o.[PIPELINE_ID]
      ,o.[STAGE_ID]
      ,[OPPORTUNITY_STATE]
      ,[RESPONSIBLE_USER_ID]
      ,u.[First_Name]
      ,u.[Last_Name]
      ,o.[VISIBLE_TO]
      ,o.VISIBLE_TEAM_ID
      ,o.[DATE_CREATED_UTC]
      ,o.[DATE_UPDATED_UTC]
      ,o.OWNER_USER_ID
      ,o.IMPORT_ID
      ,Follow_id
       ,ROW_NUMBER() OVER( ORDER BY ISNULL(p.Pipeline_Name, 'zz'), ISNULL([Pipeline.Stage].Stage_Order, 999)
       ) as intRow, 
     COUNT(o.INSTANCE_ID) OVER() AS Count
  FROM [Insightly].[dbo].[Opportunity] o with (nolock)
  LEFT JOIN [Insightly].[dbo].[Reference.Category] c with (nolock)
  on o.CATEGORY_ID = c.CATEGORY_ID AND c.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User] u WITH (NOLOCK)
  ON u.USER_ID = o.RESPONSIBLE_USER_ID AND u.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User.Follow] uf WITH (NOLOCK)
  ON (o.OPPORTUNITY_ID = uf.OPPORTUNITY_ID AND uf.USER_ID = @currentUserId AND uf.INSTANCE_ID = @instanceId)
LEFT OUTER JOIN [Pipeline.Stage] with(nolock) ON o.PIPELINE_ID = [Pipeline.Stage].PIPELINE_ID AND o.STAGE_ID = [Pipeline.Stage].STAGE_ID
LEFT OUTER JOIN [Pipeline] p with(nolock) ON o.PIPELINE_ID = p.PIPELINE_ID AND p.INSTANCE_ID = @instanceId
WHERE o.INSTANCE_ID = @instanceId AND TEMPORARY = 0 
AND (@userId IS NULL OR [RESPONSIBLE_USER_ID] =@userId) AND (@categoryId IS NULL OR c.CATEGORY_ID = @categoryId)
AND (@importId IS NULL OR o.IMPORT_ID = @importId)
SELECT * FROM cnts
WHERE intRow BETWEEN @skip AND @take
END

Answer 1

您可以基于查询创建临时表，然后计算其中的行数。

CREATE TEMPORARY TABLE my_results
<whatever query you have>;

SELECT COUNT(*) FROM my_results;