需求如下:
a商店有:1,2,3,4,5,6标识
b商店有:1,5,6标识
c商店有:4,5,6标识
每个标识都有一条记录
每条记录如下:
a-->1
a-->2
a-->3
a-->4
a-->5
a-->6
b-->3
b-->5
b-->6
c-->4
c-->5
c-->6
那么我现在要收索同时拥有标识有4,5,6的商店:搜出来的商店应该是 a 跟c
怎么搜,怎么写sql语句啊
解决方法如下:
1:SELECT shop_id FROM t_shop_hardware WHERE hardware_id IN (2,3,4,5,6) AND is_del =1 GROUP BY shop_id HAVING COUNT(DISTINCT hardware_id) = 5
2:
SELECT shop_id FROM
(SELECT GROUP_CONCAT(DISTINCT hardware_id) hardware_id,shop_id FROM t_shop_hardware WHERE is_del = 1 GROUP BY shop_id) a
WHERE FIND_IN_SET('2', a.hardware_id) AND FIND_IN_SET('3', a.hardware_id) AND FIND_IN_SET('4', a.hardware_id) AND FIND_IN_SET('5', a.hardware_id) AND FIND_IN_SET('6', a.hardware_id);
(SELECT GROUP_CONCAT(DISTINCT hardware_id) hardware_id,shop_id FROM t_shop_hardware WHERE is_del = 1 GROUP BY shop_id) a
WHERE FIND_IN_SET('2', a.hardware_id) AND FIND_IN_SET('3', a.hardware_id) AND FIND_IN_SET('4', a.hardware_id) AND FIND_IN_SET('5', a.hardware_id) AND FIND_IN_SET('6', a.hardware_id);
3:也可以用find_in_set() 函数来执行
select * from t_shop where find_in_set(hardware,'2,3,4,5');