如何在ITEMS与ITEMS_DETAILS关联后按规则查找重复值？
2026-8-25
关联表中查找重复值问题
我需要在ITEMS表与ITEMS_DETAILS表关联后的结果集中查找重复值，单表查找列重复值的SQL写法我已经掌握，但关联表后的操作不太清楚。
逻辑规则
- 若ITEM_NAME相同但SHOP_ID不同，标记为重复
- 若SHOP_ID相同，视为唯一
我尝试的SQL语句
select * from ( select a.NAME_ID from ITEMS a inner join ITEMS_DETAILS b on b.ITEM_ID = a.ITEM_ID) x inner join ITEMS y on y.NAME_ID=x.NAME_ID inner join ITEMS_DETAILS z on z.ITEM_ID=y.ITEM_ID
附两张表的数据
ITEMS表
ITEM_ID NAME_ID ITEM_NAME 1001 2001 Office chair 1002 2002 Writing Desk 1003 2003 Filing cabinet 1004 2004 Bookshelf bookcase 1005 2005 Table lamp 1006 2001 Office chair 1007 2002 Writing Desk 1008 2003 Filing cabinet 1009 2004 Bookshelf bookcase 1010 2005 Table lamp 1011 2001 Office chair 1012 2002 Writing Desk 1013 2003 Filing cabinet 1014 2004 Bookshelf bookcase 1015 2005 Table lamp 1016 2016 Triangle window 1017 2017 Screen 1018 2018 Cradle 1019 2017 Screen 1020 2018 Cradle 1021 2017 Screen 1022 2018 Cradle 1023 2023 Futon 1024 2024 Single bed 1025 2025 Bunk beds 1026 2026 Sofa bed 1027 2027 Camp bed cot sleeping bag 1028 2028 Airbed air mattress 1029 2029 Hammock 1030 2030 Loveseat 1031 2031 Sleeper sofa 1032 2032 Settee 1032 2032 Settee 1033 2001 Office chair 1034 2002 Writing Desk 1035 2003 Filing cabinet 1036 2004 Bookshelf/bookcase 1037 2005 Table lamp 1038 2001 Office chair 1039 2002 Writing Desk 1040 2003 Filing cabinet 1041 2004 Bookshelf/bookcase 1042 2005 Table lamp 1043 2017 Screen 1044 2018 Cradle 1045 2017 Screen 1046 2018 Cradle 1047 2017 Screen 1048 2018 Cradle 1049 2017 Screen 1050 2018 Cradle
ITEMS_DETAILS表
CITY ITEM_ID SHOP_ID NEW YORK 1001 4001 NEW YORK 1002 4002 NEW YORK 1003 4003 NEW YORK 1004 4004 NEW YORK 1005 4005 DALLAS 1006 4006 DALLAS 1007 4007 DALLAS 1008 4008 DALLAS 1009 4001 DALLAS 1010 4002 DALLAS 1011 4003 DALLAS 1012 4004 WASHINGTON 1013 4005 WASHINGTON 1014 4006 WASHINGTON 1015 4007 WASHINGTON 1016 4008 WASHINGTON 1017 4009 WASHINGTON 1018 4010 WASHINGTON 1019 4011 SANFRANSISCO 1020 4012 SANFRANSISCO 1021 4013 CHICAGO 1022 4014 CHICAGO 1023 4015 CHICAGO 1024 4016 CHICAGO 1025 4017 BOSTON 1026 4018 BOSTON 1027 4019 BOSTON 1028 4020 BOSTON 1029 4021 BOSTON 1030 4022 SANFRANSISCO 1031 4023 SANFRANSISCO 1032 4024 SANFRANSISCO 1032 4025 SANFRANSISCO 1033 4026 Las Vegas 1034 4027 Austin 1035 4028 Houston 1036 4029 Los Angeles 1037 4030 Seattle 1038 4031 Atlanta 1039 4032 McKinney 1040 4033 Vancouver 1041 4034 Las Vegas 1042 4035 Austin 1043 4036 Houston 1044 4037 Los Angeles 1045 4038 Seattle 1046 4034 Atlanta 1047 4035 McKinney 1048 4036 Vancouver 1049 4037 Las Vegas 1050 4043 Austin 1051 4044 Houston 1052 4045 Los Angeles 1053 4046 Seattle 1054 4047 Atlanta 1055 4048 McKinney 1056 4049 Vancouver 1057 4050 Las Vegas 1058 4051 Austin 1059 4052 Houston 1060 4053
解决方案
方法1：筛选所有存在重复的商品记录
先关联两张表得到完整数据集，再找出对应多个不同SHOP_ID的ITEM_NAME，最后取出这些商品的所有记录：
WITH joined_data AS ( SELECT i.ITEM_NAME, d.SHOP_ID, i.ITEM_ID, i.NAME_ID, d.CITY FROM ITEMS i JOIN ITEMS_DETAILS d ON i.ITEM_ID = d.ITEM_ID ), duplicate_groups AS ( SELECT ITEM_NAME FROM joined_data GROUP BY ITEM_NAME HAVING COUNT(DISTINCT SHOP_ID) > 1 ) SELECT j.*, '重复' AS duplicate_flag FROM joined_data j JOIN duplicate_groups dg ON j.ITEM_NAME = dg.ITEM_NAME ORDER BY j.ITEM_NAME, j.SHOP_ID;
方法2：给每条记录直接标记重复状态
用窗口函数统计每个ITEM_NAME对应的不同SHOP_ID数量，直接判断并标记：
SELECT i.ITEM_NAME, d.SHOP_ID, i.ITEM_ID, i.NAME_ID, d.CITY, CASE WHEN COUNT(DISTINCT d.SHOP_ID) OVER (PARTITION BY i.ITEM_NAME) > 1 THEN '重复' ELSE '唯一' END AS duplicate_flag FROM ITEMS i JOIN ITEMS_DETAILS d ON i.ITEM_ID = d.ITEM_ID ORDER BY i.ITEM_NAME, d.SHOP_ID;
说明
- 两种方法都先关联ITEMS和ITEMS_DETAILS表，获取商品与店铺的关联信息
- 方法1先定位存在重复的商品类别，再提取对应所有记录，适合只关注重复组的场景
- 方法2通过窗口函数实现一行式标记，能同时查看所有记录的状态
内容的提问来源于stack exchange，提问作者PythonWizard
相关产品推荐
相关产品推荐
