主页 / dba / 问题 / 206972
Accepted
Chloe
Asked: 2018-05-17 14:54:08 +0800 CST

是否可以将这三个连接查询合并为一个？

  • 772

我想将不同 hit_types 的统计计数合并到一个查询中。那可能吗？

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| aaa |            1 |
| cnn |           16 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 1 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            1 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 2 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            4 |
+-----+--------------+

我试图将前两个结合起来，但数字都搞砸了，它消除了第一个结果“aaa”。

MariaDB [db]> select allurls.id, count(s1.id), count(s2.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 inner join stats s2 on allurls.id = s2.allurl_id and s2.hit_type = 1 where s1.hit_date >= '2018-01-15' and s2.hit_date >= '2018-01-15' group by allurls.id;
+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| cnn |           16 |           16 |
+-----+--------------+--------------+

我期待看到

+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| aaa |            1 |            0 |
| cnn |           16 |            1 |
+-----+--------------+--------------+

最后我还想包括count(distinct(s4.source_id)).

这是一个小提琴：https ://www.db-fiddle.com/f/tGP5SbC2AdGgeEwWTAgobf/0

join count

3 个回答

  1. Best Answer

    不确定我在这里是否遗漏了什么，但是您可以有条件地计算，例如：

    select allurls.id
    , count(case when s1.hit_type = 0 then 1 end) 
    , count(case when s1.hit_type = 1 then 1 end) 
    , count(case when s1.hit_type = 2 then 1 end) 
    , count(distinct s1.source_id)
from allurls 
join stats s1 
    on allurls.id = s1.allurl_id 
where s1.hit_date >= '2018-01-15' 
  and s1.hit_type between 0 and 2 
group by allurls.id;

 id     c1  c2  c3  c4
aaa     1   0   0   1
cnn     16  1   4   3

    一些注意事项：

    我将hit_type谓词移动到 where，这在逻辑上没有区别，但如果可能的话，我更喜欢在 join 子句中只包含表之间的关系。

    distinct 不是函数，count(distinct(s1.source_id))令人困惑。更好用count(distinct s1.source_id)

    • 2

  2. 这可能有帮助，（至少部分）：

    SELECT allurls.id, COUNT(s1.id)
FROM allurls 
INNER JOIN stats s1 ON allurls.id = s1.allurl_id 
AND (s1.hit_type = 0 OR s1.hit_type = 1 OR s1.hit_type = 2) 
WHERE s1.hit_date >= '2018-01-15'  
GROUP by allurls.id;

    结果： 

     id  COUNT(s1.id)
aaa             1
cnn            21

    db-fiddle 在此处可用，数字看起来是正确的，因为它们加起来是前 3 个查询中的计数（个人 - 否OR）。我给这个问题 +1 以提供小提琴 - 如这里所建议的那样- 如果只有所有海报都会做同样的事情（叹息...）

    我在小提琴中没有看到 s2，也没有看到 s4 - 也许它需要扩展？

    • 0

  3. 好的，我找到了一种使用子查询的方法（它们解决了所有问题），但我不确定这是最有效的。

    MariaDB [db]> SELECT allurls.id,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 0 AND hit_date > '2018-01-15') AS c1,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 1 AND hit_date > '2018-01-15') AS c2,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 2 AND hit_date > '2018-01-15') AS c3,
  (SELECT count(DISTINCT( source_id )) FROM stats WHERE allurl_id = allurls.id AND hit_date > '2018-01-15') AS c4
FROM   allurls
HAVING c1 > 0; 

+-----+------+------+------+------+
| id  | c1   | c2   | c3   | c4   |
+-----+------+------+------+------+
| aaa |    1 |    0 |    0 |    1 |
| cnn |   16 |    1 |    4 |    3 |
+-----+------+------+------+------+
    • 0

相关问题