34,587
社区成员
发帖
与我相关
我的任务
分享请教一个分类统计的问题
现有一个故障记录表(err_record)，大致内容格式如下：
故障类型共有5个
err_date err_type
2010－06－06 err_type_1
2010－06－06 err_type_4
2010－06－06 err_type_2
2010－06－06 err_type_3
2010－06－07 err_type_3
2010－06－07 err_type_1
2010－06－07 err_type_4
2010－06－07 err_type_2
2010－06－07 err_type_5
2010－06－07 err_type_3
2010－06－08 err_type_1
2010－06－08 err_type_5
2010－06－08 err_type_3
2010－06－08 err_type_3
2010－06－08 err_type_2
2010－06－08 err_type_4
2010－06－08 err_type_4
我想要得到的结果是(查询时可以指定日期条件，只查询一天的数据)：
2010-06-06
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 1 1 0 4
2010-06-07
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 1 1 6
2010-06-08
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 2 1 7
我用以下语句查询：
select
err_type,
sum(case when err_type='err_type_1' then 1 else 0 end) as count1,
sum(case when err_type='err_type_2' then 1 else 0 end) as count2,
sum(case when err_type='err_type_3' then 1 else 0 end) as count3,
sum(case when err_type='err_type_4' then 1 else 0 end) as count4,
sum(case when err_type='err_type_5' then 1 else 0 end) as count5
from
err_record
where
err_date='2010-06-06'
group by
err_type
得到如下结果：
err_type count1 count2 count3 count4 count5
err_type_1 1 0 0 0 0
err_type_2 0 1 0 0 0
err_type_3 0 0 1 0 0
err_type_4 0 0 0 1 0
可是没有我想要的“总计”，请教各位大大如何操作？
故障类型共有5个
err_date err_type
2010－06－06 err_type_1
2010－06－06 err_type_4
2010－06－06 err_type_2
2010－06－06 err_type_3
2010－06－07 err_type_3
2010－06－07 err_type_1
2010－06－07 err_type_4
2010－06－07 err_type_2
2010－06－07 err_type_5
2010－06－07 err_type_3
2010－06－08 err_type_1
2010－06－08 err_type_5
2010－06－08 err_type_3
2010－06－08 err_type_3
2010－06－08 err_type_2
2010－06－08 err_type_4
2010－06－08 err_type_4
我想要得到的结果是(查询时可以指定日期条件，只查询一天的数据)：
2010-06-06
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 1 1 0 4
2010-06-07
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 1 1 6
2010-06-08
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 2 1 7
我用以下语句查询：
select
err_type,
sum(case when err_type='err_type_1' then 1 else 0 end) as count1,
sum(case when err_type='err_type_2' then 1 else 0 end) as count2,
sum(case when err_type='err_type_3' then 1 else 0 end) as count3,
sum(case when err_type='err_type_4' then 1 else 0 end) as count4,
sum(case when err_type='err_type_5' then 1 else 0 end) as count5
from
err_record
where
err_date='2010-06-06'
group by
err_type
得到如下结果：
err_type count1 count2 count3 count4 count5
err_type_1 1 0 0 0 0
err_type_2 0 1 0 0 0
err_type_3 0 0 1 0 0
err_type_4 0 0 0 1 0
可是没有我想要的“总计”，请教各位大大如何操作？
...全文
请发表友善的回复…
发表回复
dawugui 2010-06-08
- 打赏
- 举报
[Quote=引用 22 楼 wwfang 的回复:]
我是说，我本来设了50分，现在我想加到100分。
[/Quote]
不知道.
你另外发个帖,50分即可.
我是说，我本来设了50分，现在我想加到100分。
[/Quote]
不知道.
你另外发个帖,50分即可.
dawugui 2010-06-08
- 打赏
- 举报
还可以简化为如下:
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values('2010-06-06' ,'err_type_2')
insert into tb values('2010-06-06' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_1')
insert into tb values('2010-06-07' ,'err_type_4')
insert into tb values('2010-06-07' ,'err_type_2')
insert into tb values('2010-06-07' ,'err_type_5')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_1')
insert into tb values('2010-06-08' ,'err_type_5')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_2')
insert into tb values('2010-06-08' ,'err_type_4')
insert into tb values('2010-06-08' ,'err_type_4')
go
select
sum(case err_type when 'err_type_1' then 1 else 0 end) as count1,
sum(case err_type when 'err_type_2' then 1 else 0 end) as count2,
sum(case err_type when 'err_type_3' then 1 else 0 end) as count3,
sum(case err_type when 'err_type_4' then 1 else 0 end) as count4,
sum(case err_type when 'err_type_5' then 1 else 0 end) as count5,
'总计' = count(1)
from tb t where err_date='2010-06-06'
group by err_date
/*
count1 count2 count3 count4 count5 总计
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 4
（所影响的行数为 1 行）
*/
drop table tb
fangyds 2010-06-08
- 打赏
- 举报
我是说，我本来设了50分，现在我想加到100分。
fangyds 2010-06-08
- 打赏
- 举报
我想给这贴加点分怎么操作啊？
dawugui 2010-06-08
- 打赏
- 举报
[Quote=引用 19 楼 wwfang 的回复:]
dawugui 同学的结果也对的，非常感谢！
其实，可能是我之前给出的代码误导了大家的思路，真不好意思，准备结贴了。
[/Quote]
是的.
dawugui 同学的结果也对的，非常感谢！
其实，可能是我之前给出的代码误导了大家的思路，真不好意思，准备结贴了。
[/Quote]
是的.
fangyds 2010-06-08
- 打赏
- 举报
dawugui 同学的结果也对的，非常感谢！
其实，可能是我之前给出的代码误导了大家的思路，真不好意思，准备结贴了。
其实，可能是我之前给出的代码误导了大家的思路，真不好意思，准备结贴了。
dawugui 2010-06-08
- 打赏
- 举报
[Quote=引用 17 楼 wwfang 的回复:]
嗯，我要的就是这种效果，谢谢！[/Quote]
12楼的复杂了,16楼第一个即可.
嗯，我要的就是这种效果，谢谢！[/Quote]
12楼的复杂了,16楼第一个即可.
fangyds 2010-06-08
- 打赏
- 举报
[Quote=引用 12 楼 jaydom 的回复:]
SQL code
declare @date varchar(10)
set @date='2010-06-08'
select 'err_type_1'=sum( case err_type when'err_type_1'then 1 else 0 end),
'err_type_2'=sum( case err_type when'err_type_2'then 1……
[/Quote]
嗯，我要的就是这种效果，谢谢！
SQL code
declare @date varchar(10)
set @date='2010-06-08'
select 'err_type_1'=sum( case err_type when'err_type_1'then 1 else 0 end),
'err_type_2'=sum( case err_type when'err_type_2'then 1……
[/Quote]
嗯，我要的就是这种效果，谢谢！
dawugui 2010-06-08
- 打赏
- 举报
如下两个,自己选一个.
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values('2010-06-06' ,'err_type_2')
insert into tb values('2010-06-06' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_1')
insert into tb values('2010-06-07' ,'err_type_4')
insert into tb values('2010-06-07' ,'err_type_2')
insert into tb values('2010-06-07' ,'err_type_5')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_1')
insert into tb values('2010-06-08' ,'err_type_5')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_2')
insert into tb values('2010-06-08' ,'err_type_4')
insert into tb values('2010-06-08' ,'err_type_4')
go
select
sum(case err_type when 'err_type_1' then 1 else 0 end) as count1,
sum(case err_type when 'err_type_2' then 1 else 0 end) as count2,
sum(case err_type when 'err_type_3' then 1 else 0 end) as count3,
sum(case err_type when 'err_type_4' then 1 else 0 end) as count4,
sum(case err_type when 'err_type_5' then 1 else 0 end) as count5,
'总计' = (select count(1) from tb where err_date = '2010-06-06')
from tb t where err_date='2010-06-06'
group by err_date
/*
count1 count2 count3 count4 count5 总计
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 4
（所影响的行数为 1 行）
*/
select
[err_type_1] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_1'),
[err_type_2] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_2'),
[err_type_3] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_3'),
[err_type_4] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_4'),
[err_type_5] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_5'),
[总计] = (select count(1) from tb where err_date = '2010-06-06' )
/*
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 4
（所影响的行数为 1 行）
*/
drop table tbdawugui 2010-06-08
- 打赏
- 举报
你是想这样?
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values('2010-06-06' ,'err_type_2')
insert into tb values('2010-06-06' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_1')
insert into tb values('2010-06-07' ,'err_type_4')
insert into tb values('2010-06-07' ,'err_type_2')
insert into tb values('2010-06-07' ,'err_type_5')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_1')
insert into tb values('2010-06-08' ,'err_type_5')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_2')
insert into tb values('2010-06-08' ,'err_type_4')
insert into tb values('2010-06-08' ,'err_type_4')
go
select
[err_type_1] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_1'),
[err_type_2] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_2'),
[err_type_3] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_3'),
[err_type_4] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_4'),
[err_type_5] = (select count(1) from tb where err_date = '2010-06-06' and err_type = 'err_type_5'),
[总计] = (select count(1) from tb where err_date = '2010-06-06' )
/*
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 4
（所影响的行数为 1 行）
*/
drop table tbfangyds 2010-06-08
- 打赏
- 举报
[Quote=引用 13 楼 dawugui 的回复:]
你如果早给出结果,也许早解决了,楼主不要耽搁你的时间.
[/Quote]
大哥，我一早就给出我的结果要求了，请看我的提问：
我想要得到的结果是(查询时可以指定日期条件，只查询一天的数据)：
2010-06-06
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 1 1 0 4
2010-06-07
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 1 1 6
2010-06-08
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 2 1 7
你如果早给出结果,也许早解决了,楼主不要耽搁你的时间.
[/Quote]
大哥，我一早就给出我的结果要求了，请看我的提问：
我想要得到的结果是(查询时可以指定日期条件，只查询一天的数据)：
2010-06-06
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 1 1 0 4
2010-06-07
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 1 1 6
2010-06-08
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 2 1 7
dawugui 2010-06-08
- 打赏
- 举报
你如果早给出结果,也许早解决了,楼主不要耽搁你的时间.
jaydom 2010-06-08
- 打赏
- 举报
declare @date varchar(10)
set @date='2010-06-08'
select 'err_type_1'=sum( case err_type when'err_type_1'then 1 else 0 end),
'err_type_2'=sum( case err_type when'err_type_2'then 1 else 0 end),
'err_type_3'=sum( case err_type when'err_type_3'then 1 else 0 end),
'err_type_4'=sum( case err_type when'err_type_4'then 1 else 0 end),
'err_type_5'=sum( case err_type when'err_type_5'then 1 else 0 end) ,
sum( case err_type when'err_type_1'then 1 else 0 end)+sum( case err_type when'err_type_2'then 1 else 0 end)
+sum( case err_type when'err_type_3'then 1 else 0 end)+sum( case err_type when'err_type_4'then 1 else 0 end)
+sum( case err_type when'err_type_5'then 1 else 0 end) as 总计
from tb
where err_date=@date
group by err_date
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 2 2 1 7
大明湖畔_帅锅 2010-06-08
- 打赏
- 举报
所以的故障类型列出来然后 对单个故障类型进行统计 ？
fangyds 2010-06-08
- 打赏
- 举报
[Quote=引用 6 楼 dawugui 的回复:]
--这样吗?
SQL code
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values('20……
[/Quote]
这个已经很接近了，但能不能针对日期查询总计呀？
--这样吗?
SQL code
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values('20……
[/Quote]
这个已经很接近了，但能不能针对日期查询总计呀？
fangyds 2010-06-08
- 打赏
- 举报
[Quote=引用 5 楼 dawugui 的回复:]
你把你需要的结果帖出来.
[/Quote]
我要的结果其实问题中已经有了，是这样的:
2010-06-06
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 1 1 0 4
统计某天各个故障类型的数量，并取得某天所有故障（不分故障类型）的总数。
你把你需要的结果帖出来.
[/Quote]
我要的结果其实问题中已经有了，是这样的:
2010-06-06
err_type_1 err_type_2 err_type_3 err_type_4 err_type_5 总计
1 1 1 1 0 4
统计某天各个故障类型的数量，并取得某天所有故障（不分故障类型）的总数。
fangyds 2010-06-08
- 打赏
- 举报
[Quote=引用 2 楼 chuifengde 的回复:]
SQL code
select err_type,count1,count2,count3,count4,count5,count1+count2+count3+count4+count5 总计
from
(select err_type,
sum(case when err_type='err_type_1' then 1 else 0 end) as count1,
sum(case w……
[/Quote]
您的代码实现的功能其实与二楼的同学的效果量一样的
SQL code
select err_type,count1,count2,count3,count4,count5,count1+count2+count3+count4+count5 总计
from
(select err_type,
sum(case when err_type='err_type_1' then 1 else 0 end) as count1,
sum(case w……
[/Quote]
您的代码实现的功能其实与二楼的同学的效果量一样的
jwdream2008 2010-06-08
- 打赏
- 举报
[Quote=引用 6 楼 dawugui 的回复:]
--这样吗?
SQL code
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values……
[/Quote]
Up!
--这样吗?
SQL code
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values……
[/Quote]
Up!
dawugui 2010-06-08
- 打赏
- 举报
--这样吗?
create table tb(err_date datetime,err_type varchar(10))
insert into tb values('2010-06-06' ,'err_type_1')
insert into tb values('2010-06-06' ,'err_type_4')
insert into tb values('2010-06-06' ,'err_type_2')
insert into tb values('2010-06-06' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-07' ,'err_type_1')
insert into tb values('2010-06-07' ,'err_type_4')
insert into tb values('2010-06-07' ,'err_type_2')
insert into tb values('2010-06-07' ,'err_type_5')
insert into tb values('2010-06-07' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_1')
insert into tb values('2010-06-08' ,'err_type_5')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_3')
insert into tb values('2010-06-08' ,'err_type_2')
insert into tb values('2010-06-08' ,'err_type_4')
insert into tb values('2010-06-08' ,'err_type_4')
go
select
err_type,
sum(case err_type when 'err_type_1' then 1 else 0 end) as count1,
sum(case err_type when 'err_type_2' then 1 else 0 end) as count2,
sum(case err_type when 'err_type_3' then 1 else 0 end) as count3,
sum(case err_type when 'err_type_4' then 1 else 0 end) as count4,
sum(case err_type when 'err_type_5' then 1 else 0 end) as count5,
'总计' = (select count(1) from tb where err_date = '2010-06-06')
from tb t where err_date='2010-06-06'
group by err_type
drop table tb
/*
err_type count1 count2 count3 count4 count5 总计
---------- ----------- ----------- ----------- ----------- ----------- -----------
err_type_1 1 0 0 0 0 4
err_type_2 0 1 0 0 0 4
err_type_3 0 0 1 0 0 4
err_type_4 0 0 0 1 0 4
（所影响的行数为 4 行）
*/dawugui 2010-06-08
- 打赏
- 举报
你把你需要的结果帖出来.
加载更多回复（4）
