回答編集履歴
2
訂正
answer
CHANGED
|
@@ -1,21 +1,19 @@
|
|
|
1
1
|
selectがorder by より先に処理されるので、ネストさせ、order byしたものをselect する部分で変数をカウントアップすれば良いのではないでしょうか。
|
|
2
2
|
```SQL
|
|
3
|
-
select *, @n := @n + 1 as no
|
|
3
|
+
select m.*, @n := @n + 1 as no
|
|
4
4
|
from (
|
|
5
5
|
select distinct
|
|
6
6
|
A.code
|
|
7
7
|
, B.data1
|
|
8
8
|
, C.data2
|
|
9
9
|
, D.code
|
|
10
|
-
, @n
|
|
11
10
|
from tableA as A
|
|
12
11
|
inner join tableB as B on B.id = A.id
|
|
13
12
|
and B.code = 'before'
|
|
14
13
|
inner join tableB as C on C.id = A.id
|
|
15
14
|
and C.code = 'after'
|
|
16
15
|
inner join tableC as D on D.id = A.id
|
|
17
|
-
, (select @n:=0) n
|
|
18
16
|
where 1 = 1
|
|
19
17
|
order by A.code, A.order_no, D.id
|
|
20
|
-
)
|
|
18
|
+
) m, (select @n:=0) n
|
|
21
19
|
```
|
1
訂正
answer
CHANGED
|
@@ -7,6 +7,7 @@
|
|
|
7
7
|
, B.data1
|
|
8
8
|
, C.data2
|
|
9
9
|
, D.code
|
|
10
|
+
, @n
|
|
10
11
|
from tableA as A
|
|
11
12
|
inner join tableB as B on B.id = A.id
|
|
12
13
|
and B.code = 'before'
|