TechCodeview

DAVA if-then-else tipi mantıksal sorguları çalıştıran bir ifadedir. Bu ifade, belirtilen koşul Doğru olarak değerlendirildiğinde değeri döndürür. Hiçbir koşul True olarak değerlendirilmediğinde ELSE bölümünün değerini döndürür.

ELSE kısmı olmadığında ve hiçbir koşul Doğru olarak değerlendirilmediğinde NULL değeri döndürür.

Yapılandırılmış Sorgu Dili'nde CASE ifadesi, SELECT, INSERT ve DELETE ifadelerinde aşağıdaki üç cümleyle birlikte kullanılır:

1. NEREDE Maddesi
2. SİPARİŞ BY Maddesi
3. GRUP BY Madde

SQL'de bu ifadenin ardından her zaman en az bir çift WHEN ve THEN ifadesi gelir ve her zaman END anahtar sözcüğüyle tamamlanır.

CASE ifadesi ilişkisel veritabanlarında iki türden oluşur:

bir ayda kaç hafta var

1. Basit CASE ifadesi
2. CASE bildirimi arandı

SQL'de CASE ifadesinin sözdizimi

 CASE WHEN condition_1 THEN statement_1 WHEN condition_2 THEN statement_2 ……. WHEN condition_N THEN statement_N ELSE result END; 

Burada CASE ifadesi her koşulu tek tek değerlendirir.

İfade ilk WHEN yan tümcesinin koşuluyla eşleşiyorsa, diğer tüm WHEN ve THEN koşullarını atlar ve sonuçta ifade_1 değerini döndürür.

İfade ilk WHEN koşuluyla eşleşmezse saniyenin WHEN koşuluyla karşılaştırılır. Bu eşleştirme işlemi, ifade herhangi bir WHEN koşuluyla eşleşene kadar devam edecektir.

İfadeyle hiçbir koşul eşleşmezse kontrol otomatik olarak ELSE kısmına gider ve sonucunu döndürür. CASE sözdiziminde ELSE kısmı isteğe bağlıdır.

tıklamada jquery

Söz diziminde CASE ve END, CASE ifadesinin başlangıcını ve kapanışını gösteren en önemli anahtar kelimelerdir.

SQL'deki CASE deyimi örnekleri

Öğrencilerin roll_no, adı, notları, konusu ve şehrini içeren Öğrenci_Detayları tablosunu ele alalım.

Örnek 1: Aşağıdaki SQL deyimi, CASE deyimi için tek WHEN ve THEN koşulunu kullanır:

 SELECT Roll_No, Stu_Name, Stu_Subject, Stu_marks, CASE WHEN Stu_Marks >= 50 THEN 'Student_Passed' ELSE 'Student_Failed' END AS Student_Result FROM Student_Details; 

Yukarıdaki sorgunun açıklaması:

Burada CASE deyimi şunu kontrol eder: Stu_Marks 50'den büyük ve eşittir, döndürür Öğrenci_Geçti aksi halde şuraya doğru hareket eder: BAŞKA parça ve geri dönüş Öğrenci_Başarısız içinde Öğrenci_Sonucu kolon.

Çıktı:

alfabeyi numaralandırma

