如何连接表中的最新行？

发布于 2024-07-06 14:30:13 字数 467 浏览 7 评论 0原文

我经常遇到这种形式的问题，但还没有找到好的解决方案：

假设我们有两个代表电子商务系统的数据库表。

userData (userId, name, ...)
orderData (orderId, userId, orderType, createDate, ...)

对于系统中的所有用户，选择其用户信息、类型 = '1' 的最新订单信息以及类型 = '2' 的最新订单信息。我想在一个查询中完成此操作。这是一个示例结果：

(userId, name, ..., orderId1, orderType1, createDate1, ..., orderId2, orderType2, createDate2, ...)
(101, 'Bob', ..., 472, '1', '4/25/2008', ..., 382, '2', '3/2/2008', ...)

原文

I frequently run into problems of this form and haven't found a good solution yet:

Assume we have two database tables representing an e-commerce system.

userData (userId, name, ...)
orderData (orderId, userId, orderType, createDate, ...)

For all users in the system, select their user information, their most recent order information with type = '1', and their most recent order information with type = '2'. I want to do this in one query. Here is an example result:

(userId, name, ..., orderId1, orderType1, createDate1, ..., orderId2, orderType2, createDate2, ...)
(101, 'Bob', ..., 472, '1', '4/25/2008', ..., 382, '2', '3/2/2008', ...)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

在你怀里撒娇 2024-07-13 14:30:13

这应该可行，您必须调整表/列名称：

select ud.name,
       order1.order_id,
       order1.order_type,
       order1.create_date,
       order2.order_id,
       order2.order_type,
       order2.create_date
  from user_data ud,
       order_data order1,
       order_data order2
 where ud.user_id = order1.user_id
   and ud.user_id = order2.user_id
   and order1.order_id = (select max(order_id)
                            from order_data od1
                           where od1.user_id = ud.user_id
                             and od1.order_type = 'Type1')
   and order2.order_id = (select max(order_id)
                             from order_data od2
                            where od2.user_id = ud.user_id
                              and od2.order_type = 'Type2')

对数据进行非规范化也可能是一个好主意。做这种事情的成本相当高。因此，您可以将 last_order_date 添加到您的 userData 中。

This should work, you'll have to adjust the table / column names:

select ud.name,
       order1.order_id,
       order1.order_type,
       order1.create_date,
       order2.order_id,
       order2.order_type,
       order2.create_date
  from user_data ud,
       order_data order1,
       order_data order2
 where ud.user_id = order1.user_id
   and ud.user_id = order2.user_id
   and order1.order_id = (select max(order_id)
                            from order_data od1
                           where od1.user_id = ud.user_id
                             and od1.order_type = 'Type1')
   and order2.order_id = (select max(order_id)
                             from order_data od2
                            where od2.user_id = ud.user_id
                              and od2.order_type = 'Type2')

Denormalizing your data might also be a good idea. This type of thing will be fairly expensive to do. So you might add a last_order_date to your userData.

回复收藏 0 原文

似梦非梦 2024-07-13 14:30:13

我提供了三种不同的方法来解决此问题：

使用数据透视表
使用 Case 语句
在 where 子句中使用内联查询

所有解决方案都假设我们根据 orderId 列确定“最新”订单。使用 createDate 列会因时间戳冲突而增加复杂性，并严重影响性能，因为 createDate 可能不是索引键的一部分。我只使用 MS SQL Server 2005 测试了这些查询，因此我不知道它们是否适用于您的服务器。

解决方案 (1) 和 (2) 的性能几乎相同。事实上，它们都会导致从数据库中读取相同数量的数据。

处理大型数据集时，解决方案 (3) 不是首选方法。它始终比 (1) 和 (2) 进行数百次逻辑读取。当针对一个特定用户进行过滤时，方法（3）与其他方法相当。在单用户情况下，CPU 时间的下降有助于抵消显着增加的读取次数；然而，随着磁盘驱动器变得更加繁忙并且发生缓存未命中，这种微小的优势将消失。

