利用子查询解决复杂sql问题

在实际编写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)