是否可以將這三個連接查詢合併為一個？

我想將不同 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

不確定我是否在這裡想念任何東西，但你可以有條件地計算：

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謂詞移到了哪裡，它沒有邏輯上的區別，但如果可能的話，我更喜歡在連接子句中只包含表之間的關係。

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

引用自：https://dba.stackexchange.com/questions/206972