SQL> select fs_username,sum(pcj) pcj,md from luru_table where bill_statu = '审核'
2 group by fs_username,md
3 ;FS_USERNAME PCJ MD
-------------------- ---------- --------------------
李晓 100 12
马龙 120 12
贾志明 1400 16
李爱民 200 16
刘爱华 800 16
现在有三列 fs_username pcj md 问题是在显示两列。一列是 先判断 md是否在 (1,2,12)里 如果是求出md一样的对pcj汇总得到的总pcj 在 如果这个pcj在0到1500之间就显示 10% .如果md 在(16) 里 也求出md一样的对pcj汇总得到的总pcj 在 如果这个pcj在1000到1500之间就显示 10% .
第二列就好办了。FS_USERNAME PCJ MD t
-------------------- ---------- -------------------- --------------
李晓 100 12 12 in (1,2,12) 中 0<100+120<1500 所以 10%
马龙 120 12
贾志明 1400 16
李爱民 200 16
刘爱华 800 16
解决方案 »
- 求根据订单长度，更新字段值的sql
- 遍历查询问题
- 大表更新如何调整性能
- 还有一个sql语言郁闷了好久（mysql跟oracle区别这么大呢(╰_╯）
- 各位大哥，请教关于数据库恢复的问题
- pro*c中如何用结构体插入数据啊
- vb连接服务器端oracle 数据库求救！
- 无法进入Enterprise Manager Console中总控制台
- 请问 pro*c 中 oracle错误1733是什么错误?
- 各位，请问在oracle--PL/SQL中执行存储过程是否有类似SQL Server的exec 命令
- 请问下 如何把SELECT * FROM T_STUDENT 的结果集插入到COLB字段？
- oracle中如何将某时间转换为给定时区对应的时间？
select fs_username,
md,pcj,
case
when md in(1 ,2,12) and pcj > 0 and pcj < 1500
then
'10%'
when md = 16
and pcj > 100 and pcj < 1500
then
'10%'
else
' '
end title
from (select fs_username, sum(pcj) pcj, md
from luru_table
group by fs_username, md)
select * from (select fs_username,sum(pcj) pcj,md
from luru_table where bill_statu = '审核' and audit_date between
to_date('2010-01-01','yyyy-mm-dd') and to_date('2010-01-31','yyyy-mm-dd')
group by fs_username,md
)
) t,(
select sum(pcj) tpcj,md tmd from (select fs_username,sum(pcj) pcj,md
from luru_table where bill_statu = '审核' and audit_date between
to_date('2010-01-01','yyyy-mm-dd') and to_date('2010-01-31','yyyy-mm-dd')
group by fs_username,md
) group by md) t1 where t.md = t1.tmd
select * from gjhohj_fsgl
FS_USERNAME PCJ MD TPCJ TMD
-------------------- ---------- -------------------- ---------- --------------------
贾志明 1400 16 2400 16
李爱民 200 16 2400 16
刘爱华 800 16 2400 16
case
when md in ('01','02','07','12','13','15','16') and tpcj<3000 then
pcj * 0.1
when md in ('01','02','07','12','13','15','16') and tpcj<5000 and tpcj>=3000 then
pcj * 0.15
when md in ('01','02','07','12','13','15','16') and tpcj>=5000 and tpcj<10000 then
pcj * 0.2
when md in ('01','02','07','12','13','15','16') and tpcj>=10000 then
pcj * 0.25
when md in ('04','06','08','09','10') and tpcj < 1000 then
pcj * 0.1
when md in ('04','06','08','09','10') and tpcj >=1500 and tpcj>3000 then
pcj * 0.15
when md in ('04','06','08','09','10') and tpcj >=3000 and tpcj<5000 then
pcj * 0.2
when md in ('04','06','08','09','10') and tpcj>5000 then
pcj * 0.25
end fpcj
from gjhohj_fsgl
-------------------- ---------- -------------------- ---------- ----------
贾志明 1400 16 2400 140
李爱民 200 16 2400 20
刘爱华 800 16 2400 80
select n5002,n5019,n5011,sum(n5011) over(partition by n5002 order by n5002),
round(n5011/sum(n5011) over(partition by n5002 order by n5002),6)
from n50 where n5001='01' and to_char(n5010,'yyyymmdd') between '20100123' and '20100130'
Connected as billing
SQL>
SQL> with luru_table as
2 (
3 select ' 李晓' FS_USERNAME,100 pcj, 12 md from dual
4 union all
5 select ' 马龙' FS_USERNAME,120 pcj, 12 md from dual
6 union all
7 select ' 贾志明' FS_USERNAME,1400 pcj, 16 md from dual
8 union all
9 select ' 李爱民' FS_USERNAME,200 pcj, 16 md from dual
10 union all
11 select ' 刘爱华' FS_USERNAME,800 pcj, 16 md from dual
12 )
13 select fs_username,
14 pcj,
15 md,
16 (case
17 when ((md in (1, 2, 12)) and pcj > 0 and pcj < 1500) then
18 '10%'
19 when (md = 16 and (pcj > 100 and pcj < 1500)) then
20 '10%'
21 else
22 'defalut'
23 end) pct
24 from (select t.fs_username,
25 t.pcj,
26 t.md,
27 sum(t.pcj) over(partition by t.md order by 1) tmd
28 from luru_table t) temp
29 /FS_USERNAME PCJ MD PCT
----------- ---------- ---------- -------
李晓 100 12 10%
马龙 120 12 10%
贾志明 1400 16 10%
李爱民 200 16 10%
刘爱华 800 16 10%SQL>
2 (
3 select ' 李晓' FS_USERNAME,100 pcj, 12 md from dual
4 union all
5 select ' 马龙' FS_USERNAME,120 pcj, 12 md from dual
6 union all
7 select ' 贾志明' FS_USERNAME,1400 pcj, 16 md from dual
8 union all
9 select ' 李爱民' FS_USERNAME,200 pcj, 16 md from dual
10 union all
11 select ' 刘爱华' FS_USERNAME,800 pcj, 16 md from dual
12 )
13 select fs_username,
14 pcj,
15 md,
16 (case
17 when ((md in (1, 2, 12)) and tmd > 0 and tmd < 1500) then
18 '10%'
19 when (md = 16 and (tmd > 100 and tmd < 1500)) then
20 '10%'
21 else
22 'defalut'
23 end) pct
24 from (select t.fs_username,
25 t.pcj,
26 t.md,
27 sum(t.pcj) over(partition by t.md order by 1) tmd
28 from luru_table t) temp
29 /FS_USERNAME PCJ MD PCT
----------- ---------- ---------- -------
马龙 120 12 10%
李晓 100 12 10%
李爱民 200 16 defalut
刘爱华 800 16 defalut
贾志明 1400 16 defalutSQL>
15:21:30 tina@PRACTICE> select * from tb;FS_USERNAME PCJ MD
-------------------- ---------- ----------
李晓 100 12
马龙 120 12
贾志明 1400 16
李爱民 200 16
刘爱华 800 16已用时间: 00: 00: 00.01
15:21:35 tina@PRACTICE> select FS_USERNAME,PCJ,MD,TPCJ,
15:21:41 2 case when MD in ('01','02','07','12','13','15','16') then (case when tpcj<3000 then PCJ*0.1 when TPCJ>=3000 and TPCJ<5000 then PCJ*0.15
15:21:41 3 when TPCJ>=5000 and TPCJ<10000 then PCJ*0.2 when TPCJ>=10000 then PCJ*0.25 end)
15:21:41 4 when MD in ('04','06','08','09','10') then (case when tpcj<1000 then PCJ*0.1 when TPCJ>=1500 and TPCJ<3000 then PCJ*0.15
15:21:41 5 when TPCJ>=3000 and TPCJ<5000 then PCJ*0.2 when TPCJ>=5000 then PCJ*0.25 end) end FPCJ
15:21:41 6 from (select rownum rn,FS_USERNAME,PCJ,MD,sum(PCJ) over(partition by MD) TPCJ from tb) order by rn;FS_USERNAME PCJ MD TPCJ FPCJ
-------------------- ---------- ---------- ---------- ----------
李晓 100 12 220 10
马龙 120 12 220 12
贾志明 1400 16 2400 140
李爱民 200 16 2400 20
刘爱华 800 16 2400 80已用时间: 00: 00: 00.01