select account.name,goods.goods_name from account,cart,goods where cart.goods_no = goods.good_no and cart.account_id = account.id and goods_name = '薯片[火腿肠][零食]';
5.求每个人的购物车总价
select account.name,sum(cart.num * goods.price)from account,cart,goods,category where cart.goods_no = goods.good_no and goods.category_no=category.noand cart.account_id = account.id groupby account.name;
6.求哪天的营业额最高
selectsum(cart.num*goods.price),cart.create_time from goods,cart where cart.goods_no=goods.good_no groupby cart.create_time orderbysum(cart.num*goods.price)desclimit1;
7.张三最爱在什么时候购物
select cart.create_time
from account,cart
where
account.id=cart.account_id
and
account.create_time=cart.create_time
and
account.name='张三';
8.求哪个商品的利润率最高
select goods.goods_name,(goods.price-goods.cost)/goods.cost from goods,cart where cart.goods_no=goods.good_no orderby(goods.price-goods.cost)/goods.cost desclimit1;
9.求2023年3 月 12 日前一周销售的商品
select goods.goods_name from goods,cart where cart.goods_no = goods.good_no and cart.create_time between'2023-03-05'and'2023-03-12';
10.求每个用户的余额
select account.name,account.money-sum(cart.num*goods.price)from account,cart,goods where cart.account_id=account.id and goods.good_no=cart.goods_no groupby account.name,account.money;
11.求每个商品的库存还余多少
select
goods.goods_name,goods.count-sum(cart.num)from
goods,cart
where
cart.goods_no = goods.good_no
groupby goods.goods_name,goods.count;