select distinct d.name, t.qry_code ,b.info_query_times from qry_log t left join tdcode b on t.qry_code = b.code_info left join
tdcode_batch c on b.sys_batch = c.sys_batch left join product d on c.product_id = d.id left join qry_exception_def e on d.id = e.product_id
where b.info_query_times>=e.qry_times
and e.period >= (select ((max(to_date(to_char(a.qry_time,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))-min(to_date(to_char(a.qry_time,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))))*24
from qry_log a group by a.qry_code)
在这里and e.period >= 出现了单行子查询出现多个行的错误,请问谁能帮我改过来
tdcode_batch c on b.sys_batch = c.sys_batch left join product d on c.product_id = d.id left join qry_exception_def e on d.id = e.product_id
where b.info_query_times>=e.qry_times
and e.period >= (select ((max(to_date(to_char(a.qry_time,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))-min(to_date(to_char(a.qry_time,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))))*24
from qry_log a group by a.qry_code)
在这里and e.period >= 出现了单行子查询出现多个行的错误,请问谁能帮我改过来
from qry_log t left join tdcode b on t.qry_code = b.code_info
left join tdcode_batch c on b.sys_batch = c.sys_batch
left join product d on c.product_id = d.id
left join qry_exception_def e on d.id = e.product_id
where b.info_query_times >= e.qry_times
and exists (select 1 from qry_log a
where a.qry_code = t.qry_code
group by a.qry_code
having (max(a.qry_time)-min(a.qry_time))*24 <=e.period);
这句是关键
这句是关键 ,不过就是写不出来谢谢
tdcode_batch c on b.sys_batch = c.sys_batch left join product d on c.product_id = d.id left join qry_exception_def e on d.id = e.product_id
where b.info_query_times>=e.qry_times
and e.period >= (select ((max(to_date(to_char(a.qry_time,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))-min(to_date(to_char(a.qry_time,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))))*24
from qry_log a where qry_code=t.qry_code)
可以DISTINCT去重SELECT DISTINCT d.name, t.qry_code, b.info_query_times
FROM qry_log t
LEFT JOIN tdcode b
ON t.qry_code = b.code_info
LEFT JOIN tdcode_batch c
ON b.sys_batch = c.sys_batch
LEFT JOIN product d
ON c.product_id = d.id
LEFT JOIN qry_exception_def e
ON d.id = e.product_id
WHERE b.info_query_times >= e.qry_times AND
e.period >=
(SELECT DISTINCT (MAX(a.qry_time) - MIN(a.qry_time)) * 24 FROM qry_log a GROUP BY a.qry_code);