SQL查询对所有表中的列进行计数

Question

到目前为止，我可以使用以下 SQL 查询提取数据库表的列表:

SELECT
    DISTINCT
    TABLE_SCHEMA,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS

在每个表中，第一列名为“年份”。这些值从年份“2011”到年份“2017”:

CREATE TABLE foo (
    [Year] int,
    AnotherColumn varchar(50),
    ...
)

CREATE TABLE bar (
    [Year] int,
    SomeOtherColumn guid,
    ...
)

CREATE TABLE ...

现在，我需要计算每个表中不同年份的行数，并按以下格式显示输出:

| TABLE_SCHEMA | TABLE_NAME | 2011                | 2012                | ... | 2017                |
|:-------------|-----------:|:-------------------:|:-------------------:|:----|:-------------------:|
| SCHEMA       | foo        | no. of rows of 2011 | no. of rows of 2012 | ... | no. of rows of 2017 | 
| SCHEMA       | bar        | no. of rows of 2011 | no. of rows of 2012 | ... | no. of rows of 2017 | 
| SCHEMA       | ...        | no. of rows of 2011 | no. of rows of 2012 | ... | no. of rows of 2017 | 

有人有什么建议吗？非常感谢!

Answer 1

虽然每个 SQL 实现都提供某种形式的值参数化，但不存在这样的工具来参数化对象标识符(例如表名、列名等)——这意味着您必须求助于 Dynamic-SQL，这会引入其自身的风险(即SQL 注入(inject))。

对于您的具体问题，我们可以首先尝试在不使用 Dynamic-SQL 的情况下解决它，假设要查询一组已知且固定的表，然后我们可以将其转换为 Dynamic-SQL，希望以安全的方式:

SELECT
    'Table1' AS TableName
    [Year],
    COUNT(*) AS YearRowCount
FROM
    Table1
GROUP BY
    [Year]

UNION ALL

SELECT
    'Table2' AS TableName
    [Year],
    COUNT(*) AS YearRowCount
FROM
    Table2
GROUP BY
    [Year]

UNION ALL

...

希望您在这里看到了一种模式。

到目前为止，该查询将为我们提供以下形式的结果:

TableName    Year    YearRowCount
'Table1'     2017            1234
'Table1'     2016            2345
'Table1'     2015            3456
'Table1'     2014            1234
'Table1'     2013            1234
'Table1'     2011            1234
'Table2'     2017            1234
'Table2'     2016            2345
'Table2'     2015            3456
'Table2'     2013            1234
'Table2'     2012            1234
'Table2'     2011            1234
...

然后我们可以使用 PIVOT 将行转置为列。不幸的是，PIVOT(和 UNPIVOT)确实要求您显式命名要转置的每一列 - 但如果它们有一个 PIVOT ALL 那就太好了> 功能或其他东西)。

SELECT
    tableName,
    YearRowCount,
    [2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
    -- our UNION query goes here --
)
PIVOT
(
    SUM( YearRowCount )
    FOR [Year] IN ( 2011, 2012, 2013, 2014, 2015, 2016, 2017 )
)

现在我们知道内部查询的模式和围绕它的 PIVOT 语句，我们可以使其动态化。

有 3 种方法可以在“针对每一行...”的基础上生成动态 SQL。第一种是使用 CURSOR，第二种是使用某种 T-SQL 循环(WHILE 等) - 这两种方法都采用迭代 方法 - 但还有第三个版本，它功能更强大，语法更简单。我将演示这种函数式方法。

此外，我们可以通过使用(滥用)用作 sprintf 实现的 FORMATMESSAGE 函数来避免手动字符串连接的丑陋部分。要使用 FORMATMESSAGE 格式化字符串需要 SQL Server 2016 或更高版本(尽管据我所知，兼容性级别不需要为 130 )。如果您运行的是早期版本，则需要使用 CONCAT 或 'foo' + @var + 'bar' 式连接。

我还使用此答案中描述的 COALESCE( [aggregate] + [separator], '' ) + [value] 技巧:https://stackoverflow.com/a/194887/159145 - 这是连接(聚合)行的一种方法值(value)观，虽然感觉有点丑。请记住，SQL 主要关注无序元组数据集(即表)的关系代数，它通常不涵盖 View 级别的问题，例如排序或聚合排序数据 - 这就是连接。

DECLARE @unionTemplate varchar(1024) = '
SELECT
    ''%s.%s'' AS TableName
    [Year],
    COUNT(*) AS YearRowCount
FROM
    [%s].[%s]
GROUP BY
    [Year]
'

DECLARE @unionSeparator varchar(20) = '
UNION ALL
'

DECLARE @unionQuery varchar(max)

SELECT
    @unionQuery = COALESCE( @unionQuery + @unionSeparator, '' ) + FORMATMESSAGE( @unionTemplate, SCHEMA_NAME, TABLE_NAME, SCHEMA_NAME, TABLE_NAME )
FROM
    INFORMATION_SCHEMA.TABLES
ORDER BY
    SCHEMA_NAME,
    TABLE_NAME

无论如何，这个查询将生成存储在@unionQuery中的查询，所以现在我们只需要编写它......

DECLARE @pivotQuery varchar(max) = '
SELECT
    tableName,
    YearRowCount,
    [2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
    %s
)
PIVOT
(
    SUM( YearRowCount )
    FOR [Year] IN ( 2011, 2012, 2013, 2014, 2015, 2016, 2017 )
)'

SET @pivotQuery = FORMATMESSAGE( @pivotQuery, @unionQuery )

...并执行它(EXEC sp_executesql 优于古老的 EXEC()) - 另请注意 EXEC() 是与 EXEC 不同!

EXEC sp_executesql @pivotQuery

哒哒!

旧版 SQL Server 版本(2014、2012、2008 R2、2008):

这些未经测试，但如果您需要在 2016 年 (v13.0) 之前的 SQL Server 版本上运行，请尝试使用这些 FORMATMESSAGE 替代方案:

DECLARE @unionQuery nvarchar(max)

SELECT
    @unionQuery =
        COALESCE( @unionQuery + ' UNION ALL ', '' ) +
        CONCAT(
            'SELECT ''',
            SCHEMA_NAME, '.', TABLE_NAME, '[Year],
    COUNT(*) AS YearRowCount
FROM
    [', SCHEMA_NAME, '].[', TABLE_NAME, ']
GROUP BY
    [Year]
'
    )
FROM
    INFORMATION_SCHEMA.TABLES
ORDER BY
    SCHEMA_NAME,
    TABLE_NAME

由于@pivotQuery仅插入一次，因此可以使用REPLACE插入内部@unionQuery，但切勿在以下情况下这样做:处理用户提供的值，因为您容易遭受类似 SQL 注入(inject)的攻击:

SET @pivotQuery = REPLACE( @pivotQuery, '%s', @unionQuery )