加入未嵌套的整數數組列併計算數組中的出現次數

在我取消其中一個表後，我正在嘗試加入兩張表。這些表是：

CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));

CREATE TABLE training_modules (id SERIAL, course VARCHAR(60), teachers_id INTEGER[]);

我想編寫一個查詢，該查詢將返回教師姓名和他們參與的 training_modules 的數量（他們的 ID 計數），如下所示：

teacher_name  id_count
bob teacher   4
sally lady    3
jimbo jones   5

到目前為止，我已經能夠使用此查詢獲得每個 ID 的計數：

SELECT tid, count(*) as id_count
FROM training_modules tm, unnest(tm.teachers_id) as tid
GROUP BY tid;

一切都很好。我試圖將其應用於另一個查詢，我在該查詢中加入了教師表，但它沒有按預期工作。這個查詢：

SELECT t.id, concat(t.first_name, ' ', t.last_name) AS teacher_name, tm.id_count
FROM (
  SELECT unnest(training_modules.teachers_id) AS id_count, count(*)
  FROM training_modules
  GROUP BY id_count
  ) AS tm
INNER JOIN teachers t ON tm.id_count = t.id;

結果在下表中，其中id_count列不再是計數，而是返回與t.id列相同的值。

id |     t_name      | tc 
----+-----------------+----
 5 | Jimbo Jones     |  5
 4 | Frank McGee     |  4
 6 | Sara Sarason    |  6
 2 | Joshua Jesps    |  2
 1 | Larry Bucatin   |  1
 3 | Natalie Fatali  |  3

在加入表格時獲得正確計數的任何幫助將不勝感激。

基本上，您將列別名附加到第二個查詢中的錯誤表達式。

SELECT id, concat_ws(' ', t.first_name, t.last_name) AS teacher_name, tm.id_count
FROM  (
  SELECT unnest(tm.teachers_id) AS id, count(*) **AS id_count**
  FROM   training_modules tm
  GROUP  BY 1
  ) AS tm
JOIN   teachers t USING (id);

Aside:

concat_ws(' ', t.first_name, t.last_name)通常更好，因為它只在有意義的地方添加分隔符（當兩者都是first_name&last_name時NOT NULL）。

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