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