2 lim t LIm_T 于 2015.06.05 16:00 提问

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分组

1个回答

LIm_T
LIm_T   2015.06.05 16:13

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

Csdn user default icon
上传中...
上传图片
插入图片