CCNAIVE

1 提取geometry数据中心的一个点数据

2023/4/6

# 1 提取geometry数据中心的一个点数据

update tb_nzw_cybchjc set lon =st_X(ST_Centroid(geom)), lat=st_y(st_centroid(geom)

# 2 删除id、time都相同的重复数据，只保留一条

1、没有唯一标识的情况

//创建临时表

-- Create a temporary table to store the duplicate rows
CREATE TEMPORARY TABLE temp_duplicates AS
SELECT id, time
FROM your_table
GROUP BY id, time
HAVING COUNT(*) > 1;

-- Delete the duplicates from the original table using the temporary table
DELETE FROM your_table
WHERE (id, time) IN (SELECT id, time FROM temp_duplicates);

-- Drop the temporary table
DROP TEMPORARY TABLE temp_duplicates;

2.有唯一标识 rowid

DELETE t1 FROM your_table t1
JOIN your_table t2 
  ON t1.id = t2.id AND t1.time = t2.time 
  AND t1.rowid > t2.rowid;

# 3 查询结果集作为删除条件

DELETE c
FROM city_manage_vehicle c
JOIN (
    SELECT DISTINCT camera_index_code
    FROM city_manage_vehicle
    WHERE device_name NOT LIKE '湘%'
) temp ON c.camera_index_code = temp.camera_index_code;

# 4 过去24小时内的数据

SELECT id, TIME_FORMAT(create_time, '%H:%i') as createTime   FROM xxxxxxtable   WHERE create_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)

MySQL 数据库