我得到了下表:

文章

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

我需要将零分添加到 ex 的员工积分数组中。 “Andrzej Gacek”数组应如下所示:[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 .

关于mysql - 选择具有日期和文章点的行(无关系)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14944045/