Another problem of SQL syntax.-CodePudding

ORDER_HEAD
HORD_ID CURRENCY ORD_DATE
1 RMB 20200110
2 RMB 20200111
3 HKD 20200111
HKD 20200201 4
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
BBB 24 B
CCC 30 C

For all the SALESMAN last a single case and its data:
SALESMAN_ID NAME AGE CURRENCY AMOUNT
A AAA 25 HKD 150
BBB 24 RMB 100 B
C CCC 30 RMB 500

CodePudding user response:

You can't do this demand,
Results condition didn't write clear, why everyone only out of a currency amount?

CodePudding user response:

 
The create table ORDER_HEAD (hord_id int, CURRENCY varchar (10), ORD_DATE date); 
The create table ORDER_DETAIL (DORD_ID int, SALESMAN varchar (2), AMT int); 
The create table SALESMAN (SALESMAN_ID varchar (2), the NAME varchar (10), the AGE int) 

Insert into ORDER_HEAD 
Values (1, 'RMB', '20200110'), 
(2, 'RMB', '20200111'), 
HKD (3, ' ', '20200111'), 
HKD (4, 'a', '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) 

The select SALESMAN_ID, NAME, AGE, CURRENCY, AMOUNT from 
(select s.S ALESMAN_ID, s.N AME, s.A GE, CURRENCY, AMT as AMOUNT, 
ROW_NUMBER () OVER (PARTITION BY S.S ALESMAN_ID ORDER BY ORD_DATE desc) as N 
The from SALESMAN as s 
Inner join ORDER_DETAIL as od on s.S ALESMAN_ID=od. The SALESMAN 
Inner join ORDER_HEAD as o on o.h ord_id=od. DORD_ID) where a a.N=1

CodePudding user response:

 
SELECT the SM1 SALESMAN_ID, SM1. NAME, SM1. The AGE, the OH1. CURRENCY, OD1. AMT AS AMOUNT 
The FROM SALESMAN SM1 
INNER JOIN ORDER_DETAIL OD1 
ON the SM1. SALESMAN_ID=OD1. SALESMAN 
INNER JOIN ORDER_HEAD OH1 
ON OD1. DORD_ID=OH1. HORD_ID 
WHERE the EXISTS (
The SELECT SM SALESMAN_ID, MAX (OH. ORD_DATE) 
The 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 
)

CodePudding user response:

The first step in the currency of each order details first
SELECT A. *, biggest URRENCY
The FROM ORDER_DETAIL A, ORDER_HEADER B
WHERE A.O RDER_ID=B.O RDER_ID

. The second step according to ORDER_DETAIL SALESMAN and CURRENCY, grouping for ORDER_DETAIL. AMT subtotal, that is, the AMOUNT
The SELECT c.s. ALESMAN, Arthur c. URRENCY SUM (c.a. MT) AS the AMOUNT
The FROM (
SELECT A. *, biggest URRENCY
The FROM ORDER_DETAIL A, ORDER_HEADER B
WHERE A.O RDER_ID=B.O RDER_ID

C)
GROUP BY c.s. ALESMAN, Arthur c. URRENCY

The third step is associated with SALESMAN and result set above the
The SELECT e. *, d.
The FROM SALESMAN E LEFT JOIN (
The SELECT c.s. ALESMAN, Arthur c. URRENCY SUM (c.a. MT) AS the AMOUNT
The FROM (
SELECT A. *, biggest URRENCY
The FROM ORDER_DETAIL A, ORDER_HEADER B
WHERE A.O RDER_ID=B.O RDER_ID

C)
GROUP BY c.s. ALESMAN, Arthur c. URRENCY
D)
ON E.S ALESMAN_ID=D.S ALESMAN