10

## 蠻可愛的SQL Window Functions 應用 160202

http://ithelp.ithome.com.tw/question/10180955?tag=hp.all

``````On Oracle
CREATE TABLE ithelp160202a(
firm CHAR(1) NOT NULL,
part CHAR(3) NOT NULL,
price INTEGER NOT NULL
);

INSERT ALL
INTO ithelp160202a VALUES('A', '123', 100)
INTO ithelp160202a VALUES('A', '456', 200)
INTO ithelp160202a VALUES('B', '789', 300)
SELECT 1
FROM DUAL;

COMMIT;

SELECT firm
, a.part
, a.Price
, CASE
WHEN a.RowN = 1 THEN b.TotPri
ELSE NULL
END TotPri
FROM (SELECT firm
, part
, SUM(price) OVER(PARTITION BY firm, part, price) Price
, ROW_NUMBER() OVER(PARTITION BY firm ORDER BY part) RowN
FROM ithelp160202a
GROUP BY firm, part, price) a
JOIN (SELECT firm
, SUM(price) TotPri
FROM ithelp160202a
GROUP BY firm
) b
USING (firm)
ORDER BY firm, part;

FIRM PART      PRICE     TOTPRI
---- ---- ---------- ----------
A    123         100        300
A    456         200 {NULL}
B    789         300        300
``````

### 10 則留言

0

iT邦高手 1 級 ‧ 2016-02-02 12:08:41

0

0

iT邦超人 1 級 ‧ 2016-02-02 13:01:07

^^

0

iT邦高手 1 級 ‧ 2016-02-02 13:20:07

0

iT邦高手 1 級 ‧ 2016-02-02 18:11:51

0
Albert
iT邦高手 1 級 ‧ 2016-02-03 09:05:11

``````&lt;pre class="c" name="code">

SELECT a1.M_Product_ID,
a1.QTYORDERED,
a1.C_OrderLine_ID,
CASE WHEN a1.C_OrderLine_ID=a1.min_C_OrderLine_ID THEN a1.sum_QtyOrdered
ELSE NULL
END AS sum_QtyOrdered
FROM (
SELECT a.M_Product_ID,
a.QTYORDERED,
a.C_OrderLine_ID,
(SELECT MIN(b.C_OrderLine_ID) FROM C_OrderLine b WHERE b.M_Product_ID=a.M_Product_ID) AS min_C_OrderLine_ID,
(SELECT SUM(b.QtyOrdered)     FROM C_OrderLine b WHERE b.M_Product_ID=a.M_Product_ID) AS sum_QtyOrdered
FROM C_OrderLine a
ORDER BY a.M_Product_ID,a.C_OrderLine_ID
) a1
``````
0
Albert
iT邦高手 1 級 ‧ 2016-02-03 09:10:02

0
Albert
iT邦高手 1 級 ‧ 2016-02-03 09:19:18

(但是小雨大例子的資料無法展現)
ROW_NUMBER() OVER(PARTITION BY firm ORDER BY part) RowN

0

iT邦大師 1 級 ‧ 2016-02-03 09:35:36

SQL Command很有趣的.albertachen 大大的也很有意思.

0
Albert
iT邦高手 1 級 ‧ 2016-02-03 09:40:14

hitomitanaka提到：

wiselou提到：

chingfeng提到：

hitomitanaka提到：