iT邦幫忙

0

ms sql 有關case when問題

最近剛學ms sql 和之前用的terdata有些不同
前面三組語法都可以正常的跑出資料,但最後一組的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
https://ithelp.ithome.com.tw/upload/images/20200228/20120843PlVeW6Lk14.png

(第二組)
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
https://ithelp.ithome.com.tw/upload/images/20200228/20120843MWTZs8Eu3P.png

(第三組)
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,客戶類型
https://ithelp.ithome.com.tw/upload/images/20200228/20120843VxSnKLgHLw.png

(第四組)
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,客戶類型
https://ithelp.ithome.com.tw/upload/images/20200228/20120843MA1SPQyRg5.png

2 個回答

1
純真的人
iT邦高手 1 級 ‧ 2020-02-28 11:07:47

因為裡面count(b.type)>20 這句count是group語句專屬...

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

回答時沒注意到 純真大大 已經回答了,這邊做一個補充。


你的問題在子查詢沒有使用Group卻使用了count彙總函數,如圖片

改成這樣就可以

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,客戶類型

清楚了,超級感謝^^

我要發表回答

立即登入回答