我有以下表格:
物品

+----+-------------+-----------------------------+--------------+
| 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

08-04 17:20