我有個問題。我的 MySQL 查詢不起作用。我該如何解決這個問題？我的查詢
select state, city, sum((sales.retail_price - products.wholesale_price) * sales.quantity) as profit
from products, sales
where sales.product_id = products.product_id
group by rollup (state, city)
order by state, city;
我的錯誤
11:39:12 select state, city, sum((sales.retail_price - products.wholesale_price) * sales.quantity) as profit from products, sales where sales.product_id = products.product_id group by rollup (state, city) order by state, city Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(state, city) order by state, city' at line 4 0.000 sec
我的架構
-- Create some tables and insert some rows.
create table products (product_id integer, wholesale_price real);
insert into products (product_id, wholesale_price) values
(1, 1.00),
(2, 2.00);
create table sales (product_id integer, retail_price real,
quantity integer, city varchar, state varchar);
insert into sales (product_id, retail_price, quantity, city, state) values
(1, 2.00, 1, 'SF', 'CA'),
(1, 2.00, 2, 'SJ', 'CA'),
(2, 5.00, 4, 'SF', 'CA'),
(2, 5.00, 8, 'SJ', 'CA'),
(2, 5.00, 16, 'Miami', 'FL'),
(2, 5.00, 32, 'Orlando', 'FL'),
(2, 5.00, 64, 'SJ', 'PR');
uj5u.com熱心網友回復：
試試這個：
select state, city, sum((sales.retail_price - products.wholesale_price) *
sales.quantity) as profit
from products, sales
where sales.product_id = products.product_id
group by state, city WITH ROLLUP
order by state, city;
你可以在這里看到ROLL UP 行為
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/407532.html
標籤：
上一篇：缺少磁區時使用Lag()