那位高手出来帮我优化2个统计SQL语句？？？

[cheng525jj]

select y2.depid2 deptid,y2.dtitle dtitle,y2.Badge,y.exreason from ACD_ATTENDID x
inner join APLAN_RANGE y on y.AZID = x.AZID
inner join AEMPLOYEE y1 on y.EID = y1.EID
inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID
where y.Term between '2016-03-01' and '2016-03-04'
and y.INITIALIZED = '0'
and y.exreason <> ''
group by y2.depid2,y2.dtitle,y2.Badge,y.exreason

这个SQL语句查询出来如下:

deptid dtitle Badge exreason
33 内审部 58644 无卡
47 变压器部 44324 奇次卡
43 战略采购部 35819 无卡
43 战略采购部 37221 无卡
43 战略采购部 54018 无卡
32 报关部 37716 旷工
45 绕线部 62692 未申报OT
40 设施管理部 00215 无卡
40 设施管理部 59372 无卡
40 设施管理部 05761 未申报OT
40 设施管理部 36991 奇次卡
40 设施管理部 42579 奇次卡
40 设施管理部 43932 奇次卡
40 设施管理部 48277 奇次卡
37 采购部 51666 无卡

那个能帮我优化一下SQL，按如下要求写2个SQL语句：

要求一:按dtitle，exreason来统计数量

deptid dtitle num exreason
33 内审部 1 无卡
47 变压器部 1 奇次卡
43 战略采购部 3 无卡
32 报关部 1 旷工
45 绕线部 1 未申报OT
40 设施管理部 2 无卡
40 设施管理部 1 未申报OT
40 设施管理部 4 奇次卡
37 采购部 1 无卡


要求二：按dtitle 来统计数量

deptid dtitle num
33 内审部 1
47 变压器部 1
43 战略采购部 3
32 报关部 1
45 绕线部 1
40 设施管理部 7
37 采购部 1

[cheng525jj]

fredrickhu,结果统计不对。

[cheng525jj]

按你所提供的SQL统计结果不正确： 要求一，统计出来的结果: deptid dtitle num exreason 32 报关部 53 旷工 33 内审部 53 无卡 37 采购部 212 无卡 40 设施管理部 159 奇次卡 40 设施管理部 159 无卡 40 设施管理部 212 未申报OT 43 战略采购部 212 无卡 45 绕线电感部 53 未申报OT 47 变压器部 53 奇次卡 要求二 统计出来的结果: deptid dtitle num 33 内审部 53 47 变压器部 53 43 战略采购部 212 32 报关部 53 45 绕线电感部 53 40 设施管理部 530 37 采购部 212

[spiritofdragon]

;with t as (
select distinct y2.depid2 deptid,y2.dtitle dtitle,y2.Badge,y.exreason 
from ...
where ...
)
select deptid,dtitle,COUNT(1) num,exreason
from t
group by  deptid,dtitle,exreason
;
with t as (
select distinct y2.depid2 deptid,y2.dtitle dtitle,y2.Badge,y.exreason 
from ...
where ...
)
select deptid,dtitle,COUNT(1) num
from t
group by  deptid,dtitle

[cheng525jj]

spiritofdragon,你统计的结果跟 fredrickhu 一样不对。

[spiritofdragon]

子查询里加distinct了么？

[cheng525jj]

with t as ( select distinct y2.depid2 deptid,y2.dtitle dtitle,y.exreason from ACD_ATTENDID x inner join APLAN_RANGE y on y.AZID = x.AZID inner join AEMPLOYEE y1 on y.EID = y1.EID inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID where y.Term between '2016-03-01' and '2016-03-04' and y.INITIALIZED = '0' and y.exreason <> '' group by y2.depid2,y2.dtitle,y.exreason ) select deptid,dtitle,COUNT(1) num,exreason from t group by deptid,dtitle,exreason 也不行！！！汗汗！

[spiritofdragon]

注意，请仔细看我写的，别抄错了。 1：distinct后面跟4个字段，包含y2.Badge, 2：写了distinct 就不用写group by了。

[责任after自由]

sql01: select (y2.dtitle+' || '+y.exreason),COUNT(1) from ACD_ATTENDID x inner join APLAN_RANGE y on y.AZID = x.AZID inner join AEMPLOYEE y1 on y.EID = y1.EID inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID where y.Term between '2016-03-01' and '2016-03-04' and y.INITIALIZED = '0' and y.exreason <> '' group by y2.dtitle,y.exreason sql02: select y2.dtitle,COUNT(1) from ACD_ATTENDID x inner join APLAN_RANGE y on y.AZID = x.AZID inner join AEMPLOYEE y1 on y.EID = y1.EID inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID where y.Term between '2016-03-01' and '2016-03-04' and y.INITIALIZED = '0' and y.exreason <> '' group by y2.dtitle

[Ginnnnnnnn]

语句1
select y2.depid2 deptid,y2.dtitle dtitle,COUNT(DISTINCT y2.Badge) AS num,y.exreason from ACD_ATTENDID x
inner join APLAN_RANGE y on y.AZID = x.AZID
inner join AEMPLOYEE y1 on y.EID = y1.EID
inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID
where  y.Term between '2016-03-01' and '2016-03-04' 
and y.INITIALIZED = '0'
and y.exreason <> '' 
group by y2.depid2,y2.dtitle,y.exreason

语句2
select y2.depid2 deptid,y2.dtitle dtitle,COUNT(DISTINCT y2.Badge) AS num from ACD_ATTENDID x
inner join APLAN_RANGE y on y.AZID = x.AZID
inner join AEMPLOYEE y1 on y.EID = y1.EID
inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID
where  y.Term between '2016-03-01' and '2016-03-04' 
and y.INITIALIZED = '0'
and y.exreason <> '' 
group by y2.depid2,y2.dtitle

[--小F--]

select y2.depid2 deptid,y2.dtitle dtitle,count(1) as num,y.exreason from ACD_ATTENDID x
 inner join APLAN_RANGE y on y.AZID = x.AZID
 inner join AEMPLOYEE y1 on y.EID = y1.EID
 inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID
 where  y.Term between '2016-03-01' and '2016-03-04' 
 and y.INITIALIZED = '0'
 and y.exreason <> '' 
 group by y2.depid2,y2.dtitle,y.exreason

select y2.depid2 deptid,y2.dtitle dtitle,count(1) as num from ACD_ATTENDID x
 inner join APLAN_RANGE y on y.AZID = x.AZID
 inner join AEMPLOYEE y1 on y.EID = y1.EID
 inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID
 where  y.Term between '2016-03-01' and '2016-03-04' 
 and y.INITIALIZED = '0'
 and y.exreason <> '' 
 group by y2.depid2,y2.dtitle

[--小F--]

select y2.depid2 deptid,y2.dtitle dtitle,count(1) as num,y.exreason from ACD_ATTENDID x
 inner join APLAN_RANGE y on y.AZID = x.AZID
 inner join AEMPLOYEE y1 on y.EID = y1.EID
 inner join EVW_EMPLOYEE y2 on y1.EID = y2.EID
 where  y.Term between '2016-03-01' and '2016-03-04' 
 and y.INITIALIZED = '0'
 and y.exreason <> '' 
 group by y2.depid2,y2.dtitle,y2.Badge,y.exreason