如何获取聚合/分组查询的组成行？

Question

我正在尝试实现一个接口来翻转行项目的布尔状态，

在使用聚合的行集时，我陷入了更新底层行的正确方法。

表格

declare @item table(
    id int not null primary key,
    amount money not null,
    is_paid bit not null,
    client varchar(10) not null)
insert into @item values
    (1, 9.50, 0, 'Client A'), 
    (2, 11.50, 0, 'Client A'),
    (3, 20.00, 1, 'Client B')

查询

select sum(amount) as total_amount, is_paid, client
from @item
group by is_paid, client

结果

场景

现在假设上面的结果位于一个网格中，如果 is_paid=0，则带有“支付”按钮。

一行映射到单击“付款”后要更新的一行或多行的 ID 。

我的第一个想法是像这样更新：

update @item
set is_paid=1
where client='Client A'

但是，当在显示界面的时间和用户按下“支付”的时间之间插入“客户端 A”的附加行时，这种情况就会崩溃（如果我错了，请纠正我） ”。

问题：由于这似乎是一个相当简单的场景，处理它的典型方法是什么？到目前为止我唯一能想到的就是将聚合/分组移至应用程序逻辑。

Answer 1

您可以创建临时表来存储 client_id 和 item_id。然后通过将该表与您的项目表连接来选择聚合。这样，当 is_paid = 1 时，您只能更新 item 表中在临时表中具有对应记录的记录。例如：

// Assuming id in @item has been rename to item_id and client_id has been added to @item

declare @active table(
    client_id int not null,
    item_id int not null
);
insert into @active select client_id, item_id from @item;

select sum(@item.amount) as total_amount, @item.is_paid, @item.client_name
from @item inner join @active in @item.item_id = @active.item_id and @item.client_id = @active.client_id
group by @item.is_paid, @item.client_id
order by @item.client_name

update @item from @item inner join @active on @item.client_id = @active.client_id and @item.item_id = @active.item_id
set is_paid=1
where client='Client A'

或者，您可以向 @item 添加 create_time 列。这样，您只能更新在特定时间之前创建的那些。例如：

select sum(amount) as total_amount, is_paid, client, max(create_time) as last_time
from @item
group by is_paid, client

update @item
set is_paid=1
where client='Client A' and create_time <= last_time

Answer 2

您担心在读取数据和更新数据之间数据可能会发生变化吗？

您将需要以可重复读取隔离级别启动事务（我认为）。

http://msdn.microsoft.com/en-我们/库/aa259216(v=sql.80).aspx

Answer 3

你的模式是错误的。您需要第三个链接表来存储客户端 ID 和客户端名称，然后在项目表中存储 clientID 列。然后您可以正确更新：

UPDATE @item SET is_paid = 1
WHERE @item.Clientid = (SELECT client.clientID from Client 
WHERE Client.ClientName = 'Client A') AND @item.ID IN
(1, 2) -- Your list of IDs checked to mark as paid in the grid

Answer 4

我对此提出了自己的答案，我没有看到它的缺点，但是我欢迎任何人指出它。

我喜欢的是如何准确地知道要更新哪些行。

declare @MyItem table(
    id int not null primary key,
    amount money not null,
    is_paid bit not null,
    client varchar(10) not null)
insert into @MyItem values
    (1, 9.50, 0, 'Client A'), 
    (2, 11.50, 0, 'Client A'),
    (3, 20.00, 1, 'Client B')
select dbo.SumKeys(id) as [IDs], sum(amount) as total_amount, is_paid, client
from @MyItem
group by is_paid, client

我不喜欢的是我花了半天多的时间才让这段代码正常工作，因为我正在与（对我来说）针对 sql server 托管的 clr 进行编程的奇怪现象作斗争。

无论如何，我做了一个聚合，将逗号分隔的 ID 列表直接放入我的查询中。

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToDuplicates = true,
    IsInvariantToNulls = true,
    MaxByteSize = -1
    )]
public struct SumKeys : IBinarySerialize
{
    private readonly static char sep = ',';
    private SqlString result;
    public void Init()
    {
        result = string.Empty;
    }
    public void Accumulate(SqlInt32 value)
    {
        if (!value.IsNull && !Contains(value))
            this.Add(value);
    }
    private void Add(SqlInt32 value)
    {
        this.result += Wrap(value);
    }
    private void Add(string value)
    {
        Add(Convert.ToInt32(value));
    }
    private static string Wrap(SqlInt32 value)
    {
        return value.Value.ToString() + sep;
    }
    private bool Contains(SqlInt32 value)
    {
        return this.result.Value.Contains(Wrap(value));
    }
    public void Merge(SumKeys group)
    {
        foreach (var value in Items(group))
            if (!this.Contains(value))
                this.Add(value);
    }
    private static IEnumerable<SqlInt32> Items(SumKeys group)
    {
        foreach (var value in group.result.Value.Split(sep))
        {
            int i;
            if (Int32.TryParse(value, out i))
                yield return i;
        }
    }
    public SqlString Terminate()
    {
        return this.result.Value.TrimEnd(sep);
    }
    public void Read(System.IO.BinaryReader r)
    {
        this.result = r.ReadString();
    }
    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this.result.Value.TrimEnd(sep));
    }
}