17,137
社区成员
发帖
与我相关
我的任务
分享sql优化
通过程序去访问，需要1.5分的样子才能返回结果，请问怎么优化sql语句
以下是语句
select u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.c_meter_number,
u.n_area_id,
u.c_user_address,
u.c_user_phone,
p.c_properties_name,
u.n_amount as n_now_balance,
t.c_meter_reader_name,
co.c_community_name,
re.n_reading_status,
s.d_meter_reading_date,
u.n_enter_type,
decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') as type,
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999'))
from yx_detail d
where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
min(s.n_degrees_start) as minStart,
max(s.n_degrees_end) as maxEnd,
sum(s.n_real_dosage) as totalDosage,
sum(s.n_amount) as totalAmount,
s.c_situation_use_month,
decode(s.n_charge_state, 0, '欠费', '已缴费') as chargeState
from yx_situation s , yx_user u
,yx_model m
,yx_properties p
, yx_mobile_reading_record re
, yx_community co
, yx_meter_reader t
where
1=1
and s.c_user_id = u.c_user_id
and s.n_model_id = m.n_model_id
and u.n_properties_id = p.n_properties_id
<!-- and re.c_user_id=u.c_user_id -->
and co.n_company_id=u.n_company_id
and t.n_company_id=u.n_company_id
<if test="userid!=null">
and u.c_user_id=#{userid}
</if>
<if test="meterNumber!=null">
and u.c_meter_number=#{meterNumber}
</if>
group by u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.n_enter_type,
u.n_company_id,
t.c_meter_reader_name,
co.c_community_name,
re.n_reading_status,
p.c_properties_name,
u.c_user_address,
u.n_area_id,
u.c_user_phone,
u.n_meter_type,
u.n_amount,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
s.c_situation_use_month,
s.n_charge_state,
s.d_meter_reading_date,
s.n_situation_id
order by s.c_situation_use_month desc
以下是语句
select u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.c_meter_number,
u.n_area_id,
u.c_user_address,
u.c_user_phone,
p.c_properties_name,
u.n_amount as n_now_balance,
t.c_meter_reader_name,
co.c_community_name,
re.n_reading_status,
s.d_meter_reading_date,
u.n_enter_type,
decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') as type,
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999'))
from yx_detail d
where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
min(s.n_degrees_start) as minStart,
max(s.n_degrees_end) as maxEnd,
sum(s.n_real_dosage) as totalDosage,
sum(s.n_amount) as totalAmount,
s.c_situation_use_month,
decode(s.n_charge_state, 0, '欠费', '已缴费') as chargeState
from yx_situation s , yx_user u
,yx_model m
,yx_properties p
, yx_mobile_reading_record re
, yx_community co
, yx_meter_reader t
where
1=1
and s.c_user_id = u.c_user_id
and s.n_model_id = m.n_model_id
and u.n_properties_id = p.n_properties_id
<!-- and re.c_user_id=u.c_user_id -->
and co.n_company_id=u.n_company_id
and t.n_company_id=u.n_company_id
<if test="userid!=null">
and u.c_user_id=#{userid}
</if>
<if test="meterNumber!=null">
and u.c_meter_number=#{meterNumber}
</if>
group by u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.n_enter_type,
u.n_company_id,
t.c_meter_reader_name,
co.c_community_name,
re.n_reading_status,
p.c_properties_name,
u.c_user_address,
u.n_area_id,
u.c_user_phone,
u.n_meter_type,
u.n_amount,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
s.c_situation_use_month,
s.n_charge_state,
s.d_meter_reading_date,
s.n_situation_id
order by s.c_situation_use_month desc
...全文
请发表友善的回复…
发表回复
wkc1203 2017-12-12
- 打赏
- 举报
没有优化，把这个sql 语句拆分为两个分开查询了，问题解决了，结贴，感谢楼上的大佬们
minsic78 2017-12-04
- 打赏
- 举报
没有执行计划的SQL优化都是耍流氓 
wkc1203 2017-12-01
- 打赏
- 举报
这个没用过，要学习下
wkc1203 2017-12-01
- 打赏
- 举报
没有啊，，好难，[/quote]
不难，你把这个SQL语句格式化下。[/quote]
SELECT u.c_old_user_id, u.c_user_id, u.c_user_name, u.c_meter_number, u.n_area_id
, u.c_user_address, u.c_user_phone, p.c_properties_name, u.n_amount AS n_now_balance, co.c_area_name
, re.n_reading_status, s.d_meter_reading_date, u.n_enter_type, decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') AS type, (
SELECT wm_concat(to_char(d.n_detail_price, 'fm9999990.9999'))
FROM yx_detail d
WHERE d.n_cost_id = 1
AND d.n_situation_id = s.n_situation_id
) AS detailPrice
, m.c_model_name, u.c_meter_number, p.c_properties_name, MIN(s.n_degrees_start) AS minStart, MAX(s.n_degrees_end) AS maxEnd
, SUM(s.n_real_dosage) AS totalDosage, SUM(s.n_amount) AS totalAmount, s.c_situation_use_month, decode(s.n_charge_state, 0, '欠费', '已缴费') AS chargeState
FROM yx_situation s
JOIN yx_user u ON s.c_user_id = u.c_user_id
JOIN yx_model m ON s.n_model_id = m.n_model_id
JOIN yx_properties p ON u.n_properties_id = p.n_properties_id
JOIN yx_area co ON co.n_area_id = u.n_area_id
LEFT JOIN yx_mobile_reading_record re ON u.c_user_id = re.c_user_id
LEFT JOIN yx_meter_reader t ON t.n_company_id = u.n_company_id
WHERE 1 = 1
AND u.c_user_id = 19090540200327
GROUP BY u.c_old_user_id, u.c_user_id, u.c_user_name, u.n_enter_type, u.n_company_id, re.n_reading_status, co.c_area_name, p.c_properties_name, u.c_user_address, u.n_area_id, u.c_user_phone, u.n_meter_type, u.n_amount, m.c_model_name, u.c_meter_number, p.c_properties_name, s.c_situation_use_month, s.n_charge_state, s.d_meter_reading_date, s.n_situation_id, s.n_degrees_end
ORDER BY s.c_situation_use_month DESC, s.n_degrees_end DESC
wkc1203 2017-12-01
- 打赏
- 举报
没有啊，，好难，[/quote]
不难，你把这个SQL语句格式化下。[/quote]
格式化什么意思，有什么好处？？
select u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.c_meter_number,
u.n_area_id,
u.c_user_address,
u.c_user_phone,
p.c_properties_name,
u.n_amount as n_now_balance,
co.c_area_name,
re.n_reading_status,
s.d_meter_reading_date,
u.n_enter_type,
decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') as type,
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999'))
from yx_detail d
where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
min(s.n_degrees_start) as minStart,
max(s.n_degrees_end) as maxEnd,
sum(s.n_real_dosage) as totalDosage,
sum(s.n_amount) as totalAmount,
s.c_situation_use_month,
decode(s.n_charge_state, 0, '欠费', '已缴费') as chargeState
from yx_situation s join yx_user u on s.c_user_id = u.c_user_id
join yx_model m on s.n_model_id = m.n_model_id
join yx_properties p on u.n_properties_id = p.n_properties_id
join yx_area co on co.n_area_id=u.n_area_id
left join yx_mobile_reading_record re on u.c_user_id=re.c_user_id
left join yx_meter_reader t on t.n_company_id=u.n_company_id
where
1=1
group by u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.n_enter_type,
u.n_company_id,
re.n_reading_status,
co.c_area_name,
p.c_properties_name,
u.c_user_address,
u.n_area_id,
u.c_user_phone,
u.n_meter_type,
u.n_amount,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
s.c_situation_use_month,
s.n_charge_state,
s.d_meter_reading_date,
s.n_situation_id,
s.n_degrees_end
order by s.c_situation_use_month desc,s.n_degrees_end desc
碧水幽幽泉 2017-12-01
- 打赏
- 举报
没有啊，，好难，[/quote]
不难，你把这个SQL语句格式化下。
wkc1203 2017-12-01
- 打赏
- 举报
用了下，一直报未明确定义列 00918
wkc1203 2017-12-01
- 打赏
- 举报
就是希望select里的字段都显示出来，但是select里有的字段，group by 里也必须要有，很恼火，
wkc1203 2017-12-01
- 打赏
- 举报
没有啊，，好难，
「已注销」 2017-12-01
- 打赏
- 举报
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999'))
from yx_detail d
where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice,
这句话慢，把这个函数换成listagg，而且这样查询是走嵌套查询的，查全量数据的时候会慢，把子查询改成表关联查询
「已注销」 2017-12-01
- 打赏
- 举报
这个没用过，要学习下[/quote]
因为wm_concate返回的是大字段，而listagg是单独的拼接而已，还是varchar2类型，这个只是sql改写而已，做个子查询还不懂吗？
jdsnhan 2017-12-01
- 打赏
- 举报
贴出执行计划，大家一起帮你分析
碧水幽幽泉 2017-11-30
- 打赏
- 举报
SQL语句还没调整？
alex259 2017-11-30
- 打赏
- 举报
子查询中又行专列的函数，直接用with table as写成临时表再做关联吧，应该会快不少
碧水幽幽泉 2017-11-30
- 打赏
- 举报
怎么还有 <!-- and re.c_user_id=u.c_user_id --> <if </if>？
请调整好SQL再发一次。
请调整好SQL再发一次。
