t-sql查询问题

6qfn3psc 于 2021-06-25 发布在 Mysql

关注(0)|答案(4)|浏览(421)

我有一个这样的问题

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我的失败在哪里？

电子数据交换̇t型

表架构

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

sql mysql Join

来源：https://stackoverflow.com/questions/50192257/t-sql-query-issue

4条答案

按热度按时间

zujrkrfu1#

希望工作顺利

UPDATE a
SET a.Wallet = a.Wallet+f.valsum
FROM 
(
  SELECT  b.IID,SUM(b.Total) valsum
  FROM b
  WHERE b.IsActive = 1 AND b.ID= 19
  GROUP BY  b.IID
) f
WHERE ON a.ID = f.IID;

赞(0）回复(0）举报 2021-06-25

bxfogqkk2#

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

赞(0）回复(0）举报 2021-06-25

soat7uwm3#

UPDATE a
  SET a.Wallet= a.Wallet+ tmp.Total    
FROM a 
CROSS APPLY (SELECT SUM(Total) as Total
             FROM b 
             WHERE b.Id = a.Id 
             AND   b.ID= 19
             AND b.IsActive = 1
             ) tmp

赞(0）回复(0）举报 2021-06-25

dkqlctbz4#

您必须使用sum（b.total）和group by b.id来匹配该值。现在，更新查询只是匹配它找到的第一个id。

赞(0）回复(0）举报 2021-06-25