时间戳

首页 > 解决方案 > 关键字 GROUP 附近的语法不正确

sql-server - 关键字 GROUP 附近的语法不正确

问题描述

我有以下更新语句，但出现错误：

关键字“GROUP”附近的语法不正确

执行时。

CREATE TRIGGER [dbo].[target_cumulative]
ON [dbo].[Appointments]
AFTER INSERT, UPDATE
AS 
BEGIN
    IF TRIGGER_NESTLEVEL() > 1
        RETURN

    UPDATE T1
    SET T1.[Target_Cumulative] = SUM(ISNULL(CAST([TARGET] AS FLOAT), 0))
    FROM dbo.Appointments AS T1
    INNER JOIN inserted AS I ON I.UniqueId = T1.UniqueId
    GROUP BY CONVERT(DATE, T1.StartDate), T1.ResourceId;
END;

这是我的应用程序的外观 在此处输入图像描述 我在这里错过了什么？

标签： sql-servergroup-bysql-update

解决方案

干得好：

create table appt (target int, target_cumulative int, unique_id int, resource_id int, start_date datetime);
create table inserted(unique_id int, value int);

insert into appt values(10, 0, 1, 1, '2019-07-26 08:00:00');
insert into appt values(20, 0, 2, 2, '2019-07-26 07:00:00');
insert into appt values(30, 0, 3, 1, '2019-07-26 10:00:00');
insert into appt values(10, 0, 4, 1, '2019-07-26 11:00:00');
insert into appt values(20, 0, 5, 2, '2019-07-26 09:00:00');

insert into inserted values(1, 10);
insert into inserted values(2, 10);
insert into inserted values(3, 10);
insert into inserted values(4, 10);
insert into inserted values(5, 10);

update appt set target_cumulative=xtarget
from (select t1.resource_id, sum(t1.target)as xtarget from appt t1
inner join inserted as i on t1.unique_id=i.unique_id
group by convert(date, t1.start_date), t1.resource_id) t2
where appt.resource_id=t2.resource_id;

这是您可以根据需要检查和修改的小提琴：dbfiddle

推荐阅读