以下是我写的语句,不完整,
select a.cdm,a.vmc,b.czddm,c.djysj,sum(c.njyje),sum(c.njyjf),sum(c.nzjjf),c.nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM=b.cshdm
left join b_jyxx_cjjymxb c on b.czddm=c.czdd
where 1=1
group by a.cdm,a.vmc,b.czddm,c.djysj,其中c.nzjzt字段是中奖状态,‘0’是中奖,‘1’是没有中奖,在这条汇总数据中,要有统计中奖次数的信息,但不知该怎么写这条语句,请各位赐教!
select a.cdm,a.vmc,b.czddm,c.djysj,sum(c.njyje),sum(c.njyjf),sum(c.nzjjf),c.nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM=b.cshdm
left join b_jyxx_cjjymxb c on b.czddm=c.czdd
where 1=1
group by a.cdm,a.vmc,b.czddm,c.djysj,其中c.nzjzt字段是中奖状态,‘0’是中奖,‘1’是没有中奖,在这条汇总数据中,要有统计中奖次数的信息,但不知该怎么写这条语句,请各位赐教!
a.vmc,
b.czddm,
c.djysj,
sum(c.njyje),
sum(c.njyjf),
sum(c.nzjjf),
sum(decode(c.nzjzt, '1', 1, 0)) nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM = b.cshdm
left join b_jyxx_cjjymxb c on b.czddm = c.czdd
where 1 = 1
group by a.cdm, a.vmc, b.czddm, c.djysj
试下
select a.cdm,a.vmc,b.czddm,c.djysj,sum(c.njyje),sum(c.njyjf),sum(c.nzjjf),
sum(Case c.nzjzt when '0' then 1 else 0 end) nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM=b.cshdm
left join b_jyxx_cjjymxb c on b.czddm=c.czdd
where 1=1
group by a.cdm,a.vmc,b.czddm,c.djysj
a.vmc,
b.czddm,
c.djysj,
sum(c.njyje),
sum(c.njyjf),
sum(c.nzjjf),
sum(decode(c.nzjzt, '0', 1, 0)) nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM = b.cshdm
left join b_jyxx_cjjymxb c on b.czddm = c.czdd
where 1 = 1
group by a.cdm, a.vmc, b.czddm, c.djysj
a.vmc,
to_date(c.djysj,'yyyy-mm-dd'),
sum(c.njyje),
sum(c.njyjf),
sum(c.nzjjf),
sum(decode(c.nzjzt, '0', 1, 0)) nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM = b.cshdm
left join b_jyxx_cjjymxb c on b.czddm = c.czddm
where 1 = 1
group by a.cdm, a.vmc,to_date(c.djysj,'yyyy-mm-dd')
c.djysj字段内容中的包含小时、分、秒,我只想取年月日,用上面的 写法为什么会报错呢
这么写
select a.cdm,
a.vmc,
to_char(c.djysj, 'yyyy-mm-dd'),
sum(c.njyje),
sum(c.njyjf),
sum(c.nzjjf),
sum(decode(c.nzjzt, '0', 1, 0)) nzjzt
from b_shxx_shxxb a
left join b_shxx_zdxxb b on a.CDM = b.cshdm
left join b_jyxx_cjjymxb c on b.czddm = c.czddm
where 1 = 1
group by a.cdm, a.vmc, to_char(c.djysj, 'yyyy-mm-dd')