SQL:如何限制查询一条记录?

我一直坚持将记录限制为每位用户一条记录,但无法找到任何解决方案,我不是SQL查询方面的专家,因此需要一些帮助。 “ FETCH FIRST ROW”不起作用。

需求: 从交易表中获取使用同一张卡进行支付的所有用户。

我写了查询,这给了我想要的结果。查询如下:

SELECT distinct 
D1.ID, D1.CUSTOMER_ID, D1.TOKEN_VALUE FROM ORDER_DETAILS D1, ORDER_DETAILS D2
WHERE D1.TOKEN_VALUE = D2.TOKEN_VALUE AND D1.CUSTOMER_ID <> D2.CUSTOMER_ID 
ORDER BY  D1.TOKEN_VALUE desc;

Output for the query is as:
Query Result

查询的问题是我要为同一用户获取多个记录,但是我想将数据限制为每个用户一行。 查询中需要进行哪些修改才能将数据限制为每个用户一个记录,而不是获取所有事务。

评论
  • 小呆呱er
    小呆呱er 回复

    您可以使用窗口功能:

    SELECT DISTINCT OD.CUSTOMER_ID, OD.TOKEN_VALUE
    FROM (SELECT OD.*,
                 MIN(CUSTOMER_ID) OVER (PARTITION BY TOKEN_VALUE) as MIN_CUSTOMER_ID,
                 MAX(CUSTOMER_ID) OVER (PARTITION BY TOKEN_VALUE) as MAX_CUSTOMER_ID
          FROM ORDER_DETAILS OD
         ) OD
    WHERE MIN_CUSTOMER_ID <> MAX_CUSTOMER_ID
    ORDER BY OD.TOKEN_VALUE DESC;
    

    No JOIN is needed.

    An alternative method uses EXISTS:

    SELECT DISTINCT OD.CUSTOMER_ID, OD.TOKEN_VALUE
    FROM ORDER_DETAILS OD
    WHERE EXISTS (SELECT 1
                  FROM ORDER_DETAILS OD2
                  WHERE OD2.TOKEN_VALUE = OD.TOKEN_VALUE AND
                        OD2.CUSTOMER_ID = OD.CUSTOMER_ID
                 )
    ORDER BY OD.TOKEN_VALUE;