ORDER_HEAD
HORD_ID CURRENCY ORD_DATE
1 RMB 20200110
2 RMB 20200111
3 HKD 20200111
4 HKD 20200201
5 RMB 20200210
6 RMB 20200301
ORDER_DETAIL
DORD_ID SALESMAN AMT
1 A 100
2 B 50
3 B 200
4 A 150
5 C 500
6 B 100
SALESMAN
SALESMAN_ID NAME AGE
A AAA 25
B BBB 24
C CCC 30
求所有SALESMAN 最后一張單及其資料 例：
SALESMAN_ID NAME AGE CURRENCY AMOUNT
A AAA 25 HKD 150
B BBB 24 RMB 100
C CCC 30 RMB 500
uj5u.com熱心網友回復：
你這種需求沒法做。結果的條件根本沒寫清。為什么每個人只出一個幣種的金額？
uj5u.com熱心網友回復：
create table ORDER_HEAD(hord_id int,CURRENCY varchar(10),ORD_DATE date);
create table ORDER_DETAIL(DORD_ID int,SALESMAN varchar(2),AMT int);
create table SALESMAN(SALESMAN_ID varchar(2) , NAME varchar(10) ,AGE int )
insert into ORDER_HEAD
values(1,'RMB','20200110'),
(2,'RMB','20200111'),
(3,'HKD','20200111'),
(4,'HKD','20200201'),
(5,'RMB','20200210'),
(6,'RMB','20200301')
insert into ORDER_DETAIL
values(1,'A',1000),
(2,'B',50),
(3,'B',200),
(4,'A',150),
(5,'C',500),
(6,'B',100)
insert into SALESMAN
values('A','AAA',25),
('B','BBB',24),
('C','CCC',30)
select SALESMAN_ID,NAME,AGE,CURRENCY ,AMOUNT from
(select s.SALESMAN_ID,s.NAME,s.AGE,CURRENCY,AMT as AMOUNT ,
ROW_NUMBER() OVER(PARTITION BY S.SALESMAN_ID ORDER BY ORD_DATE desc) as N
from SALESMAN as s
inner join ORDER_DETAIL as od on s.SALESMAN_ID=od.SALESMAN
inner join ORDER_HEAD as o on o.hord_id=od.DORD_ID) a where a.N=1
uj5u.com熱心網友回復：
SELECT SM1.SALESMAN_ID,SM1.NAME,SM1.AGE,OH1.CURRENCY,OD1.AMT AS AMOUNT
FROM SALESMAN SM1
INNER JOIN ORDER_DETAIL OD1
ON SM1.SALESMAN_ID=OD1.SALESMAN
INNER JOIN ORDER_HEAD OH1
ON OD1.DORD_ID=OH1.HORD_ID
WHERE EXISTS(
SELECT SM.SALESMAN_ID,MAX(OH.ORD_DATE)
FROM SALESMAN SM
INNER JOIN ORDER_DETAIL OD
ON SM.SALESMAN_ID=OD.SALESMAN
INNER JOIN ORDER_HEAD OH
ON OD.DORD_ID=OH.HORD_ID
GROUP BY SM.SALESMAN_ID
HAVING SM.SALESMAN_ID=SM1.SALESMAN_ID AND MAX(OH.ORD_DATE)=OH1.ORD_DATE
)
uj5u.com熱心網友回復：
第一步先求出每個訂單詳情的幣種SELECT A.*,B.CURRENCY
FROM ORDER_DETAIL A, ORDER_HEADER B
WHERE A.ORDER_ID=B.ORDER_ID
第二步 根據ORDER_DETAIL.SALESMAN和CURRENCY，分組求ORDER_DETAIL.AMT 小計 也就是AMOUNT
SELECT C.SALESMAN,C.CURRENCY SUM(C.AMT) AS AMOUNT
FROM (
SELECT A.*,B.CURRENCY
FROM ORDER_DETAIL A, ORDER_HEADER B
WHERE A.ORDER_ID=B.ORDER_ID
) C
GROUP BY C.SALESMAN,C.CURRENCY
第三步 就是拿 SALESMAN 和上面結果集進行關聯了
SELECT E.*,D.
FROM SALESMAN E LEFT JOIN (
SELECT C.SALESMAN,C.CURRENCY SUM(C.AMT) AS AMOUNT
FROM (
SELECT A.*,B.CURRENCY
FROM ORDER_DETAIL A, ORDER_HEADER B
WHERE A.ORDER_ID=B.ORDER_ID
) C
GROUP BY C.SALESMAN,C.CURRENCY
) D
ON E.SALESMAN_ID=D.SALESMAN
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16126.html
標籤：應用實例
下一篇：大神求解SQL陳述句