选择每个客户端的最大日期
本文关键字:日期 客户端 选择 | 更新日期: 2023-09-27 18:12:43
我正在努力建立一个查询,其中只有一个客户端的记录将被显示,但显示的记录必须是最后一个记录添加到该客户端的数据库…我可以使用MAX(date)来获取最新的日期。这样做的原因是我正在构建一个Gridview,其中每个客户端的一条记录将被列出(最后添加的记录),然后他们可以选择查看历史记录。不能看到同一客户端的第二行....
这是我的查询:
SELECT Client, Model, SerialNo, Dep, Level, Colour, Status
FROM TonerRequest
WHERE Status = 'Open'
谁能告诉我该怎么做?或者我可以在其他地方了解如何做到这一点?
您可以使用row_number()
选择每个客户端的最新行:
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (
PARTITION BY Client
ORDER BY Date DESC) as rn
, Client
, Model
, SerialNo
, ...
FROM TonerRequest
WHERE Status = 'Open'
) SubQueryALias
WHERE rn = 1
假设Client是您的PK,您可以采用如下方法
Select * from TonerRequest
where Status = 'Open'
and date in (select max(date) from TonerRequest group by Client)
像这样:
SELECT * FROM TonerRequest TR,
( Max(YourDateColumn) MaxDate, Client
FROM TonerRequest
WHERE Status = 'Open'
GROUP BY Client ) TRSub
WHERE TR.Client == TRSub.Client
AND TR.YourDateColum == TRSub.MaxDate
我将使用self join(到同一表的INNER join)来解决这个问题。
SELECT DISTINCT
a.Client
,a.Model
,a.SerialNo
,a.Dep
,a.Level
,a.Colour
,a.Status
FROM TonerRequest AS a
INNER JOIN
(SELECT
MAX([Date]) OVER (PARTITION BY Client) AS 'Date'
,Client
,Status
FROM TonerRequest
WHERE Status = 'Open'
) AS b
ON a.Client = b.Client
AND a.[Date] = b.[Date]
AND a.Status = b.Status
;
a是SELECT子句将针对的对象,而b将限制从from子句中给出的SELECT结果。
我把一个样本放在SQL Fiddle: http://sqlfiddle.com/#!6/ba532/6
查看此处获取更多关于自连接的信息:http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/
试试这个:
SELECT TOP 1 Client, Model, SerialNo, Dep, Level, Colour, Status FROM TonerRequest WHERE Status = 'Open' ORDER BY Date DESC