这里可以用OR代替UNION吗？

显示全部楼层 · 2007-9-26 17:10:34

select dm_call_ticket.CITY_CODE,city_name,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE)as fee1,0 as fee2,0 as fee3,0 as fee4,0 as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and to_char(snap_date,'yyyymmdd') =20060406

group by dm_call_ticket.CITY_CODE,city_name

)

union
(

select dm_call_ticket.city_code,city_name,0 as fee1,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE) as fee2,0 as fee3,0 as fee4,0 as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and snap_date =to_date(20060408,'YYYYMMDD')-1

group by dm_call_ticket.CITY_CODE,city_name

)

union
(

select dm_call_ticket.city_code,city_name,0 as fee1,0 as fee2,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE) as fee3 ,null as fee4,null as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and snap_date =to_date(20060408,'YYYYMMDD')-7

group by dm_call_ticket.CITY_CODE,city_name
--------------------------------------------------------------------------------------------------------------------
select dm_call_ticket.CITY_CODE,city_name,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE)as fee1,0 as fee2,0 as fee3,0 as fee4,0 as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and

(to_char(snap_date,'yyyymmdd') =20060406

or snap_date =to_date(20060408,'YYYYMMDD')-1

or snap_date =to_date(20060408,'YYYYMMDD')-7

)

group by dm_call_ticket.CITY_CODE,city_name
用下面的语句代替上面的语句可以吗，两个语句的查询结果有可能不一样吗？

千问 · 2007-9-26 17:10:34

不说你的fee2，fee3字段的值了，这样得出的记录数都很可能不同啊

千问 · 2007-9-26 17:10:34

是啊，实际数据就是不同的，可是我想不明白为什么会不同呢？
所有的条件都是一样的只是时间不一样用OR为什么就不可以呢？

千问 · 2007-9-26 17:10:34

最初由 sinaisen 发布
[B]是啊，实际数据就是不同的，可是我想不明白为什么会不同呢？
所有的条件都是一样的只是时间不一样用OR为什么就不可以呢？ [/B]
用OR替代UNION, 很好的主意啊, 但要注意SQL的相应变化呢.
这个SQL是最好的可代换的例子, 加油!

千问 · 2007-9-26 17:10:34

lz写的sql中除了一些问题, 大的问题是两个部分的逻辑是不同的
下面是个人修改后的两种描述
[php]
TOLL_FEE + INFO_FEE + OTHER_FEE + FAVOUR_BASE_FEE + FAVOUR_TOLL_FEE + FAVOUR_INFO_FEE + FAVOUR_OTHER_FEE
有点长, 用 fees 代替(为了看起来方便一点)
select dct.city_code,
city_name,
sum(fees) as fee1,
0 as fee2,
0 as fee3,
0 as fee4,
0 as fee5,
snap_date
from dm_call_ticket dct, etl.city c
where dct.city_code = city.city_code
and snap_date = to_date('20060406', 'YYYYMMDD')
group by dct.city_code, city_name, snap_date
union all
select dct.city_code,
city_name,
0 as fee1,
sum(fees) as fee2,
0 as fee3,
0 as fee4,
0 as fee5,
snap_date
from dm_call_ticket dct, etl.city c
where dct.city_code = city.city_code
and snap_date = to_date('20060408', 'YYYYMMDD') - 1
group by dct.city_code, city_name, snap_date
union all
select dct.city_code,
city_name,
0 as fee1,
0 as fee2,
sum(fees) as fee3,
0 as fee4,
0 as fee5,
snap_date
from dm_call_ticket dct, etl.city c
where dct.city_code = city.city_code
and snap_date = to_date('20060408', 'YYYYMMDD') - 7
group by dct.city_code, city_name, snap_date;
---------------------------------------------------------------------------------------------
select dct.city_code,
city_name,
sum(case when snap_date=to_date('20060406', 'YYYYMMDD') then fees else 0 end) as fee1,
sum(case when snap_date=to_date('20060408', 'YYYYMMDD') - 1 then fees else 0 end) as fee2,
sum(case when snap_date=to_date('20060408', 'YYYYMMDD') - 7 then fees else 0 end) as fee3,
0 as fee4,
0 as fee5,
snap_date
from dm_call_ticket dct, etl.city c
where dct.city_code = city.city_code
and snap_date in (to_date('20060406', 'YYYYMMDD'),

to_date('20060408', 'YYYYMMDD') - 1,

to_date('20060408', 'YYYYMMDD') - 7)
group by dct.city_code, city_name, snap_date;
----------------------------------------------------------------------------------------
[/php]

千问 · 2007-9-26 17:10:34

还是要看看实际PLAN是什么，有些时候Oracle会把Or转换为UNION。

千问 · 2007-9-26 17:10:34

最初由 sinaisen 发布
[B]select dm_call_ticket.CITY_CODE,city_name,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE)as fee1,0 as fee2,0 as fee3,0 as fee4,0 as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and to_char(snap_date,'yyyymmdd') =20060406

group by dm_call_ticket.CITY_CODE,city_name

)

union
(

select dm_call_ticket.city_code,city_name,0 as fee1,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE) as fee2,0 as fee3,0 as fee4,0 as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and snap_date =to_date(20060408,'YYYYMMDD')-1

group by dm_call_ticket.CITY_CODE,city_name

)

union
(

select dm_call_ticket.city_code,city_name,0 as fee1,0 as fee2,sum(TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE) as fee3 ,null as fee4,null as fee5

from dm_call_ticket,etl.city

where dm_call_ticket.CITY_CODE=city.city_code

and snap_date =to_date(20060408,'YYYYMMDD')-7

group by dm_call_ticket.CITY_CODE,city_name
[/B]
这个应该和上面的结果是一样的，你的语句其实是一个行列转换。
select dm_call_ticket.city_code, city_name,
sum(decode(snap_date, to_date('20060406', 'yyyymmdd'), TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE, 0)) fee1,
sum(decode(snap_date, to_date('20060408', 'yyyymmdd') - 1, TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE, 0)) fee2,
sum(decode(snap_date, to_date('20060408', 'yyyymmdd') - 7, TOLL_FEE+INFO_FEE+OTHER_FEE+FAVOUR_BASE_FEE+FAVOUR_TOLL_FEE+FAVOUR_INFO_FEE+FAVOUR_OTHER_FEE, 0)) fee3,
0 as fee4, 0 as fee5
from dm_call_ticket, etl.city
where
dm_call_ticket.CITY_CODE=city.city_code
and snap_date in (to_date('20060406', 'yyyymmdd'), to_date('20060408', 'yyyymmdd') - 1, to_date('20060408', 'yyyymmdd') - 7)
group by dm_call_ticket.CITY_CODE,city_name;

Jacky手好快