欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql-MYSQL语句优化,在线等

程序员文章站 2024-02-01 12:36:52
...
mysql

SELECT tt.int_interpreting_data_id,tt.dt_report_date,tn.str_norm_number,tnt.str_norm_type_name,tt.int_whether,tt.str_value,tt.str_detection_result,tt.str_remark,tn.str_name,tn.int_response_type,tt.str_device_name,tt.str_ip,tt.db_name,tn.str_code
FROM t_norm tn LEFT JOIN
(
SELECT tid1.str_code,tid1.int_interpreting_data_id,tid1.dt_report_date,tid1.str_norm_type_name,tid1.int_whether,tid1.str_value,tid1.str_detection_result,tid1.str_remark,tid1.str_device_name,tid1.str_ip,tid1.db_name,tid1.str_norm_number FROM t_interpreting_data tid1,
(
SELECT str_code,int_interpreting_data_id,str_norm_type_name,int_whether,str_value,str_detection_result,str_remark,str_device_name,str_ip,db_name,str_norm_number,max(dt_report_date) max_date FROM t_interpreting_data WHERE 1=1
AND str_property_id='Windows 7_852'
AND STR_TO_DATE(dt_report_date,'%Y-%m-%d') ='2015-06-05'
GROUP BY str_norm_number,db_name
) tid2
WHERE tid1.dt_report_date=tid2.max_date
GROUP BY tid1.str_norm_number,tid1.db_name
ORDER BY tid1.dt_report_date DESC
) tt on tn.str_norm_number=tt.str_norm_number
LEFT JOIN t_norm_type tnt ON tn.int_norm_type_id=tnt.int_norm_type_id
WHERE tn.str_code IN (SELECT str_code FROM t_interpreting_data WHERE str_property_id='Windows 7_852' GROUP BY str_code)
ORDER BY tt.str_norm_number DESC

1.以t_norm表为标准
2.查找 t_interpreting_data表中同一个类型最新的记录,同时根据db_name分组

相关标签: mysql