如何通过加入 SQL Server 的单个表来构建摘要? [英] How do I build a summary by joining to a single table with SQL Server?

问题描述

我有一个更新的表，其中有一些包含重复数据的字段.我想把这些数据汇总起来总结一下.如何为 SQL Server 执行此操作?我有下面的输出和结构示例.我尝试了一些不同的连接，但我看到了重复的数据和一些我不明白的错误.

I have a table which is update has some fields with repeating data. I want to roll up this data to summarize it. How do I do this for SQL Server? I have an example of the output and structure below. I've tried some different joins but I've seen repeating data and some errors that I don't understand.

表结构

• 日志文件名(字符串)
• 状态 (int) - 可能是 1、2、3，具体取决于应用程序输入

数据

f1, 3, 0
f1, 2, 1
f1, 3, 0
f2, 1, 1
f2, 1, 1
f2, 2, 1
....

输出

File | Count of status == 1 | Count of status == 2 
f1   | 59                  | 43
f2   | 28                  | 99
f3   | 23                  | 16

推荐答案

使用汇总汇总数据:

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

你使用的是什么版本的sql server?

What version of sql server are you using ?

如果您不想使用汇总，这应该会有所帮助:

If you dont have want to use rollup this should help:

SELECT
      FileName,
      SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS CountOf1,
      SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) AS CountOf2,
      SUM(CASE WHEN Status = 3 THEN 1 ELSE 0 END) AS CountOf3
FROM
      MyTable
GROUP BY FileName
ORDER BY FileName

这篇关于如何通过加入 SQL Server 的单个表来构建摘要?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！