Örnek 2: Aşağıdaki SQL deyimi, CASE deyimine birden fazla WHEN ve THEN koşulu ekler:

 SELECT Roll_No, Stu_Name, Stu_Subject, Stu_marks, CASE WHEN Stu_Marks &gt;= 90 THEN &apos;Outstanding&apos; WHEN Stu_Marks &gt;= 80 AND Stu_Marks = 70 AND Stu_Marks = 60 AND Stu_Marks = 50 AND Stu_Marks <60 50 then 'bad' when stu_marks < 'failed' end as stu_remarks from student_details; pre> <p> <strong>Explanation of above query:</strong> </p> <p>Here, the CASE statement checks multiple WHEN and THEN conditions one by one. If the value of <strong>Stu_Marks</strong> column is greater than or equals to <strong>90</strong> , it returns <strong>Outstanding</strong> otherwise moves to the further WHEN and THEN conditions.</p> <p>If none of the conditions is matched with the <strong>Student_Details</strong> table, CASE returns <strong>the NULL</strong> value in the <strong>Stu_Remarks</strong> column because there is no ELSE part in the query.</p> <p> <strong>Output:</strong> </p> <table class="table"> <tr> <th>Roll_No</th> <th>Stu_Name</th> <th>Stu_Subject</th> <th>Stu_Marks</th> <th>Stu_Remarks</th> </tr> <tr> <td>2001</td> <td>Akshay</td> <td>Science</td> <td>92</td> <td>Outstanding</td> </tr> <tr> <td>2002</td> <td>Ram Math</td> <td>49</td> <td>Failed</td> </tr> <tr> <td>2004</td> <td>Shyam</td> <td>English</td> <td>52</td> <td>Bad</td> </tr> <tr> <td>2005</td> <td>Yatin</td> <td>Hindi</td> <td>45</td> <td>Failed</td> </tr> <tr> <td>2006</td> <td>Manoj</td> <td>Computer</td> <td>70</td> <td>Good</td> </tr> <tr> <td>2007</td> <td>Sheetal</td> <td>Math</td> <td>82</td> <td>Excellent</td> </tr> <tr> <td>2008</td> <td>Parul</td> <td>Science</td> <td>62</td> <td>Average</td> </tr> <tr> <td>2009</td> <td>Yogesh</td> <td>English</td> <td>42</td> <td>Failed</td> </tr> <tr> <td>2010</td> <td>Ram</td> <td>Computer</td> <td>88</td> <td>Excellent</td> </tr> <tr> <td>2011</td> <td>Shyam</td> <td>Hindi</td> <td>35</td> <td>Failed</td> </tr> </table> <p> <strong>Example 3:</strong> </p> <p>Let&apos;s take another Employee_Details table which contains Emp_ID, Emp_Name, Emp_Dept, and Emp_Salary.</p> <table class="table"> <tr> <th>Emp_Id</th> <th>Emp_Name</th> <th>Emp_Dept</th> <th>Emp_Salary</th> </tr> <tr> <td>1</td> <td>Akshay</td> <td>Finance</td> <td>9000</td> </tr> <tr> <td>2</td> <td>Ram</td> <td>Marketing</td> <td>4000</td> </tr> <tr> <td>3</td> <td>Shyam</td> <td>Sales</td> <td>5000</td> </tr> <tr> <td>4</td> <td>Yatin</td> <td>Coding</td> <td>4000</td> </tr> <tr> <td>5</td> <td>Manoj</td> <td>Marketing</td> <td>5000</td> </tr> <tr> <td>1</td> <td>Akshay</td> <td>Finance</td> <td>8000</td> </tr> <tr> <td>2</td> <td>Ram</td> <td>Coding</td> <td>6000</td> </tr> <tr> <td>3</td> <td>Shyam</td> <td>Coding</td> <td>4000</td> </tr> <tr> <td>4</td> <td>Yatin</td> <td>Marketing</td> <td>8000</td> </tr> <tr> <td>5</td> <td>Manoj</td> <td>Finance</td> <td>3000</td> </tr> </table> <p> <strong>The following SQL query uses GROUP BY clause with CASE statement:</strong> </p> <pre> SELECT Emp_Id, Emp_Name, Emp_Dept, sum(Emp_Salary) as Total_Salary, CASE WHEN SUM(Emp_Salary) &gt;= 10000 THEN &apos;Increment&apos; ELSE &apos;Constant&apos; END AS Emp_Remarks FROM Employee_Details GROUP BY Emp_id, Emp_Name; </pre> <p> <strong>Output:</strong> </p> <table class="table"> <tr> <th>Emp_Id</th> <th>Emp_Name</th> <th>Emp_Dept</th> <th>Total_Salary</th> <th>Emp_Remarks</th> </tr> <tr> <td>1</td> <td>Akshay</td> <td>Finance</td> <td>17000</td> <td>Increment</td> </tr> <tr> <td>2</td> <td>Ram</td> <td>Marketing</td> <td>9000</td> <td>Decrement</td> </tr> <tr> <td>3</td> <td>Shyam</td> <td>Sales</td> <td>10000</td> <td>Increment</td> </tr> <tr> <td>4</td> <td>Yatin</td> <td>Coding</td> <td>12000</td> <td>Increment</td> </tr> <tr> <td>5</td> <td>Manoj</td> <td>Marketing</td> <td>8000</td> <td>Decrement</td> </tr> </table> <p> <strong>Example 4: In this example, we use the ORDER BY clause with a CASE statement in SQL:</strong> </p> <p>Let&apos;s take another Employee_Details table which contains Emp_ID, Emp_Name, Emp_Dept, and Emp_Age.</p> <p>We can check the data of Employee_Details by using the following query in SQL:</p> <pre> Select * From Employee_Details; </pre> <p> <strong>Output:</strong> </p> <table class="table"> <tr> <th>Emp_Id</th> <th>Emp_Name</th> <th>Emp_Dept</th> <th>Emp_Age</th> </tr> <tr> <td>1</td> <td>Akshay</td> <td>Finance</td> <td>23</td> </tr> <tr> <td>2</td> <td>Ram</td> <td>Marketing</td> <td>24</td> </tr> <tr> <td>3</td> <td>Balram</td> <td>Sales</td> <td>25</td> </tr> <tr> <td>4</td> <td>Yatin</td> <td>Coding</td> <td>22</td> </tr> <tr> <td>5</td> <td>Manoj</td> <td>Marketing</td> <td>23</td> </tr> <tr> <td>6</td> <td>Sheetal</td> <td>Finance</td> <td>24</td> </tr> <tr> <td>7</td> <td>Parul</td> <td>Finance</td> <td>22</td> </tr> <tr> <td>8</td> <td>Yogesh</td> <td>Coding</td> <td>25</td> </tr> <tr> <td>9</td> <td>Naveen</td> <td>Marketing</td> <td>22</td> </tr> <tr> <td>10</td> <td>Tarun</td> <td>Finance</td> <td>23</td> </tr> </table> <p>The following SQL query shows all the details of employees in the ascending order of employee names:</p> <pre> SELECT * FROM Employee_Details ORDER BY Emp_Name; </pre> <p> <strong>Output:</strong> </p> <table class="table"> <tr> <th>Emp_Id</th> <th>Emp_Name</th> <th>Emp_Dept</th> <th>Emp_Age</th> </tr> <tr> <td>1</td> <td>Akshay</td> <td>Finance</td> <td>23</td> </tr> <tr> <td>3</td> <td>Balram</td> <td>Sales</td> <td>25</td> </tr> <tr> <td>5</td> <td>Manoj</td> <td>Marketing</td> <td>23</td> </tr> <tr> <td>9</td> <td>Naveen</td> <td>Marketing</td> <td>22</td> </tr> <tr> <td>7</td> <td>Parul</td> <td>Finance</td> <td>22</td> </tr> <tr> <td>2</td> <td>Ram</td> <td>Marketing</td> <td>24</td> </tr> <tr> <td>6</td> <td>Sheetal</td> <td>Finance</td> <td>24</td> </tr> <tr> <td>10</td> <td>Tarun</td> <td>Finance</td> <td>23</td> </tr> <tr> <td>4</td> <td>Yatin</td> <td>Coding</td> <td>22</td> </tr> <tr> <td>8</td> <td>Yogesh</td> <td>Coding</td> <td>25</td> </tr> </table> <p>If you want to show those employees at the top who work in the Coding Department, then for this operation, you have to use single WHEN and THEN statement in the CASE statement as shown in the following query:</p> <pre> SELECT * FROM Employee_Details ORDER BY CASE WHEN Emp_Dept = &apos;Coding&apos; THEN 0 ELSE 1 END, Emp_Name; </pre> <p> <strong>Output:</strong> </p> <table class="table"> <tr> <th>Emp_Id</th> <th>Emp_Name</th> <th>Emp_Dept</th> <th>Emp_Age</th> </tr> <tr> <td>4</td> <td>Yatin</td> <td>Coding</td> <td>22</td> </tr> <tr> <td>8</td> <td>Yogesh</td> <td>Coding</td> <td>25</td> </tr> <tr> <td>1</td> <td>Akshay</td> <td>Finance</td> <td>23</td> </tr> <tr> <td>3</td> <td>Balram</td> <td>Sales</td> <td>25</td> </tr> <tr> <td>5</td> <td>Manoj</td> <td>Marketing</td> <td>23</td> </tr> <tr> <td>9</td> <td>Naveen</td> <td>Marketing</td> <td>22</td> </tr> <tr> <td>7</td> <td>Parul</td> <td>Finance</td> <td>22</td> </tr> <tr> <td>2</td> <td>Ram</td> <td>Marketing</td> <td>24</td> </tr> <tr> <td>6</td> <td>Sheetal</td> <td>Finance</td> <td>24</td> </tr> <tr> <td>10</td> <td>Tarun</td> <td>Finance</td> <td>23</td> </tr> </table> <hr></60>

Çıktı:

Örnek 4: Bu örnekte, SQL'de ORDER BY deyimini CASE deyimiyle birlikte kullanıyoruz:

nesne java'sı nedir

Emp_ID, Emp_Name, Emp_Dept ve Emp_Age'i içeren başka bir Employee_Details tablosunu alalım.

SQL'de aşağıdaki sorguyu kullanarak Employee_Details verilerini kontrol edebiliriz:

 Select * From Employee_Details; 

Çıktı:

Aşağıdaki SQL sorgusu çalışanların tüm ayrıntılarını çalışan adlarına göre artan sırada gösterir:

 SELECT * FROM Employee_Details ORDER BY Emp_Name; 

Çıktı:

Kodlama Departmanında çalışan en üstteki çalışanları göstermek istiyorsanız bu işlem için aşağıdaki sorguda gösterildiği gibi CASE deyiminde tek WHEN ve THEN deyimini kullanmanız gerekir:

tarihe dönüştürülecek dize

 SELECT * FROM Employee_Details ORDER BY CASE WHEN Emp_Dept = 'Coding' THEN 0 ELSE 1 END, Emp_Name; 

Çıktı: