总价值总和PL/Sql
我想获取一个PLSQL语句来显示客户ID的所有列表和每个客户的订单价值总和。下面的代码让我得到了正确的答案,但问题是我拥有多个具有相同ID的客户,并且我只需要在1个ID输出下汇总所有订单值,而不是multiple.my代码为同一个客户提供多个输出。总价值总和PL/Sql
Create table sales (customer_ID number(10), product_ID number(10), quantity number(10));
INSERT INTO sales (customer_ID, product_ID, quantity) Values(3,1,23);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(1,2,34);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(1,3,654);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(3,7,32);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(4,3,23);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(3,3,111);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(5,4,6);
Create table products (product_ID number(10), price number(10));
INSERT INTO products (product_ID, price) Values(1,32);
INSERT INTO products (product_ID, price) Values(2,121);
INSERT INTO products (product_ID, price) Values(3,3000);
INSERT INTO products (product_ID, price) Values(4,621);
INSERT INTO products (product_ID, price) Values(5,363);
INSERT INTO products (product_ID, price) Values(6,32);
INSERT INTO products (product_ID, price) Values(7,3);
INSERT INTO products (product_ID, price) Values(8,432);
INSERT INTO products (product_ID, price) Values(9,11);
INSERT INTO products (product_ID, price) Values(10,73);
declare
cursor cur is select unique sales.quantity,products.price,sales.customer_ID
from sales,products
where sales.product_id=products.product_id
order by customer_ID desc;
prod number;
quan number(10);
pri number(10);
c_id number(10);
begin
open cur;
loop
fetch cur into quan,pri,c_id;
exit when cur%notfound;
prod:=pri*quan;
DBMS_OUTPUT.PUT_LINE('customer_id =' || c_id);
DBMS_OUTPUT.PUT_LINE('quantity value =' || quan);
DBMS_OUTPUT.PUT_LINE('price =' || pri);
DBMS_OUTPUT.PUT_LINE('The total value of customer purchases is = ' || prod);
end loop;
close cur;
END
please adivse。
你只需要使用组使用SUM作为聚合函数BY。
使用PL/SQL
SQL> SET serveroutput ON
SQL> DECLARE
2 CURSOR cur
3 IS
4 SELECT SUM(sales.quantity),
5 SUM(products.price),
6 sales.customer_ID
7 FROM sales,
8 products
9 WHERE sales.product_id=products.product_id
10 GROUP BY customer_ID
11 ORDER BY customer_ID DESC;
12 prod NUMBER;
13 quan NUMBER(10);
14 pri NUMBER(10);
15 c_id NUMBER(10);
16 BEGIN
17 OPEN cur;
18 LOOP
19 FETCH cur INTO quan,pri,c_id;
20 EXIT
21 WHEN cur%notfound;
22 prod:=pri*quan;
23 DBMS_OUTPUT.PUT_LINE('customer_id =' || c_id);
24 DBMS_OUTPUT.PUT_LINE('quantity value =' || quan);
25 DBMS_OUTPUT.PUT_LINE('price =' || pri);
26 DBMS_OUTPUT.PUT_LINE('The total value of customer purchases is = ' || prod);
27 END LOOP;
28 CLOSE cur;
29 END;
30/
customer_id =5
quantity value =6
price =621
The total value of customer purchases is = 3726
customer_id =4
quantity value =23
price =3000
The total value of customer purchases is = 69000
customer_id =3
quantity value =166
price =3035
The total value of customer purchases is = 503810
customer_id =1
quantity value =688
price =3121
The total value of customer purchases is = 2147248
PL/SQL procedure successfully completed.
SQL>
顺便说一句,整个PL/SQL块可以用纯SQL。使用SQL
SQL> SELECT SUM(sales.quantity) AS "quantity",
2 SUM(products.price) AS "price",
3 sales.customer_ID
4 FROM sales,
5 products
6 WHERE sales.product_id=products.product_id
7 GROUP BY customer_ID
8 ORDER BY customer_ID DESC;
quantity price CUSTOMER_ID
---------- ---------- -----------
6 621 5
23 3000 4
166 3035 3
688 3121 1
SQL>
更新 OP希望过滤基于用户输入的行。
在的SQL * Plus,你可以声明一个变量,在过滤谓词使用它:
SQL> variable cust_id NUMBER
SQL> EXEC :cust_id:= 4
PL/SQL procedure successfully completed.
没有,让我们使用上述变量在过滤谓词:
SQL> SET serveroutput ON
SQL> DECLARE
2 CURSOR cur
3 IS
4 SELECT SUM(sales.quantity),
5 SUM(products.price),
6 sales.customer_ID
7 FROM sales,
8 products
9 WHERE sales.product_id=products.product_id
10 AND sales.customer_ID = :cust_id
11 GROUP BY customer_ID
12 ORDER BY customer_ID DESC;
13 prod NUMBER;
14 quan NUMBER(10);
15 pri NUMBER(10);
16 c_id NUMBER(10);
17 BEGIN
18 OPEN cur;
19 LOOP
20 FETCH cur INTO quan,pri,c_id;
21 EXIT
22 WHEN cur%notfound;
23 prod:=pri*quan;
24 DBMS_OUTPUT.PUT_LINE('customer_id =' || c_id);
25 DBMS_OUTPUT.PUT_LINE('quantity value =' || quan);
26 DBMS_OUTPUT.PUT_LINE('price =' || pri);
27 DBMS_OUTPUT.PUT_LINE('The total value of customer purchases is = ' || prod);
28 END LOOP;
29 CLOSE cur;
30 END;
31/
customer_id =4
quantity value =23
price =3000
The total value of customer purchases is = 69000
PL/SQL procedure successfully completed.
SQL>
如果要从前端应用程序执行此操作,可以将整个逻辑放在PROCEDURE并接受customer_id
as IN参数。并在过滤器谓词中使用它。
非常感谢。最后一个问题,我怎样才能让最终用户输入客户ID,输出的是输入的ID? – Shak
然后您需要使用过程来拥有INPUT参数。然后在谓词中使用此输入参数作为**绑定变量**来过滤行。或者,在SQL * Plus中,可以使用**替代变量**作为SQL。 –
@Shak请将其标记为已回答,以帮助他人。 –
您可以使用sum()函数,即select sum(sales.quantity)而不是sales-quantity。这会给你的总数量为CUSTOMER_ID
试试这个
SELECT CUSTOMER_ID
,SUM(QUANTITY) as Total_Quantity
,SUM(QUANTITY*PRICE) as Total_Price
FROM
SALES s INNER JOIN
PRODUCTS p on p.PRODUCT_ID=s.PRODUCT_ID
GROUP BY CUSTOMER_ID // Group By each customer Id.
DECLARE
//声明变量的
a NUMBER;
b NUMBER;
Cal NUMBER;
PROCEDURE calcul (a IN NUMBER,b IN NUMBER,Cal OUT NUMBER)IS
//检查IF条件如果不等于b,那么它会增加一个和B BEGIN
IF a <> b THEN
Cal := a + b;
END IF;
END;
//传对a和b的值然后调用calcul过程将做加法。
BEGIN
a := 10;
b := 15;
calcul(a,b,Cal);
dbms_output.put_line('Sum is ' || Cal);
END;
请编辑包括一个小的评论,解释这段代码如何工作。 –
编辑,谢谢。 –
请提供create table语句,并确保将INSERT添加到table_name子句中并添加语句。而且,你真的需要使用'PL/SQL'吗?它可以用普通的'SQL'完成。 PL/SQL'LOOP','逐行'又名'slow-by-slow'。 –
更改完成,是的,我需要得到它的PL/SQL ,,代码准备就绪,我只需要在1输出下相同的客户ID的总和 – Shak
什么是你期待的输出,为什么你“有”在PL/SQL中执行它?请更新您的问题与预期的输出。 – Boneist