仅在不同值时选择 [英] select only when different value

问题描述

我有这个专栏:名称和价格.我真的不知道如何或为什么在 mysql 数据库中有几行与前一行完全相同.

I have this column: name and price. I don't really know how or why in mysql database there are few line that are double record exactly from the previous line.

如果记录是双排且前后有一条线，如何选择所有记录但只显示其中一条记录?

how to select all records but show only one of the records if the record is double with a line in front or behind it?

例如我有这样的记录:

<头>

id	姓名	价格
1	书	5
2	灯	7
3	灯	7
4	书	5
5	书	5

我想要的结果是:

<头>

id	姓名	价格
1	书	5
2	灯	7
4	书	5

推荐答案

如果要排除与前面的name匹配的行，有如下几种方法.

If you want to exclude rows that match the previous name, there are several ways like the following.

>

案例 1:如果你使用 MySQL8，你可以使用 LAG 函数.

SELECT t1.id,t1.name,t1.price FROM (
  SELECT t2.id,t2.name,t2.price,
    LAG(t2.name) OVER(ORDER BY t2.id) prev
  FROM mytable t2
) t1
WHERE t1.prev IS NULL OR t1.name<>t1.prev
ORDER BY 1

情况 2:如果ids是连续的，没有任何步骤，通过JOIN比较name和之前的id会得到预期的结果.

Case 2: If the ids are continuous without any steps, you will get the expected result by comparing name and the previous id by JOIN.

SELECT t1.id,t1.name,t1.price FROM mytable t1
  LEFT JOIN mytable t2
  ON t1.name=t2.name AND
     t1.id=t2.id-1
WHERE t1.id=1 OR t2.id IS NOT NULL
ORDER BY 1

案例 3:如果id不连续，有办法得到不超过其他id的最大id.

Case 3: If the ids are not continuous, there is a way to get the maximum id that does not exceed the other id.

SELECT t1.id,t1.name,t1.price FROM mytable t1
  LEFT JOIN mytable t2
  ON t1.name=t2.name AND
     t1.id=(SELECT MAX(t3.id) FROM mytable t3 WHERE t3.id<t2.id)
WHERE t1.id=1 OR t2.id IS NOT NULL
ORDER BY 1

数据库小提琴

这篇关于仅在不同值时选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！