select a.meterial_id as 物料编码 ,a.meterial_name as 物料名称,a.meterial_spec as 物料规格,sum(b.bear_q) as 本期啤数,sum(b.bear_q*c.out_quantity) as 本期个数,sum(b.in_q )as 本期收货, sum(b.bear_q*c.out_quantity-b.in_q ) as 本期欠收 from meterial a inner join produce_bear b on a.meterial_id=b.meterial_id inner join mould_meterial c on b.meterial_id=c.meterial_id group by a.meterial_id,a.meterial_name,a.meterial_spec,a.meterial_id, b.bear_q,b.in_q,b.bear_q*c.out_quantity having a.meterial_id='02100317000107' 我想实现三个表中b.bear_q和b.b.in_q,b.bear_q*c.out_quantity的sum函数
如果单是
select a.meterial_id as 物料编码 ,a.meterial_name as 物料名称,a.meterial_spec as 物料规格,b.bear_q as 本期啤数目,b.bear_q *c.out_quantity as 本期个数,b.in_q as 本期收货,(b.bear_q*c.out_quantity-b.in_q ) as 本期欠收 from meterial a inner join produce_bear b on a.meterial_id=b.meterial_id inner join mould_meterial c on b.meterial_id=c.meterial_id where meterial_id='02100317000107'
这样就是单纯的查询到02100317000107的多条记录。
我的目的:想求sum(b.bear_q),sum(b.bear_in),sum(b.bear_q*c.out_quantity),sum(b.bear_q*c.out_quantity-b.in_q) 总和,就是求出一条总记录
如果单是
select a.meterial_id as 物料编码 ,a.meterial_name as 物料名称,a.meterial_spec as 物料规格,b.bear_q as 本期啤数目,b.bear_q *c.out_quantity as 本期个数,b.in_q as 本期收货,(b.bear_q*c.out_quantity-b.in_q ) as 本期欠收 from meterial a inner join produce_bear b on a.meterial_id=b.meterial_id inner join mould_meterial c on b.meterial_id=c.meterial_id where meterial_id='02100317000107'
这样就是单纯的查询到02100317000107的多条记录。
我的目的:想求sum(b.bear_q),sum(b.bear_in),sum(b.bear_q*c.out_quantity),sum(b.bear_q*c.out_quantity-b.in_q) 总和,就是求出一条总记录
解决方案 »
- 新手求解决问题SQL查询排序后
- -- SQL Server 2005 创建跨服务器“触发器” --
- varchar 相加 变空
- SSIS包如何调试呢,如果出现错误感觉很难查找错误原因?求各位指教
- select cdate(left(a1,8)) from 表名是否可以这样用
- 请教,存储过程执行的一个小错误
- 空间商那里租用的SQL数据库,日志文件只能由他们来清除。是不是很麻烦啊,有没有程序可以监测日志文件已满。
- SQL2008 同步复制 合并复制'uid' 中的标识范围检查约束冲突
- 请教下SQL2005的数据库我想还原到另外一台电脑上,为何还原出错啊!?
- Sql Server的字段类型中哪个是逻辑型?是bit吗?
- 附加数据库出错了 这是为什么?
- 不显示删除回复显示所有回复显示星级回复显示得分回复 求助连接数据库的问题???[
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
a.meterial_name AS 物料名称,
a.meterial_spec AS 物料规格, --根据业务考虑去除此字段
SUM(b.bear_q) AS 本期啤数,
SUM(b.bear_q * c.out_quantity) AS 本期个数,
SUM(b.in_q) AS 本期收货,
SUM(b.bear_q * c.out_quantity - b.in_q) AS 本期欠收
FROM meterial a
INNER JOIN produce_bear b
ON a.meterial_id = b.meterial_id
INNER JOIN mould_meterial c
ON b.meterial_id = c.meterial_id
GROUP BY a.meterial_id,
a.meterial_name,
a.meterial_spec --根据业务考虑去除此字段
HAVING a.meterial_id = '02100317000107'