结论

对于所呈现的场景，如果您的 DBMS 支持，请使用数据透视方法。它比 case 语句需要更少的代码，并简化了将来添加订单类型的过程。

请注意，在某些情况下，PIVOT 不够灵活，使用 case 语句的特征值函数是可行的方法。

使用 PIVOT 的代码

方法 (1)：

select 
    ud.userId, ud.fullname, 
    od1.orderId as orderId1, od1.createDate as createDate1, od1.orderType as orderType1,
    od2.orderId as orderId2, od2.createDate as createDate2, od2.orderType as orderType2

from userData ud
    inner join (
            select userId, [1] as typeOne, [2] as typeTwo
            from (select
                userId, orderType, orderId
            from orderData) as orders
            PIVOT
            (
                max(orderId)
                FOR orderType in ([1], [2])
            ) as LatestOrders) as LatestOrders on
        LatestOrders.userId = ud.userId 
    inner join orderData od1 on
        od1.orderId = LatestOrders.typeOne
    inner join orderData od2 on
        od2.orderId = LatestOrders.typeTwo

使用 Case 语句的方法 (2)：

select 
    ud.userId, ud.fullname, 
    od1.orderId as orderId1, od1.createDate as createDate1, od1.orderType as orderType1,
    od2.orderId as orderId2, od2.createDate as createDate2, od2.orderType as orderType2

from userData ud 
    -- assuming not all users will have orders use outer join
    inner join (
            select 
                od.userId,
                -- can be null if no orders for type
                max (case when orderType = 1 
                        then ORDERID
                        else null
                        end) as maxTypeOneOrderId,

                -- can be null if no orders for type
                max (case when orderType = 2
                        then ORDERID 
                        else null
                        end) as maxTypeTwoOrderId
            from orderData od
            group by userId) as maxOrderKeys on
        maxOrderKeys.userId = ud.userId
    inner join orderData od1 on
        od1.ORDERID = maxTypeTwoOrderId
    inner join orderData od2 on
        OD2.ORDERID = maxTypeTwoOrderId

在 where 子句中使用内联查询的方法 (3)（基于 Steve K. 的响应）：

select  ud.userId,ud.fullname, 
        order1.orderId, order1.orderType, order1.createDate, 
        order2.orderId, order2.orderType, order2.createDate
  from userData ud,
       orderData order1,
       orderData order2
 where ud.userId = order1.userId
   and ud.userId = order2.userId
   and order1.orderId = (select max(orderId)
                            from orderData od1
                           where od1.userId = ud.userId
                             and od1.orderType = 1)
   and order2.orderId = (select max(orderId)
                             from orderData od2
                            where od2.userId = ud.userId
                              and od2.orderType = 2)

用于生成表和 1000 个用户（每个用户有 100 个订单）的脚本：

CREATE TABLE [dbo].[orderData](
    [orderId] [int] IDENTITY(1,1) NOT NULL,
    [createDate] [datetime] NOT NULL,
    [orderType] [tinyint] NOT NULL, 
    [userId] [int] NOT NULL
) 

CREATE TABLE [dbo].[userData](
    [userId] [int] IDENTITY(1,1) NOT NULL,
    [fullname] [nvarchar](50) NOT NULL
) 

-- Create 1000 users with 100 order each
declare @userId int
declare @usersAdded int
set @usersAdded = 0

while @usersAdded < 1000
begin
    insert into userData (fullname) values ('Mario' + ltrim(str(@usersAdded)))
    set @userId = @@identity

    declare @orderSetsAdded int
    set @orderSetsAdded = 0
    while @orderSetsAdded < 10
    begin
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-06-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-02-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-08-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-09-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-01-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-06-06', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-02-02', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-08-09', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-09-01', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-01-04', 2)

        set @orderSetsAdded = @orderSetsAdded + 1
    end
    set @usersAdded = @usersAdded + 1
end

除了 SQL Profiler 之外，用于测试 MS SQL Server 上的查询性能的小片段：

-- Uncomment these to clear some caches
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE

