IN（）返回'Cardinality violation: 1242 Subquery returns more than 1 row'

6 回答

• 2

  您可以使用：

  WHERE 
     rpcf.place_id IN (SELECT rpcf.place_id 
                       FROM rel_place_custom_fields rpcf 
                       WHERE rpcf.field_id = 29 AND rpcf.field_value = :value_29_0)
  
     OR 
  
     rpcf.place_id IN (SELECT rpcf.place_id 
                       FROM rel_place_custom_fields rpcf 
                       WHERE rpcf.field_id = 29 AND rpcf.field_value = :value_29_1)
  

  或者，甚至更好：

  WHERE 
     rpcf.place_id IN (SELECT rpcf.place_id 
                       FROM rel_place_custom_fields rpcf 
                       WHERE rpcf.field_id = 29 AND 
                              rpcf.field_value IN (:value_29_0, :value_29_1))
  

  回复于 2024-05-16T16:20:14+08:00
• 1

  试试下面的脚本：

  SELECT p.place_id 
  FROM places p 
  LEFT JOIN rel_place_custom_fields rpcf ON p.place_id = rpcf.place_id 
  WHERE rpcf.place_id IN ((SELECT rpcf.place_id 
                           FROM rel_place_custom_fields rpcf 
                           WHERE rpcf.field_id = 29 AND 
                                 (rpcf.field_value = :value_29_0 OR
                                  rpcf.field_value = :value_29_1)
  GROUP BY place_id
  

  回复于 2024-05-16T16:20:14+08:00
• 0

  无需JOIN，无需GROUP BY .

  SELECT DISTINCT p.place_id
  FROM places p
  WHERE p.place_id_id IN 
      (SELECT rpcf.place_id FROM rel_place_custom_fields rpcf
       WHERE rpcf.field_id = 29 AND rpcf.field_value IN (:value_29_0, :value_29_1))
  

  回复于 2024-05-16T16:20:14+08:00
• 1

  看起来像你的内部查询返回多行，所以这个错误来了 .

  您可以在select查询中使用group_concat来获取所有带逗号的行，请尝试如下，

  SELECT 
    p.place_id 
  FROM
    places p 
    LEFT JOIN rel_place_custom_fields rpcf 
      ON p.place_id = rpcf.place_id 
  WHERE rpcf.place_id IN (
      (SELECT 
        GROUP_CONCAT(rpcf.place_id) 
      FROM
        rel_place_custom_fields rpcf 
      WHERE rpcf.field_id = 29 
        AND rpcf.field_value = :value_29_0),
      (SELECT 
        GROUP_CONCAT(rpcf.place_id) 
      FROM
        rel_place_custom_fields rpcf 
      WHERE rpcf.field_id = 29 
        AND rpcf.field_value = :value_29_1)
    ) 
  GROUP BY place_id
  

  愿这对你有所帮助

  回复于 2024-05-16T16:20:14+08:00
• 0

  SELECT p.place_id
  FROM places p
  LEFT JOIN rel_place_custom_fields rpcf ON p.place_id = rpcf.place_id 
  WHERE rpcf.place_id IN 
      ((SELECT rpcf.place_id FROM rel_place_custom_fields rpcf
        WHERE rpcf.field_id = 29 AND rpcf.field_value IN ('value_29_0','value_29_1')
  GROUP BY place_id
  

  回复于 2024-05-16T16:20:14+08:00
• 0

  我真的不这么认为当你加入同一个表时需要子查询只需使用 WHERE 子句来过滤掉你想要的结果 .

  SELECT DISTINCT p.place_id
  FROM places p
  JOIN rel_place_custom_fields rpcf ON p.place_id = rpcf.place_id 
  WHERE rpcf.field_id = 29
  AND rpcf.field_value IN(:value_29_0,:value_29_1)
  

  使用group by而不使用聚合函数也不是一个好主意，所以我使用了distinct而不是 . 另一个我没有看到 LEFT 的用法加入你的条件变成加入 INNER 加入

  另一种做同样的方法就是这样

  SELECT p.place_id
  FROM places p
  JOIN rel_place_custom_fields rpcf ON p.place_id = rpcf.place_id 
  GROUP BY p.place_id
  HAVING SUM(rpcf.field_id = 29)
  AND SUM(rpcf.field_value IN(:value_29_0,:value_29_1))
  

  回复于 2024-05-16T16:20:14+08:00