对同一字段的许多不同结果的数据求和

Question

我正试图找到一种更好的方法来编写这个sql server代码2008。它有效，数据准确。我问的原因是，我将被要求为未来的其他几个报告做这件事，并希望减少未来需要维护的代码量。我如何在不对每个字段中的yes/no/-（破折号）求和的情况下取一个字段，而不像代码中那样进行单独的求和。每个表都是一个月的详细数据，我在CTE中使用这些数据进行汇总。我更改了每个月的表名和Union All以将数据放在一起。有更好的方法吗。这是一个小的代码示例。谢谢你的帮助。

WITH H AS (
SELECT 'August' AS Month_Name
    , SUM(CASE WHEN G.FFS = '-'     THEN 1 ELSE 0 END) AS FFS_Dash
    , SUM(CASE WHEN G.FFS = 'Yes'   THEN 1 ELSE 0 END) AS FFS_Yes
    , SUM(CASE WHEN G.FFS = 'No'    THEN 1 ELSE 0 END) AS FFS_No
    , SUM(CASE WHEN G.DNA = '-'     THEN 1 ELSE 0 END) AS DNA_Dash
    , SUM(CASE WHEN G.DNA = 'Yes'   THEN 1 ELSE 0 END) AS DNA_Yes
    , SUM(CASE WHEN G.DNA = 'No'    THEN 1 ELSE 0 END) AS DNA_No
FROM table08 G )
, G AS (
SELECT 'July' AS Month_Name
    , SUM(CASE WHEN G.FFS = '-'     THEN 1 ELSE 0 END) AS FFS_Dash
    , SUM(CASE WHEN G.FFS = 'Yes'   THEN 1 ELSE 0 END) AS FFS_Yes
    , SUM(CASE WHEN G.FFS = 'No'    THEN 1 ELSE 0 END) AS FFS_No
    , SUM(CASE WHEN G.DNA = '-'     THEN 1 ELSE 0 END) AS DNA_Dash
    , SUM(CASE WHEN G.DNA = 'Yes'   THEN 1 ELSE 0 END) AS DNA_Yes
    , SUM(CASE WHEN G.DNA = 'No'    THEN 1 ELSE 0 END) AS DNA_No
FROM table07 G )
select * from H 
UNION ALL
select * from G 

Answer 1

怎么样：

SELECT Month_Name,
       SUM(CASE WHEN G.FFS = '-'     THEN 1 ELSE 0 END) AS FFS_Dash,
       SUM(CASE WHEN G.FFS = 'Yes'   THEN 1 ELSE 0 END) AS FFS_Yes,
       SUM(CASE WHEN G.FFS = 'No'    THEN 1 ELSE 0 END) AS FFS_No,
       SUM(CASE WHEN G.DNA = '-'     THEN 1 ELSE 0 END) AS DNA_Dash,
       SUM(CASE WHEN G.DNA = 'Yes'   THEN 1 ELSE 0 END) AS DNA_Yes,
       SUM(CASE WHEN G.DNA = 'No'    THEN 1 ELSE 0 END) AS DNA_No
FROM ((select 'July' as Month_Name, G.*
       from table07 G
      ) union all
      (select 'August', H.*
       from table08 H
      )
     ) gh
GROUP BY Month_Name;

然而，具有相同结构的表通常是数据库设计不佳的标志。您应该有一个单独的表，其中有一列表示月份。