Bagaimana untuk Memilih Pengguna yang Memenuhi Syarat ID Pelbagai Peranan dengan Cekap dalam SQL?

Pengoptimuman Pertanyaan SQL: Memilih Pengguna dengan ID Berbilang Peranan

Pertanyaan pangkalan data selalunya memerlukan mendapatkan semula data yang memenuhi pelbagai syarat merentas baris yang berbeza. Mari kita pertimbangkan jadual (userrole) dengan lajur userid dan roleid:

<code>userid | roleid
-------|--------
   1   |    1
   1   |    2
   1   |    3
   2   |    1</code>

Matlamatnya adalah untuk mencari userid unik yang memiliki ID peranan 1, 2 dan 3. Dalam contoh ini, hanya userid 1 yang memenuhi kriteria ini.

Dua pendekatan SQL biasa wujud: GROUP BY dengan HAVING dan kaedah berasaskan JOIN.

Kaedah 1: GROUP BY dan HAVING

Kaedah ini mengumpulkan pengguna mengikut ID dan mengira padanan roleids:

<code class="language-sql">SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(*) = 3;</code>

Pertanyaan ini menapis untuk roleids 1, 2 dan 3, kumpulan mengikut userid dan hanya mengembalikan pengguna dengan tepat tiga padanan.

Kaedah 2: JOIN Operasi

Pendekatan yang lebih cekap menggunakan JOINs untuk menapis pengguna secara progresif:

<code class="language-sql">SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
WHERE t1.roleid = 1;</code>

Pertanyaan ini bermula dengan memilih pengguna dengan roleid 1, kemudian bergabung untuk mencari mereka yang juga mempunyai roleid 2, dan akhirnya mereka yang mempunyai roleid 3. Pemilihan sasaran ini biasanya lebih pantas, terutamanya dengan set data yang besar dan padanan yang jarang berlaku .

Perbandingan Prestasi

Walaupun kaedah GROUP BY ringkas, kaedah JOIN selalunya memberikan prestasi unggul, terutamanya apabila berhadapan dengan kebarangkalian rendah padanan ID peranan berbilang. JOIN memanfaatkan indeks dengan lebih berkesan, mengurangkan bilangan baris yang diproses. Walau bagaimanapun, pendekatan optimum bergantung pada faktor seperti saiz data, konfigurasi pangkalan data dan ketersediaan indeks. Menanda aras kedua-dua kaedah terhadap data khusus anda adalah penting untuk menentukan penyelesaian yang paling cekap.

Atas ialah kandungan terperinci Bagaimana untuk Memilih Pengguna yang Memenuhi Syarat ID Pelbagai Peranan dengan Cekap dalam SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!