业务逻辑没看懂,不过 "我要把1022,1027,1028 都只当作公话来看待,于是我要把这三种类型的公话的新装和拆机数给合计起来" 可以用 select case when serv_type_ in (1022,1027,1028) then 1022 else serv_type_ end as serv_type_ ... from ... group by case when serv_type_ in (1022,1027,1028) then 1022 else serv_type_ end
改三处可以不用建视图 select (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-------------1 serv_type_, s_name=(SELECT s_name FROM serv_ty_name WHERE id=serv_type_), sum(case when serv_state='F1N' then 1 else 0 end) as 新装, sum(case when serv_state='F1R' then 1 else 0 end) as 拆机, sum(case when serv_state='F1S' then 1 else 0 end) as 停机, sum(case when serv_state!='F1R' then 1 else 0 end) as 到达数 from dhzl3 where serv_type_ in (1000,1010,1003,1813,1006,1022,1027,1028) group by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-----------2 order by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-----------3
还有一处 id=serv_type_------------ id=(case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-----------
楼上的大大 我完全按照你的修改: select (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end), s_name=(SELECT s_name FROM serv_ty_name WHERE id=(case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)), sum(case when serv_state='F1N' then 1 else 0 end) as 新装, sum(case when serv_state='F1R' then 1 else 0 end) as 拆机, sum(case when serv_state='F1S' then 1 else 0 end) as 停机, sum(case when serv_state!='F1R' then 1 else 0 end) as 到达数 from dhzl3 where serv_type_ in (1000,1010,1003,1813,1006,1022,1027,1028) group by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)就会出现: 列 'dhzl3.serv_type_' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。 这样的错误,但是,如果我把 s_name=(SELECT s_name FROM serv_ty_name WHERE id=(case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)), 也就是用于重命名的这段给删除掉,就可以正常显示结果,只不过是不可按照我的要求,根据serv_type_ 的值来显示产品名称。
这样改试试select (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end), s_name, sum(case when serv_state='F1N' then 1 else 0 end) as 新装, sum(case when serv_state='F1R' then 1 else 0 end) as 拆机, sum(case when serv_state='F1S' then 1 else 0 end) as 停机, sum(case when serv_state!='F1R' then 1 else 0 end) as 到达数 from dhzl3 inner join serv_ty_name on serv_ty_name.id=case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end where serv_type_ in (1000,1010,1003,1813,1006,1022,1027,1028) group by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end),s_name
"我要把1022,1027,1028 都只当作公话来看待,于是我要把这三种类型的公话的新装和拆机数给合计起来"
可以用
select case when serv_type_ in (1022,1027,1028) then 1022 else serv_type_ end as serv_type_
...
from ...
group by case when serv_type_ in (1022,1027,1028) then 1022 else serv_type_ end
select (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-------------1
serv_type_, s_name=(SELECT s_name FROM serv_ty_name WHERE id=serv_type_),
sum(case when serv_state='F1N' then 1 else 0 end) as 新装,
sum(case when serv_state='F1R' then 1 else 0 end) as 拆机,
sum(case when serv_state='F1S' then 1 else 0 end) as 停机,
sum(case when serv_state!='F1R' then 1 else 0 end) as 到达数
from dhzl3
where serv_type_ in (1000,1010,1003,1813,1006,1022,1027,1028)
group by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-----------2
order by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-----------3
id=serv_type_------------
id=(case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)-----------
楼上的大大
我完全按照你的修改:
select (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end),
s_name=(SELECT s_name FROM serv_ty_name WHERE id=(case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)),
sum(case when serv_state='F1N' then 1 else 0 end) as 新装,
sum(case when serv_state='F1R' then 1 else 0 end) as 拆机,
sum(case when serv_state='F1S' then 1 else 0 end) as 停机,
sum(case when serv_state!='F1R' then 1 else 0 end) as 到达数
from dhzl3
where serv_type_ in (1000,1010,1003,1813,1006,1022,1027,1028)
group by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)就会出现:
列 'dhzl3.serv_type_' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
这样的错误,但是,如果我把
s_name=(SELECT s_name FROM serv_ty_name WHERE id=(case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end)),
也就是用于重命名的这段给删除掉,就可以正常显示结果,只不过是不可按照我的要求,根据serv_type_ 的值来显示产品名称。
s_name,
sum(case when serv_state='F1N' then 1 else 0 end) as 新装,
sum(case when serv_state='F1R' then 1 else 0 end) as 拆机,
sum(case when serv_state='F1S' then 1 else 0 end) as 停机,
sum(case when serv_state!='F1R' then 1 else 0 end) as 到达数
from dhzl3
inner join serv_ty_name on serv_ty_name.id=case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end
where serv_type_ in (1000,1010,1003,1813,1006,1022,1027,1028)
group by (case when serv_type_ in(1022,1027,1028) then 1022 else serv_type_ end),s_name