mysql - 连接中的子选择 - 问题

Question

我的声明有一点问题:

SELECT 
    p1.Modell_nr, 
    p1.ProductID,
    p2.count_modlieffarbe_vl,
    concat(p1.Modell_nr,'_',p1.LiefFarbe) as modfarb_id1
FROM produkte as p1
LEFT JOIN 
(
    SELECT p2.ProductID as tester,
       count(*) as count_modlieffarbe_vl
       FROM produkte as p2 
       WHERE p2.Vl>p2.vl_min
       group by p2.Modell_nr, p2.LiefFarbe
) as count_modlieffarbe_vla ON p1.ProductID = tester 

...它的工作没有错误消息。 ...但在我的结果中仅获取 Modell_nr、ProductID 和 modfarb_id1 作为列。为什么我在结果中看不到 count_modliefarbe_vl？

Answer 1

我会这么说，因为它不在 p2 表中。您的查询应如下所示:

SELECT 
p1.Modell_nr, 
p1.ProductID,
count_modlieffarbe_vla.count_modlieffarbe_vl,
concat(p1.Modell_nr,'_',p1.LiefFarbe) as modfarb_id1
FROM produkte as p1
LEFT JOIN 
(
SELECT p2.ProductID as tester,
count(*) as count_modlieffarbe_vl
FROM produkte as p2 
WHERE p2.Vl>p2.vl_min
group by p2.Modell_nr, p2.LiefFarbe
) as count_modlieffarbe_vla ON p1.ProductID = tester 

count_modliefarbe_vla.count_modliefarbe_vl 是关键。