我对 MySQL 很陌生，正在做一个学校项目。我有这张桌子：
CREATE TABLE IF NOT EXISTS `Reserva` (
`idReservacion` int(10) NOT NULL AUTO_INCREMENT,
`importe` int(10) DEFAULT NULL,
`fecha` date DEFAULT NULL,
`idCliente` int(11) DEFAULT NULL,
PRIMARY KEY (`idReservacion`),
KEY `idCliente` (`idCliente`),
CONSTRAINT `Reserva_ibfk_1` FOREIGN KEY (`idCliente`) REFERENCES `Cliente` (`idcliente`)
) ENGINE=InnoDB AUTO_INCREMENT=60001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
我正在尝试通过使用下一个表的更新使 Importe 成为一个生成的字段
CREATE TABLE IF NOT EXISTS `PaseDeAbordar` (
`idPaseDeAbordar` int(11) NOT NULL AUTO_INCREMENT,
`hora` time DEFAULT NULL,
`idReservacion` int(5) NOT NULL,
`idAsiento` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`idVuelo` int(11) NOT NULL,
`precioc` int(10) DEFAULT NULL,
PRIMARY KEY (`idPaseDeAbordar`),
KEY `idReservacion` (`idReservacion`),
KEY `idAsiento` (`idAsiento`),
KEY `idVuelo` (`idVuelo`),
CONSTRAINT `PaseDeAbordar_ibfk_1` FOREIGN KEY (`idVuelo`) REFERENCES `Vuelo` (`idvuelo`),
CONSTRAINT `PaseDeAbordar_ibfk_2` FOREIGN KEY (`idReservacion`) REFERENCES `Reservacion` (`idreservacion`),
CONSTRAINT `PaseDeAbordar_ibfk_3` FOREIGN KEY (`idAsiento`) REFERENCES `Asiento` (`idasiento`)
) ENGINE=InnoDB AUTO_INCREMENT=225001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Pasedeabordar 有来自 Vuelo 和 Asiento 的外键，这是它们的结构：
CREATE TABLE IF NOT EXISTS `Asiento` (
`idAsiento` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`idClase` int(11) NOT NULL,
`precio` int(11) NOT NULL,
PRIMARY KEY (`idAsiento`),
KEY `idClase` (`idClase`),
CONSTRAINT `Asiento_ibfk_1` FOREIGN KEY (`idClase`) REFERENCES `Clase` (`idclase`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
航班：
CREATE TABLE IF NOT EXISTS `Vuelo` (
`idVuelo` int(8) NOT NULL AUTO_INCREMENT,
`matriculaAvion` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`origen` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`destino` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`fechaVuelo` date NOT NULL,
`horaSalida` time NOT NULL,
`horaLlegada` time NOT NULL,
`duracion` time NOT NULL,
`tieneEscala` varchar(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'NO',
`precio` int(10) NOT NULL,
PRIMARY KEY (`idVuelo`),
KEY `origen` (`origen`),
KEY `destino` (`destino`),
KEY `matriculaAvion` (`matriculaAvion`),
CONSTRAINT `Vuelo_ibfk_1` FOREIGN KEY (`origen`) REFERENCES `Terminal` (`idterminal`),
CONSTRAINT `Vuelo_ibfk_2` FOREIGN KEY (`destino`) REFERENCES `Terminal` (`idterminal`),
CONSTRAINT `Vuelo_ibfk_3` FOREIGN KEY (`matriculaAvion`) REFERENCES `Avion` (`matricula`)
) ENGINE=InnoDB AUTO_INCREMENT=5501 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Importe 应该以这种方式生成：
reservacion.importe=(precio from asiento+precio from vuelo) where paseabordar.reservacionid=importe.reservacionid
UPDATE reservacion r, pasedeabordar pa, vuelo v, asiento a
SET r.importe=SUM(v.precio+a.precio)
WHERE pa.idreservacion=r.idreservacion
AND pa.idAsiento=a.idAsiento
AND pa.idVuelo=v.idVuelo;
但这给我带来了一个错误
Error(1111): Invalid use of group function
关于我做错了什么的任何想法？
SUM()是一个分组函数，用于将同一列的单元格相加。您只是试图将同一行中的两个值相加。
r.importe=v.precio+a.precio 应该管用。
顺便说一句，显式连接表被认为是一种很好的做法，如下所示：
UPDATE reservacion r
JOIN pasedeabordar pa ON pa.idreservacion = r.idreservacion
JOIN vuelo v ON pa.idVuelo = v.idVuelo
JOIN asiento a ON pa.idAsiento = a.idAsiento
SET r.importe = v.precio + a.precio;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句