如何查询最后购买指定商品的客户？现有SQL方案是否有更优解？

我有如下orders表：

需求是编写SQL查询找出最后购买指定商品的客户，比如查询商品ID 9011时，期望返回结果为c1 2023-09-19T01:15。

目前我用下面的查询实现了需求，但想知道有没有更优的解决方案：

select customerID, max(date) as d from orders where articleIDs like '%9011%' group by customerID order by d desc limit 1;

一、针对现有表结构的优化 #

你的现有查询能实现需求，但like '%9011%'这种模糊匹配无法利用索引，数据量较大时性能会很差。可以用更精准的匹配方式替代：

1. 数据库专属字符串匹配函数

   • MySQL环境用FIND_IN_SET：

     SELECT customerID, MAX(date) AS d
     FROM orders
     WHERE FIND_IN_SET('9011', REPLACE(articleIDs, ' ', '')) > 0
     GROUP BY customerID
     ORDER BY d DESC
     LIMIT 1;
     

     先通过REPLACE去掉逗号后的空格，保证FIND_IN_SET能精准匹配单个商品ID，避免出现匹配到90110这类误判，同时比like的匹配逻辑更严谨。
   • PostgreSQL环境用string_to_array配合ANY：

     SELECT customerID, MAX(date) AS d
     FROM orders
     WHERE '9011' = ANY(string_to_array(REPLACE(articleIDs, ' ', ''), ','))
     GROUP BY customerID
     ORDER BY d DESC
     LIMIT 1;
     

2. 跳过分组，直接取最新记录
   如果只需要最后购买该商品的单条记录，完全可以跳过分组聚合，直接筛选含目标商品的订单后按日期倒序取第一条：

   SELECT customerID, date
   FROM orders
   WHERE FIND_IN_SET('9011', REPLACE(articleIDs, ' ', '')) > 0
   ORDER BY date DESC
   LIMIT 1;
   

   这个逻辑更直接，性能也更好，尤其数据量较大时，省去分组操作的资源消耗。

二、长期最优方案：重构表结构 #

当前articleIDs用字符串存储多个商品ID属于反范式设计，会带来查询不便、无法利用索引、数据维护困难等问题。最优做法是拆分表：

1. 创建order_articles关联表（结构示例）：

   orderID	articleID
   1	9012
   1	9011
   1	9013
   2	9013
   ...	...

2. 基于新结构的高效查询：

   SELECT o.customerID, o.date
   FROM orders o
   JOIN order_articles oa ON o.orderID = oa.orderID
   WHERE oa.articleID = '9011'
   ORDER BY o.date DESC
   LIMIT 1;
   

   这种结构可以给order_articles.articleID和orders.date建立索引，查询性能大幅提升，同时支持更复杂的统计需求（比如统计商品购买频次），符合数据库设计的范式规范。

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