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

left join 的问题
两个问题。。。第一个,按照我的写法,AVG()里面两个值出错了(在包含外部引用的被聚合表达式中指定了多个列)
第二个问题。。。能不能给改成 left join 的写法


SQL code
SELECT A.F_GSID,a.f_date
    ,F_0026=(SELECT COUNT(*) FROM C1T13 m  WHERE  m.F_GSID= a.F_GSID AND m.F_SJRQ= CAST(A.F_YEAR AS varchar)+'1231')
    ,F_0027=(SELECT AVG(LEFT(a.F_YEAR,4)-m.F_1002) FROM m..C1T13 m WHERE m.F_GSID= a.F_GSID AND m.F_SJRQ= CAST(a.F_YEAR AS varchar)+'1231' AND m.F_1002>1900 AND m.F_1002<9999)
from 
C1T05 a 





------解决方案--------------------
SQL code
SELECT
 A.F_GSID,a.f_date
,a.num
, AVG(LEFT(b.F_YEAR,4)-m.F_1002)
from 
  C1T05 b,
  (select F_GSID,count(1) as num from C1T13 group by F_GSID)a,
  C1T13 m 
where
  m.F_GSID= a.F_GSID AND m.F_SJRQ= CAST(A.F_YEAR AS varchar)+'1231'
AND
  m.F_1002>1900 AND m.F_1002<9999 
and
   a.f_gsid=b.f_gsid and a.f_date=b.f_date