从同一个表中的行计算SQL存储过程中的总计

I'm trying to build a stored procedure that will allow me to subtract a value based on a specific condition from another value based on another condition but my problem is that all rows reside in the same table. It's probably easier to show you what I mean.

我正在尝试构建一个存储过程,允许我根据另一个条件从另一个值中减去基于特定条件的值,但我的问题是所有行都驻留在同一个表中。告诉你我的意思可能更容易。

Imagine if you have the following table (kept simple for simplicity sake)

想象一下,如果你有下表(为简单起见保持简单)

ID    Name     Total    Date
1     User1    5        20111106
2     User2    6        20111106
3     User3    7        20111105
4     User1    8        20111106
5     User3    4        20111117
6     User1    4        20111117
7     User5    1        20111105
8     User1    9        20111105
9     User3    5        20111106
10    User1    3        20111117

First I want to sum the total by date just for user1 and group by date

首先,我想按日期对用户1和按日期分组总计

Total     Date
9        20111105
13       20111106
7        20111117

Next I want to sum the total by date for all other users excluding user1 and group by date

接下来,我想总结所有其他用户的日期总计,不包括user1和按日期分组

Total     Date
8         20111105
11        20111106
4         20111117

and finally I somehow want my stored procedure to take the above data and give me the following. I want to subtract the total obtained from "User1" from the total obtained from all other users but using the date as the matching parameter.

最后我想以某种方式希望我的存储过程获取上述数据并给我以下内容。我想从从所有其他用户获得的总数中减去从“User1”获得的总数,但是使用日期作为匹配参数。

and the final result would be as follows:

最终结果如下:

Total     Date
-1        20111105
-2        20111106
-3        20111117

Is this making sense???

这有意义吗?

Thanks.

3 个解决方案

#1

This seems to work.

这似乎有效。

with User1Totals as 
(
  select [Name], sum(Total) as Total, [Date]
  from YourTable
  where [Name] = 'User1'
  group by [Name], [Date]
), 
OtherUsersTotals as
(
  select sum(Total) as Total, [Date]
  from YourTable
  where [Name] <> 'User1'
  group by [Date]
)

select isnull(t2.Total, 0) - isnull(t1.Total, 0), coalesce(t2.[Date], t1.[Date])
from User1Totals t1
full outer join OtherUsersTotals t2 on t1.[Date] = t2.[Date]

This handles the scenarios in your sample data, as well as two other scenarios - 1) when User1 has an entry on a day that other users do not, and 2) when other users have data on a day that User1 does not.

这样可以处理示例数据中的场景,以及其他两个场景 - 1)当User1在其他用户没有的一天有条目时,以及2)当其他用户在User1没有的那天有数据时。

Working example here - http://data.stackexchange.com/*/q/120224/

这里的工作示例 - http://data.stackexchange.com/*/q/120224/

#2

I think this will work...

我认为这会奏效......

DECLARE @Name VARCHAR(20)
SET @Name='User1'
SELECT
    SUM(
        CASE
            WHEN Name=@Name THEN Total*-1
            ELSE Total
        END
    ) [Total Adj]
    ,Date
FROM Table
GROUP BY Date

#3

You can achieve this result by creating temp tables.

您可以通过创建临时表来实现此结果。

Step # 01: Create a temp1 table and bring your first required result for only user 1 in it.

步骤#01:创建一个temp1表,并为其中只有用户1带来您的第一个必需结果。

CREATE TABLE Temp1
(
ID IDENTITY (1, 1) NOT NULL,
Total int,
Date datetime)

insert into Temp1(Total , Date)
values (select sum(Total) as Total , Date where Name = 'user1' group by Date order by Date asc)

Step # 02: Create a temp2 table and fill it with other user inforamtion.

步骤#02:创建一个temp2表并用其他用户信息填充它。

CREATE TABLE Temp2
(
ID IDENTITY (1, 1) NOT NULL,
Total int,
Date datetime)

insert into Temp1(Total , Date)
values (select sum(Total) as Total , Date where Name is not 'user1' group by Date

order by Date asc)

按日期排序asc)

Step # 03: Now subtract records and drop temp tables.

步骤#03:现在减去记录并删除临时表。

select (t2.Total - t1.Total) as Total , Date  from temp2 t2
inner join temp1 t1 on t1.ID = t2.ID;

drop table temp1;
drop table temp2;

#1