想請問以下語法如何抓出 '20歲以下' 佔a前25% 的所有a跟b和c~ 因為以下語法是列出依a排序將所有的資料一筆一筆列出~但我希望是佔前25%的加總><
SELECT TOP 25 percent case when (SUBSTRING(生日,1,4)) BETWEEN 1998 AND 2018 then '20歲以下' end as '年齡'
,sum(a) as 'a'
,sum(b/10000) as 'b(萬)'
,sum(b/10000)/count() as 'c'
,count() as '人數'
FROM member JOIN tb001 AS O1 on 身份證字號=會員編號
WHERE '年齡' IN ( SELECT '年齡'
FROM member JOIN tb001 AS O2 on 身份證字號=會員編號
WHERE o2.A = o1.A
ORDER BY [A] DESC)
AND (生日 <> null or 生日<>'') and (SUBSTRING(生日,1,4))>1998 and (SUBSTRING(生日,1,4))<2018 and 客服姓名<>'未分配'
GROUP BY case when (SUBSTRING(生日,1,4)) BETWEEN 1998 AND 2018 then '20歲以下' end ,B
ORDER BY A DESC
這是上面語法跑出來的資料，但是他是逐一列出顯示
但是我希望資料可以跑出的是佔前25%的加總~
已邀請的邦友 {{ invite_list.length }}/5
1.假設原 SQL 是對的
2.其中"平均資產"改用 sum(CC)/sum(EE),而未使用 sum(DD) <= 可能不合理
SELECT AA as '年齡',
sum(BB) as '總會數',
sum(CC) as '總資產(萬)',
round(sum(CC)/sum(EE),0) as '平均資產',
sum(EE) as '人數'
FROM (
SELECT TOP 25 percent case when (SUBSTRING(生日,1,4)) BETWEEN 1998 AND 2018 then '20歲以下' end as 'AA'
,sum(總會數) as 'BB'
,sum(總資產/10000) as 'CC'
,count() as 'EE'
FROM [dbo].[VW_BS_會員基本資料1]
...略
ORDER BY 總會數 DESC
) AS TempTable
where 1=1
group by AA
having sum(EE) >0
原 SQL 應可優化:
1.以下應該可去除
'年齡' IN ( SELECT '年齡'
FROM [dbo].[VW_BS_會員基本資料1] JOIN SPL_DIS..PRT429 AS O2 on 身份證字號=會員編號
WHERE o2.總資產 = o1.總資產
ORDER BY [總資產] DESC)
2.
生日 <> null
改為 生日 is not null
3.
case when (SUBSTRING(生日,1,4)) BETWEEN 1998 AND 2018 then '20歲以下' end
改為
case when SUBSTRING(生日,1,4)>1998 and SUBSTRING(生日,1,4)<2018 then '20歲以下' end
rogeryao
謝謝您的指導~!
剛剛沒注意到9點多你還有回復，優化的部分我再來修改~~感謝您~
錯誤訊息 行 77 => 那行 ...前後是否有多的單引號
另外 , round(sum(CC)/sum(EE),0) as '平均資產',
1.因為你已經下好 SQL (類似明細表)才要做 sum ,大概就是這樣解.
2.可能還有一種解法更好,就是把你原本的 group 跟 sum 去除 , 只在最外層做一次 group 跟 sum ;這樣就不用做兩次 group 跟 sum
iThome鐵人賽
看影片追技術