create table customers (
id int primary key,
first_name varchar2(30) not null,
addresses varchar2(30)
);
create table items (
item_id int primary key,
seller varchar2(30) not null,
description varchar2(30) not null,
weight int,
price numeric(100,2),
quantity int not null
);
create table bought (
id int,
item_id int,
bdate date,
primary key id, item_id, bdate),
foreign key (id) references customers,
foreign key (item_id) items
);
oracle 查詢 - 顯示每個客戶購買商品所花費的 id、first_name、地址和總價。如果客戶購買了具有空價格的商品，即使該特定客戶購買的其他商品沒有空價格，也將總價格更改為空。
我可以做第一部分
SELECT c.id, c.first_name, c.addresses, SUM(i.price) as total_price
FROM customers c
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id
但是，如果我檢測到空值，如何將 total_price 更改為空值？
uj5u.com熱心網友回復：
大多數聚合函式會忽略空值，因此您需要進行 NULL 檢查，然后聚合結果。如果 null 的數量大于 0，則顯示 null，否則顯示總價
SELECT c.id, c.first_name, c.addresses,
case when count(case when i.price is null then 1 end) > 0
then null else SUM(i.price) end as total_price
FROM customers c
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id
uj5u.com熱心網友回復：
加里邁爾斯是絕對正確的，但我會寫得有點不同：
SELECT c.id, c.first_name, c.addresses,
case
when count(i.price)<count(*)
then null
else SUM(i.price)
end as total_price
FROM customers c
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id
或具有特定于 oracle 的功能decode：
SELECT
c.id, c.first_name, c.addresses,
decode(count(i.price),count(*),SUM(i.price)) as total_price
FROM customers c
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432929.html
上一篇：Oracle存盤程序與casewheninwhere的原因
下一篇：使用LISTAGG()聚合多列