在表discount_inf中有字段discount_value,compy_code....一些字段。要求统计出每个公司各种折扣的数量。
我写了如下代码,但是输出的结果我觉得有点问题,我想让输出的结果按每个compy_code只显示出一条数据
比如输出结果中compy_code为7的只该出现一行,7---0---2---1),请帮忙解答下。
select compy_code,VIP_NO,serior_NO,general_NO
from (
select compy_code,count(discount_value) VIP_NO,0 serior_NO,0 general_NO
from discount_inf
where discount_value > 1000
group by compy_code union all
select compy_code,0 VIP_NO,count(discount_value) serior_NO,0 general_NO
from discount_inf
where discount_value between 300 and 1000
group by compy_code union all
select compy_code,0 VIP_NO,0 serior_NO,count(discount_value) general_NO
from discount_inf
where discount_value < 300
group by compy_code
)
order by compy_code结果为:COMPY_CODE VIP_NO SERIOR_NO GENERAL_NO
---------- ---------- ---------- ----------
0 0 0 3
1 0 0 3
2 0 1 0
3 0 1 0
3 0 0 1
4 0 0 2
5 0 3 0
6 0 2 0
7 0 2 0
7 0 0 1
8 0 2 0
8 0 0 1
9 0 2 0
9 0 0 1
14 rows selected
我写了如下代码,但是输出的结果我觉得有点问题,我想让输出的结果按每个compy_code只显示出一条数据
比如输出结果中compy_code为7的只该出现一行,7---0---2---1),请帮忙解答下。
select compy_code,VIP_NO,serior_NO,general_NO
from (
select compy_code,count(discount_value) VIP_NO,0 serior_NO,0 general_NO
from discount_inf
where discount_value > 1000
group by compy_code union all
select compy_code,0 VIP_NO,count(discount_value) serior_NO,0 general_NO
from discount_inf
where discount_value between 300 and 1000
group by compy_code union all
select compy_code,0 VIP_NO,0 serior_NO,count(discount_value) general_NO
from discount_inf
where discount_value < 300
group by compy_code
)
order by compy_code结果为:COMPY_CODE VIP_NO SERIOR_NO GENERAL_NO
---------- ---------- ---------- ----------
0 0 0 3
1 0 0 3
2 0 1 0
3 0 1 0
3 0 0 1
4 0 0 2
5 0 3 0
6 0 2 0
7 0 2 0
7 0 0 1
8 0 2 0
8 0 0 1
9 0 2 0
9 0 0 1
14 rows selected
select compy_code,
sum(case when discount_value > 1000 then 1 else 0 end) VIP_NO,
sum(case when discount_value between 300 and 1000 then 1 else 0 end) serior_NO,
sum(case when discount_value < 300 then 1 else 0 end) general_NO
from discount_inf
group by compy_code
order by compy_code
这个方法我还是第一次见到,请问你能给我介绍点oracle方面的书籍不?
from (
select compy_code,count(discount_value) VIP_NO,0 serior_NO,0 general_NO
from discount_inf
where discount_value > 1000
group by compy_code union all
select compy_code,0 VIP_NO,count(discount_value) serior_NO,0 general_NO
from discount_inf
where discount_value between 300 and 1000
group by compy_code union all
select compy_code,0 VIP_NO,0 serior_NO,count(discount_value) general_NO
from discount_inf
where discount_value < 300
group by compy_code
) a group by companycode
select compy_code,max(VIP_NO),max(serior_NO),max(general_NO)
from (
select compy_code,count(discount_value) VIP_NO,0 serior_NO,0 general_NO
from discount_inf
where discount_value > 1000
group by compy_codeunion all
select compy_code,0 VIP_NO,count(discount_value) serior_NO,0 general_NO
from discount_inf
where discount_value between 300 and 1000
group by compy_codeunion all
select compy_code,0 VIP_NO,0 serior_NO,count(discount_value) general_NO
from discount_inf
where discount_value < 300
group by compy_code
)
group by compy_code
order by compy_code
但应该不是最简单的
只好放分给bobfang了。