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 对相应的条件进行排序