問題描述

我有一個費用表，例如:

I have an expense table like:

WorkWeek  Catg  Item    Cost
WorkWeek1 Cat1  Item1   Price
WorkWeek1 Cat1  Item2   Price
WorkWeek1 Cat1  Item3   Price
WorkWeek1 Cat1  Item4   Price
WorkWeek1 Cat2  Item1   Price
WorkWeek1 Cat2  Item5   Price
WorkWeek1 Cat2  Item6   Price
WorkWeek1 Cat3  Item1   Price
WorkWeek1 Cat3  Item5   Price
.
.
WorkWeekA CatB  ItemC   Price

這就是我現在的做法:

select top(1)
     (select sum(cost) from DataTable where Catg like 'Cat1') as Cat1TotalCost
    ,(select sum(cost) from DataTable where Catg like 'Cat2') as Cat2TotalCost
    ,(select sum(cost) from DataTable where Catg like 'Cat3') as Cat3TotalCost
    .
    .
    .
    .
from DataTable where WorkWeek like 'WorkWeek1'

如果我不使用 top 1 那么我會得到相同的總和，就像數千行一樣重復.此外，我的做法只占 1 個工作周.:(

And If I don't use the top 1 then I get the same sums repeated over like thousands of rows. Also, my way of doing it only accounts for 1 workweek. :(

我想創建一個表格，其中每個工作周的總費用取決于每個類別，例如:

I want to create a Table with each workweeks total expense depending in each category something like :

WorkWeek1   Cat1TotalCost   Cat2TotalCost   Cat3TotalCost
WorkWeek2   Cat1TotalCost   Cat2TotalCost   Cat3TotalCost
.
.

推薦答案

試試這個:

select
    workweek
    ,(select sum(cost) from DataTable where Catg = 'Cat1') as Cat1TotalCost
    ,(select sum(cost) from DataTable where Catg = 'Cat2') as Cat2TotalCost
    ,(select sum(cost) from DataTable where Catg = 'Cat3') as Cat3TotalCost
    .
    .
    .
    .
from DataTable
group by Workweek

現在，您正在按工作周字段分組.此外，我將 like 更改為 = 以使其稍微快一些.

Now, you are grouping by the workweek field. Also, I changed the like to = to make it slightly faster.

這篇關于創建/附加表，其中包含按不同類別分組的值的總和的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！