时间戳

首页 > 解决方案 > T-sql 查询问题

mysql - T-sql 查询问题

问题描述

我有一个这样的查询

SELECT
    a.ID,
    b.ID,
    a.Wallet,
    b.Total
FROM
    a
INNER JOIN b ON b.IID= a.ID
WHERE
    b.ID= 19
AND b.IsActive = 1

我得到这个结果

a.ID      b.ID     Wallet      Total
1         19        1000        250
1         19        1000        500

当我运行这个查询

UPDATE a
INNER JOIN b ON b.IID= a.ID
SET a.Wallet= a.Wallet+ b.Total
WHERE
    b.ID= 19
AND b.IsActive = 1

我的 b 表钱包值从 1000 更新到 1250，但真正的结果将是 b 表钱包值从 1000 到 1750 我的失败在哪里？

****** 编辑 ******

表模式

CREATE TABLE IF NOT EXISTS `wallets` (
  `wallet_id` int(6) unsigned NOT NULL,
  `wallet` int(3) unsigned NOT NULL,
  PRIMARY KEY (`wallet_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `wallets` (`wallet_id`, `wallet`) VALUES
  ('1', '100'),
  ('2', '100');

  CREATE TABLE IF NOT EXISTS `totals` (
  `id` int(6) unsigned NOT NULL,
   `TotalID` int(6) unsigned NOT NULL,
  `wallet_id` int(6) unsigned NOT NULL,
  `total` int(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `totals` (`id`, `TotalID`,`wallet_id`,`total`) VALUES
  ('1', '19','1', '250'),
 ('2', '19','1', '750');

更新查询

UPDATE wallets inner join totals on totals.wallet_id=wallets.wallet_id
SET 
wallets.wallet=wallets.wallet+totals.total
Where totals.TotalID=19

选择查询

SELECT * from wallets inner join totals on totals.wallet_id=wallets.wallet_id

标签： mysqlsqljoin

解决方案

UPDATE  a
SET     wallet = wallet + b_sum
FROM    a
CROSS APPLY
        (
        SELECT  SUM(total) AS b_sum
        FROM    b
        WHERE   b.iid = a.id
                AND b.id = 19
                AND b.isActive = 1
        ) b

推荐阅读