☰

分组：对没有数据的组返回 0

tag-icon • tag-icon sql
分组：对没有数据的组返回 0

我有一张包含文本字段的表。我构建了一个查询，该查询获取这些字段的数据长度并基本上返回一个直方图。

SELECT TEXTFieldSize, COUNT(TEXTFieldSize) AS Count FROM (SELECT DATALENGTH(data) AS TEXTFieldSize FROM table) AS Fields GROUP BY TEXTFieldSize ORDER BY TEXTFieldSize;
 ________________
| Length | Count |
|      1 |     5 |
|      3 |     2 |
|      6 |    12 |
|________|_______|

我的问题是，为了正确分析这一点，我还需要计数为零的值的行。

SELECT ???
 ________________
| Length | Count |
|      1 |     5 |
|      2 |     0 |
|      3 |     2 |
|      4 |     0 |
|      5 |     0 |
|      6 |    12 |
|________|_______|

提前致谢！

答案1

生成数字表（SQL Server 方式）

CREATE TABLE dbo.Numbers (Num int NOT NULL PRIMARY KEY)
INSERT dbo.Numbers (Num)
SELECT TOP 8000 ROW_NUMBER() OVER (ORDER BY C1.column_id)
FROM sys.columns C1 CROSS JOIN sys.columns C2

答案：

SELECT N.Num AS TEXTFieldSize, COUNT(t.data) AS CountOf
FROM
  dbo.Numbers N
  LEFT JOIN
  mytable t ON N.Num = DATALENGTH(t.data)
WHERE
  N.Num <= (SELECT MAX(DATALENGTH(data)) FROM mytable)
GROUP BY N.Num
ORDER BY N.Num;

相关内容