语句如下:
SELECT user_id,SERIAL_NUMBER,eparchy_code,Discount_code,effect_value,price,sumofee,sumdisfee,remind_tag
FROM (
SELECT a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price,
SUM(b.ofee) sumofee,SUM(b.discount_fee) sumdisfee,
( SELECT decode(c.remind_tag,null,0,c.remind_tag)
FROM ti_a_gprssmssend c where c.user_id=b.user_id and
c.discount_id=b.Discount_code
) remind_tag
FROM ti_a_user_all a,tp_a_user_gprsbdetailinfo_0 b
WHERE a.USER_ID=b.user_id AND b.Discount_code<>-1 and effect_item_code<>-1
AND b.discount_fee<>-1
GROUP BY a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price
)
WHERE effect_value-sumdisfee<1024 ; ============================================
语句执行会报分组错误,如果将语句改成下面的就可以执行:
SELECT user_id,SERIAL_NUMBER,eparchy_code,Discount_code,effect_value,price,sumofee,sumdisfee,remind_tag
FROM (
SELECT a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price,
SUM(b.ofee) sumofee,SUM(b.discount_fee) sumdisfee,
( SELECT 0 ------这里的select语句做了修改
FROM dual ) remind_tag
FROM ti_a_user_all a,tp_a_user_gprsbdetailinfo_0 b
WHERE a.USER_ID=b.user_id AND b.Discount_code<>-1 and effect_item_code<>-1
AND b.discount_fee<>-1
GROUP BY a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price
)
WHERE effect_value-sumdisfee<1024 ;请问如何修改上面的语句,让语句能正确执行。
另外,这种语句效率如何,我想用这个语句的结果放入游标做循环,如果中间select remind_tag放到后面执行效率会不会高一点!
在线等!
SELECT user_id,SERIAL_NUMBER,eparchy_code,Discount_code,effect_value,price,sumofee,sumdisfee,remind_tag
FROM (
SELECT a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price,
SUM(b.ofee) sumofee,SUM(b.discount_fee) sumdisfee,
( SELECT decode(c.remind_tag,null,0,c.remind_tag)
FROM ti_a_gprssmssend c where c.user_id=b.user_id and
c.discount_id=b.Discount_code
) remind_tag
FROM ti_a_user_all a,tp_a_user_gprsbdetailinfo_0 b
WHERE a.USER_ID=b.user_id AND b.Discount_code<>-1 and effect_item_code<>-1
AND b.discount_fee<>-1
GROUP BY a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price
)
WHERE effect_value-sumdisfee<1024 ; ============================================
语句执行会报分组错误,如果将语句改成下面的就可以执行:
SELECT user_id,SERIAL_NUMBER,eparchy_code,Discount_code,effect_value,price,sumofee,sumdisfee,remind_tag
FROM (
SELECT a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price,
SUM(b.ofee) sumofee,SUM(b.discount_fee) sumdisfee,
( SELECT 0 ------这里的select语句做了修改
FROM dual ) remind_tag
FROM ti_a_user_all a,tp_a_user_gprsbdetailinfo_0 b
WHERE a.USER_ID=b.user_id AND b.Discount_code<>-1 and effect_item_code<>-1
AND b.discount_fee<>-1
GROUP BY a.USER_ID,a.SERIAL_NUMBER,a.eparchy_code,b.Discount_code,b.effect_value,b.price
)
WHERE effect_value-sumdisfee<1024 ;请问如何修改上面的语句,让语句能正确执行。
另外,这种语句效率如何,我想用这个语句的结果放入游标做循环,如果中间select remind_tag放到后面执行效率会不会高一点!
在线等!
放在后面吧,在最后那个where前面放子查询,在where条件里面在加上两个查询的匹配条件.
FROM ti_a_gprssmssend c where c.user_id=b.user_id and
c.discount_id=b.Discount_code
) remind_tag remind_tag 这个不是分组中的字段
你的目的不就是想在c.remind_tag是空的情况下 返回0
否则用原值 那为什么不用 nvl(c.remind_tag,0)
还有你下面的分组字段里面没有用到remind_tag