回答編集履歴
4
修正
answer
CHANGED
|
@@ -21,7 +21,7 @@
|
|
|
21
21
|
, t2.SUM_Qty as Qty, t2.SUM_Tax as Tax
|
|
22
22
|
, Date, Bikou1, Bikou2
|
|
23
23
|
FROM ZZZ t1 inner join (
|
|
24
|
-
select
|
|
24
|
+
select SUM(Qty) SUM_QTY, SUM(Tax) SUM_TAX, Min(ID) as Min_ID
|
|
25
25
|
FROM ZZZ
|
|
26
26
|
group by Line1, Line2
|
|
27
27
|
) t2
|
3
修正
answer
CHANGED
|
@@ -25,5 +25,5 @@
|
|
|
25
25
|
FROM ZZZ
|
|
26
26
|
group by Line1, Line2
|
|
27
27
|
) t2
|
|
28
|
-
on t1.
|
|
28
|
+
on t1.ID=T2.Min_ID
|
|
29
29
|
```
|
2
推敲
answer
CHANGED
|
@@ -2,7 +2,7 @@
|
|
|
2
2
|
|
|
3
3
|
ですが、Existsの使用方法も誤っています。
|
|
4
4
|
```SQL
|
|
5
|
-
SELECT
|
|
5
|
+
SELECT ID, t1.Line1, t1.Line2, Line3, Line4, Product
|
|
6
6
|
, t2.SUM_Qty as Qty, t2.SUM_Tax as Tax
|
|
7
7
|
, Date, Bikou1, Bikou2
|
|
8
8
|
FROM ZZZ t1 inner join (
|
|
@@ -17,7 +17,7 @@
|
|
|
17
17
|
```
|
|
18
18
|
他の回答をみて追記（上記より高速だと思います）
|
|
19
19
|
```SQL
|
|
20
|
-
SELECT
|
|
20
|
+
SELECT ID, t1.Line1, t1.Line2, Line3, Line4, Product
|
|
21
21
|
, t2.SUM_Qty as Qty, t2.SUM_Tax as Tax
|
|
22
22
|
, Date, Bikou1, Bikou2
|
|
23
23
|
FROM ZZZ t1 inner join (
|
1
追記
answer
CHANGED
|
@@ -14,4 +14,16 @@
|
|
|
14
14
|
WHERE NOT EXISTS (
|
|
15
15
|
SELECT 1 FROM ZZZ WHERE Line1 =t1.Line1 AND Line2 = t1.Line2 AND ID < t1.ID
|
|
16
16
|
)
|
|
17
|
+
```
|
|
18
|
+
他の回答をみて追記（上記より高速だと思います）
|
|
19
|
+
```SQL
|
|
20
|
+
SELECT t1.ID, t1.Line1, t1.Line2, Line3, Line4, Product
|
|
21
|
+
, t2.SUM_Qty as Qty, t2.SUM_Tax as Tax
|
|
22
|
+
, Date, Bikou1, Bikou2
|
|
23
|
+
FROM ZZZ t1 inner join (
|
|
24
|
+
select Line1, Line2, SUM(Qty) SUM_QTY, SUM(Tax) SUM_TAX, Min(ID) as Min_ID
|
|
25
|
+
FROM ZZZ
|
|
26
|
+
group by Line1, Line2
|
|
27
|
+
) t2
|
|
28
|
+
on t1.Line1=t2.Line1 and t1.Line2=t2.Line2 and t1.ID=T2.Min_ID
|
|
17
29
|
```
|