Consult a SQL statement-CodePudding

Table 2 num accumulate until enough to table a total number, and then with the accumulation of this num * price, get the value in table 1, refer to the SQL should be how to write

CodePudding user response:

Table 1, a row?

CodePudding user response:

reference 1st floor wmxcn2000 response:

table, a row?

Table a table 2 are multiple lines, is just one example, has used the code to read, take data calculated separately, just think you could use SQL directly fix

CodePudding user response:

 
With ba as (select the 'A' name, total 10000, 0 money from dual), 
Bb2 as (select the 'A' name, 1 xh, 1000 nm, 13 price from dual union all 
Select the 'A' name, 2 xh, 2000 nm, 12 price from dual union all 
Select the 'A' name, 3 xh, 1000 nm, 12.1 price from dual union all 
Select the 'A' name, 4 xh, 2000 nm, 12.2 price from dual union all 
Select the 'A' name, 5 xh, 6000 nm, 12.3 price from dual union all 
Select the 'A' name, 6 xh, 7000 nm, 12.4 price from dual), 
Bb as (select bb2. * (select total from ba where name=bb2. Name) total from bb2), 
Aa as (select * from bb 
The model 
Dimension by (name, xh) 
Measures (nm, price, total, 0 sm, 0 yl) 
Rules 
(sm [name, xh]=NVL (sm/CV (), CV () - 1), 0) + nm/CV (), CV (), 
Yl [name, xh]=case when sm/CV (), CV () & gt; Total [CV (), CV ()] + nm/CV (), CV () then null when sm/CV (), CV () & lt; Total/CV (), CV () then the nm/CV (), CV () else nm/CV (), CV () - (sm/CV (), CV () - total [CV (), CV ()]) end)) 
Select co name, ba. Total, (select sum (aa) yl * aa) price) from aa where aa. The name=co name) money from ba;

CodePudding user response:

 
Update A set A arjun oney=(
The select c. oney from (
The select a.n ame, case 
When the sum (b.n um * p. rice) & gt; Min (a.t otal) then the sum (b.n um * p. rice) else min (arjun oney) end as money from A, B, where a.n ame=b.n ame 
Group by a.n ame) c 
Where a.n ame=c.n ame)

This kind of writing, request A table name is not repeated

CodePudding user response:

Borrow upstairs data analysis function of writing

 with ba as (select the 'A' name, total 10000, 0 money from dual), 
Bb2 as (select the 'A' name, 1 xh, 1000 nm, 13 price from dual union all 
Select the 'A' name, 2 xh, 2000 nm, 12 price from dual union all 
Select the 'A' name, 3 xh, 1000 nm, 12.1 price from dual union all 
Select the 'A' name, 4 xh, 2000 nm, 12.2 price from dual union all 
Select the 'A' name, 5 xh, 6000 nm, 12.3 price from dual union all 
Select the 'A' name, 6 xh, 7000 nm, 12.4 price from dual) 
SELECT the NAME, the TOTAL SUM ((NM - GREATEST (NM_TOL - TOTAL, 0)) * PRICE) AS MONEY 
The FROM (SELECT T1. The NAME, XH, 
The SUM (NM) OVER (PARTITION BY T.N AME ORDER BY XH) AS NM_TOL, T1. TOTAL, 
NM, PRICE 
The FROM bb2 T, BA T1 
WHERE T.N AME=T1. NAME) 
WHERE NM_TOL - TOTAL & lt; NM 
GROUP BY NAME, TOTAL

CodePudding user response:

Select the name, the sum (the sum), sum (money) as money from (select name, sum (num) as the sum of num * price as money from testB group by name, price, num) a group by the name so I don't know operators do not meet your requirement

CodePudding user response:

 select name, sum (the sum), sum (money) as money from (select name, sum (num) as the sum of num * price as money from testB group by name, price, num) a group by the name