一个MySQL查询语句

Question

三个表c_item,c_attr,c_item_attr

c_item 表字段 item_id
c_attr 表字段 attr_id
c_item_attr 表字段 item_id、attr_id

比如数据

SELECT i.* FROM c_item i, c_item_attr ia WHERE i.item_id = ia.item_id AND ia.attr_id IN (4, 7, 14) GROUP BY i.item_id

用IN只有表中有就显示，现在想必须同时满足4, 7, 14，应该怎么写

---

================================================================

意思是传进来的数组，必须要c_item_attr 表里面的 attr_id 全部有才显示，只要有一个没，就不显示

Answer 1

SELECT i.* FROM c_item i, c_item_attr ia WHERE i.item_id = ia.item_id AND ia.attr_id IN (4, 7, 14) GROUP BY i.item_id having count(i.item_id)>2

Answer 2

SELECT ia.* FROM c_item as i, c_item_attr as ia WHERE i.item_id = ia.item_id AND 4 in (SELECT ia.attr_id from c_item_attr) AND 7 IN (SELECT ia.attr_id from c_item_attr) and 14 in (SELECT ia.attr_id from c_item_attr)

Answer 3

SELECT
    *
FROM
    (
        SELECT
            GROUP_CONCAT(attr_id) AS con_where,
            COUNT(*) AS count,
            c_item_attr.item_id
        FROM
            c_item_attr
        WHERE
            attr_id IN (4, 7,14)
        GROUP BY
            item_id
        HAVING
            count > 2
        ORDER BY
            attr_id
    ) AS con_where
WHERE
    con_where = "4, 7,14"