SQL - 拉取客户端(结果集中每行 1 个客户端)以及这些客户端的最近订单日期
下面是一些用于提取客户端列表的 SQL。正如你所看到的,我添加了用“--ADDED”注释的行——一个新字段、一个连接和一个排序依据。我试图在结果集中每行显示一个客户端,但对于每个客户端,我想显示最新的订单。最好的方法是什么?我应该在 JOIN 中放置一个 where 还是使用 TOP 1?我想有几种方法,但我想让它尽可能高效。
SELECT
CONVERT(NVARCHAR(255),client.identifyingnumber) As [Client ID],
CONVERT(VARCHAR,client.name) As [Client Name],
client.clientid As [Id],
CONVERT(CHAR(10),[ecom_order].[order_date],101) -- ADDED
FROM
client
JOIN clientstatus ON client.clientstatusid = clientstatus.clientstatusid
JOIN clienttype ON clienttype.clienttypeid = client.clienttypeid
-- keep left outer join since some clients won't have any orders, thus no last order
LEFT OUTER JOIN [ecom_order] ON client.clientid = [ecom_order].clientid -- ADDED
WHERE
client.name LIKE '%_%'
AND
client.name <= (
SELECT MAX(maxsubquery.ordercolumn) FROM (
SELECT
TOP 10 client.name AS ordercolumn
FROM
client
JOIN clientstatus ON client.clientstatusid = clientstatus.clientstatusid
JOIN clienttype ON clienttype.clienttypeid = client.clienttypeid
WHERE
client.name LIKE '%_%'
ORDER BY client.name
)
AS maxsubquery)
ORDER BY
client.name,
[ecom_order].order_date DESC -- ADDED
测试数据结果集示例:
Below is some SQL to pull a list of clients. As you can see I added the lines annotated by "-- ADDED"--a new field, a join and an order by. I'm trying to display one client per row in my result set, but for each client, I want to display the most recent order. What's the best way to do this? Should I put a where in my JOIN or use the TOP 1? I suppose there are a few ways, but I want to make it as efficient as possible.
SELECT
CONVERT(NVARCHAR(255),client.identifyingnumber) As [Client ID],
CONVERT(VARCHAR,client.name) As [Client Name],
client.clientid As [Id],
CONVERT(CHAR(10),[ecom_order].[order_date],101) -- ADDED
FROM
client
JOIN clientstatus ON client.clientstatusid = clientstatus.clientstatusid
JOIN clienttype ON clienttype.clienttypeid = client.clienttypeid
-- keep left outer join since some clients won't have any orders, thus no last order
LEFT OUTER JOIN [ecom_order] ON client.clientid = [ecom_order].clientid -- ADDED
WHERE
client.name LIKE '%_%'
AND
client.name <= (
SELECT MAX(maxsubquery.ordercolumn) FROM (
SELECT
TOP 10 client.name AS ordercolumn
FROM
client
JOIN clientstatus ON client.clientstatusid = clientstatus.clientstatusid
JOIN clienttype ON clienttype.clienttypeid = client.clienttypeid
WHERE
client.name LIKE '%_%'
ORDER BY client.name
)
AS maxsubquery)
ORDER BY
client.name,
[ecom_order].order_date DESC -- ADDED
Test Data Result Set Example:
试试这个
Try this