|
#2
sdta2022-04-10 21:26
 程序代码：CREATE CURSOR ta (js n(10), time1 t, time2 t) INSERT INTO ta VALUES (0, {^2022/04/10 10:21:45}, {^2022/04/10 11:21:45}) INSERT INTO ta VALUES (0, {^2022/04/10 11:21:45}, {^2022/04/10 12:21:45}) INSERT INTO ta VALUES (0, {^2022/04/10 12:21:45}, {^2022/04/10 13:21:45}) INSERT INTO ta VALUES (0, {^2022/04/10 13:21:45}, {^2022/04/10 14:21:45}) CREATE CURSOR tb (订单号 c(10), 订货数量 n(10), 操作时间 t) INSERT INTO tb VALUES ("A001", 100,{^2022/04/10 12:01:00}) INSERT INTO tb VALUES ("A002", 120,{^2022/04/10 10:30:23}) INSERT INTO tb VALUES ("A005", 80,{^2022/04/10 11:10:55}) SELECT SUM(订货数量) sl, time1, time2 FROM tb, ta WHERE BETWEEN(操作时间, time1, time2) GROUP BY time1, time2 INTO CURSOR tc UPDATE ta SET js = sl FROM tc WHERE ta.time1 = tc.time1 AND ta.time2 = tc.time2 SELECT ta BROWSE [此贴子已经被作者于2022-4-10 21:31编辑过] |
表A：
js time1 time2
0 2022/04/10 10:21:45 2022/04/10 11:21:45
0 2022/04/10 11:21:45 2022/04/10 12:21:45
0 2022/04/10 12:21:45 2022/04/10 13:21:45
0 2022/04/10 13:21:45 2022/04/10 14:21:45
表B：
订单号 订货数量 操作时间 、...（计算相关字段：订货数量，操作时间）
A001 100 2022/04/10 12:01:00
A002 120 2022/04/10 10:30:23
A005 80 2022/04/10 11:10:55
需求：按表A的时间范围，求表B的sum(订货数量)，保存到表A的字段js中
求助：SQL语句应该怎么写（一时想不出这种没有关键字段的汇总应该怎么写了）
按上面的举例，表A第一条记录的js=120+80=200，第二条记录的js=100
[此贴子已经被作者于2022-4-10 20:38编辑过]
