外企 SQL Server 面試題精選：日期計算、CASE 邏輯、去重、交叉表

Q1：單一 SQL 算當月天數

SELECT DATEPART(dd, DATEADD(dd, -1, DATEADD(mm, 1,
  CAST(CAST(YEAR(GETDATE()) AS varchar) + '-' + CAST(MONTH(GETDATE()) AS varchar) + '-01' AS datetime))))

Q2：價格分段顯示 (CASE)

SELECT bookid, bookname,
  price = CASE
    WHEN price IS NULL THEN 'unknown'
    WHEN price BETWEEN 10 AND 20 THEN '10 to 20'
    ELSE CAST(price AS varchar)
  END
FROM books;

Q3：找出同姓作者 (去重計數)

SELECT au_lname, number_dups = COUNT(*)
FROM authors
GROUP BY au_lname
HAVING COUNT(*) > 1;

Q4：1993 年各門市各季銷售交叉表

SELECT stor_name,
  SUM(qty) AS Total,
  SUM(CASE WHEN DATEPART(qq, ord_date)=1 THEN qty ELSE 0 END) AS Qtr1,
  SUM(CASE WHEN DATEPART(qq, ord_date)=2 THEN qty ELSE 0 END) AS Qtr2,
  SUM(CASE WHEN DATEPART(qq, ord_date)=3 THEN qty ELSE 0 END) AS Qtr3,
  SUM(CASE WHEN DATEPART(qq, ord_date)=4 THEN qty ELSE 0 END) AS Qtr4
FROM sales s JOIN stores st ON s.stor_id = st.stor_id
WHERE YEAR(ord_date) = 1993
GROUP BY stor_name
WITH ROLLUP; -- 產生總計行

涵蓋日期運算、條件邏輯、聚合去重、動態列轉行 (PIVOT) 核心考點。