select sum(project_fj.danjia*project_fj.mianji) from project_fj,orderform where project_fj.zhuangtai='未售' and project_fj.project_id=30问题是表orderform没有哪列在语句中被用到,但是加上orderform以后算出来的值大了很多?这是怎么一回事啊
调试欢乐多
select * from a ,b
有什么区别。
--笛卡儿积,交叉关联,project_fj表中的一列会匹配到orderform的所有列,sum造成错误的结果
select sum(project_fj.danjia*project_fj.mianji) from project_fj,orderform where project_fj.zhuangtai='未售' and project_fj.project_id=30
and project_fj.id=orderform.roject_id
--你那个表又没用,直接这样就好了啊?
select sum(danjia*mianji) from project_fj where zhuangtai='未售' and project_id=30
--如果和那个表有关联,再用关联列join起来啊
完整的sql语句为
select project_name, sum(case when zhuangtai= '已售' and project_fj.id=orderform.fj_id then (orderform.dd_chengjiaojiage) else 0 end) as 已售总值,sum(case when zhuangtai in('未售','大定','小定','保留') then (danjia*mianji+jg_ccs*mj_ccs+jg_ck*mj_ck) else 0 end) as 未售总值,sum(danjia*mianji+jg_ccs*mj_ccs+jg_ck*mj_ck) as 全部总值 from project_fj,project,orderform where project.id=project_fj.project_id GROUP BY project_name
这里不就是有关联了么
这个语句中when条件中可以用in?
sum(case
when zhuangtai = '已售' then
(orderform.dd_chengjiaojiage)
else
0
end) as 已售总值,
sum(case
when zhuangtai in ('未售', '大定', '小定', '保留') then
(danjia * mianji + jg_ccs * mj_ccs + jg_ck * mj_ck)
else
0
end) as 未售总值,
sum(danjia * mianji + jg_ccs * mj_ccs + jg_ck * mj_ck) as 全部总值
from project_fj left join orderform on project_fj.id = orderform.fj_id, project
where project.id = project_fj.project_id
GROUP BY project_name这个试试看的呢,我这边没有sql server 暂时没办法试