mysql - SQL : How to get 0 count instead of 1 and null with join tables or views

Question

我试图从连接的 View 中计算数据，但给出了错误的结果。

查询表(主表)

+----+----------------------+---------+
| id | title                | history |
+----+----------------------+---------+
|  1 | test search title 2  |       0 |
|  2 | test search title 3  |       1 |
|  3 | test search title 4  |       0 |
|  4 | test search title 5  |       1 |
|  5 | test search title 6  |       0 |
|  6 | test search title 7  |       0 |
|  7 | test search title 8  |       1 |
|  8 | test search title 9  |       0 |
|  9 | test search title 10 |       1 |
+----+----------------------+---------+

结果表

+------+----------+---------------------+----------+
|  id  | query_id |    published_at     |  status  |
+------+----------+---------------------+----------+
|    1 |       2  | 2017-11-27 12:00:56 | negative |
|    2 |       5  | 2017-12-20 16:17:42 | positive |
|    3 |       6  | 2017-12-20 16:20:22 | positive |
|    4 |       8  | 2017-12-20 16:28:26 | negative |
|    5 |       7  | 2017-12-20 16:29:48 | neutral  |
+------+----------+---------------------+----------+

我创建了 4 个 View 来按天计算数据和 results 表中的状态

每日总计结果

CREATE VIEW v_results_daily_total AS (
           SELECT query_id, DATE(published_at) as day, 
            COUNT(*) as total FROM results
            GROUP BY query_id, day)

每日积极结果

CREATE VIEW v_sentiment_daily_positive AS (
           SELECT query_id, DATE(published_at) as day, 
            COUNT(*) as pos FROM results
            WHERE status='positive'
            GROUP BY query_id, day)

每日负面结果

CREATE VIEW v_sentiment_daily_negative AS (
               SELECT query_id, DATE(published_at) as day, 
                COUNT(*) as neg FROM results
                WHERE status='negative'
                GROUP BY query_id, day)

每日中性结果

CREATE VIEW v_sentiment_daily_neutral AS (
               SELECT query_id, DATE(published_at) as day, 
                COUNT(*) as neg FROM results
                WHERE status='neutral'
                GROUP BY query_id, day)

当尝试运行以下查询时，它显示错误的计数。

select
  q.id,
  q.title,
  vt.day,
  vt.total as total,
  vp.pos as pos,
  vn.neg as neg,
  ve.neu as neu

from queries q
  left join v_results_daily_total vt on vt.query_id=q.id
  LEFT join v_sentiment_daily_positive vp on vp.query_id=q.id
  LEFT join v_sentiment_daily_negative vn on vn.query_id=q.id
  LEFT join v_sentiment_daily_neutral ve on ve.query_id=q.id
WHERE q.history=0
GROUP BY q.id,vt.day;

错误的结果

+----+----------------------+------------+-------+------+------+------+
| id | title                |    day     | total | pos  | neg  | neu  |
+----+----------------------+------------+-------+------+------+------+
|  1 | Test Search Title 2  | NULL       | NULL  | NULL | NULL | NULL |
|  3 | Test Search Title 3  | NULL       | NULL  | NULL | NULL | NULL |
| 35 | Test Search Title 11 | NULL       | NULL  | NULL | NULL | NULL |
| 38 | Test Search Title 12 | 2017-12-20 | 17    | 6    | 7    | 4    |
| 38 | Test Search Title 13 | 2017-12-21 | 3     | 6    | 7    | 4    |
| 40 | Test Search Title 14 | NULL       | NULL  | NULL | NULL | NULL |
| 41 | Test Search Title 15 | NULL       | NULL  | NULL | NULL | NULL |
| 51 | Test Search Title 16 | NULL       | NULL  | NULL | NULL | NULL |
| 52 | Test Search Title 17 | NULL       | NULL  | NULL | NULL | NULL |
| 53 | Test Search Title 18 | NULL       | NULL  | NULL | NULL | NULL |
| 57 | Test Search Title 19 | 2017-08-02 | 1     | 1    | 1    | 1    |
| 57 | Test Search Title 20 | 2017-08-06 | 1     | 1    | 1    | 1    |
| 57 | Test Search Title 21 | 2017-12-20 | 4     | 1    | 1    | 1    |
| 58 | Test Search Title 22 | 2017-12-03 | 1     | NULL | NULL | 1    |
| 60 | Test Search Title 23 | NULL       | NULL  | NULL | NULL | NULL |
| 61 | Test Search Title 24 | NULL       | NULL  | NULL | NULL | NULL |
| 62 | Test Search Title 25 | NULL       | NULL  | NULL | NULL | NULL |
| 63 | Test Search Title 26 | NULL       | NULL  | NULL | NULL | NULL |
| 64 | Test Search Title 27 | 2017-12-21 | 133   | 39   | 41   | 53   |
| 67 | Test Search Title 28 | NULL       | NULL  | NULL | NULL | NULL |
| 69 | Test Search Title 29 | 2017-12-12 | 1     | 3    | 1    | NULL |
| 69 | Test Search Title 30 | 2017-12-17 | 3     | 3    | 1    | NULL |
| 69 | Test Search Title 31 | 2017-12-18 | 1     | 3    | 1    | NULL |
| 70 | Test Search Title 32 | 2017-12-12 | 1     | NULL | 3    | 1    |
| 70 | Test Search Title 33 | 2017-12-17 | 3     | NULL | 3    | 1    |
| 70 | Test Search Title 34 | 2017-12-18 | 1     | NULL | 3    | 1    |
| 73 | Test Search Title 35 | 2017-12-17 | 3     | 1    | 1    | 1    |
| 74 | Test Search Title 36 | 2013-10-06 | 1     | 1    | 1    | 1    |
+----+----------------------+------------+-------+------+------+------+

如果没有结果，它会给出空天数、空总计和错误计数。主要问题是计数错误。如何解决？

Answer 1

不需要所有这些。您可以像这样使用 CASE:

select
  DATE(r.published_at) as day,
  SUM(CASE WHEN status = 'negative' THEN 1 ELSE 0 END) AS Negative,
  SUM(CASE WHEN status = 'positive' THEN 1 ELSE 0 END) AS Positive,
  SUM(CASE WHEN status = 'neutral' THEN 1 ELSE 0 END) AS Neutral,
  COUNT(r.id) AS Total
from results as r
left join queries q ON q.id = r.query_id
GROUP BY DATE(r.published_at);

这将为您提供每天的状态计数和总计数。

请注意:我使用表 Results 作为左表，这样即使查询表中没有结果，我也可以获得所有天数。这取决于您，具体取决于您的数据。这样，您在第一个查询中得到的天数为空，因为您的结果表是具有左连接的右表。

Results :

|        day | Negative | Positive | Neutral | Total |
|------------|----------|----------|---------|-------|
| 2017-11-27 |        1 |        0 |       0 |     1 |
| 2017-12-20 |        2 |        2 |       0 |     4 |

---

如果您想获取每个问题和每天的计数，请按如下方式将 id 和 title 添加到组中:

select
  q.id, q.title,
  DATE(r.published_at) as day,
  SUM(CASE WHEN status = 'negative' THEN 1 ELSE 0 END) AS Negative,
  SUM(CASE WHEN status = 'positive' THEN 1 ELSE 0 END) AS Positive,
  SUM(CASE WHEN status = 'neutral' THEN 1 ELSE 0 END) AS Neutral,
  COUNT(r.*) AS Total
from queries q
left join results as r ON q.id = r.query_id
WHERE q.history=0
GROUP BY q.id, q.title, DATE(r.published_at)