T-SQL：基礎練習（雜）

1.返回 每月最後一天訂單

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);

 使用EMONTH 對輸入的日期返回月末日期   類似動態條件

DATEDIFF(month, '19991231', orderdate)  相差多少月  從19991231到 orderdate之間先查多少月

DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231') 中間是加上的月數  最後一個從什麼時間開始加

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');

兩種寫法  記住 可以用動態條件 區分記錄

2. 姓氏a上有相同的 

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE '%a%a%';

3.對每個客戶的訂單日期排序

SELECT custid, orderdate, orderid,
  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;

4.條件篩選case

SELECT empid, firstname, lastname, titleofcourtesy,
  CASE titleofcourtesy
    WHEN 'Ms.'  THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Mr.'  THEN 'Male'
    ELSE             'Unknown'
  END AS gender
FROM HR.Employees;

SELECT empid, firstname, lastname, titleofcourtesy,
CASE 
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;

第一種 是指向搜索  固定的指向  第二種沒有可以多欄位條件

5.在order by 加限制排序條件

SELECT custid, region
FROM Sales.Customers
ORDER BY
  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

兩個條件 0，1 預設排序規則 通過int 對相應的條件進行排序