摘要:SQL 運算
Q1,Q2:
Code:
create table #temp(GroupID varchar(50),Cost int)
insert into #temp values('A','50')
insert into #temp values('A','250')
insert into #temp values('A','200')
insert into #temp values('B','200')
insert into #temp values('B','400')
--Q1
select * from #temp
--Q2
select GroupId, Cost
,'RatioToGroup1' = Cost/SUM(Cost) OVER (PARTITION BY GroupId) --Don't work
,'RatioToGroup2' = 1. * Cost / SUM(Cost) OVER (PARTITION BY GroupId)
,'%' = convert(numeric(4,2), 1. * Cost / SUM(Cost) OVER (PARTITION BY GroupId) * 100)
,'GroupTotal' = SUM(Cost) OVER (PARTITION BY GroupId)
,'Avg' = AVG(Cost) OVER()
,'Count' = COUNT(Cost) OVER()
,'Min' = MIN(Cost) OVER()
,'Max' = MAX(Cost) OVER()
,'Total' = SUM(Cost) OVER ()
,'Row number' = row_number() over ( order by GroupID)
FROM #temp
drop table #temp
參考1:http://blog.darkthread.net/post-2012-02-13-ratio-to-report.aspx
參考2:http://msdn.microsoft.com/zh-tw/library/ms189461(v=sql.105)
------------------
熱愛生命 喜愛新奇 有趣的事物
過去 是無法改變
將來 卻能夠創造
希望使大家生活更便利
世界更美好
a guy who loves IT and life