日期:2014-05-18  浏览次数:20407 次

我又来了,大神们进来帮我一下#36
select b.fkgx_fjid as 房间,c.khda_khmc 业主姓名
from fkgx b 
left join 
(select sum(FYSQ_QSJE) 欠收金额 , count(fysq_qsje) as 欠费月数 ,fysq_fkgxwj ,min(fysq_ssny)+'--'+max(fysq_ssny) as 费用期间
from fysq 
where fysq_sfxmmc='物业服务费' and fysq_qsje<>0
group by fysq_fkgxwj ) a

on a.fysq_fkgxwj=b.fkgx_zj
left join khda as c
on b.fkgx_khwj=c.khda_zj 



这是我做了一半的SQL语句

我需要把房间,业主姓名,欠收金额,欠费月数,费用期间 这几个都写出来
上面是我师傅叫我完成
我自己写的下面的,师傅说会有问题,求下帮主怎么解决

select sum(a.FYSQ_QSJE) 欠收金额 , count(a.fysq_qsje) as 欠费月数 ,a.fysq_fkgxwj ,min(a.fysq_ssny)+'--'+max(a.fysq_ssny) as 费用期间,
  b.fkgx_fjid,c.khda_khmc
from fysq as a
left join fkgx as b
on a.fysq_fkgxwj=b.fkgx_zj
left join khda as c
on b.fkgx_khwj=c.khda_zj
where fysq_sfxmmc='物业服务费' and fysq_qsje<>0
group by b.fkgx_fjid ,a.fysq_fkgxwj,a.fysq_qsje,c.khda_khmc





表关系是


FYSQ_FKGXWJ=FKGX_ZJ
FKGX_KHWJ=KHDA_ZJ












------解决方案--------------------
或者你第一个写法多返回几个字段
SQL code
select b.fkgx_fjid as 房间,c.khda_khmc 业主姓名
,b.欠收金额 , b.欠费月数 ,b.费用期间

from fkgx b  
left join  
(select sum(FYSQ_QSJE) 欠收金额 , count(fysq_qsje) as 欠费月数 ,fysq_fkgxwj ,min(fysq_ssny)+'--'+max(fysq_ssny) as 费用期间
from fysq  
where fysq_sfxmmc='物业服务费' and fysq_qsje<>0
group by fysq_fkgxwj ) a

on a.fysq_fkgxwj=b.fkgx_zj
left join khda as c
on b.fkgx_khwj=c.khda_zj

------解决方案--------------------
探讨
表结构是

FYSQ

FYSQ_QSJE FYSQ_SSNY FYSQ_FKGXWJ FYSQ_ZJ


FKGX

FKGX_ZJ FKGX_KHWJ FKGX_FJID


KHDA

KHDA_ZJ KHDA_KHMC



其中FYSQ_FKGXWJ=FKGX_ZJ
FKGX_KHWJ=KHDA_ZJ