Prasyarat:
Anda harus membuat pangkalan data dan beberapa jadual yang berkaitan di mana baris satu jadual akan ditukar menjadi lajur seperti fungsi PIVOT (). Jalankan penyataan SQL berikut untuk membuat pangkalan data bernama ‘ unidb 'Dan buat tiga jadual bernama' pelajar ',' kursus 'Dan' hasil '. pelajar dan hasil jadual akan dihubungkan oleh hubungan satu-ke-banyak dan kursus dan hasil jadual akan dihubungkan oleh hubungan satu-ke-banyak di sini. BUAT penyataan dari hasil jadual mengandungi dua kekangan kunci asing untuk bidang, std_id , dan kursus_id .
BUAT DATABASE unidb;
GUNAKAN unidb;
BUAT JADUAL pelajar(
IDKUNCI PRIMER INT,
nama varchar(lima puluh)BUKAN NULL,
jabatan VARCHAR(lima belas)BUKAN NULL);
Kursus BUAT JADUAL(
kursus_id VARCHAR(dua puluh)KUNCI UTAMA,
nama varchar(lima puluh)BUKAN NULL,
kredit SMALLINT BUKAN NULL);
BUAT hasil JADUAL(
std_id TIDAK ADA NULL,
kursus_id VARCHAR(dua puluh)BUKAN NULL,
mark_type VARCHAR(dua puluh)BUKAN NULL,
menandakan SMALLINT BUKAN NULL,
KUNCI ASING(std_id)RUJUKAN pelajar(ID),
KUNCI ASING(kursus_id)Kursus RUJUKAN(kursus_id),
KUNCI UTAMA(std_id, course_id, mark_type));
Masukkan beberapa rekod ke dalam pelajar, kursus dan hasil jadual. Nilai mesti dimasukkan ke dalam jadual berdasarkan sekatan yang ditetapkan pada masa pembuatan jadual.
MASUKKAN NILAI pelajar
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
INSERT INTO kursus NILAI
( 'CSE-401','Pengaturcaraan Berorientasikan Objek',3),
( 'CSE-403','Struktur Data',2),
( 'CSE-407','Pengaturcaraan Unix',2);
MASUKKAN NILAI hasil
( '1937463','CSE-401','Peperiksaan Dalaman',lima belas),
( '1937463','CSE-401','Peperiksaan pertengahan penggal',dua puluh),
( '1937463','CSE-401','Peperiksaan akhir',35),
( '1937464','CSE-403','Peperiksaan Dalaman',17),
( '1937464','CSE-403','Peperiksaan pertengahan penggal',lima belas),
( '1937464','CSE-403','Peperiksaan akhir',30),
( '1937465','CSE-401','Peperiksaan Dalaman',18),
( '1937465','CSE-401','Peperiksaan pertengahan penggal',2. 3),
( '1937465','CSE-401','Peperiksaan akhir',38),
( '1937466','CSE-407','Peperiksaan Dalaman',dua puluh),
( '1937466','CSE-407','Peperiksaan pertengahan penggal',22),
( '1937466','CSE-407','Peperiksaan akhir',40);
Di sini, hasil jadual mengandungi beberapa nilai yang sama untuk std_id , tanda_jenis dan kursus_id lajur di setiap baris. Cara menukar baris ini menjadi lajur jadual ini untuk memaparkan data dalam format yang lebih teratur ditunjukkan di bahagian seterusnya dari tutorial ini.
Putar baris ke lajur menggunakan pernyataan CASE:
Jalankan pernyataan PILIH sederhana berikut untuk memaparkan semua rekod hasil meja.
PILIH*DARI hasil;
Hasilnya menunjukkan empat markah pelajar untuk tiga jenis peperiksaan tiga kursus. Jadi nilai-nilai std_id , kursus_id dan tanda_jenis diulang berkali-kali untuk pelajar, kursus dan jenis peperiksaan yang berbeza.
Hasilnya akan lebih mudah dibaca sekiranya pertanyaan SELECT dapat ditulis dengan lebih berkesan dengan menggunakan pernyataan CASE. PILIH berikut dengan pernyataan CASE akan mengubah nilai berulang baris menjadi nama lajur dan memaparkan kandungan jadual dalam format yang lebih difahami pengguna.
PILIH hasil.std_id, result.course_id,MAX(KES KETIKA hasil.mark_type ='Peperiksaan Dalaman'KEMUDIAN result.mark TAMAT) 'Peperiksaan Dalaman',
MAX(KES KETIKA hasil.mark_type ='Peperiksaan pertengahan penggal'KEMUDIAN result.mark TAMAT) 'Peperiksaan pertengahan penggal',
MAX(KES KETIKA hasil.mark_type ='Peperiksaan akhir'KEMUDIAN result.mark TAMAT) 'Peperiksaan akhir'
DARI hasil
KUMPULAN MENGIKUT hasil.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Output berikut akan muncul setelah menjalankan pernyataan di atas yang lebih mudah dibaca daripada output sebelumnya.
Putar baris ke lajur menggunakan CASE dan SUM ():
Sekiranya anda ingin mengira jumlah keseluruhan kursus setiap pelajar dari jadual maka anda harus menggunakan fungsi agregat RUMUSAN () kumpulan mengikut std_id dan kursus_id dengan penyataan KES. Pertanyaan berikut dibuat dengan mengubah permintaan sebelumnya dengan fungsi SUM () dan klausa GROUP BY.
PILIH hasil.std_id, result.course_id,MAX(KES KETIKA hasil.mark_type ='Peperiksaan Dalaman'KEMUDIAN result.mark TAMAT) 'Peperiksaan Dalaman',
MAX(KES KETIKA hasil.mark_type ='Peperiksaan pertengahan penggal'KEMUDIAN result.mark TAMAT) 'Peperiksaan pertengahan penggal',
MAX(KES KETIKA hasil.mark_type ='Peperiksaan akhir'KEMUDIAN result.mark TAMAT) 'Peperiksaan akhir',
RUMUSAN(hasil.markah) sebagaiJumlah
DARI hasil
KUMPULAN MENGIKUT hasil.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Keluaran menunjukkan lajur baru bernama Jumlah yang menunjukkan jumlah markah semua jenis peperiksaan setiap kursus yang diperoleh oleh setiap pelajar tertentu.
Putar baris ke lajur dalam beberapa jadual:
Dua pertanyaan sebelumnya diterapkan pada hasil meja. Jadual ini berkaitan dengan dua jadual yang lain. Ini adalah pelajar dan kursus . Sekiranya anda ingin memaparkan nama pelajar dan bukan id pelajar dan nama kursus dan bukannya kursus maka anda harus menulis pertanyaan PILIH menggunakan tiga jadual yang berkaitan, pelajar , kursus dan hasil . Pertanyaan SELECT berikut dibuat dengan menambahkan tiga nama jadual setelah klausa FORM dan menetapkan syarat yang sesuai dalam klausa WHERE untuk mengambil data dari ketiga jadual dan menghasilkan output yang lebih sesuai daripada pertanyaan SELECT sebelumnya.
PILIH pelajar.namasebagai 'Nama pelajar', kursus.namasebagai 'Nama kursus',MAX(KES KETIKA hasil.mark_type ='Peperiksaan Dalaman'KEMUDIAN result.mark TAMAT) 'CT',
MAX(KES KETIKA hasil.mark_type ='Peperiksaan pertengahan penggal'KEMUDIAN result.mark TAMAT) 'Pertengahan',
MAX(KES KETIKA hasil.mark_type ='Peperiksaan akhir'KEMUDIAN result.mark TAMAT) 'Akhir',
RUMUSAN(hasil.markah) sebagaiJumlah
DARI pelajar, kursus, hasil
DI MANA hasil.std_id = students.id dan result.course_id =ursus.course_id
KUMPULAN MENGIKUT hasil.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Output berikut akan dihasilkan setelah menjalankan pertanyaan di atas.
Kesimpulan:
Bagaimana anda dapat melaksanakan fungsi fungsi Pivot () tanpa sokongan fungsi Pivot () di MySQL ditunjukkan dalam artikel ini dengan menggunakan beberapa data palsu. Saya harap, pembaca dapat mengubah data peringkat baris menjadi data peringkat lajur dengan menggunakan pertanyaan SELECT setelah membaca artikel ini.