我有一张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
2条答案
按热度按时间brc7rcf01#
或者,您可以简单地更改联接以获得所需的结果:
b4qexyjb2#
你只需要给行编号。为此,将得到的最新正确结果 Package 到另一个子查询中,使其类似于:
然后添加序列:
它产生正确的
结果对我来说。