mysql - 使用查询结果更新表

Question

在 MySQL 数据库中，我有下表

CREATE TABLE `store` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `storetypeID` int(10) unsigned NOT NULL,
  `storename` varchar(100) NOT NULL,
  `VATnumber` char(9) NOT NULL,
  `visits` int(10) unsigned DEFAULT '0000000000',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  UNIQUE KEY `VATnumber_UNIQUE` (`VATnumber`),
  KEY `storeID_idx` (`storetypeID`),
  CONSTRAINT `storeID` FOREIGN KEY (`storetypeID`) REFERENCES `storetype` (`ID`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=947 DEFAULT CHARSET=utf8;

CREATE TABLE `expenses` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `storeID` int(10) unsigned DEFAULT NULL,
  `expensedate` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `storeID_idx` (`storeID`),
  CONSTRAINT `storeIDidx` FOREIGN KEY (`storeID`) REFERENCES `store` (`ID`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7502 DEFAULT CHARSET=utf8;

使用以下查询，我可以获得每个商店的费用数量。

SELECT COUNT(*) AS visits, storeID FROM taxfriend.expenses GROUP BY storeID ORDER BY visits desc

有了这个结果，我想用查询中的字段访问来更新表存储中的字段访问。一种选择是编写一个小子例程(例如使用 VB)。

有什么方法可以在 MySQL 中创建 UPDATE 查询来完成此操作吗？

Answer 1

您可以在 UPDATE 查询中使用 JOIN。

UPDATE store AS s
JOIN (SELECT COUNT(*) AS visits, storeID 
      FROM taxfriend.expenses 
      GROUP BY storeID) AS e
ON s.ID = e.storeID
SET s.visits = e.visits