SQL使用inner join提示报错

SELECT     医疗机构名称=shospitalname, 
人次=count(*), 
总费用= sum(sbczfy), 
自费药=sum(sbfh_medi),
自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt),
补偿总额= sum(sbc_bcje) ,
平均住院天数=avg(szydays),
平均床日费用=(sum(sbczfy)/count(*))/avg(szydays),
均次费用=sum(sbczfy)/count(*)

FROM         NH_InchgBC_List a
inner join(
SELECT     医疗机构名称=shospitalname, 
人次=count(*), 
总费用= sum(sbczfy), 
自费药=sum(sbfh_medi),
自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt),
补偿总额= sum(sbc_bcje) ,
平均住院天数=avg(szydays),
平均床日费用=(sum(sbczfy)/count(*))/avg(szydays),
均次费用=sum(sbczfy)/count(*)
FROM         NH_InchgBC_List b
where syear = 2011 
and shospitaltype = '定点地市医疗机构'
and dchgdate > '2011-01-01'  and dchgdate < '2011-11-25'
GROUP BY shospitalname)
on a.shospitalname = b.shospitalname
where syear = 2012 
and shospitaltype = '定点地市医疗机构'
and dchgdate > '2012-01-01'  and dchgdate < '2012-11-25'
GROUP BY shospitalname

错误代码：

消息 156，级别 15，状态 1，第 27 行
关键字 'on' 附近有语法错误。

[解决办法]

SELECT     医疗机构名称=shospitalname,  
人次=count(*),  
总费用= sum(sbczfy),  
自费药=sum(sbfh_medi), 
自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt), 
补偿总额= sum(sbc_bcje) , 
平均住院天数=avg(szydays), 
平均床日费用=(sum(sbczfy)/count(*))/avg(szydays), 
均次费用=sum(sbczfy)/count(*) 
  
FROM         NH_InchgBC_List a 
inner join( 
            SELECT     医疗机构名称=shospitalname,  
            人次=count(*),  
            总费用= sum(sbczfy),  
            自费药=sum(sbfh_medi), 
            自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt), 
            补偿总额= sum(sbc_bcje) , 
            平均住院天数=avg(szydays), 
            平均床日费用=(sum(sbczfy)/count(*))/avg(szydays), 
            均次费用=sum(sbczfy)/count(*) 
            FROM         NH_InchgBC_List b 
            where syear = 2011  
            and shospitaltype = '定点地市医疗机构'
            and dchgdate > '2011-01-01'  and dchgdate < '2011-11-25'
            GROUP BY shospitalname) b
on a.shospitalname = b.shospitalname  
 
where syear = 2012  
and shospitaltype = '定点地市医疗机构'
and dchgdate > '2012-01-01'  and dchgdate < '2012-11-25'
GROUP BY shospitalname

[解决办法]

引用:

SQL code?12345678910111213141516171819202122232425262728293031SELECT 医疗机构名称=shospitalname, 人次=count(*), 总费用= sum(sbczfy), 自费药=sum(sbfh_medi), 自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt)……

SELECT 医疗机构名称=shospitalname,
人次=count(*),
总费用= sum(sbczfy),
自费药=sum(sbfh_medi),
自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt),
补偿总额= sum(sbc_bcje) ,
平均住院天数=avg(szydays),
平均床日费用=(sum(sbczfy)/count(*))/avg(szydays),
均次费用=sum(sbczfy)/count(*)

FROM NH_InchgBC_List a
inner join(
SELECT 医疗机构名称=shospitalname, -- 已定名
人次=count(*),
总费用= sum(sbczfy),
自费药=sum(sbfh_medi),
自费项目=sum(sbfh_medi+sbfh_treat+sbfh_qt),
补偿总额= sum(sbc_bcje) ,
平均住院天数=avg(szydays),
平均床日费用=(sum(sbczfy)/count(*))/avg(szydays),
均次费用=sum(sbczfy)/count(*)
FROM NH_InchgBC_List /*b 名放地方*/
where syear = 2011
and shospitaltype = '定点地市医疗机构'
and dchgdate > '2011-01-01' and dchgdate < '2011-11-25'
GROUP BY shospitalname) As b
on a.shospitalname = b.医疗机构名称 -- 在Inner Join 里已定名
where syear = 2012
and shospitaltype = '定点地市医疗机构'
and dchgdate > '2012-01-01' and dchgdate < '2012-11-25'
GROUP BY shospitalname