我有以下表格:
物品
+----+-------------+-----------------------------+--------------+
| ID | ID_group_AG | Title | Date_publish |
+----+-------------+-----------------------------+--------------+
| 1 | 10 | O obrotach sfer niebieskich | 2009-05-07 |
| 2 | 11 | Technologia betonu | 2011-03-21 |
| 3 | 12 | test | 2008-01-13 |
+----+-------------+-----------------------------+--------------+
雇员
+----+-----------+-----------+
| ID | Name | Surname |
+----+-----------+-----------+
| 1 | Andrzej | Gacek |
| 2 | Leszek | Ksiazek |
| 3 | Krzysztof | Skibinski |
| 4 | Andrzej | Inny |
+----+-----------+-----------+
文章组
+----+----------+---------------+----------------+
| ID | ID_group | ID_employee | Points |
+----+----------+---------------+----------------+
| 1 | 10 | 1 | 3 |
| 2 | 10 | 3 | 3 |
| 3 | 11 | 1 | 2 |
| 4 | 11 | 2 | 2 |
| 5 | 11 | 4 | 2 |
| 6 | 12 | 4 | 6 |
+----+----------+---------------+----------------+
以及以下关系:
articles.ID_group_AG => articlesGroup.ID_group
articlesGroup.ID_employee => employee.ID
我需要做的是打印与员工相关的所有文章点、文章和发布日期,因此我使用以下查询:
SELECT
p.Name,
p.Surname,
a.Date_publish,
ag.Points
FROM
employee p,
articles a,
articlesGroup ag
WHERE
(ag.ID_group = a.ID_group_AG) AND
(ag.ID_employee = p.ID)
我得到:
+-----------+-----------+--------------+----------------+
| Name | Surname | Date_publish | Points |
+-----------+-----------+--------------+----------------+
| Andrzej | Gacek | 2009-05-07 | 3 |
| Andrzej | Gacek | 2011-03-21 | 2 |
| Leszek | Ksiazek | 2011-03-21 | 2 |
| Krzysztof | Skibinski | 2009-05-07 | 3 |
| Andrzej | Inny | 2011-03-21 | 2 |
| Andrzej | Inny | 2008-01-13 | 6 |
+-----------+-----------+--------------+----------------+
现在让我们来谈谈问题:)
我使用PChart库制作图表。
我想把每个员工的日期都放在y轴上,x轴上。
所以员工“andrzej gacek”的点数数组将为:[3,2]
对于员工“krzysztof skibinski”将为:[3]
和日期数组(排序):[“2008-01-13”、“2009-05-07”、“2011-03-21”]
我需要为例如“andrzej gacek”的employee points数组添加0点。数组应该如下所示:[0,3,2]
所以这一点与日期有关。
如何形成查询以向点添加零,以便查询的输出如下所示:
+-----------+-----------+--------------+----------------+
| Name | Surname | Date_publish | Points |
+-----------+-----------+--------------+----------------+
| Andrzej | Gacek | 2009-05-07 | 3 |
| Andrzej | Gacek | 2011-03-21 | 2 |
| Andrzej | Gacek | 2008-01-13 | 0 |
| Leszek | Ksiazek | 2011-03-21 | 2 |
| Leszek | Ksiazek | 2009-05-07 | 0 |
| Leszek | Ksiazek | 2008-01-13 | 0 |
| Krzysztof | Skibinski | 2009-05-07 | 3 |
| Krzysztof | Skibinski | 2011-03-21 | 0 |
| Krzysztof | Skibinski | 2008-01-13 | 0 |
| Andrzej | Inny | 2011-03-21 | 2 |
| Andrzej | Inny | 2008-01-13 | 6 |
| Andrzej | Inny | 2009-05-07 | 0 |
+-----------+-----------+--------------+----------------+
最佳答案
你必须创建一个笛卡尔积才能得到你想要的结果。我用了一个CROSS JOIN
来获取每个员工的日期。
试试看:
SELECT DISTINCT E.Name,
E.SurName,
a.Date_Publish,
IFNULL(AG.Points,0) Points
FROM Articles A CROSS JOIN
Employee E LEFT JOIN
ArticlesGroup AG ON ag.ID_group = a.ID_group_AG
AND E.Id = ag.ID_employee
这里是SQL Fiddle。