常用sql

select * from t_executor;

insert into t_executor (executor_ip) values ('1.1.1.1');

update t_executor set executor_ip = '192.168.3.10' where id=20;

delete from t_executor where id=20;

select 
id, count(id) 
from 
t_r2_result 
where 
to_time 
BETWEEN 
'2023-08-29 10:00:00' and '2023-08-29 20:00:00' 
group by id;

select id, count(id), from_time
from t_r1_result 
where
from_time BETWEEN '2023-08-29 10:00:00' and '2023-08-29 20:00:00'
GROUP BY id ORDER BY from_time ;

select * from t_r2_result as a  
join
(select 
r2.auto_id, r2.id, r2.slot_num, r2.r2_name, r2.result, r2.from_time, r2.to_time,
sum(case when result='pass' then 1 else 0 end) as 成功,
sum(case when result='fail' then 1 else 0 end) as 失败
from t_r2_result r2 where r1_id = '56e19399eac447cd98d29cba3d48e4be' group by r2_name) as b on a.auto_id = b.auto_id 
where a.r1_id = '56e19399eac447cd98d29cba3d48e4be'  group by a.r2_name;

select max(auto_id) from t_r2_result where r1_id = '56e19399eac447cd98d29cba3d48e4be' group by r2_name;

select *
from t_r3_result 
where r2_id='2d50ca4e3c124c7c8b4b4e27c9389501';

#--r2汇总页---------------
SELECT t1.r1_id,  t1.auto_id, t2.max_auto_id, t1.id, t1.slot_num, t1.r2_name, t1.result, t1.from_time, t1.to_time,
t2.成功, t2.失败, (case when t2.失败>0 then 'failf' else 'passp' end) as 状态
FROM t_r2_result t1
INNER JOIN (
    SELECT max(auto_id) AS max_auto_id,
        sum(case when result='pass' then 1 else 0 end) as 成功,
        sum(case when result='fail' then 1 else 0 end) as 失败
    FROM t_r2_result
        where r1_id = '56e19399eac447cd98d29cba3d48e4be'
    GROUP BY r2_name
) t2 ON t1.auto_id = t2.max_auto_id;

#----------------
select id, r2_name from t_r2_result as a  where a.r1_id = '56e19399eac447cd98d29cba3d48e4be' and r2_name = 'camera_fps';

#--查看r3子项详情--------------------
select * from t_r3_result t1 where r2_id in (select id from t_r2_result as a  where a.r1_id = '56e19399eac447cd98d29cba3d48e4be' and r2_name = 'camera_fps')
and t1.r3_name='Camera FPS test';

#--勾选查看r2测试详情r3汇总--速度快--------------------
select 
t1.*,    t4.成功, t4.失败
from t_r3_result t1 
inner JOIN
    (select max(t2.auto_id) as max_auto_id,
            sum(case when t2.result='pass' then 1 else 0 end) as 成功,
            sum(case when t2.result='fail' then 1 else 0 end) as 失败
    from t_r3_result t2
    where t2.r2_id in 
        (select id from t_r2_result a 
        where a.r1_id = '56e19399eac447cd98d29cba3d48e4be' and r2_name = 'camera_fps')
    GROUP BY t2.r3_name) t4 
on t4.max_auto_id = t1.auto_id;

#--勾选查看r2测试详情r3汇总--速度较慢------------------
select t1.* from t_r3_result t1 
where t1.auto_id in
    (select max(t2.auto_id) as max_auto_id from t_r3_result t2
    where t2.r2_id in 
        (select id from t_r2_result a 
        where a.r1_id = '56e19399eac447cd98d29cba3d48e4be' and r2_name = 'camera_fps')
    GROUP BY t2.r3_name);

#----------------------
select * from t_r1_result ORDER BY from_time desc limit 10;

#----------------------
select * from t_r2_result where r1_id = '81cf5c7b2c8d45b3882cb5787427735c' GROUP BY r2_name;

#----------------------
set @var='81cf5c7b2c8d45b3882cb5787427735c';
SELECT t1.r1_id,  t1.auto_id, t2.max_auto_id, t1.id, t1.slot_num, t1.r2_name, t1.result, t1.from_time, t1.to_time,
t2.成功, t2.失败, (case when t2.失败>0 then 'failf' else 'passp' end) as 状态
FROM t_r2_result t1
INNER JOIN (
    SELECT max(auto_id) AS max_auto_id,
        sum(case when result='pass' then 1 else 0 end) as 成功,
        sum(case when result='fail' then 1 else 0 end) as 失败
    FROM t_r2_result
        where r1_id = @var
    GROUP BY r2_name
) t2 ON t1.auto_id = t2.max_auto_id;

#----------------------
select * from t_r3_result t3 inner join
    (select max(t1.auto_id) as max_id from t_r3_result t1 where t1.r2_id in
        (select t2.id 
        from t_r2_result t2 
        where r1_id = '81cf5c7b2c8d45b3882cb5787427735c' and r2_name = 'uss_check') 
    group by t1.r3_name) t4
on t4.max_id = t3.auto_id;

select * from t_r3_result t1 where r2_id  in 
    (select id from t_r2_result t2
    where t2.r1_id = '81cf5c7b2c8d45b3882cb5787427735c' and t2.r2_name = 'uss_check')
and t1.r3_name = 'get uss_id';

select * from t_r3_result t1 inner join
    (select id from t_r2_result t2
    where t2.r1_id = '81cf5c7b2c8d45b3882cb5787427735c' and t2.r2_name = 'uss_check') t3
on t1.r2_id = t3.id
where t1.r3_name = 'get uss_id';

select * from t_test_case;

select * from t_r1_result limit 100;

EXPLAIN select id from t_r1_result;

delete from t_executor where id=5 or id=6;

show status;

show status like 'slow_queries';

show variables like 'long_query_time';