我有个SQL语句,代码如下:select '总人数:'+str(count(distinct xs_sfz))+',新生:'+str((select count(distinct(xs_sfz)) from view_kw_xs_tk_bk where kaoc_bm=kaoci_bm and xs_zxm=b.xs_zxm and qx_bm=@qx_bm)) xs_xm,count(kc_bm) kc_bm,xs_zxm from View_kw_Xs_Tk_Bk b where b.qx_bm=@qx_bm group by xs_zxm order by xs_zxm desc
得到的结果如下:
总人数: 159,新生: 116 300 012214
总人数: 28,新生: 6 41 012209
总人数: 80,新生: 10 165 012208
总人数: 309,新生: 158 584 012206
总人数: 377,新生: 0 596 012204
总人数: 111,新生: 111 222 012203
总人数: 34,新生: 34 68 012202
总人数: 132,新生: 132 264 012201
总人数: 2077,新生: 1194 3886 012200
这个结果是对的,但是这个语句运行的时间实在是不敢恭维,如果是再程序中运行,会报告超时,忘大侠给优化,万分感谢!
得到的结果如下:
总人数: 159,新生: 116 300 012214
总人数: 28,新生: 6 41 012209
总人数: 80,新生: 10 165 012208
总人数: 309,新生: 158 584 012206
总人数: 377,新生: 0 596 012204
总人数: 111,新生: 111 222 012203
总人数: 34,新生: 34 68 012202
总人数: 132,新生: 132 264 012201
总人数: 2077,新生: 1194 3886 012200
这个结果是对的,但是这个语句运行的时间实在是不敢恭维,如果是再程序中运行,会报告超时,忘大侠给优化,万分感谢!
,count(kc_bm) kc_bm
,xs_zxm
from View_kw_Xs_Tk_Bk AS b
where b.qx_bm=@qx_bm
group by xs_zxm order by xs_zxm DESC
select '总人数:'+str(count(distinct xs_sfz))+',新生:'+str(count(case when kaoc_bm=kaoci_bm then xs_sfz else null end)) as xs_xm,
count(kc_bm) kc_bm,
xs_zxm
from View_kw_Xs_Tk_Bk b where b.qx_bm=@qx_bm group by xs_zxm order by xs_zxm desc
+',新生:'+ str(count(distinct
CASE WHEN kaoc_bm=kaoci_bm
and xs_zxm=b.xs_zxm THEN xs_sfz END )) xs_xm
,count(kc_bm) kc_bm
,xs_zxm
from View_kw_Xs_Tk_Bk b
where b.qx_bm=@qx_bm
group by xs_zxm
order by xs_zxm desc
试试这样是不是好点:
select '总人数:'+ltrim(a.xs1)+',新生:'+ltrim(b.xs2) xs_xm,a.kc_bm,a.xs_zxm from(
select count(distinct xs_sfz) xs1,count(kc_bm) kc_bm,xs_zxm from View_kw_Xs_Tk_Bk group by xs_zxm
)a inner join(
select count(distinct xs_sfz) xs2,xs_zxm from View_kw_Xs_Tk_Bk where kaoc_bm=kaoci_bm group by xs_zxm
)b on a.xs_zxm=b.xs_zxm
order by xs_zxm desc