mysql - 分配每个月Mysql中的所有行

Question

你好我是mysql新手

Address        Type    MonthCreated       YearCreated
AddA1           M1     August                 2018
AddA1           I2     August                 2018
AddA1           K2     October                2018

期望的输出就像这三行将在每个月分配，例如 1 月 2 月 3 月到 2018 年 12 月

Select  AddA1,Type,MonthCreated,YearCreated 
from tbltry 
where YearCreated = '2018'

期望输出是每个月的所有行，因为它仍在同一年我只是想知道是否真的需要帮助或建议

Address        Type    MonthCreated       YearCreated
AddA1           M1     January                2018
AddA1           I2     January                2018
AddA1           K2     January                2018
AddA1           M1     February               2018
AddA1           I2     February               2018
AddA1           K2     February               2018
AddA1           M1     March                  2018
AddA1           I2     March                  2018
AddA1           K2     March                  2018

直到 12 月，它会返回所有行，这可能吗？真的需要建议

Answer 1

• 在Derived Table ，您可以为所有月份创建一个“主表”。
• 在另一个 Derived 表中，您可以获得 2018 年的 AddA1 和 Type 的不同组合。
• 简单地在它们之间做一个CROSS JOIN以获得所有可能的月份。

尝试:

SELECT dttry.*, all_months.*
FROM 
(SELECT DISTINCT AddA1, Type, YearCreated
 FROM tbltry 
 WHERE YearCreated = '2018') AS dttry
CROSS JOIN 
(SELECT 'January' AS MonthCreated UNION ALL
 SELECT 'February' UNION ALL 
 SELECT 'March' UNION ALL
 SELECT 'April' UNION ALL
 SELECT 'May' UNION ALL
 SELECT 'June' UNION ALL
 SELECT 'July' UNION ALL
 SELECT 'August' UNION ALL
 SELECT 'September' UNION ALL
 SELECT 'October' UNION ALL
 SELECT 'November' UNION ALL
 SELECT 'December') AS all_months