set statistics io on
set statistics time on

-- INSERT TEST QUERY HERE

set statistics time off
set statistics io off

I have provided three different approaches for solving this problem:

Using Pivots
Using Case Statements
Using inline queries in the where clause

All of the solutions assume we are determining the "most recent" order based on the orderId column. Using the createDate column would add complexity due to timestamp collisions and seriously hinder performance since createDate is probably not part of the indexed key. I have only tested these queries using MS SQL Server 2005, so I have no idea if they will work on your server.

Solutions (1) and (2) perform almost identically. In fact, they both result in the same number of reads from the database.

Solution (3) is not the preferred approach when working with large data sets. It consistently makes hundreds of logical reads more than (1) and (2). When filtering for one specific user, approach (3) is comparable to the other methods. In the single user case, a drop in the cpu time helps to counter the significantly higher number of reads; however, as the disk drive becomes busier and cache misses occur, this slight advantage will disappear.

Conclusion

For the presented scenario, use the pivot approach if it is supported by your DBMS. It requires less code than the case statement and simplifies adding order types in the future.

Please note, in some cases, PIVOT is not flexible enough and characteristic value functions using case statements are the way to go.

Code

Approach (1) using PIVOT:

select 
    ud.userId, ud.fullname, 
    od1.orderId as orderId1, od1.createDate as createDate1, od1.orderType as orderType1,
    od2.orderId as orderId2, od2.createDate as createDate2, od2.orderType as orderType2

from userData ud
    inner join (
            select userId, [1] as typeOne, [2] as typeTwo
            from (select
                userId, orderType, orderId
            from orderData) as orders
            PIVOT
            (
                max(orderId)
                FOR orderType in ([1], [2])
            ) as LatestOrders) as LatestOrders on
        LatestOrders.userId = ud.userId 
    inner join orderData od1 on
        od1.orderId = LatestOrders.typeOne
    inner join orderData od2 on
        od2.orderId = LatestOrders.typeTwo

Approach (2) using Case Statements:

select 
    ud.userId, ud.fullname, 
    od1.orderId as orderId1, od1.createDate as createDate1, od1.orderType as orderType1,
    od2.orderId as orderId2, od2.createDate as createDate2, od2.orderType as orderType2

from userData ud 
    -- assuming not all users will have orders use outer join
    inner join (
            select 
                od.userId,
                -- can be null if no orders for type
                max (case when orderType = 1 
                        then ORDERID
                        else null
                        end) as maxTypeOneOrderId,

                -- can be null if no orders for type
                max (case when orderType = 2
                        then ORDERID 
                        else null
                        end) as maxTypeTwoOrderId
            from orderData od
            group by userId) as maxOrderKeys on
        maxOrderKeys.userId = ud.userId
    inner join orderData od1 on
        od1.ORDERID = maxTypeTwoOrderId
    inner join orderData od2 on
        OD2.ORDERID = maxTypeTwoOrderId

