select DISTINCT COLLECT_TIME, PERF_VALUE, t1.unit, RULEA, RULEB, RULEC, EQP_OBJ_ID, tar.OBJECT_ID from ( select COLLECT_TIME, PERF_VALUE, UNIT as unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME from t_perf_sensor_history tpsh, t_perf_defined tpd where PERF_OBJ_ID = tpd.OBJ_ID ) as t1 left join t_alarm_rule tar on IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE) where 1=1 and COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW() and EQP_OBJ_ID = '30625d79-56ae-4576-b278-24a7a1684856' and INDEX_NAME = '距离' EQP_OBJ_ID, tar.OBJECT_ID这个会有重复怎么办

如果EQP_OBJ_ID和tar.OBJECT_ID都会有重复，可以加上DISTINCT关键字来去重。例如：

SELECT DISTINCT t1.COLLECT_TIME, t1.PERF_VALUE, t1.unit, t1.RULEA, t1.RULEB, t1.RULEC, t1.EQP_OBJ_ID, tar.OBJECT_ID
FROM (
  SELECT COLLECT_TIME, PERF_VALUE, UNIT AS unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME 
  FROM t_perf_sensor_history tpsh, t_perf_defined tpd
  WHERE PERF_OBJ_ID = tpd.OBJ_ID
) AS t1
LEFT JOIN t_alarm_rule tar ON IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE)
WHERE 1=1
  AND COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()
  AND EQP_OBJ_ID = '30625d79-56ae-4576-b278-24a7a1684856'
  AND INDEX_NAME = '距离';

相关问题

select DISTINCT COLLECT_TIME, PERF_VALUE, t1.unit, RULEA, RULEB, RULEC from ( select COLLECT_TIME, PERF_VALUE, UNIT as unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME from t_perf_sensor_history tpsh, t_perf_defined tpd where PERF_OBJ_ID = tpd.OBJ_ID ) as t1 left join t_alarm_rule tar on IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE) where 1=1 and COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 62 DAY) AND now() order by COLLECT_TIME desc 怎末改才能避免重复数据

你可以在select子句中使用DISTINCT关键字去除重复数据，例如：

select DISTINCT COLLECT_TIME, PERF_VALUE, t1.unit, RULEA, RULEB, RULEC 
from ( 
    select COLLECT_TIME, PERF_VALUE, UNIT as unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME 
    from t_perf_sensor_history tpsh, t_perf_defined tpd 
    where PERF_OBJ_ID = tpd.OBJ_ID 
) as t1 
left join t_alarm_rule tar on IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE) 
where 1=1 and COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 62 DAY) AND now() 
order by COLLECT_TIME desc

另外，你也可以使用 GROUP BY 子句来去除重复数据，例如：

select COLLECT_TIME, PERF_VALUE, t1.unit, RULEA, RULEB, RULEC 
from ( 
    select COLLECT_TIME, PERF_VALUE, UNIT as unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME 
    from t_perf_sensor_history tpsh, t_perf_defined tpd 
    where PERF_OBJ_ID = tpd.OBJ_ID 
) as t1 
left join t_alarm_rule tar on IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE) 
where 1=1 and COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 62 DAY) AND now() 
group by COLLECT_TIME, PERF_VALUE, t1.unit, RULEA, RULEB, RULEC 
order by COLLECT_TIME desc

注意，使用 GROUP BY 语句时需要将 select 子句中的每个非聚合列都包含在 GROUP BY 子句中。

select DISTINCT COLLECT_TIME, PERF_VALUE, t1.unit, RULEA, RULEB, RULEC, EQP_OBJ_ID, tar.OBJECT_ID from ( select COLLECT_TIME, PERF_VALUE, UNIT as unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME from t_perf_sensor_history tpsh, t_perf_defined tpd where PERF_OBJ_ID = tpd.OBJ_ID ) as t1 left join t_alarm_rule tar on IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE) where 1=1 and COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW() and EQP_OBJ_ID = '30625d79-56ae-4576-b278-24a7a1684856' and INDEX_NAME = '距离' 加上判断EQP_OBJ_ID, tar.OBJECT_ID是否相等

如果要加上判断EQP_OBJ_ID和tar.OBJECT_ID是否相等，可以使用一个子查询来先去重，然后再加上判断条件。例如：

SELECT COLLECT_TIME, PERF_VALUE, unit, RULEA, RULEB, RULEC, EQP_OBJ_ID, OBJECT_ID
FROM (
  SELECT DISTINCT t1.COLLECT_TIME, t1.PERF_VALUE, t1.unit, t1.RULEA, t1.RULEB, t1.RULEC, t1.EQP_OBJ_ID, tar.OBJECT_ID
  FROM (
    SELECT COLLECT_TIME, PERF_VALUE, UNIT AS unit, EQP_OBJ_ID, OBJECT_TYPE, INDEX_TYPE, DEVICE_ID, INDEX_NAME 
    FROM t_perf_sensor_history tpsh, t_perf_defined tpd
    WHERE PERF_OBJ_ID = tpd.OBJ_ID
  ) AS t1
  LEFT JOIN t_alarm_rule tar ON IF(tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = t1.DEVICE_ID, tar.ALARM_OBJECT_TYPE = OBJECT_TYPE)
  WHERE COLLECT_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()
    AND EQP_OBJ_ID = '30625d79-56ae-4576-b278-24a7a1684856'
    AND INDEX_NAME = '距离'
) AS t2
WHERE EQP_OBJ_ID = OBJECT_ID;

这样可以先用子查询将EQP_OBJ_ID和OBJECT_ID去重，然后再筛选出相等的记录。