在实际编写sql的过程中,我们往往会遇到一些比较复杂的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)