语句:
select a.contract_no,a.hkout_m_id,a.Payee,a.Name,a.Model,a.Unit,a.Amount,a.mtype
,a.mrate,a.payee_uprice,a.payee_uprice_usd,a.App_usd,a.payee_info,a.ftok,a.PO_NO from (
select * from DB_CBSCS_Sell..V_Pmapply_add
where ftok=1 and payee=1233 and ( ( Amount*payee_uprice > 0 and (round(Amount*payee_uprice,3)-App_usd) > 0)
or ( Amount*payee_uprice < 0 and round(Amount*payee_uprice,3)-App_usd < 0) )
) as a group by a.contract_no,a.hkout_m_id,a.Payee,a.Name,a.Model,a.Unit,a.Amount,a.mtype
,a.mrate,a.payee_uprice,a.payee_uprice_usd,a.App_usd,a.payee_info,a.ftok,a.PO_NO结果:
166 HT1109-0001 1233 N-0001 测试1号 都说人家是测试的啦 PCS 1234 RMB 6.8000002 184.6160 27.1494 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1
168 HT1109-0001 1233 N-0002 测试2号 都说人家是测试的啦 PCS 4321 RMB 6.8000002 184.6160 27.1494 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1
169 HT1109-0002 1233 22012454 IC IC1239 PCS 122 RMB 6.8000002 19.6400 2.8882 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1
170 HT1109-0001 1233 1 IC IC1239 PCS 122 RMB 6.8000002 19.6400 2.8882 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1理想结果..
根据合同编号分组的..htxxxx-xxx 并求数量,单价,总价的和.是对应合同的总价...我前面有用过sum去求...结果照旧..
坐等高手...
select a.contract_no,a.hkout_m_id,a.Payee,a.Name,a.Model,a.Unit,a.Amount,a.mtype
,a.mrate,a.payee_uprice,a.payee_uprice_usd,a.App_usd,a.payee_info,a.ftok,a.PO_NO from (
select * from DB_CBSCS_Sell..V_Pmapply_add
where ftok=1 and payee=1233 and ( ( Amount*payee_uprice > 0 and (round(Amount*payee_uprice,3)-App_usd) > 0)
or ( Amount*payee_uprice < 0 and round(Amount*payee_uprice,3)-App_usd < 0) )
) as a group by a.contract_no,a.hkout_m_id,a.Payee,a.Name,a.Model,a.Unit,a.Amount,a.mtype
,a.mrate,a.payee_uprice,a.payee_uprice_usd,a.App_usd,a.payee_info,a.ftok,a.PO_NO结果:
166 HT1109-0001 1233 N-0001 测试1号 都说人家是测试的啦 PCS 1234 RMB 6.8000002 184.6160 27.1494 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1
168 HT1109-0001 1233 N-0002 测试2号 都说人家是测试的啦 PCS 4321 RMB 6.8000002 184.6160 27.1494 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1
169 HT1109-0002 1233 22012454 IC IC1239 PCS 122 RMB 6.8000002 19.6400 2.8882 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1
170 HT1109-0001 1233 1 IC IC1239 PCS 122 RMB 6.8000002 19.6400 2.8882 .0000 全称:dafadfadf;开户行:r;银行地址:fqrefads;帐号:gfagfad;银行代码:sfadsgfadsf 1理想结果..
根据合同编号分组的..htxxxx-xxx 并求数量,单价,总价的和.是对应合同的总价...我前面有用过sum去求...结果照旧..
坐等高手...
group by必须和选择的项一一对应.