連接兩個複雜的 mysql select 語句的正確方法是什麼？

Question

我可以從我的表格中取出兩個清單。其中顯示了每個隊列中每個學生的所有單元。另一個顯示是否已針對特定群體中特定學生的作業提交每個單元的所有部分。我想加入列表，以便我可以查看每個隊列中每個學生的每個單元的每個部分的提交（或未提交）。

cohort_units:
cohort_id    unit    part
235          ABC     A
235          ABC     B
246          DEF     A
246          DEF     B
246          DEF     C

cohort_students:
user_id      cohort_id
21           235
24           235
43           235
53           246

assignments:
user_id    cohort_id    unit     draft1recdt 
21         235          ABCA     2023-01-03
21         235          ABCB     NULL
24         235          ABCA     2023-02-01
24         235          ABCB     2023-02-02

這會提取帶有使用者 ID 和群組 ID 的單位清單。

SELECT cohort_students.user_id,
       cohort_units.unit,
       cohort_units.cohort_id 
FROM cohort_units 
LEFT JOIN cohort_students 
    ON cohort_units.cohort_id = cohort_students.cohort_id 
GROUP BY cohort_units.unit,cohort_students.user_id 
ORDER BY cohort_students.user_id;

result:
user_id    unit    cohort_id
21         ABC    235
24         ABC    235
43         ABC    235
53         DEF    236

如果作業的部分多於已提交的部分，則對於每個佇列中的每個學生應在給定佇列ID、使用者ID 和單元名稱的情況下完成的每個單元，此操作將傳回一​​行。

SELECT GROUP_CONCAT(CASE WHEN draft1recdt IS NOT NULL THEN draft1recdt END) AS drafts,
       (LENGTH(GROUP_CONCAT(DISTINCT draft1recdt))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT draft1recdt), ',', '')))+1 as numDrafts,
       cohort_units.unit,
       GROUP_CONCAT(cohort_units.part) as parts,
       (LENGTH(GROUP_CONCAT(DISTINCT cohort_units.part))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT cohort_units.part), ',', '')))+1 as numParts 
FROM assignments 
LEFT JOIN cohort_units 
    ON assignments.cohort_id = cohort_units.cohort_id 
    AND assignments.unit = CONCAT(cohort_units.unit,cohort_units.part) 
WHERE assignments.cohort_id = 235 
    AND cohort_units.unit = 'ABC' AND assignments.user_id = 21 
GROUP BY cohort_units.unit 
HAVING numParts > numDrafts;

如何使用第一個 select 語句上的三列作為連接訊息，使第二個 select 語句成為第一個 select 語句的一部分？

我想對第一個查詢的每個結果執行第二個查詢。使用上面的數據，我希望提取出用戶 ID 21，因為他們只提交了兩部分單元的一部分。

user_id    unit   cohort_id   parts   numParts   numDrafts
21         ABC    235         A,B     2          1

這是一個加入嗎？或子查詢？

Answer 1

（就其價值而言，我相信群體是各個社會科學學科中公認的術語。）

如果您的assignments 表有一個與您的cohort_unitspart 表相符的part 列，您的問題將會變得更容易代碼>表。因此，讓我們從子查詢開始，產生一個包含該列的虛擬表。

SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units 
     ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part)

我們將使用此子查詢來代替賦值。這有點雜亂，但它會讓以後的工作更加清晰。

接下來我們需要每個單元中的零件數量。這是一個簡單的聚合：

SELECT COUNT(*) num_parts,
       cohort_id,
       unit
  FROM cohort_units
 GROUP BY cohort_id, unit

我們可以使用通用表格運算式來組織查詢，如下所示。

WITH completed AS (
 SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units 

      ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part) 
),
partcount AS (
SELECT COUNT(*) num_parts,
       cohort_id,
       unit
  FROM cohort_units
 GROUP BY cohort_id, unit
)
SELECT completed.user_id, cohort_units.cohort_id, cohort_units.unit, 
       GROUP_CONCAT(completed.part) parts,
       COUNT(*) completed_parts,
       partcount.num_parts
  FROM cohort_units
  JOIN partcount
         ON cohort_units.cohort_id = partcount.cohort_id
        AND cohort_units.unit = partcount.unit
  JOIN completed
         ON completed.cohort_id = cohort_units.cohort_id
        AND completed.unit = cohort_units.unit
        AND completed.part = cohort_units.part
GROUP BY completed.user_id, cohort_units.cohort_id, cohort_units.unit, num_parts
HAVING COUNT(*) < partcount.num_parts

這是一個小提琴。 https://dbfiddle.uk/FvGkiAnl

#實現這一點的技巧之一是單獨聚合來獲取零件計數。