在mysql中生成序列号

46scxncf 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(343)

我有一张table：xyz

id    |survey_id | submitted  | username
---------------------------------------
  155 | 8        | 1537276842 | 2
  156 | 8        | 1537276842 | 2
  157 | 8        | 1537276877 | 2

我在数据库中运行以下查询：

SELECT DISTINCT case when result.aid < result.bid then result.aid else result.bid end as original, case when result.aid < result.bid then result.bid else result.aid end as duplicate FROM (SELECT a.id AS aid, b.id AS bid, a.submitted AS asubmitted, b.submitted AS bsubmitted FROM xyz AS a INNER JOIN xyz AS b ON (a.username = b.username AND a.survey_id = b.survey_id) WHERE a.id != b.id AND (abs(a.submitted - b.submitted) <= 300) ORDER BY a.id) AS result

获得：

original     |duplicate
--------------------
  155        | 157
  155        | 156
  156        | 157

之后，我想在第一列中添加序列号，然后执行以下查询：

SELECT DISTINCT 
    @row_number:=@row_number+1 AS row_number, 
    case when result.aid < result.bid then result.aid 
      else result.bid end as original, 
    case when result.aid < result.bid then result.bid 
      else result.aid end as duplicate 
FROM (SELECT a.id AS aid, b.id AS bid, a.submitted AS asubmitted, b.submitted AS bsubmitted 
      FROM xyz AS a 
      INNER JOIN xyz AS b ON (a.username = b.username AND a.survey_id = b.survey_id), 
      (SELECT @row_number:=0) AS result 
WHERE a.id != b.id AND (abs(a.submitted - b.submitted) <= 300) 
ORDER BY a.id) AS result

获得：

row_number   |original     |duplicate
---------------------------------------
  1          | 155         | 157
  2          | 155         | 156
  3          | 156         | 157
  4          | 155         | 156
  5          | 155         | 157
  6          | 156         | 157

所以我有一个问题，为什么在我的查询中添加序列号后它会变成两倍。我需要如下格式的结果

row_number   |original     |duplicate
---------------------------------------
  1          | 155         | 157
  2          | 155         | 156
  3          | 156         | 157

sql mysql

来源：https://stackoverflow.com/questions/52404085/to-generate-serial-number-in-mysql

2条答案

按热度按时间

brc7rcf01#

或者，您可以简单地更改联接以获得所需的结果：

CREATE TABLE AAU.xyz
(
id INTEGER,
survey_id INTEGER,
submitted INTEGER,
username INTEGER
);

INSERT INTO AAU.xyz VALUES (155,8,1537276842,2),
(156,8,1537276842,2),
(157,8,1537276877,2);

SELECT ROW_NUMBER() OVER (PARTITION BY a.username ORDER BY a.id) AS "row_number", a.id, b.id
FROM AAU.xyz a
LEFT JOIN AAU.xyz b ON (a.username = b.username AND a.survey_id = b.survey_id) 
WHERE a.id < b.id

赞(0）回复(0）举报 2021-06-20

b4qexyjb2#

你只需要给行编号。为此，将得到的最新正确结果 Package 到另一个子查询中，使其类似于：

SELECT * FROM (SELECT DISTINCT case when result.aid < result.bid then result.aid else result.bid end as original, case when result.aid < result.bid then result.bid else result.aid end as duplicate 
    FROM 
    (
        SELECT a.id AS aid, b.id AS bid, a.submitted AS asubmitted, b.submitted AS bsubmitted 
        FROM xyz AS a 
        INNER JOIN xyz AS b ON (a.username = b.username AND a.survey_id = b.survey_id) 
        WHERE a.id != b.id AND (abs(a.submitted - b.submitted) <= 300) 
        ORDER BY a.id
    ) AS result) AS resultresult;

然后添加序列：

SELECT @row := @row + 1 as row, resultresult.* FROM (SELECT DISTINCT case when result.aid < result.bid then result.aid else result.bid end as original, case when result.aid < result.bid then result.bid else result.aid end as duplicate 
    FROM 
    (
        SELECT a.id AS aid, b.id AS bid, a.submitted AS asubmitted, b.submitted AS bsubmitted 
        FROM xyz AS a 
        INNER JOIN xyz AS b ON (a.username = b.username AND a.survey_id = b.survey_id) 
        WHERE a.id != b.id AND (abs(a.submitted - b.submitted) <= 300) 
        ORDER BY a.id
    ) AS result) AS resultresult , (SELECT @row := 0) r;

它产生正确的

1   155 156
2   155 157
3   156 157

结果对我来说。

赞(0）回复(0）举报 2021-06-20

我来回答

在mysql中生成序列号

2条答案

相关问题

热门标签

最新问答