0

## ms sql 有關case when問題

(第一組)
SELECT a.dbms_id,a.dbms,b.type
FROM msdb.dbo.MSdbms a
left join msdb.dbo.MSdbms_datatype b
on a.dbms_id=b.dbms_id

(第二組)
SELECT a.dbms_id,a.dbms,count(b.type) as"數量"
FROM msdb.dbo.MSdbms a
left join msdb.dbo.MSdbms_datatype b
on a.dbms_id=b.dbms_id
group by a.dbms_id,a.dbms

(第三組)
SELECT dbms_id,dbms,客戶類型,count(c.type) as"數量"
FROM (select
case when dbms in('SYBASE','ORACLE') then'大客戶'
else'小客戶' end as"客戶類型",a.dbms_id,a.dbms,b.type
from msdb.dbo.MSdbms a
left join msdb.dbo.MSdbms_datatype b
on a.dbms_id=b.dbms_id)c
--where a.dbms =('SYBASE') OR a.dbms =('ORACLE')
--AND a.dbms_id>5
group by dbms_id,dbms,客戶類型

(第四組)
SELECT dbms_id,dbms,客戶類型,count(c.type) as"數量"
FROM (select
case when count(b.type)>20 then'大客戶'
else'小客戶' end as"客戶類型",a.dbms_id,a.dbms,b.type
from msdb.dbo.MSdbms a
left join msdb.dbo.MSdbms_datatype b
on a.dbms_id=b.dbms_id)c
--where a.dbms =('SYBASE') OR a.dbms =('ORACLE')
--AND a.dbms_id>5
group by dbms_id,dbms,客戶類型

### 2 個回答

1

iT邦高手 1 級 ‧ 2020-02-28 11:07:47

``````SELECT dbms_id
,dbms
,客戶類型
,count(c.type) as "數量"
FROM (
select
case when count(b.type)>20 then '大客戶' else'小客戶' end as "客戶類型"
,a.dbms_id
,a.dbms
,b.type
from msdb.dbo.MSdbms a
left join msdb.dbo.MSdbms_datatype b
on a.dbms_id=b.dbms_id
) c
--where a.dbms =('SYBASE') OR a.dbms =('ORACLE')
--AND a.dbms_id>5
group by dbms_id
,dbms
,客戶類型
``````

1

iT邦大師 1 級 ‧ 2020-02-28 11:12:14

``````select dbms_id,dbms,客戶類型,count(1) as "數量"
from (
select a.dbms_id,a.dbms
,case when count(b.type)>20 then'大客戶'
else'小客戶'
end as"客戶類型"
from msdb.dbo.msdbms a
left join msdb.dbo.msdbms_datatype b on a.dbms_id=b.dbms_id
group by a.dbms_id,a.dbms
) c
group by dbms_id,dbms,客戶類型
``````