SELECT goods.goods_name,account.name FROM goods,cart,account
WHERE goods.good_no = cart.goods_no AND account.id = cart.account_id
AND goods.goods_name ='火腿肠';
2、零食被谁买了
SELECT account.name,category.name FROM goods,cart,account,category
WHERE goods.good_no = cart.goods_no AND account.id = cart.account_id
AND category.no = goods.category_no
AND category.name = '零食';
3、张三花了多少钱
SELECT account.name,SUM(goods.price*cart.num)AS'总额'FROM goods,cart,account
WHERE goods.good_no = cart.goods_no AND account.id = cart.account_id
AND account.name ='张三'GROUPBY account.name;
4、每个人购物车总价
SELECT account.name,SUM(goods.price * cart.num)AS 总数
FROM cart,category,goods,account
WHERE cart.goods_no = goods.good_no
AND cart.account_id = account.id ADN category.no= goods.category_no
GROUPBY account.name;
5、一周内哪一天的商品总价最高
SELECT dayofweek(cart.create_time)AS week_day,MAX(goods.price*cart.num)AS 总价
FROM goods,cart,account
WHERE cart.account_id = account.id
AND cart.goods_no = goods.good_no
GROUPBY cart.create_time
ORDERBY 总价 DESC;
6、张三的购物时间在什么时候
SELECT cart.create_time AS 购物时间
FROM account, cart
WHERE account.id = cart.account_id
AND account.name ='张三';
7、购物车内的总销售额
SELECTSUM((goods.price-goods.cost)*cart.num)AS 盈利
FROM account, cart ,goods WHERE account.id = cart.account_id
AND cart.goods_no = goods.good_no ;
8、那个商品利润率最高
SELECTDISTINCT goods.goods_name,(goods.price-goods.cost)/goods.cost AS 利润率 FROM cart ,goods
WHERE cart.goods_no = goods.good_no
ORDERBY 利润率 DESC;
9、求2023年3月12日前一周销售的商品
SELECT goods.goods_name FROM cart,goods
WHERE cart.goods_no = goods.good_no
AND
cart.create_time
BETWEEN DATE_SUB('2023-03-12',INTERVAL1 WEEK)AND'2023-03-12';
10、求用户购买东西后还有多少余额
SELECT account.name,sum(goods.price * cart.num)AS 总花费,
account.money-sum(goods.price * cart.num)AS 余额
FROM account,goods,cart
WHERE account.id=cart.account_id and cart.goods_no=goods.good_no
GROUPBY account.money,account.name;
11、减去用户购买后商品剩余库存
SELECT goods.goods_name,goods.count-sum(cart.num)AS 剩余库存 FROM goods,cart
WHERE goods.good_no = cart.goods_no
GROUPBY goods.goods_name,goods.count;