SQL 分类统计

fengling2001 2011-07-18 08:20:20
表结构如下,

ID Code Category Month Rate
1 0001 A 1 92
2 0001 A 2 93
3 0001 D 3 94
4 0001 B 1 91
5 0001 C 5 97
6 0002 B 4 92
7 0002 D 6 94
8 0002 D 3 91
9 0002 B 6 99
10 0002 C 6 100

如何得到如下结果:
Month A B C D
1 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate)
2 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate)
.
.
.
12 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate)
Total不同Code个数 AVG(Rate) 不同Code个数 AVG(Rate) 不同Code个数AVG(Rate) 不同Code个数 AVG(Rate)

Month固定是12个月,如果表里面没有对应月份数据,记作0,上例子中总共有2个不同Code

...全文
209 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
cd731107 2011-07-22
  • 打赏
  • 举报
 回复 


DECLARE @sql VARCHAR(8000);



SELECT @sql='SELECT a.number as [Month],

isnull(b.Acode,0)Acode,ISNULL(b.JCDLB001,0) AS Arate,

isnull(b.Bcode,0)Bcode,ISNULL(b.JCDLB002,0) AS Brate,

isnull(b.Dcode,0)Dcode,ISNULL(b.JCDLB004,0) AS Drate 

FROM (SELECT number FROM master.dbo.spt_values 

      WHERE type=''p'' AND number BETWEEN 1 AND 12) a 

