sql - 如何进行分组？

Question

我有一张 table 如下



我希望输出为



我触发以下查询

;WITH CTE AS
(
    Select script_Type = 'SP',detail_warnings ='Consider using EXISTS predicate instead of IN predicate' UNION ALL
    Select script_Type = 'SP',detail_warnings ='ExcludeItem does not exist in database SQLEye or is invalid for this operation' UNION ALL
    Select script_Type='SP',detail_warnings ='Values hardcoded in where-clause condition' UNION ALL
    Select script_Type='Table',detail_warnings ='Table name is not singular  Remarks :1:- Missing create index statement.' UNION ALL
    Select script_Type='Table',detail_warnings ='Check for existence object then Drop statement before create statement' UNION ALL
    Select script_Type='View',detail_warnings ='Invalid name'
)

SELECT script_Type,detail_warnings,COUNT(script_Type)
FROM  CTE c WITH(NOLOCK)
GROUP BY ROLLUP(script_Type,detail_warnings)

但输出如下



我需要做哪些改变才能得到想要的结果？

Answer 1

你已经在这里完成了所有的艰苦工作，真的，你只需要处理各种ROLLUP SELECT 中的行.

这对我来说看起来很不错:

WITH CTE AS
(
    Select script_Type = 'SP',detail_warnings ='Consider using EXISTS predicate instead of IN predicate' UNION ALL
    Select script_Type = 'SP',detail_warnings ='ExcludeItem does not exist in database SQLEye or is invalid for this operation' UNION ALL
    Select script_Type='SP',detail_warnings ='Values hardcoded in where-clause condition' UNION ALL
    Select script_Type='Table',detail_warnings ='Table name is not singular  Remarks :1:- Missing create index statement.' UNION ALL
    Select script_Type='Table',detail_warnings ='Check for existence object then Drop statement before create statement' UNION ALL
    Select script_Type='View',detail_warnings ='Invalid name'
)

SELECT script_Type = case
    when script_Type is null and detail_warnings is null then 'Total'
    when detail_warnings is null then script_Type + ' Count'
    else script_Type end
  ,detail_warnings = isnull(detail_warnings, '')
  ,COUNT(script_Type)
FROM  CTE c WITH(NOLOCK)
GROUP BY ROLLUP(script_Type,detail_warnings)

SQL Fiddle with demo .