如何筛选bsr_pickpack表中存在非空转空sales_rank的重复asin

问题说明 #

我有一张名为bsr_pickpack的表，包含两个字段：

• asin：STRING类型
• sales_rank：INTEGER类型

部分asin会重复出现，其中部分重复的asin既有sales_rank非空的记录，也有后续sales_rank为null的记录。我需要获取这类asin的列表，当前使用的SQL语句如下：

SELECT asin,sales_rank
from bsr_pickpack
WHERE asin !='' AND (sales_rank is not null)
and asin in(
select asin from bsr_pickpack where not (sales_rank is not null)
)

优化后的查询方案 #

1. 仅获取符合条件的去重asin列表 #

如果只需要得到满足条件的asin集合，用下面的语句更高效清晰：

SELECT DISTINCT asin
FROM bsr_pickpack
WHERE asin != ''
GROUP BY asin
HAVING COUNT(CASE WHEN sales_rank IS NOT NULL THEN 1 END) > 0
   AND COUNT(CASE WHEN sales_rank IS NULL THEN 1 END) > 0

2. 获取这类asin的所有相关记录（含空/非空sales_rank） #

如果需要查看这些asin的全部记录，包括sales_rank为空和非空的条目，可以用嵌套查询：

SELECT asin, sales_rank
FROM bsr_pickpack
WHERE asin IN (
    SELECT asin
    FROM bsr_pickpack
    WHERE asin != ''
    GROUP BY asin
    HAVING COUNT(CASE WHEN sales_rank IS NOT NULL THEN 1 END) > 0
       AND COUNT(CASE WHEN sales_rank IS NULL THEN 1 END) > 0
)

方案说明 #

• 原SQL的子查询not (sales_rank is not null)可以简化为sales_rank is null，可读性更强
• 用GROUP BY + HAVING的方式直接筛选出同时存在两种记录的asin，避免了多次全表扫描，性能更优
• COUNT(CASE ...)用来统计对应条件的记录数，两个统计值都大于0则说明该asin同时满足既有非空排名又有空排名的要求

内容的提问来源于stack exchange，提问作者MS Access GuruJi