跳过具有重复列值的行？

Question

假设我使用Findall（）返回集合，但是许多行在一列中具有相同的值。

-----------------------------
| ID     name    placeId    |
-----------------------------
| 1      abc     123        |
| 2      def     123        | <- skip this (duplicate placeId)
| 3      ghi     456        |
| 4      jkl     789        |
| 5      mno     576        |
| 6      pqr     576        | <- skip this (duplicate placeId)
----------------------------

是否有一种续集的手段来跳过任何具有重复列值的行（第一个除外）？

查询：

const items = await models.Item.findAll({
where: {
    [Op.and]: [
        sequelize.where(distance, { [Op.lte]: radius }),
        sequelize.where(descriptionFilter, { [Op.gte]: 2 }),
        {
            name: {
                [Op.notLike]: `%Example%`
            }
        }, {
            userId: {
                [Op.not]: me.id
            }
        },
    ]
},
order: distance,
limit: 25,
})

Answer 1

这是一个差距和岛屿问题。使用Raw Postgres查询最容易处理此操作，因此我建议以下内容：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) rn1,
              ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ID) rn2
    FROM yourTable
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY placeId, rn1 - rn2
                                 ORDER BY ID) rn
    FROM cte
)

SELECT ID, name, placeId
FROM cte2
WHERE rn = 1
ORDER BY ID;

Answer 2

续集支持聚合功能min（），因此您可以在我们的代码上应用此功能。

const items = await models.Item.findAll({
    attributes: [
        [sequelize.fn('MIN', sequelize.col('ID')), 'ID'],
    ],
    where: {
    [Op.and]: [
        sequelize.where(distance, { [Op.lte]: radius }),
        sequelize.where(descriptionFilter, { [Op.gte]: 2 }),
        {
            name: {
                [Op.notLike]: `%Example%`
            }
        }, {
            userId: {
                [Op.not]: me.id
            }
        },
        group: ['placeId'],
    ]
},
order: distance,
limit: 25,
})

Answer 3

PostgreSQL可以做

select distinct on (placeid)
 * from example_table order by placeid,id 

Answer 4

也许不是最好的，但它解决了问题：

  SELECT T2.ID, T2.NAME, T2.PLACEID
  FROM
  (
    SELECT T1.*, RANK() OVER(PARTITION BY placeId ORDER BY ID) RNK
    FROM test_tbl T1
  ) T2
  WHERE T2.RNK = 1
  ORDER BY T2.ID