mysql - 如何将值传递给 MySQL 中的子查询

Question

我无法将值传递给 MySQL 中的子查询，因为我读过的内容在 MySQL 中是不可能的，但我无法找到另一种方法来执行此查询。

要传递的值是来自查询第一部分的“pnl_partsA.part_image”。我想要获得的是存在于多个制造商中的 part_image。

SELECT manufacturer, chapter, part_image
FROM pnl_parts pnl_partsA
WHERE 1 = 1
AND part_image <> ''
#AND manufacturer = 'fiat'
#AND part_image = 'F01A050'
AND (   SELECT COUNT(chapter)
        FROM (  SELECT manufacturer, chapter, part_image
                FROM pnl_parts
                WHERE part_image = pnl_partsA.part_image
                AND part_image <> ''
                AND manufacturer = pnl_partsA.manufacturer
                GROUP BY manufacturer, chapter, part_image
             )  chaptercount 
    )  > 1
ORDER BY part_image
; 

添加更多信息......我需要得到的是有重复 part_image 的章节

任何帮助将不胜感激。

标签： mysql

Answer 1

您可以使用 and 获取所有包含多行的manufacturerand组。我们将在派生表中使用此结果集，并加入主表，以获取相关行：part_imageGroup ByHaving COUNT(*) > 1

请尝试以下查询：

SELECT 
  pp1.manufacturer, 
  pp1.chapter, 
  pp1.part_image 
FROM pnl_parts AS pp1
JOIN (
       SELECT 
         pp2.manufacturer, 
         pp2.part_image 
       FROM pnl_parts AS pp2 
       WHERE pp2.part_image <> ''
       GROUP BY 
         pp2.manufacturer, 
         pp2.part_image
       HAVING COUNT(*) > 1
     ) AS dt 
  ON dt.manufacturer = pp1.manufacturer AND 
     dt.part_image = pp1.part_image