在 MY SQL 中运行 select 语句时如何生成自动增量值列? [英] How to generate auto increment values column when select statement run in MY SQL?

问题描述

如何显示我何时运行以从我的表中查找重复值此时我想在具有自动增量值的 select 语句中添加新列

How to display when i was run to find duplicate values from my table at this time i want to add new column in select statement with auto increment values

SELECT * FROM tutorials_tbl
WHERE Lscno IN (
SELECT Lscno
FROM tutorials_tbl
GROUP BY Lscno
HAVING COUNT(Lscno) > 1
)ORDER BY Lscno

ID       Title          LSCNo
10       titl_14    000000/06/3/1944
19       titl_13    000000/06/3/19
561     titl_1      000000/10/125/1955
671     titl_1      000000/10/125/1955
1765    titl_1      000000/06/65/1976
1834    titl_1      000000/06/65/1976
1909    titl_1      000000/06/3/1971
1910    titl_1      000000/06/3/1971

 **Excepted output like** in MY SQL ?




 Add
NewCol    ID    Title   LSCNo
  1       10    titl_14 000000/06/3/1944
  2       19    titl_13 000000/06/3/19
  3       561   titl_1  000000/10/125/1955
  4       671   titl_1  000000/10/125/1955
  5       1765  titl_1  000000/06/65/1976
  6       1834  titl_1  000000/06/65/1976
  7       1909  titl_1  000000/06/3/1971
  8       1910  titl_1  000000/06/3/1971

推荐答案

创建一个变量.类似的东西:

Create a variable. Something like:

SELECT @myCount:=@myCount+1 AS NewCol, ID, Title, LSCNo
FROM tutorials_tbl, (SELECT @myCount:= 0) t
WHERE Lscno IN (
    SELECT Lscno
    FROM tutorials_tbl
    GROUP BY Lscno
    HAVING COUNT(Lscno) > 1
)
ORDER BY Lscno

这篇关于在 MY SQL 中运行 select 语句时如何生成自动增量值列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！