在一系列UnionAlls之后計算表的所有行一次

創建資料質量規則 - 在這種情況下，我正在對表的幾列進行空檢查。我想弄清楚的是，如何獲取要查詢一次的表的總記錄\行數（以最大程度地減少服務器上的壓力），但在列 TOTAL_COUNT 中結果集的所有行中填充。我相信我需要一個左連接 - 只是想學習如何以及在哪里使用它。

所以到目前為止我擁有的代碼是：

SELECT
 CAST('RULE_ID_1') AS VARCHAR(255)) AS RULE_ID
 CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME
 CAST('1STCOLUMN') AS VARCHAR(255)) AS COLUMN_NAME
 A.DEFECT_COUNT AS DEFECT_COUNT
FROM
(SELECT COUNT(1) AS DEFECT_COUNT
FROM [Name of Db]
WHERE [Name of column 1] IS NULL
AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))) A
UNION ALL

SELECT
 CAST('RULE_ID_2') AS VARCHAR(255)) AS RULE_ID
 CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME
 CAST('2NDCOLUMN') AS VARCHAR(255)) AS COLUMN_NAME
 A.DEFECT_COUNT AS DEFECT_COUNT
FROM
(SELECT COUNT(1) AS DEFECT_COUNT
FROM [Name of Db]
WHERE [Name of column 2] IS NULL
AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))) A
UNION ALL

SELECT
 CAST('RULE_ID_3') AS VARCHAR(255)) AS RULE_ID
 CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME
 CAST('3RDCOLUMN') AS VARCHAR(255)) AS COLUMN_NAME
 A.DEFECT_COUNT AS DEFECT_COUNT
FROM
(SELECT COUNT(1) AS DEFECT_COUNT
FROM [Name of Db]
WHERE [Name of column 3] IS NULL
AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))) A

LEFT JOIN
(SELECT 'DB_NAME_HERE' AS DB_NAME, COUNT(*) AS TOTAL_COUNT FROM [Name of Db] WHERE [What goes here?] IS NULL) B
ON
A.DB_NAME=B.DB_NAME

輸出| 我需要報告：

uj5u.com熱心網友回復：

您布置查詢的方式為您和 SQL Server 帶來了很多額外的作業。

首先，如果使用“條件聚合”采用稍微不同的結構，則可以在單個查詢中完成大部分操作；那是里面COUNT()有一個CASE運算式。

然后您可以簡單地為TOTAL_COUNT. 雖然，我不完全確定你想要什么，所以我已經包含了一些選項......

SELECT
  CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME,

  CAST('1STCOLUMN')    AS VARCHAR(255))                   AS RULE_ID_1_COLUMN_NAME,
  COUNT(CASE WHEN [Name of column 1] IS NULL THEN 1 END)  AS RULE_ID_1_DEFECT_COUNT,

  CAST('2NDCOLUMN')    AS VARCHAR(255))                   AS RULE_ID_2_COLUMN_NAME,
  COUNT(CASE WHEN [Name of column 2] IS NULL THEN 1 END)  AS RULE_ID_2_DEFECT_COUNT,

  CAST('3RDCOLUMN')    AS VARCHAR(255))                   AS RULE_ID_3_COLUMN_NAME,
  COUNT(CASE WHEN [Name of column 3] IS NULL THEN 1 END)  AS RULE_ID_3_DEFECT_COUNT,

  COUNT(*)                                                AS TOTAL_DAILY_ROWS,
  COUNT(CASE WHEN [Name of column 1] IS NULL
               OR [Name of column 2] IS NULL 
               OR [Name of column 3] IS NULL THEN 1 END)  AS TOTAL_DAILY_ROWS_WITH_NULLS,
  (SELECT COUNT(*) FROM [Name of Db])                     AS TABLE_TOTAL_ROWS
FROM
  [Name of Db]
WHERE
  DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))

然后，如果您確實必須擁有現有格式，則可以取消旋轉它...

WITH
  yesterday_summary AS
(
  SELECT
    CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME,
    COUNT(CASE WHEN [Name of column 1] IS NULL THEN 1 END)  AS RULE_ID_1_DEFECT_COUNT,
    COUNT(CASE WHEN [Name of column 2] IS NULL THEN 1 END)  AS RULE_ID_2_DEFECT_COUNT,
    COUNT(CASE WHEN [Name of column 3] IS NULL THEN 1 END)  AS RULE_ID_3_DEFECT_COUNT
  FROM
    [Name of Db]
  WHERE
    DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
)
SELECT
  y.DB_NAME,
  p.RULE_ID,
  p.COLUMN_NAME,
  p.DEFECT_COUNT,
  (SELECT COUNT(*) FROM [Name of Db])   AS TABLE_TOTAL_ROWS
FROM
  yesterday_summary   AS y
CROSS APPLY
(
  VALUES
    ('RULE_ID_1', '1STCOLUMN', y.RULE_ID_1_DEFECT_COUNT),
    ('RULE_ID_2', '2NDCOLUMN', y.RULE_ID_2_DEFECT_COUNT),
    ('RULE_ID_3', '3RDCOLUMN', y.RULE_ID_3_DEFECT_COUNT)
)
  AS p(RULE_ID, COLUMN_NAME, DEFECT_COUNT)

標籤：sql sql-server 查询语句

上一篇：一個表中的最大值交叉連接以從另一個表中選擇值

下一篇：SQL在多個條目中查找不存在資源的位置