为一行中一对多的所有详细信息选择语句。

Question

有两张桌子：

产品

ID (主键)，

ProductName

PlannedByMonths

ProductID (主键)(链接到Products表多对一)，

MonthNumber (主键)，

QtytoProduce

如何编写SELECT语句以检索下列格式的结果？

ProductName, QtytoProduceMonth1, QtytoProduceMonth2, QtytoProduceMonth3, QtytoProduceMonth4, QtytoProduceMonth5, QtytoProduceMonth6, QtytoProduceMonth7, QtytoProduceMonth8, QtytoProduceMonth9, QtytoProduceMonth10, QtytoProduceMonth11, QtytoProduceMonth12

Answer 1

我不知道在MySQL中是否有更简单的支点方法，但这应该是可行的：

        select 
        (select descriptions from products d where d.productid = p.productid )
           description,
         max(if(month=1,Qty, null)) m1,
         max(if(month=2, Qty,null)) m2,
         max(if(month=3, Qty, null)) m3 
         /* more here */
         from Planned p 
         group by productid  ;

Answer 2

我仍然不确定这是否是自电视晚宴以来最糟糕的技术进步，但从v4.1开始，MySQL具有GROUP_CONCAT()函数，可以满足您的需要：

  SELECT p.ProductName
         , group_concat(pbm.QtytoProduce order by pbm.MonthNumber)
    FROM Products p
         INNER JOIN PlannedByMonths pbm
         ON p.ID = pbm.ProductID
GROUP BY p.prodname

这将为包含以下内容的每个ProductName返回一行：

1. ProductName，和
2. QtytoProduce用于水滴中的每个MonthNumber。

Answer 3

为此，您需要12次加入：

select ProductName, pbm1.QtytoProduce, pbm2.QtytoProduce, pbm3.QtytoProduce, pbm4.QtytoProduce, pbm5.QtytoProduce, pbm6.QtytoProduce, pbm7.QtytoProduce, pbm8.QtytoProduce, pbm9.QtytoProduce, pbm10.QtytoProduce, pbm11.QtytoProduce, pbm12.QtytoProduce from Products p
left join PlannedByMonths pbm1 on p.ID=pbm1.ProductID and pbm1.MonthNumber=1
left join PlannedByMonths pbm1 on p.ID=pbm2.ProductID and pbm2.MonthNumber=2
left join PlannedByMonths pbm1 on p.ID=pbm3.ProductID and pbm3.MonthNumber=3
left join PlannedByMonths pbm1 on p.ID=pbm4.ProductID and pbm4.MonthNumber=4
left join PlannedByMonths pbm1 on p.ID=pbm5.ProductID and pbm5.MonthNumber=5
left join PlannedByMonths pbm1 on p.ID=pbm6.ProductID and pbm6.MonthNumber=6
left join PlannedByMonths pbm1 on p.ID=pbm7.ProductID and pbm7.MonthNumber=7
left join PlannedByMonths pbm1 on p.ID=pbm8.ProductID and pbm8.MonthNumber=8
left join PlannedByMonths pbm1 on p.ID=pbm9.ProductID and pbm9.MonthNumber=9
left join PlannedByMonths pbm1 on p.ID=pbm10.ProductID and pbm10.MonthNumber=10
left join PlannedByMonths pbm1 on p.ID=pbm11.ProductID and pbm11.MonthNumber=11
left join PlannedByMonths pbm1 on p.ID=pbm12.ProductID and pbm12.MonthNumber=12