在實際編寫sql的過程中,我們往往會遇到一些比較複雜的sql場景,這個時候,我們
利用自查詢可以解決.
問題: 傳回在每月最後實際訂單日期發生的訂單
T-sql代碼
-- 傳回在每月最後實際訂單日期發生的訂單
-- (Orders placed on last actual order date of the month)
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate IN
(SELECT MAX(OrderDate)
FROM dbo.Orders
GROUP BY CONVERT(CHAR(6), OrderDate, 112))
order by OrderDate
GO
傳回結果:
OrderID CustomerID EmployeeID OrderDate
----------- ---------- ----------- -----------------------
10269 WHITC 5 1996-07-31 00:00:00.000
10294 RATTC 4 1996-08-30 00:00:00.000
10317 LONEP 6 1996-09-30 00:00:00.000
10343 LEHMS 4 1996-10-31 00:00:00.000
10368 ERNSH 2 1996-11-29 00:00:00.000
10399 VAFFE 8 1996-12-31 00:00:00.000
10432 SPLIR 3 1997-01-31 00:00:00.000
10460 FOLKO 8 1997-02-28 00:00:00.000
10461 LILAS 1 1997-02-28 00:00:00.000
10490 HILAA 7 1997-03-31 00:00:00.000
10491 FURIB 8 1997-03-31 00:00:00.000
10522 LEHMS 4 1997-04-30 00:00:00.000
10553 WARTH 2 1997-05-30 00:00:00.000
10554 OTTIK 4 1997-05-30 00:00:00.000
10583 WARTH 2 1997-06-30 00:00:00.000
10584 BLONP 4 1997-06-30 00:00:00.000
10616 GREAL 1 1997-07-31 00:00:00.000
10617 GREAL 4 1997-07-31 00:00:00.000
10650 FAMIA 5 1997-08-29 00:00:00.000
10686 PICCO 2 1997-09-30 00:00:00.000
10687 HUNGO 9 1997-09-30 00:00:00.000
10725 FAMIA 4 1997-10-31 00:00:00.000
10758 RICSU 3 1997-11-28 00:00:00.000
10759 ANATR 3 1997-11-28 00:00:00.000
10806 VICTE 3 1997-12-31 00:00:00.000
10807 FRANS 4 1997-12-31 00:00:00.000
10861 WHITC 4 1998-01-30 00:00:00.000
10862 LEHMS 8 1998-01-30 00:00:00.000
10914 QUEEN 6 1998-02-27 00:00:00.000
10915 TORTU 2 1998-02-27 00:00:00.000
10916 RANCH 1 1998-02-27 00:00:00.000
10987 EASTC 8 1998-03-31 00:00:00.000
10988 RATTC 3 1998-03-31 00:00:00.000
10989 QUEDE 2 1998-03-31 00:00:00.000
11060 FRANS 2 1998-04-30 00:00:00.000
11061 GREAL 4 1998-04-30 00:00:00.000
11062 REGGC 4 1998-04-30 00:00:00.000
11063 HUNGO 3 1998-04-30 00:00:00.000
11074 SIMOB 7 1998-05-06 00:00:00.000
11075 RICSU 8 1998-05-06 00:00:00.000
11076 BONAP 4 1998-05-06 00:00:00.000
11077 RATTC 1 1998-05-06 00:00:00.000
(42 row(s) affected)