已知:需要查询 采购员,采购时间,合同编号,合同金额 这四个字段,其中前三个字段能从一张表里查出来,合同金额是另一张表的多条订单的和,
求:用一条SQL查出四个字段
解:订单表:
select * from view_order
前三个字段查询:
select t.ORDER_BUYERNAME ,t.ORDER_BUYDATE ,t.ORDER_CODE from view_order t order by t.ORDER_BUYDATE desc--订单信息表:采购员、采购时间、合同编号
订单明细表:
select * from view_orderdetail
第四个字段查询:
select sum(v.OD_TOTALPRICE)*1.17 sum_TOTALPRICE from view_orderdetail v ,view_order t where v.OD_ORDERCODE =t.ORDER_CODE and t.ORDER_CODE ='4500498951'----合同金额 --1.17是含税
用Group by ,一条SQL写出:
select t.ORDER_BUYERNAME ,t.ORDER_BUYDATE ,t.ORDER_CODE,tt.sum_TOTALPRICE from view_order t , (select t.ORDER_CODE ORDER_CODE , sum(v.OD_TOTALPRICE)*1.17 sum_TOTALPRICE from view_orderdetail v ,view_order t
where v.OD_ORDERCODE =t.ORDER_CODEgroup by t.ORDER_CODE) tt
where t.ORDER_CODE=tt.ORDER_CODE
分析:
select t.ORDER_CODE ORDER_CODE , sum(v.OD_TOTALPRICE)*1.17 sum_TOTALPRICE from view_orderdetail v ,view_order t where v.OD_ORDERCODE =t.ORDER_CODE group by t.ORDER_CODE) tt
查出来的是ORDER_CODE 和 sum_TOTALPRICE这两个字段,这两个字段当成一个整体,和view_order 进行关系比较查询,通过 t.ORDER_CODE=tt.ORDER_CODE查到要求的四个字段