Membuat query lebih simple dengan Common Table Expression

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 :

  1. Buat dahulu CTE tersebut yang akan menggunakan nama alias tertentu
  2. 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 :

recursive-uml

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

Leave a Reply

Your email address will not be published. Required fields are marked *