我有一個跟隨行的表：
ID price rowNo
1 100 1
1 100 2
1 200 3
1 100 4
1 300 5
1 100 6
1 100 7
2 500 9
2 500 10
2 500 11
2 500 12
2 500 13
2 500 14
3 400 15
我想獲取價格已更改的每個 ID 的行。輸出如下：
ID price rowNo
1 100 1
1 200 3
1 100 4
1 300 5
1 100 6
2 500 9
3 400 15
uj5u.com熱心網友回復：
您可以在 where 子句中使用相關子查詢來測驗先前的值或是否存在
drop table if exists t;
create table t
(ID int, price int, rowNo int);
insert into t values
(1 , 100 , 1),
(1 , 100 , 2),
(1 , 200 , 3),
(1 , 100 , 4),
(1 , 300 , 5),
(1 , 100 , 6),
(1 , 100 , 7),
(2 , 500 , 9),
(2 , 500 , 10),
(2 , 500 , 11),
(2 , 500 , 12),
(2 , 500 , 13),
(2 , 500 , 14),
(3 , 400 , 15);
select t.*
from t
where t.price <> (select t1.price from t t1 where t1.id = t.id and t1.rowno < t.rowno order by t1.rowno desc limit 1) or
(select t1.price from t t1 where t1.id = t.id and t1.rowno < t.rowno order by t1.rowno desc limit 1) is null;
------ ------- -------
| ID | price | rowNo |
------ ------- -------
| 1 | 100 | 1 |
| 1 | 200 | 3 |
| 1 | 100 | 4 |
| 1 | 300 | 5 |
| 1 | 100 | 6 |
| 2 | 500 | 9 |
| 3 | 400 | 15 |
------ ------- -------
7 rows in set (0.003 sec)
uj5u.com熱心網友回復：
**
用戶的所有功勞：@1000111 當列值更改時，Mysql 選擇行
**
對于MySQL不支持視窗函式的舊版本：
SELECT id,price,rowNo
FROM ( SELECT *,
IF(@prevprice = YT.price, @rn := @rn 1,
IF(@prevprice := YT.price, @rn := 1, @rn := 1)
) AS rn
FROM test_tbl YT
CROSS JOIN
(
SELECT @prevprice := -1, @rn := 1
) AS var
ORDER BY YT.id
) AS t
WHERE t.rn = 1
ORDER BY t.id
演示：https : //dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a0deed41b868781e7b7a84b69556769e
結果：
id price rowNo 1 100 1 1 200 3 1 100 4 1 300 5 1 100 6 2 500 9 3 400 15
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/383446.html
標籤：mysql
上一篇：MYSQL資料庫中按字串日期排序
下一篇：MYSQL查詢年齡計算