select ch,xq,SUM(dcsj) as dcsj,SUM(case when xq=1 then gls end) as ct, SUM(case when xq=0 then gls end) as dt from cl_pcdj where sqsj between '2004-3-15' and '2004-3-15' group by ch,xq order by ch
把count改成sum不就行了 sum(case when xq=0 then gls else 0 end)
select ch,xq,count(dcsj) as dcsj ,sum(case when xq=1 then gls end) as ct , sum(case when xq=0 then gls end) as dt from cl_pcdj where sqsj between '2004-3-15' and '2004-3-15' group by ch,xq order by ch
select ch,xq,count(dcsj) as dcsj,SUM(case when xq=1 then gls end) as ct, SUM(case when xq=0 then gls end) as dt from cl_pcdj where sqsj between '2004-3-15' and '2004-3-15' group by ch,xq order by ch
select ch,xq,count(dcsj) as dcsj,sum(case when xq=1 then gls end) as ct, sum(case when xq=0 then gls end) as dt from cl_pcdj where sqsj between '2004-3-15' and '2004-3-15' group by ch,xq order by ch
常用集合函數,其他函參見幫助 AVG 平均值 MAX 最大值 MIN 最小值 SUM 累加值 COUNT 記錄數
sum(case when xq=0 then gls else 0 end)
,sum(case when xq=1 then gls end) as ct
, sum(case when xq=0 then gls end) as dt
from cl_pcdj
where sqsj between '2004-3-15' and '2004-3-15'
group by ch,xq order by ch
select ch,xq,count(dcsj) as dcsj,SUM(case when xq=1 then gls end) as ct, SUM(case when xq=0 then gls end) as dt from cl_pcdj where sqsj between '2004-3-15' and '2004-3-15' group by ch,xq order by ch
AVG 平均值
MAX 最大值
MIN 最小值
SUM 累加值
COUNT 記錄數