回答編集履歴
7
推敲
answer
CHANGED
|
@@ -6,7 +6,7 @@
|
|
|
6
6
|
--
|
|
7
7
|
```SQL
|
|
8
8
|
select t.*
|
|
9
|
-
, (BASE_NUMBER -1 + case when ReceiptId =
|
|
9
|
+
, (BASE_NUMBER -1 + case when ReceiptId = 1 then 0 else 1 end )as number
|
|
10
10
|
from (
|
|
11
11
|
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2 AS BASE_NUMBER
|
|
12
12
|
FROM TBL_MatchingReceipt
|
|
@@ -16,7 +16,7 @@
|
|
|
16
16
|
```SQL
|
|
17
17
|
SELECT *
|
|
18
18
|
, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2
|
|
19
|
-
+ case when ReceiptId =
|
|
19
|
+
+ case when ReceiptId = 1 then 0 else 1 end -1
|
|
20
20
|
AS BASE_NUMBER
|
|
21
21
|
FROM TBL_MatchingReceipt
|
|
22
22
|
```
|
6
訂正
answer
CHANGED
|
@@ -6,7 +6,7 @@
|
|
|
6
6
|
--
|
|
7
7
|
```SQL
|
|
8
8
|
select t.*
|
|
9
|
-
, (BASE_NUMBER + case when ReceiptId = 0 then 0 else 1 end )as number
|
|
9
|
+
, (BASE_NUMBER -1 + case when ReceiptId = 0 then 0 else 1 end )as number
|
|
10
10
|
from (
|
|
11
11
|
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2 AS BASE_NUMBER
|
|
12
12
|
FROM TBL_MatchingReceipt
|
|
@@ -16,7 +16,7 @@
|
|
|
16
16
|
```SQL
|
|
17
17
|
SELECT *
|
|
18
18
|
, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2
|
|
19
|
-
+ case when ReceiptId = 0 then 0 else 1 end
|
|
19
|
+
+ case when ReceiptId = 0 then 0 else 1 end -1
|
|
20
20
|
AS BASE_NUMBER
|
|
21
21
|
FROM TBL_MatchingReceipt
|
|
22
22
|
```
|
5
推敲
answer
CHANGED
|
@@ -6,9 +6,17 @@
|
|
|
6
6
|
--
|
|
7
7
|
```SQL
|
|
8
8
|
select t.*
|
|
9
|
-
|
|
9
|
+
, (BASE_NUMBER + case when ReceiptId = 0 then 0 else 1 end )as number
|
|
10
10
|
from (
|
|
11
11
|
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2 AS BASE_NUMBER
|
|
12
12
|
FROM TBL_MatchingReceipt
|
|
13
13
|
) t
|
|
14
|
+
```
|
|
15
|
+
ネストさせなくても良いかも
|
|
16
|
+
```SQL
|
|
17
|
+
SELECT *
|
|
18
|
+
, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2
|
|
19
|
+
+ case when ReceiptId = 0 then 0 else 1 end
|
|
20
|
+
AS BASE_NUMBER
|
|
21
|
+
FROM TBL_MatchingReceipt
|
|
14
22
|
```
|
4
訂正
answer
CHANGED
|
@@ -8,7 +8,7 @@
|
|
|
8
8
|
select t.*
|
|
9
9
|
case ReceiptId = 0 then BASE_NUMBER else BASE_NUMBER +1 end as number
|
|
10
10
|
from (
|
|
11
|
-
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
|
|
11
|
+
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2 AS BASE_NUMBER
|
|
12
12
|
FROM TBL_MatchingReceipt
|
|
13
13
|
) t
|
|
14
14
|
```
|
3
訂正
answer
CHANGED
|
@@ -10,10 +10,5 @@
|
|
|
10
10
|
from (
|
|
11
11
|
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
|
|
12
12
|
FROM TBL_MatchingReceipt
|
|
13
|
-
where ReceiptId = 0
|
|
14
|
-
union all
|
|
15
|
-
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
|
|
16
|
-
FROM TBL_MatchingReceipt
|
|
17
|
-
where ReceiptId = 1
|
|
18
13
|
) t
|
|
19
14
|
```
|
2
修正
answer
CHANGED
|
@@ -10,5 +10,10 @@
|
|
|
10
10
|
from (
|
|
11
11
|
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
|
|
12
12
|
FROM TBL_MatchingReceipt
|
|
13
|
+
where ReceiptId = 0
|
|
14
|
+
union all
|
|
15
|
+
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
|
|
16
|
+
FROM TBL_MatchingReceipt
|
|
17
|
+
where ReceiptId = 1
|
|
13
18
|
) t
|
|
14
19
|
```
|
1
追記
answer
CHANGED
|
@@ -1,3 +1,14 @@
|
|
|
1
1
|
求めた連番を以下の様に加工すれば、希望のものになりそうですが。
|
|
2
2
|
偶数連番：連番*2
|
|
3
|
-
奇数連番：連番*2-1
|
|
3
|
+
奇数連番：連番*2-1
|
|
4
|
+
|
|
5
|
+
追記
|
|
6
|
+
--
|
|
7
|
+
```SQL
|
|
8
|
+
select t.*
|
|
9
|
+
case ReceiptId = 0 then BASE_NUMBER else BASE_NUMBER +1 end as number
|
|
10
|
+
from (
|
|
11
|
+
SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
|
|
12
|
+
FROM TBL_MatchingReceipt
|
|
13
|
+
) t
|
|
14
|
+
```
|