LEFT JOIN (select [Month],

(select count(distinct code) from t ta where Category=''JCDLB001'' and month=t.month) as Acode,

(select count(distinct code) from t ta where Category=''JCDLB002'' and month=t.month) as Bcode,

(select count(distinct code) from t ta where Category=''JCDLB004'' and month=t.month) as Dcode '



SELECT @sql=@sql+',sum(case when Category='''+Category+''' then Rate end)/'

                +'(select count(*) from t ta where Category='''+Category+''' and month=t.month)'

                +' as '+Category

FROM (SELECT DISTINCT Category FROM t)a

 

SELECT @sql=@sql+' from t group by [Month]) b ON a.number=b.[Month]'



exec (@sql)
fengling2001 2011-07-22
  • 打赏
  • 举报
 回复
结果有问题，Acode数目不对啊，你可以看看我给你的测试数据，Code中只有三个不同的编码，所以Acode肯定不应该超过3
cd731107 2011-07-22
  • 打赏
  • 举报
 回复 
/*　建表

CREATE TABLE [dbo].[t](

	[id] [int] IDENTITY(1,1) NOT NULL,

	[CODE] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

	[Category] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

	[Rate] [int] NULL,

	[Month] [int] NULL

) ON [PRIMARY]



*/



/*

 发现Category字段只有三个数值JCDLB001　JCDLB002　JCDLB004

 原语句修改如下

*/

DECLARE @sql VARCHAR(8000);



SELECT @sql='SELECT a.number as [Month],

isnull(b.Acode,0)Acode,ISNULL(b.JCDLB001,0) AS Arate,

isnull(b.Bcode,0)Bcode,ISNULL(b.JCDLB002,0) AS Brate,

isnull(b.Dcode,0)Dcode,ISNULL(b.JCDLB004,0) AS Drate 

FROM (SELECT number FROM master.dbo.spt_values 

      WHERE type=''p'' AND number BETWEEN 1 AND 12) a 

LEFT JOIN (select [Month],

(select count(*) from t ta where Category=''JCDLB001'' and month=t.month) as Acode,

(select count(*) from t ta where Category=''JCDLB002'' and month=t.month) as Bcode,

(select count(*) from t ta where Category=''JCDLB004'' and month=t.month) as Dcode '



SELECT @sql=@sql+',sum(case when Category='''+Category+''' then Rate end)/'

                +'(select count(*) from t ta where Category='''+Category+''' and month=t.month)'

                +' as '+Category

FROM (SELECT DISTINCT Category FROM t)a

 

SELECT @sql=@sql+' from t group by [Month]) b ON a.number=b.[Month]'



exec (@sql)



/*　显示结果

Month       Acode       Arate       Bcode       Brate       Dcode       Drate

----------- ----------- ----------- ----------- ----------- ----------- -----------

1           60          99          60          99          60          99

2           56          99          56          99          56          99

3           60          99          60          99          60          99

4           60          99          60          99          60          99

5           4           100         60          99          60          99

6           54          99          54          99          60          99

7           0           0           0           0           0           0

8           0           0           0           0           0           0

9           0           0           0           0           0           0

10          0           0           0           0           0           0

11          0           0           0           0           0           0

12          0           0           0           0           0           0

*/
fengling2001 2011-07-20
  • 打赏
  • 举报
 回复
有邮件吗、可以把测试数据发给你试试
fengling2001 2011-07-20
  • 打赏
  • 举报
 回复
另外的数据，代表性数据可能需要单独跳下
cd731107 2011-07-20
  • 打赏
  • 举报
 回复
是指目前的数据，还是你另外的数据，后者的化再找一部分有代表性的贴上来
fengling2001 2011-07-20
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 cd731107 的回复:]
SQL code
CREATE TABLE t(ID INT, Code CHAR(4), Category CHAR(1), [Month] INT, Rate INT)
INSERT dbo.t
SELECT 1, '0001', 'A', 1, 92 UNION ALL
SELECT 2, '0001', 'A', 2, 93 UNION ALL
SELECT 3, '0001',……
[/Quote]
用我的数据测试，还是有问题
Acode Bcode Ccode Dcode结果还是不对
cd731107 2011-07-19
  • 打赏
  • 举报
 回复 
CREATE TABLE t(ID INT, Code CHAR(4), Category CHAR(1), [Month] INT, Rate INT)

INSERT dbo.t

SELECT 1, '0001', 'A', 1, 92 UNION ALL

SELECT 2, '0001', 'A', 2, 93 UNION ALL

SELECT 3, '0001', 'D', 3, 94 UNION ALL

SELECT 4, '0001', 'B', 1, 91 UNION ALL

SELECT 5, '0001', 'C', 5, 97 UNION ALL

SELECT 6, '0002', 'B', 4, 92 UNION ALL

SELECT 7, '0002', 'D', 6, 94 UNION ALL

SELECT 8, '0002', 'D', 3, 91 UNION ALL

SELECT 9, '0002', 'B', 6, 99 UNION ALL

SELECT 10, '0002', 'C', 6, 100;



DECLARE @sql VARCHAR(8000);



SELECT @sql='SELECT a.number as [Month],

isnull(b.Acode,0)Acode,ISNULL(b.A,0) AS Arate,

isnull(b.Bcode,0)Bcode,ISNULL(b.B,0) AS Brate,

isnull(b.Ccode,0)Ccode,ISNULL(b.C,0) AS Crate,

isnull(b.Dcode,0)Dcode,ISNULL(b.D,0) AS Drate 

FROM (SELECT number FROM master.dbo.spt_values 

      WHERE type=''p'' AND number BETWEEN 1 AND 12) a 

LEFT JOIN (select [Month],

(select count(*) from t ta where Category=''A'' and month=t.month) as Acode,

(select count(*) from t ta where Category=''B'' and month=t.month) as Bcode,

(select count(*) from t ta where Category=''C'' and month=t.month) as Ccode,

(select count(*) from t ta where Category=''D'' and month=t.month) as Dcode '



SELECT @sql=@sql+',sum(case when Category='''+Category+''' then Rate end)/'

                +'(select count(*) from t ta where Category='''+Category+''' and month=t.month)'

                +' as '+Category

FROM (SELECT DISTINCT Category FROM t)a

 

SELECT @sql=@sql+' from t group by [Month]) b ON a.number=b.[Month]'



EXEC (@sql)



/*

Month  Acode  Arate  Bcode   Brate   Ccode  Crate  Dcode  Drate       

-------------------------------------------------------------- 

1       1       92    1       91      0       0     0      0

2       1       93    0       0       0       0     0      0

3       0       0     0       0       0       0     2      92

4       0       0     1       92      0       0     0      0

5       0       0     0       0       1       97    0      0

6       0       0     1       99      1       100   1      94

7       0       0     0       0       0       0     0      0

8       0       0     0       0       0       0     0      0

9       0       0     0       0       0       0     0      0

10      0       0     0       0       0       0     0      0

11      0       0     0       0       0       0     0      0

12      0       0     0       0       0       0     0      0

*/
fengling2001 2011-07-19
  • 打赏
  • 举报
 回复
有点多维统计的意思
changyi1595 2011-07-18
  • 打赏
  • 举报
 回复
数据库的左连接那些拿来有什么用？
fengling2001 2011-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 qgqch2008 的回复:]

SQL code
CREATE TABLE t(ID INT, Code CHAR(4), Category CHAR(1), [Month] INT, Rate INT)
INSERT dbo.t
SELECT 1, '0001', 'A', 1, 92 UNION ALL
SELECT 2, '0001', 'A', 2, 93 UNION ALL
SELECT 3, '0001', 'D……
[/Quote]

谢谢,学习下代码,但是这个不是我想要的

上面的例子,应该得到1月份数据
Month ACode ARate BCode BRate CCode CRate DCode Drate
1 1 92 1 91 0 0 0 0
fengling2001 2011-07-18
  • 打赏
  • 举报
 回复
Rate平均值,比如上述例子中 1月 A类 Code数量为1,由于只有一个Rate,所以就是92,如果有多个需要求平均
qgqch2008 2011-07-18
  • 打赏
  • 举报
 回复 
CREATE TABLE t(ID INT, Code CHAR(4), Category CHAR(1), [Month] INT, Rate INT)

INSERT dbo.t

SELECT 1, '0001', 'A', 1, 92 UNION ALL

SELECT 2, '0001', 'A', 2, 93 UNION ALL

SELECT 3, '0001', 'D', 3, 94 UNION ALL

SELECT 4, '0001', 'B', 1, 91 UNION ALL

SELECT 5, '0001', 'C', 5, 97 UNION ALL

SELECT 6, '0002', 'B', 4, 92 UNION ALL

SELECT 7, '0002', 'D', 6, 94 UNION ALL

SELECT 8, '0002', 'D', 3, 91 UNION ALL

SELECT 9, '0002', 'B', 6, 99 UNION ALL

SELECT 10, '0002', 'C', 6, 100;



DECLARE @i INT;

DECLARE @sql VARCHAR(max);

SELECT @i=COUNT(DISTINCT Code) FROM dbo.t;

SELECT @sql='SELECT a.number as [Month],ISNULL(b.A,0) AS A,ISNULL(b.B,0) AS B,ISNULL(b.C,0) AS C,ISNULL(b.D,0) AS D FROM (SELECT number FROM master.dbo.spt_values WHERE type=''p'' AND number BETWEEN 1 AND 12) a LEFT JOIN (select [Month],';

SELECT @sql=@sql+'sum(case when Category='''+Category+''' then Rate end)/'+LTRIM(@i)+' as '+Category+' ,'

FROM (SELECT DISTINCT Category FROM dbo.t)a

SELECT @sql=SUBSTRING(@sql,0,LEN(@sql))+'from t group by [Month]) b ON a.number=b.[Month]'

--PRINT @sql

EXEC (@sql);

DROP TABLE dbo.t

/*

Month       A           B           C           D

----------- ----------- ----------- ----------- -----------

1           46          45          0           0

2           46          0           0           0

3           0           0           0           92

4           0           46          0           0

5           0           0           48          0

6           0           49          50          47

7           0           0           0           0

8           0           0           0           0

9           0           0           0           0

10          0           0           0           0

11          0           0           0           0

12          0           0           0           0

警告: 聚合或其他 SET 操作消除了空值。



(12 行受影响)

*/
--小F-- 2011-07-18
  • 打赏
  • 举报
 回复
不同Code个数 AVG(Rate)是什么意思?
fengling2001 2011-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 chuanzhang5687 的回复:]

先行列转换，后平均值
[/Quote]
不太明白
chuanzhang5687 2011-07-18
  • 打赏
  • 举报
 回复
先行列转换，后平均值
SQL Server数据库基础到精通课程
本课程讲解从最开始的 创建数据库、创建数据表、已经数据的写入、查询、修改更新、删除，字段运算、字段运算统计、分类汇总、数据分析、条件判断，条件筛选及一些常用的SQL数据库语句，变量定义，字段约束、等等，...
sql 分类统计及连接分组查询
一、数据表 1.zuser 表 ...1.统计platform表中个字段值在zuer表中的总数 2.按日期查询 zuer 表中的每日pid 类别总数 三、查询名利 1.连接查询各分类总数 select platform.*,(select coun...
SQL分类查询并统计，使用 coalesce(）函数等
这个sql是关于数量分类统计的，总结一下所用到函数。 /** * 第三方系统拥有证书统计 查询 * * @param customerSysName * @param startTime * @param endTime * @return */ public List&l...
SQL 根据分类进行个数统计
前言：使用数据库存储数据时，常会有根据存储数据的类型对数据的个数进行分类统计，可采用以下两种方法使用sql直接进行统计。 业务场景：消息表中存储各种消息，消息有多种类型，每条消息对应一个类型，前端展示时...
sql 统计和分类
1.sql语句中多加一列不是自己表中的语法：eg: select sname,grade,deptid, 可用数量 = case deptid when 2 then 20 else grade end /**这个可用数量就是用户自己添加上去的与表无关的列**/ from test_groupby2....
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章