Approach (3) using inline queries in the where clause (based on Steve K.'s response):

select  ud.userId,ud.fullname, 
        order1.orderId, order1.orderType, order1.createDate, 
        order2.orderId, order2.orderType, order2.createDate
  from userData ud,
       orderData order1,
       orderData order2
 where ud.userId = order1.userId
   and ud.userId = order2.userId
   and order1.orderId = (select max(orderId)
                            from orderData od1
                           where od1.userId = ud.userId
                             and od1.orderType = 1)
   and order2.orderId = (select max(orderId)
                             from orderData od2
                            where od2.userId = ud.userId
                              and od2.orderType = 2)

Script to generate tables and 1000 users with 100 orders each:

CREATE TABLE [dbo].[orderData](
    [orderId] [int] IDENTITY(1,1) NOT NULL,
    [createDate] [datetime] NOT NULL,
    [orderType] [tinyint] NOT NULL, 
    [userId] [int] NOT NULL
) 

CREATE TABLE [dbo].[userData](
    [userId] [int] IDENTITY(1,1) NOT NULL,
    [fullname] [nvarchar](50) NOT NULL
) 

-- Create 1000 users with 100 order each
declare @userId int
declare @usersAdded int
set @usersAdded = 0

while @usersAdded < 1000
begin
    insert into userData (fullname) values ('Mario' + ltrim(str(@usersAdded)))
    set @userId = @@identity

    declare @orderSetsAdded int
    set @orderSetsAdded = 0
    while @orderSetsAdded < 10
    begin
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-06-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-02-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-08-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-09-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-01-08', 1)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-06-06', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-02-02', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-08-09', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-09-01', 2)
        insert into orderData (userId, createDate, orderType) 
            values ( @userId, '01-01-04', 2)

        set @orderSetsAdded = @orderSetsAdded + 1
    end
    set @usersAdded = @usersAdded + 1
end

Small snippet for testing query performance on MS SQL Server in addition to SQL Profiler:

-- Uncomment these to clear some caches
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE

set statistics io on
set statistics time on

-- INSERT TEST QUERY HERE

set statistics time off
set statistics io off

回复收藏 0 原文

很酷不放纵 2024-07-13 14:30:13

抱歉，我面前没有 oracle，但这是我在 oracle 中执行操作的基本结构：

SELECT b.user_id, b.orderid, b.orderType, b.createDate, <etc>,
       a.name
FROM orderData b, userData a
WHERE a.userid = b.userid
AND (b.userid, b.orderType, b.createDate) IN (
  SELECT userid, orderType, max(createDate) 
  FROM orderData 
  WHERE orderType IN (1,2)
  GROUP BY userid, orderType)

Sorry I don't have oracle in front of me, but this is the basic structure of what I would do in oracle:

SELECT b.user_id, b.orderid, b.orderType, b.createDate, <etc>,
       a.name
FROM orderData b, userData a
WHERE a.userid = b.userid
AND (b.userid, b.orderType, b.createDate) IN (
  SELECT userid, orderType, max(createDate) 
  FROM orderData 
  WHERE orderType IN (1,2)
  GROUP BY userid, orderType)

回复收藏 0 原文

深海蓝天 2024-07-13 14:30:13

T-SQL 示例解决方案 (MS SQL)：

SELECT
    u.*
    , o1.*
    , o2.* 
FROM
(
    SELECT
        , userData.*
        , (SELECT TOP 1 orderId.url FROM orderData WHERE orderData.userId=userData.userId AND orderType=1 ORDER BY createDate DESC)
            AS order1Id
        , (SELECT TOP 1 orderId.url FROM orderData WHERE orderData.userId=userData.userId AND orderType=2 ORDER BY createDate DESC)
            AS order2Id
    FROM userData
) AS u
LEFT JOIN orderData o1 ON (u.order1Id=o1.orderId)
LEFT JOIN orderData o2 ON (u.order2Id=o2.orderId)

在 SQL 2005 中，您还可以使用 RANK ( ) OVER 函数。（但据我所知，它完全是 MSSQL 特有的功能）

T-SQL sample solution (MS SQL):

SELECT
    u.*
    , o1.*
    , o2.* 
FROM
(
    SELECT
        , userData.*
        , (SELECT TOP 1 orderId.url FROM orderData WHERE orderData.userId=userData.userId AND orderType=1 ORDER BY createDate DESC)
            AS order1Id
        , (SELECT TOP 1 orderId.url FROM orderData WHERE orderData.userId=userData.userId AND orderType=2 ORDER BY createDate DESC)
            AS order2Id
    FROM userData
) AS u
LEFT JOIN orderData o1 ON (u.order1Id=o1.orderId)
LEFT JOIN orderData o2 ON (u.order2Id=o2.orderId)

In SQL 2005 you could also use RANK ( ) OVER function. (But AFAIK its completely MSSQL-specific feature)

回复收藏 0 原文

暮年 2024-07-13 14:30:13

您也许可以为此执行联合查询。确切的语法需要一些工作，特别是按部分分组，但联合应该能够做到。

例如：

SELECT orderId, orderType, createDate
FROM orderData
WHERE type=1 AND MAX(createDate)
GROUP BY orderId, orderType, createDate

UNION

SELECT orderId, orderType, createDate
FROM orderData
WHERE type=2 AND MAX(createDate)
GROUP BY orderId, orderType, createDate

You might be able to do a union query for this. The exact syntax needs some work, especially the group by section, but the union should be able to do it.

For example:

SELECT orderId, orderType, createDate
FROM orderData
WHERE type=1 AND MAX(createDate)
GROUP BY orderId, orderType, createDate

UNION

SELECT orderId, orderType, createDate
FROM orderData
WHERE type=2 AND MAX(createDate)
GROUP BY orderId, orderType, createDate

回复收藏 0 原文

橘寄 2024-07-13 14:30:13

他们最新的你的意思是当今的所有新东西？如果 createDate >= 当天，您可以随时检查 createDate 并获取所有用户和订单数据。

SELECT * FROM
"orderData", "userData"
WHERE
"userData"."userId"  ="orderData"."userId"
AND "orderData".createDate >= current_date;

已更新

这是您在此处发表评论后想要的内容

SELECT * FROM
"orderData", "userData"
WHERE
"userData"."userId"  ="orderData"."userId"
AND "orderData".type = '1'
AND "orderData"."orderId" = (
SELECT "orderId" FROM "orderData"
WHERE 
"orderType" = '1'
ORDER "orderId" DESC
LIMIT 1

：）

Their newest you mean all new in the current day? You can always check with your createDate and get all user and order data if the createDate >= current day.

SELECT * FROM
"orderData", "userData"
WHERE
"userData"."userId"  ="orderData"."userId"
AND "orderData".createDate >= current_date;

UPDATED

Here is what you want after your comment here:

SELECT * FROM
"orderData", "userData"
WHERE
"userData"."userId"  ="orderData"."userId"
AND "orderData".type = '1'
AND "orderData"."orderId" = (
SELECT "orderId" FROM "orderData"
WHERE 
"orderType" = '1'
ORDER "orderId" DESC
LIMIT 1

)

回复收藏 0 原文

莳間冲淡了誓言ζ 2024-07-13 14:30:13

我在 MySQL 中使用类似的东西：

SELECT
   u.*,
   SUBSTRING_INDEX( MAX( CONCAT( o1.createDate, '##', o1.otherfield)), '##', -1) as o2_orderfield,
   SUBSTRING_INDEX( MAX( CONCAT( o2.createDate, '##', o2.otherfield)), '##', -1) as o2_orderfield
FROM
   userData as u
   LEFT JOIN orderData AS o1 ON (o1.userId=u.userId AND o1.orderType=1)
   LEFT JOIN orderData AS o2 ON (o1.userId=u.userId AND o2.orderType=2)
GROUP BY u.userId

简而言之，通过将条件字段（createDate）添加到感兴趣的字段（otherfield）之前，使用 MAX() 来获取最新的数据。然后 SUBSTRING_INDEX() 删除日期。

OTOH，如果您需要任意数量的订单（如果 userType 可以是任何数字，而不是有限的 ENUM）；最好处理单独的查询，如下所示：

select * from orderData where userId=XXX order by orderType, date desc group by orderType

针对每个用户。

i use things like this in MySQL:

SELECT
   u.*,
   SUBSTRING_INDEX( MAX( CONCAT( o1.createDate, '##', o1.otherfield)), '##', -1) as o2_orderfield,
   SUBSTRING_INDEX( MAX( CONCAT( o2.createDate, '##', o2.otherfield)), '##', -1) as o2_orderfield
FROM
   userData as u
   LEFT JOIN orderData AS o1 ON (o1.userId=u.userId AND o1.orderType=1)
   LEFT JOIN orderData AS o2 ON (o1.userId=u.userId AND o2.orderType=2)
GROUP BY u.userId

In short, use MAX() to get the newest, by prepending the criteria field (createDate) to the interesting field(s) (otherfield). SUBSTRING_INDEX() then strips off the date.

OTOH, if you need an arbitrary number of orders (if userType can be any number, and not a limited ENUM); it's better to handle with a separate query, something like this:

select * from orderData where userId=XXX order by orderType, date desc group by orderType

for each user.

回复收藏 0 原文

动次打次papapa 2024-07-13 14:30:13

假设 orderId 随时间单调增加：

SELECT *
FROM userData u
INNER JOIN orderData o
  ON o.userId = u.userId
INNER JOIN ( -- This subquery gives the last order of each type for each customer
  SELECT MAX(o2.orderId)
    --, o2.userId -- optional - include if joining for a particular customer
    --, o2.orderType -- optional - include if joining for a particular type
  FROM orderData o2
  GROUP BY o2.userId
    ,o2.orderType
) AS LastOrders
  ON LastOrders.orderId = o.orderId -- expand join to include customer or type if desired

然后在客户端进行透视，或者如果使用 SQL Server，则有 PIVOT 功能

Assuming orderId is monotonic increasing with time:

SELECT *
FROM userData u
INNER JOIN orderData o
  ON o.userId = u.userId
INNER JOIN ( -- This subquery gives the last order of each type for each customer
  SELECT MAX(o2.orderId)
    --, o2.userId -- optional - include if joining for a particular customer
    --, o2.orderType -- optional - include if joining for a particular type
  FROM orderData o2
  GROUP BY o2.userId
    ,o2.orderType
) AS LastOrders
  ON LastOrders.orderId = o.orderId -- expand join to include customer or type if desired

Then pivot at the client or if using SQL Server, there is a PIVOT functionality

回复收藏 0 原文

只为一人 2024-07-13 14:30:13

以下是将类型 1 和 2 数据移动到同一行的一种方法：
（通过将类型 1 和类型 2 信息放入它们自己的选择中，然后在 from 子句中使用。）

SELECT
  a.name, ud1.*, ud2.*
FROM
    userData a,
    (SELECT user_id, orderid, orderType, reateDate, <etc>,
    FROM orderData b
    WHERE (userid, orderType, createDate) IN (
      SELECT userid, orderType, max(createDate) 
      FROM orderData 
      WHERE orderType = 1
      GROUP BY userid, orderType) ud1,
    (SELECT user_id, orderid, orderType, createDate, <etc>,
    FROM orderData 
    WHERE (userid, orderType, createDate) IN (
      SELECT userid, orderType, max(createDate) 
      FROM orderData 
      WHERE orderType = 2
      GROUP BY userid, orderType) ud2

Here is one way to move the type 1 and 2 data on to the same row:
(by placing the type 1 and type 2 information into their own selects that then get used in the from clause.)

SELECT
  a.name, ud1.*, ud2.*
FROM
    userData a,
    (SELECT user_id, orderid, orderType, reateDate, <etc>,
    FROM orderData b
    WHERE (userid, orderType, createDate) IN (
      SELECT userid, orderType, max(createDate) 
      FROM orderData 
      WHERE orderType = 1
      GROUP BY userid, orderType) ud1,
    (SELECT user_id, orderid, orderType, createDate, <etc>,
    FROM orderData 
    WHERE (userid, orderType, createDate) IN (
      SELECT userid, orderType, max(createDate) 
      FROM orderData 
      WHERE orderType = 2
      GROUP BY userid, orderType) ud2

回复收藏 0 原文

柳絮泡泡 2024-07-13 14:30:13

我是这样做的。这是标准 SQL，适用于任何品牌的数据库。

SELECT u.userId, u.name, o1.orderId, o1.orderType, o1.createDate,
  o2.orderId, o2.orderType, o2.createDate
FROM userData AS u
  LEFT OUTER JOIN (
    SELECT o1a.orderId, o1a.userId, o1a.orderType, o1a.createDate
    FROM orderData AS o1a 
      LEFT OUTER JOIN orderData AS o1b ON (o1a.userId = o1b.userId 
        AND o1a.orderType = o1b.orderType AND o1a.createDate < o1b.createDate)
    WHERE o1a.orderType = 1 AND o1b.orderId IS NULL) AS o1 ON (u.userId = o1.userId)
  LEFT OUTER JOIN (
    SELECT o2a.orderId, o2a.userId, o2a.orderType, o2a.createDate
    FROM orderData AS o2a 
      LEFT OUTER JOIN orderData AS o2b ON (o2a.userId = o2b.userId 
        AND o2a.orderType = o2b.orderType AND o2a.createDate < o2b.createDate)
    WHERE o2a.orderType = 2 AND o2b.orderId IS NULL) o2 ON (u.userId = o2.userId);

请注意，如果您有多个任一类型的订单，其日期等于最新日期，您将在结果集中获得多行。如果您有多个这两种类型的订单，您将在结果集中获得 N x M 行。因此，我建议您在单独的查询中获取每种类型的行。

Here's how I do it. This is standard SQL and works in any brand of database.

SELECT u.userId, u.name, o1.orderId, o1.orderType, o1.createDate,
  o2.orderId, o2.orderType, o2.createDate
FROM userData AS u
  LEFT OUTER JOIN (
    SELECT o1a.orderId, o1a.userId, o1a.orderType, o1a.createDate
    FROM orderData AS o1a 
      LEFT OUTER JOIN orderData AS o1b ON (o1a.userId = o1b.userId 
        AND o1a.orderType = o1b.orderType AND o1a.createDate < o1b.createDate)
    WHERE o1a.orderType = 1 AND o1b.orderId IS NULL) AS o1 ON (u.userId = o1.userId)
  LEFT OUTER JOIN (
    SELECT o2a.orderId, o2a.userId, o2a.orderType, o2a.createDate
    FROM orderData AS o2a 
      LEFT OUTER JOIN orderData AS o2b ON (o2a.userId = o2b.userId 
        AND o2a.orderType = o2b.orderType AND o2a.createDate < o2b.createDate)
    WHERE o2a.orderType = 2 AND o2b.orderId IS NULL) o2 ON (u.userId = o2.userId);

Note that if you have multiple orders of either type whose dates are equal to the latest date, you'll get multiple rows in the result set. If you have multiple orders of both types, you'll get N x M rows in the result set. So I would recommend that you fetch the rows of each type in separate queries.

回复收藏 0 原文

赏烟花じ飞满天 2024-07-13 14:30:13

史蒂夫·K 完全正确，谢谢！我确实稍微重写了他的答案，以考虑到特定类型可能没有顺序的事实（我没有提及，所以我不能责怪 Steve K。）

这就是我最终使用的内容：

select ud.name,
       order1.orderId,
       order1.orderType,
       order1.createDate,
       order2.orderId,
       order2.orderType,
       order2.createDate
  from userData ud
  left join orderData order1
   on order1.orderId = (select max(orderId)
                            from orderData od1
                           where od1.userId = ud.userId
                             and od1.orderType = '1')
  left join orderData order2
   on order2.orderId = (select max(orderId)
                            from orderData od2
                           where od2.userId = ud.userId
                             and od2.orderType = '2')
 where ...[some limiting factors on the selection of users]...;

Steve K is absolutely right, thanks! I did rewrite his answer a little to account for the fact that there might be no order for a particular type (which I failed to mention, so I can't fault Steve K.)

Here's what I wound up using:

select ud.name,
       order1.orderId,
       order1.orderType,
       order1.createDate,
       order2.orderId,
       order2.orderType,
       order2.createDate
  from userData ud
  left join orderData order1
   on order1.orderId = (select max(orderId)
                            from orderData od1
                           where od1.userId = ud.userId
                             and od1.orderType = '1')
  left join orderData order2
   on order2.orderId = (select max(orderId)
                            from orderData od2
                           where od2.userId = ud.userId
                             and od2.orderType = '2')
 where ...[some limiting factors on the selection of users]...;

回复收藏 0 原文

~没有更多了~