求解SQL解答
产品表goods(gid name price )
顾客表buyer(bid name)
销售表sales (id gid bid num)
1.查询各个商品各个卖出去了多少钱
2.查询没有买商品A的顾客!
3.查询前三个花钱买商品最多的顾客!
-
SQL code
1. select s.gid,sum(s.num*g.price) as mm from sales s,goods g where s.gid=g.gid group by s.gid 2. select b.* from buyer b,sales ss where b.bid=ss.bid and b.bid not in( select bid from sales s where exists (select 1 from goods g where s.gid=g.gid and g.name='A') ) 3. select s.bid,b.name,sum(s.num*g.price) as mm from sales s,goods g,buyer b where s.gid=g.gid and b.bid=s.bid group by s.bid,b.name order by 3 limit 3
贴错了。。。重新贴一次
-
SQL code
1. --如果销售记录在sales为空则total = 0 select a.gid,a.name,sum(ifnull(a.price*b.num,0)) as total from goods a left join sales b on a.gid = b.gid group by a.gid
探讨
引用:
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create tabl……
1:
-
SQL code
SELECT a.gid,(a.price*b.total) AS total FROM goods a LEFT JOIN (SELECT gid,SUM(num) AS total FROM sales GROUP BY gid) b ON a.gid=b.gid