SELECT tbl_dyi_ma_materialoutputdatail.char_materialID AS '物料代码',
tbl_dyi_ma_material.varc_description AS '物料名称',
tbl_dyi_ma_material.varc_type AS '规格型号' ,
tbl_dyi_ma_material.varc_standardunitID AS '单位',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount) AS '出库数量',
@UnitTime AS '单位时间',
@Frequency AS '比率限制',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime as '频次'
FROM tbl_dyi_ma_materialoutput
JOIN tbl_dyi_ma_materialoutputdatail
ON tbl_dyi_ma_materialoutput.char_outputID=tbl_dyi_ma_materialoutputdatail.char_outputID
LEFT JOIN tbl_dyi_ma_material
ON tbl_dyi_ma_materialoutputdatail.char_materialID=tbl_dyi_ma_material.char_materalID
WHERE tbl_dyi_ma_materialoutput.date_outputdate BETWEEN @StartDate and @EndDate
AND tbl_dyi_ma_material.char_materalID like @MaterID
AND tbl_dyi_ma_material.varc_description like @MaterName
AND tbl_dyi_ma_material.varc_type like @MaterType
group by tbl_dyi_ma_materialoutputdatail.char_materialID,tbl_dyi_ma_material.varc_description,
tbl_dyi_ma_material.varc_type,tbl_dyi_ma_material.varc_standardunitID
其中select里面有个sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime的信息,要求在where条件里面加上这个结果>100的计算,如何写?
tbl_dyi_ma_material.varc_description AS '物料名称',
tbl_dyi_ma_material.varc_type AS '规格型号' ,
tbl_dyi_ma_material.varc_standardunitID AS '单位',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount) AS '出库数量',
@UnitTime AS '单位时间',
@Frequency AS '比率限制',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime as '频次'
FROM tbl_dyi_ma_materialoutput
JOIN tbl_dyi_ma_materialoutputdatail
ON tbl_dyi_ma_materialoutput.char_outputID=tbl_dyi_ma_materialoutputdatail.char_outputID
LEFT JOIN tbl_dyi_ma_material
ON tbl_dyi_ma_materialoutputdatail.char_materialID=tbl_dyi_ma_material.char_materalID
WHERE tbl_dyi_ma_materialoutput.date_outputdate BETWEEN @StartDate and @EndDate
AND tbl_dyi_ma_material.char_materalID like @MaterID
AND tbl_dyi_ma_material.varc_description like @MaterName
AND tbl_dyi_ma_material.varc_type like @MaterType
group by tbl_dyi_ma_materialoutputdatail.char_materialID,tbl_dyi_ma_material.varc_description,
tbl_dyi_ma_material.varc_type,tbl_dyi_ma_material.varc_standardunitID
其中select里面有个sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime的信息,要求在where条件里面加上这个结果>100的计算,如何写?
解决方案 »
- sql server2000 创建规则问题(老师布置的作业,囧)
- VS2005 使用ASP连接SQL2000数据库错误
- 求一条sql语句
- 哪里有这个CDataGrid类的reference?谢谢
- 用sql server profiler如何跟踪某个库
- SQL语句或者实现方案请教
- 关于数据库设计的问题?
- 问几个困扰我很久的问题:1、连接方式的概念、2、LOCK及并发控制的概念、3、如何恢复一个损坏的6。5库、4、移动数据库的问题 5、性能调
- 学习邹大哥写的资料写的存储过程,但无法实现效果....
- 数据库错误能汉化吗?
- 请教一个更新余数的问题,不用游标的情况下
- 关于如何将文本文件导入到sqlserver中
select * from (SELECT tbl_dyi_ma_materialoutputdatail.char_materialID AS '物料代码',
tbl_dyi_ma_material.varc_description AS '物料名称',
tbl_dyi_ma_material.varc_type AS '规格型号' ,
tbl_dyi_ma_material.varc_standardunitID AS '单位',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount) AS '出库数量',
@UnitTime AS '单位时间',
@Frequency AS '比率限制',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime as '频次'
FROM tbl_dyi_ma_materialoutput
JOIN tbl_dyi_ma_materialoutputdatail
ON tbl_dyi_ma_materialoutput.char_outputID=tbl_dyi_ma_materialoutputdatail.char_outputID
LEFT JOIN tbl_dyi_ma_material
ON tbl_dyi_ma_materialoutputdatail.char_materialID=tbl_dyi_ma_material.char_materalID
WHERE tbl_dyi_ma_materialoutput.date_outputdate BETWEEN @StartDate and @EndDate
AND tbl_dyi_ma_material.char_materalID like @MaterID
AND tbl_dyi_ma_material.varc_description like @MaterName
AND tbl_dyi_ma_material.varc_type like @MaterType
group by tbl_dyi_ma_materialoutputdatail.char_materialID,tbl_dyi_ma_material.varc_description,
tbl_dyi_ma_material.varc_type,tbl_dyi_ma_material.varc_standardunitID)a
where a.频次>100
(
SELECT tbl_dyi_ma_materialoutputdatail.char_materialID AS '物料代码',
tbl_dyi_ma_material.varc_description AS '物料名称',
tbl_dyi_ma_material.varc_type AS '规格型号' ,
tbl_dyi_ma_material.varc_standardunitID AS '单位',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount) AS '出库数量',
@UnitTime AS '单位时间',
@Frequency AS '比率限制',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime as '频次'
FROM tbl_dyi_ma_materialoutput
JOIN tbl_dyi_ma_materialoutputdatail
ON tbl_dyi_ma_materialoutput.char_outputID=tbl_dyi_ma_materialoutputdatail.char_outputID
LEFT JOIN tbl_dyi_ma_material
ON tbl_dyi_ma_materialoutputdatail.char_materialID=tbl_dyi_ma_material.char_materalID
WHERE tbl_dyi_ma_materialoutput.date_outputdate BETWEEN @StartDate and @EndDate
AND tbl_dyi_ma_material.char_materalID like @MaterID
AND tbl_dyi_ma_material.varc_description like @MaterName
AND tbl_dyi_ma_material.varc_type like @MaterType
group by tbl_dyi_ma_materialoutputdatail.char_materialID,tbl_dyi_ma_material.varc_description,
tbl_dyi_ma_material.varc_type,tbl_dyi_ma_material.varc_standardunitID
)t1
where 频次 > 100
括弧中的语句就是你提供的查询语句
SELECT tbl_dyi_ma_materialoutputdatail.char_materialID AS '物料代码',
tbl_dyi_ma_material.varc_description AS '物料名称',
tbl_dyi_ma_material.varc_type AS '规格型号' ,
tbl_dyi_ma_material.varc_standardunitID AS '单位',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount) AS '出库数量',
@UnitTime AS '单位时间',
@Frequency AS '比率限制',
sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime as '频次'
FROM tbl_dyi_ma_materialoutput
JOIN tbl_dyi_ma_materialoutputdatail
ON tbl_dyi_ma_materialoutput.char_outputID=tbl_dyi_ma_materialoutputdatail.char_outputID
LEFT JOIN tbl_dyi_ma_material
ON tbl_dyi_ma_materialoutputdatail.char_materialID=tbl_dyi_ma_material.char_materalID
WHERE tbl_dyi_ma_materialoutput.date_outputdate BETWEEN @StartDate and @EndDate
AND tbl_dyi_ma_material.char_materalID like @MaterID
AND tbl_dyi_ma_material.varc_description like @MaterName
AND tbl_dyi_ma_material.varc_type like @MaterType
group by tbl_dyi_ma_materialoutputdatail.char_materialID,tbl_dyi_ma_material.varc_description,
tbl_dyi_ma_material.varc_type,tbl_dyi_ma_material.varc_standardunitID
having (sum(tbl_dyi_ma_materialoutputdatail.deci_endcount)/@UnitTime)>100