select u.types as TYPES,u.id as ID ,sum(u.num) as NUM,sum(u.total) as TOTAL,
cast(100*CAST(cast(u.num as decimal)/cast(isnull(nullif(sum(u.num),0),1)as decimal)as decimal(38, 2))as varchar)+'%' as PERCENTS from
(
select t.types as types,t.id as id,t.num as num,t.total as total, (isnull(t.percents,0)+isnull(t.offsetamount,0)) as percents from (
select top 100 percent t.types as types,t.id as id,sum(t.num) as num,sum(t.total) as total,sum(t.percents) as percents,
(case when sum(t.percents)over()!=100 then 100-sum(t.percents)over() else 0 end)offsetamount from (
select top 100 percent t.types as types,t.id as id,t.num as num,sum(t.num)over() as total,
cast(100*CAST(cast(t.num as decimal)/cast(sum(t.num)over() as decimal)as decimal(38, 2))as decimal) as percents from (
select top 100 PERCENT s.name as types,s.code as id, count(1) as num from HZ_AS_AssumpsitItem a inner join
(select distinct iid,eid from HZ_AS_ProjectEmpRequest where eid='4038133293514762' )z on a.id=z.iid
inner join sys_departmentgroup d on d.id = a.projectdid inner join (select * from ( select
(case when ca.iscustom = '2' then '2' when ca.iscustom='1' then '1' end)ifdefined,
(case when cs.CertificateType='1' then '1' when cs.CertificateType='2' then '2' when
cs.CertificateType='3' then '3' when cs.CertificateType='4' then '4' else ca.CertificateType end)CertificateType,
ca.projectid,ca.certificatecode,ca.status,ca.CertificateType as CertificateType1, ca.ReturnLetter,ca.CertificateMode,ca.sendconfirmationid,
row_number()over(partition by left(certificatecode,10) order by updated_time )rn from hz_certificateapply ca left join
(select distinct certificateapplyid,CertificateType from HZ_CERTIFICATESAMPLE )cs on cs.certificateapplyid = ca.id ) c where c.rn = 1) ca on ca.ProjectId=a.id
inner join sys_dict s on s.code=ca.CertificateType and s.parent_id in(select s1.id from sys_dict s1 where s1.code='hzlb')
where z.eid='4038133293514762' and iyear between cast('2018' as int) and cast('2020' as int) and d.id='19'
and needreport in ( 1,0 ) group by s.name,s.code order by num desc )t )t group by t.id,t.types,t.percents order by num desc )t
union ALL select da.name,da.code,0,0,0 from sys_dict da where da.parent_id in
(select s1.id from sys_dict s1 where s1.code='hzlb')
)u group by u.id,u.types order by cast(u.id as bigint)
u.num 改列已經在sum中了，執行的時候報錯
選擇串列中的列 'u.num' 無效，因為該列沒有包含在聚合函式或 GROUP BY 子句中。
拜托大家分析下，這個是什么原因導致？
uj5u.com熱心網友回復：
cast(100*CAST(cast(u.num as decimal) 這里沒有用聚合函式轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16156.html
標籤：疑難問題
上一篇：SQL求助
下一篇：原始碼安裝redis 5.0.9