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

最新推荐文章于 2023-03-09 19:39:13 发布

weixin_34014277

最新推荐文章于 2023-03-09 19:39:13 发布

阅读量243

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/davidgu/archive/2011/02/07/1949617.html

本文介绍了一种使用T-SQL自查询技巧，来找出每个月最后一天的实际订单日期的订单记录的方法。通过内层查询获取每个月的最大OrderDate，外层查询则根据这些日期筛选出相应的订单详情。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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