如何正确将PostgreSQL查询语句转换为MySQL语句？

我来帮你拆解下原PostgreSQL查询的核心逻辑：它从table2中筛选出时间范围在2018-03-19 00:00:00到2018-03-20 00:00:00的记录，然后对每条记录，单独统计table1中对应id且state为processing的行数。

你之前的转换遇到了查询效率极低和结果完全不准的问题，下面分别分析原因并给出解决方案：

问题根源分析 #

1. 第一个转换版本慢的原因：
   你用FROM_UNIXTIME(time)转换时间戳后再和日期字符串比较，这会导致table2的time字段索引直接失效，MySQL不得不做全表扫描。再加上关联子查询会对table2的每条符合条件的记录都去查询一次table1，数据量大时效率自然极差。

2. 第二个转换版本结果不准的原因：
   你在子查询里额外JOIN了table2，这会让子查询统计的是所有符合时间条件的table2记录对应的table1总行数，而不是每条o.id单独对应的数量，完全偏离了原查询的逻辑。

正确的转换方案 #

方案1：优化关联子查询（保留原逻辑，提升效率） #

把时间条件的转换逻辑反过来：将日期字符串转成时间戳，直接和time字段比较，这样可以利用time字段的索引，同时保留原查询的关联子查询逻辑：

SELECT 
    (SELECT COUNT(1) FROM table1 WHERE id = o.id AND state = 'processing') AS number
FROM table2 o 
WHERE o.time >= UNIX_TIMESTAMP('2018-03-19') 
  AND o.time <= UNIX_TIMESTAMP('2018-03-20');

这个版本和原PostgreSQL查询的逻辑完全一致，同时解决了索引失效导致的慢查询问题。

方案2：改用JOIN+GROUP BY（更高效的批量统计方式） #

如果table2的数据量较大，关联子查询的效率还是不够，推荐先预统计table1中每个id的processing状态行数，再和table2筛选后的结果左连接，这样只需要扫描table1一次：

SELECT 
    o.id,
    COALESCE(l.count_processing, 0) AS number -- 用COALESCE确保没有匹配的id返回0而不是NULL
FROM table2 o
LEFT JOIN (
    SELECT id, COUNT(1) AS count_processing
    FROM table1 
    WHERE state = 'processing'
    GROUP BY id
) l ON l.id = o.id
WHERE o.time >= UNIX_TIMESTAMP('2018-03-19') 
  AND o.time <= UNIX_TIMESTAMP('2018-03-20');

这个方案的效率比关联子查询高很多，尤其是当table2有大量符合条件的记录时。

额外优化建议 #

• 给table1创建id+state的联合索引：CREATE INDEX idx_table1_id_state ON table1(id, state);，这样统计时能快速定位符合条件的数据。
• 给table2的time字段创建索引：CREATE INDEX idx_table2_time ON table2(time);，进一步提升时间范围筛选的速度。

内容的提问来源于stack exchange，提问作者rose