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
select concat(substr(uage,1,1),0),count(*) from mysql_liu where uage >=10 and uage < 100 group by substr(uage,1,1) having count(*) > 3 limit 10select 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 --如果销售记录在sales为空则total = 0 select bid,name from buyer where bid not in(select distinct(c.bid) from goods a,sales c where a.gid = c.gid and a.name = 'A')select b.name,c.bid,sum(a.price*c.num) as most_pay from goods a,sales c,buyer b where a.gid = c.gid and c.bid = b.bid group by c.bid order by most_pay desc limit 3
贴错了重新贴一次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 2. select bid,name from buyer where bid not in (select distinct(c.bid) from goods a,sales c where a.gid = c.gid and a.name = 'A')3. select b.name,c.bid,sum(a.price*c.num) as most_pay from goods a,sales c,buyer b where a.gid = c.gid and c.bid = b.bid group by c.bid order by most_pay desc limit 3
产品表goods(gid name price ) create table goods ( gid int, name varchar(10), price double(10,2) ) insert into goods values(1,'苹果',5.2) insert into goods values(2,'香蕉',3.6) insert into goods values(3,'梨子',8.3) 顾客表buyer(bid name) create table buyer ( bid int, name varchar(20) ) insert into buyer values(1,'张三') insert into buyer values(2,'李四') insert into buyer values(3,'王五') insert into buyer values(4,'赵六')销售表sales (sid gid bid num) create table sales ( sid int, gid int, bid int, num int ) insert into sales values(1,1,1,3) insert into sales values(2,2,1,3) insert into sales values(3,3,1,3) insert into sales values(4,1,2,3) insert into sales values(5,2,2,3) insert into sales values(6,3,2,3) insert into sales values(7,3,3,3) insert into sales values(8,2,3,3)
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 2. select bid,name from buyer where bid not in (select distinct(c.bid) from goods a,sales c where a.gid = c.gid and a.name = '苹果')3. select b.name,c.bid,sum(a.price*c.num) as most_pay from goods a,sales c,buyer b where a.gid = c.gid and c.bid = b.bid group by c.bid order by most_pay desc limit 3 PS: 最好在你的第三张表加入外键gid和bid (个人意见)
1:
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
2:SELECT `name` FROM buyer WHERE bid NOT IN ( SELECT s.bid FROM goods g INNER JOIN sales s ON s.gid=g.gid WHERE g.name='苹果' ) 3:SELECT d.bid,bu.name FROM buyer bu INNER JOIN (SELECT bid,SUM(total) AS SUM FROM (SELECT b.bid,a.price*b.num AS total FROM goods a INNER JOIN s ales b ON b.gid=a.gid) c GROUP BY bid ORDER BY SUM DESC LIMIT 3) d ON bu.bid=d.bid
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
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
select concat(substr(uage,1,1),0),count(*) from mysql_liu where uage >=10 and uage < 100 group by substr(uage,1,1) having count(*) > 3 limit 10select 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 --如果销售记录在sales为空则total = 0
select bid,name from buyer where bid not in(select distinct(c.bid) from goods a,sales c where a.gid = c.gid and a.name = 'A')select b.name,c.bid,sum(a.price*c.num) as most_pay from
goods a,sales c,buyer b
where a.gid = c.gid and c.bid = b.bid
group by c.bid
order by most_pay desc
limit 3
--如果销售记录在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 2.
select bid,name from buyer
where bid not in
(select distinct(c.bid) from goods a,sales c
where a.gid = c.gid and a.name = 'A')3.
select b.name,c.bid,sum(a.price*c.num) as most_pay from
goods a,sales c,buyer b
where a.gid = c.gid and c.bid = b.bid
group by c.bid
order by most_pay desc
limit 3
产品表goods(gid name price )
create table goods
(
gid int,
name varchar(10),
price double(10,2)
)
insert into goods values(1,'苹果',5.2)
insert into goods values(2,'香蕉',3.6)
insert into goods values(3,'梨子',8.3)
顾客表buyer(bid name)
create table buyer
(
bid int,
name varchar(20)
)
insert into buyer values(1,'张三')
insert into buyer values(2,'李四')
insert into buyer values(3,'王五')
insert into buyer values(4,'赵六')销售表sales (sid gid bid num)
create table sales
(
sid int,
gid int,
bid int,
num int
)
insert into sales values(1,1,1,3)
insert into sales values(2,2,1,3)
insert into sales values(3,3,1,3)
insert into sales values(4,1,2,3)
insert into sales values(5,2,2,3)
insert into sales values(6,3,2,3)
insert into sales values(7,3,3,3)
insert into sales values(8,2,3,3)
1.求出苹果、香蕉、梨子三种产品的营业额是好多?
2.求出没有买商品是苹果的顾客的名字。
3.求出前三位花钱最多的顾客的名字和ID。
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
2.
select bid,name from buyer
where bid not in
(select distinct(c.bid) from goods a,sales c
where a.gid = c.gid and a.name = '苹果')3.
select b.name,c.bid,sum(a.price*c.num) as most_pay from
goods a,sales c,buyer b
where a.gid = c.gid and c.bid = b.bid
group by c.bid
order by most_pay desc
limit 3
PS: 最好在你的第三张表加入外键gid和bid (个人意见)
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
2:SELECT `name` FROM buyer WHERE bid NOT IN
(
SELECT s.bid FROM goods g INNER JOIN sales s ON s.gid=g.gid WHERE g.name='苹果'
)
3:SELECT d.bid,bu.name FROM buyer bu INNER JOIN (SELECT bid,SUM(total) AS SUM FROM (SELECT b.bid,a.price*b.num AS total FROM goods a INNER JOIN s
ales b ON b.gid=a.gid) c GROUP BY bid ORDER BY SUM DESC LIMIT 3) d ON bu.bid=d.bid