select distinct a.code,b.name
from product_summary a,antibody b
where a.code=b.code and a.price<=74 and a.status=1
order by a.price这是只按价格时排序的sql, 现在要求加第二个排序条件,同样的价格时按销售量来排
销售量是这样得到的,在另一个表里,
select sum(qty) from po.order_item where code = '一个具体的code值'怎样用一个Sql实现呢?我sql不是很在行,对高手来说应该不是难题
谢谢!
from product_summary a,antibody b
where a.code=b.code and a.price<=74 and a.status=1
order by a.price这是只按价格时排序的sql, 现在要求加第二个排序条件,同样的价格时按销售量来排
销售量是这样得到的,在另一个表里,
select sum(qty) from po.order_item where code = '一个具体的code值'怎样用一个Sql实现呢?我sql不是很在行,对高手来说应该不是难题
谢谢!
正确的结果应该是三列,code, price, qty
code是主键,先按price排,同样的价格下按qty 排
try:
select a.code,b.name,sum(qty)
from product_summary a,antibody b,po.order_item c
where a.code=b.code and a.price <=74 and a.status=1 and a.code=c.code
group by a.code,b.name
order by a.price,3
select a.code,b.name
from product_summary a,antibody b,po.order_item c
where a.code=b.code and a.price <=74 and a.status=1 and a.code=c.code
group by a.code,b.name
order by a.price,sum(qty)
其它的两个字段的结果到是挺对,
还有能不能解释下为啥要group by a.code,b.name 呢?
select distinct a.code,b.name, a.price, sum(c.qty) as qty
from product.product_summary a,product.antibody b, po.order_item as c
where a.code=b.code and a.price<=74 and a.status=1 or a.code = c.code
group by a.code
order by a.price asc , qty desc
只是还有一个问题,a表中的code在c表中不一定有,按照上面的做法,会只选出c表中有的code
也就是本来想选出来的结果少了很多,怎样才能以a表中的code为准,如果在c表中找不到相关的销售记录,
也包含到结果中来,只是qty为0呢?
问题似乎出在a.code = b.code上,但我又不知道怎么解决
from product.product_summary a
LEFT JOIN product.antibody b ON a.code=b.code and a.price <=74 and a.status=1
LEFT JOIN po.order_item as c ON a.code = c.code
group by a.code
order by a.price asc , qty desc OR
select distinct a.code,b.name, a.price, sum(c.qty) as qty
from product.product_summary a
LEFT JOIN product.antibody b ON a.code=b.code
LEFT JOIN po.order_item as c ON a.code = c.code
WHERE a.price <=74 and a.status=1
group by a.code
order by a.price asc , qty desc
不过第一个Sql总是跑不出东西来,慢地要死,第二个不错
只是第二个出来很多没有name的记录,没什么意义,我加了一个条件 a.code = b.codeselect distinct a.code,b.name, a.price, sum(c.qty) as qty
from product.product_summary a
LEFT JOIN product.antibody b ON a.code=b.code
LEFT JOIN po.order_item as c ON a.code = c.code
WHERE a.price <=74 and a.status=1 and a.code = b.code
group by a.code
order by a.price asc , qty desc
from product.product_summary a
LEFT JOIN product.antibody b ON a.code=b.code
LEFT JOIN po.order_item as c ON a.code = c.code
WHERE a.price <=74 and a.status=1 and a.code = b.code
group by a.code
order by a.price asc , qty desc自己解决更好,按道理说a.code = b.code已经定义了。