如何將 2 個表連接在一起，然後按 2 列計算？

假設我有這兩張桌子：

我加入這兩個表的最佳方法是什麼，然後計算每個國家、每個類別的使用者 ID 數量？

您可以嘗試以下查詢，我沒有 mySQL，但它應該是標準 SQL。

SELECT country, category, count(1) FROM users u 
  JOIN submissions s ON u.userid= s.userid GROUP BY country, category

您可以執行以下操作（下面的所有程式碼都可以在此處的小提琴中找到）：

CREATE TABLE users
(
 user_id INTEGER NOT NULL PRIMARY KEY,
 u_date  DATE NOT NULL,
 country VARCHAR (100) NOT NULL
);

填充它（一些額外的記錄用於展示目的）：

INSERT INTO users VALUES
(123, '2022-12-01', 'USA'),
(124, '2022-12-01', 'Ireland'),
(125, '2022-12-01', 'Japan');

請注意使用 ISO 8601 日期格式（即 YYYY-MM-DD） - 這是一種標準，大多數數據庫系統都接受這種格式的日期。

和：

CREATE TABLE submissions
(
 submission_id   INTEGER PRIMARY KEY,
 user_id         INTEGER NOT NULL,
 submission_date DATE,
 category        VARCHAR (100) NOT NULL,
 CONSTRAINT sub_user_fk FOREIGN KEY (user_id) REFERENCES users (user_id)
);

再有一些額外的記錄：

INSERT INTO submissions VALUES
(1, 123, '2022-12-02', 'business'),
(2, 123, '2022-12-02', 'business'),
(3, 123, '2022-12-02', 'tourism'),
(4, 123, '2022-12-02', 'tourism'),
(5, 123, '2022-12-02', 'tourism'),
(6, 124, '2022-12-02', 'business'),
(7, 124, '2022-12-02', 'tourism');

--
-- Note: no records in submissions for user 125
--

然後我們執行：

SELECT 
 u.user_id, u.country, s.category, 
 COUNT(s.category) AS cat_cnt
FROM
 users u
INNER JOIN
 submissions s
 ON u.user_id = s.user_id
GROUP BY u.user_id, u.country, s.category
ORDER BY u.user_id, u.country;

結果：

user_id  country    category    cat_cnt
123          USA    tourism           3
123          USA    business          2
124      Ireland    tourism           1
124      Ireland    business          1

請注意，JOIN這裡是一個INNER連接 - 如果未指定，則為預設連接。但是，我們可能很想知道誰沒有送出任何內容（或購買或您感興趣的任何內容）。

我們通過 a 的方式執行此操作，LEFT JOIN其中將包含連接左側的所有記錄，右側的那些記錄為 NULL，沒有相應的條目 - 在這種情況下，來自日本的使用者沒有送出，所以將沒有出現在第一個報告中，但確實出現在第二個報告中，如下所示：

SELECT 
 u.user_id, u.country, COALESCE(s.category, '------'), 
 COUNT(s.category) AS cat_cnt
FROM
 users u
LEFT JOIN
 submissions s
 ON u.user_id = s.user_id
GROUP BY u.user_id, u.country, s.category
ORDER BY u.user_id, u.country;

結果：

user_id  country    coalesce    cat_cnt
123          USA     tourism          3
123          USA    business          2
124      Ireland     tourism          1
124      Ireland    business          1
125        Japan     ------           0

我們使用該COALESCE()函式輸入要替換的字元串NULL——只是一個表示的東西——你可能希望讓你的應用程序的表示層來處理它！

正如我在評論中提到的，請始終包含您的 MySQL 版本！此外，當將來問這樣的問題時，請在您的表格定義和一些範例數據中添加一個小提琴，以便 a）我們對問題有一個單一的事實來源，並且 b）代表這些人沒有重複的努力試圖回答 - 幫助我們幫助你！

編輯：

正如@AnandSowmithiran 所指出的，我在原始答案中包含了太多細節-我沒有改變原始答案，但是正如他所指出的，您的問題的確切答案更恰當地表述如下（此處添加了新小提琴):

首先，我們可以在usersandsubmissions表中添加一些記錄：

INSERT INTO users VALUES
(126, '2022-12-01', 'USA');

和：

INSERT INTO submissions VALUES
( 8, 126, '2022-12-02',  'tourism'),
( 9, 126, '2022-12-02',  'tourism'),
(10, 126, '2022-12-02',  'tourism'),
(11, 126, '2022-12-02', 'business'),
(12, 126, '2022-12-02', 'business');

接著：

SELECT 
 u.country, COALESCE(s.category, '------') AS category, 
 COUNT(s.category) AS cat_cnt
FROM
 users u
LEFT JOIN
 submissions s
 ON u.user_id = s.user_id
GROUP BY u.country, s.category
ORDER BY u.country, category DESC, s.category;

結果：

country     category    cat_cnt
Ireland      tourism          1
Ireland     business          1
Japan        ------           0
USA          tourism          6
USA         business          4

即，您不希望user_id按country和細分category。檢查表明這是正確的結果。

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