回答編集履歴

推敲

2019/08/26 07:55

投稿

スコア25195

test CHANGED Viewed

@@ -14,7 +14,7 @@
 select t.*
-   , (BASE_NUMBER -1 + case when ReceiptId = 0 then 0 else 1 end )as number
+   , (BASE_NUMBER -1 + case when ReceiptId = 1 then 0 else 1 end )as number
 from (
@@ -34,7 +34,7 @@
      , ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2
-     + case when ReceiptId = 0 then 0 else 1 end -1
+     + case when ReceiptId = 1 then 0 else 1 end -1
      AS BASE_NUMBER

訂正

2019/08/26 07:55

投稿

スコア25195

test CHANGED Viewed

@@ -14,7 +14,7 @@
 select t.*
-   , (BASE_NUMBER + case when ReceiptId = 0 then 0 else 1 end )as number
+   , (BASE_NUMBER -1 + case when ReceiptId = 0 then 0 else 1 end )as number
 from (
@@ -34,7 +34,7 @@
      , ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2
-     + case when ReceiptId = 0 then 0 else 1 end
+     + case when ReceiptId = 0 then 0 else 1 end -1
      AS BASE_NUMBER

推敲

2019/08/26 07:54

投稿

スコア25195

test CHANGED Viewed

@@ -14,7 +14,7 @@
 select t.*
-     case ReceiptId = 0 then BASE_NUMBER else  BASE_NUMBER +1 end as number
+   , (BASE_NUMBER + case when ReceiptId = 0 then 0 else 1 end )as number
 from (
@@ -25,3 +25,19 @@
 ) t
 ```
+ネストさせなくても良いかも
+```SQL
+SELECT *
+     , ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2
+     + case when ReceiptId = 0 then 0 else 1 end
+     AS BASE_NUMBER
+FROM TBL_MatchingReceipt
+```

訂正

2019/08/26 07:51

投稿

スコア25195

test CHANGED Viewed

@@ -18,7 +18,7 @@
 from (
-  SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
+  SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID,RceiptId) * 2 AS BASE_NUMBER
   FROM TBL_MatchingReceipt

訂正

2019/08/26 07:48

投稿

スコア25195

test CHANGED Viewed

@@ -22,16 +22,6 @@
   FROM TBL_MatchingReceipt
-  where ReceiptId = 0
-union all
-  SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
-  FROM TBL_MatchingReceipt
-  where ReceiptId = 1
 ) t
 ```

修正

2019/08/26 07:46

投稿

スコア25195

test CHANGED Viewed

@@ -22,6 +22,16 @@
   FROM TBL_MatchingReceipt
+  where ReceiptId = 0
+union all
+  SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
+  FROM TBL_MatchingReceipt
+  where ReceiptId = 1
 ) t
 ```

追記

2019/08/26 07:12

投稿

スコア25195

test CHANGED Viewed

@@ -3,3 +3,25 @@
 偶数連番：連番*2
 奇数連番：連番*2-1
+追記
+--
+```SQL
+select t.*
+     case ReceiptId = 0 then BASE_NUMBER else  BASE_NUMBER +1 end as number
+from (
+  SELECT *, ROW_NUMBER() ORVER(PARTITION BY ID,RceiptId ORDER BY ID) * 2 AS BASE_NUMBER
+  FROM TBL_MatchingReceipt
+) t
+```