setelah melihat bagaimana menggunakan paging di sqlserver, sekarang saya akan mencoba mengulas sedikit mengenai kemampuan sqlserver yang lain yaitu Common Table Expression (CTE) [1]. Dengan CTE ini kita dapat membuat query kita lebih simple.
Contoh Sederhana CTE
mari ambil contoh salah satu query dari postingan saya sebelumnya, yaitu query
pada query itu kita akan menambahkan kolom nomor baris, dan selanjutnya akan dilanjutkan dengan query
yang akan melakukan seleksi baris yang akan kita ambil dan tampilkan.
nah sekarang kita coba dengan menggunakan cara yang sedikit berebeda , yaitu menggunakan CTE. Untuk melakukan itu, kita membutuhkan keyword with.
berikut langkah – langkah sederahananya :
- Buat dahulu CTE tersebut yang akan menggunakan nama alias tertentu
- Buat query yang memanggil alias tersebut sebagai sebuah table.
tahap 1 dimulai dari kita membuat CTE nya, seperti pada query :
setelah itu kita dapat menambahkan query dibawahnya dengan memanggil CTE tersebut, seperti pada query penuh seperti :
maka kita dapat menggunakan CTE sebagai alternatif dalam menuliskan paging tanpa nested query.
Menggunakan CTE untuk mengaggregasi
contoh pada web microsoft [1] memberikan contoh yang baik untuk hal ini , misalnya saja kita mempunyai query untuk menghasilkan jumlah penjualan (transaksi) per Sales, maka dapat kita selesaikan dengan query
dan untuk menghitung rata – rata penjualan per sales maka akan kita dapatkan dengan query
yang mana sama dengan query
jika kita tidak menggunakan CTE.
Multiple CTE
misalkan saja berdasarkan contoh di atas, kita ingin melihat siapa saja yang sales yang diatas rata – rata maka dengan mudah kita mendefinisikan query sebagai berikut :
jauh lebih simple daripada kita menggunakan nested query berikut :
Hirarki dengan CTE
masalah ini sangat terkait dengan masalah yang terkenal di dunia design yaitu masalah rekursif, contohnya adalah design pada masalah employee-manager [2]. Dalam gambar kira2 bisa digambarkan seperti :
untuk menggenerate masalah ini sebelumnya kita perlu membuat tabel untuk itu, karena di adventure works 2012 tidak ada permasalahan rekursif ini secara mudah untuk dilihat. Untuk itu kembali kita lihat contoh dari website microsoft [1]. Yaitu menggunakan query :
dengan ini kita akan membuat table myEmployees yang berisikan beberapa data dummy.
maka untuk menampilkan list employee beserta managernya kita bisa memanfaatkan query
di dalam query itu kita menggunakan (memanggil ) CTE di dalam deklarasi CTE itu sendiri
so asik bukan.. silakan untuk melihat – lihat contoh lain pada [1].
Referensi
[1]. https://msdn.microsoft.com/en-us/library/ms175972.aspx
[2] http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php