Apakah cara yang betul untuk menyertai dua penyata pilihan mysql kompleks?

Question

Saya boleh mengeluarkan dua senarai dari meja saya. Semua unit untuk setiap pelajar dalam setiap kohort ditunjukkan. Satu lagi menunjukkan sama ada semua bahagian setiap unit telah diserahkan untuk kerja untuk pelajar tertentu dalam kumpulan tertentu. Saya ingin menyertai senarai supaya saya boleh melihat penyerahan (atau bukan penyerahan) untuk setiap bahagian setiap unit untuk setiap pelajar dalam setiap kohort.

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

Ini akan menarik senarai organisasi dengan ID pengguna dan ID kumpulan.

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

Jika tugasan mempunyai lebih banyak bahagian daripada yang telah diserahkan, operasi ini akan mengembalikan satu baris untuk setiap unit yang perlu diselesaikan oleh setiap pelajar dalam setiap kohort berdasarkan ID kohort, ID pengguna dan nama unit.

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;

Bagaimana untuk menggunakan tiga lajur pada pernyataan pilih pertama sebagai maklumat sambungan untuk menjadikan pernyataan pilih kedua sebahagian daripada pernyataan pilih pertama?

Saya ingin menjalankan pertanyaan kedua pada setiap hasil pertanyaan pertama. Menggunakan data di atas, saya ingin mengekstrak ID pengguna 21 kerana mereka hanya menyerahkan sebahagian daripada unit dua bahagian.

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

Adakah ini satu penyertaan? Atau subquery?

Answer 1

(Untuk nilainya, saya percaya kumpulan ialah istilah yang diterima merentas pelbagai disiplin sains sosial.)

Masalah anda akan menjadi lebih mudah jika assignments 表有一个与您的 cohort_unitspart 表相匹配的 part lajur anda ialah Kod>Jadual. Jadi mari kita mulakan dengan subquery dan jana jadual dummy yang mengandungi lajur.

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)

Kami akan menggunakan subquery ini sebaliknya 赋值. Ini agak kemas, tetapi ia akan menjadikan kerja masa depan lebih jelas.

Seterusnya kita perlukan bilangan bahagian dalam setiap unit. Berikut ialah pengagregatan mudah:

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

Kami boleh menggunakan ungkapan jadual biasa untuk mengatur pertanyaan seperti yang ditunjukkan di bawah.

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

Ini adalah biola. https://dbfiddle.uk/FvGkiAnl

Salah satu helah untuk mencapai ini adalah dengan mengagregat secara individu untuk mendapatkan kiraan bahagian.