我有一个MySQL查询的困难。
数据库是这样的:
Name Unit Int
---------------------
A1 Kilo 20
A1 Price 5
A2 Kilo 15
A2 Price 3
下面是我要做的:
| Name | Unit | int |
| A1 | Kilo | 20 |
| A1 | Price | 5 |
| A1 | K/P | 4 |
| A2 | Kilo | 15 |
| A2 | Price | 3 |
| A2 | K/P | 5 |
如您所见,我想为每个A1和A2插入一个新行,其结果为Kilo除以Price。
这是可能的吗?如果有,谁能给我指个正确的方向?我在这里迷路了,在过去的几天里,我试着扫荡互联网,但没有结果。我已经尝试了很多想法,不想让你厌烦。
您可以在SELECT中做这样的事情,使用最新的可用数据实时计算它(从而防止在Price/Kilo发生变化时硬存储可能过时的东西):
SELECT `kilo`.`name`, (`kilo`.`int` / `price`.`int`) AS 'K/P' FROM `stuff` AS `kilo`
LEFT JOIN `stuff` AS `price` ON (`kilo`.`name` = `price`.`name`)
WHERE `kilo`.`unit`='Kilo' AND `price`.`unit`='Price';
这将返回:
+------------+
| name | K/P |
|------------|
| A1 | 4 |
| A2 | 5 |
+------------+
如果你真的想把它插入到你的数据库中,你可以insert…选择以上选项。像这样:
INSERT INTO `stuff` (`name`, `unit`, `int`)
SELECT `kilo`.`name`, 'K/P', (`kilo`.`int` / `price`.`int`) FROM `stuff` AS `kilo`
LEFT JOIN `stuff` AS `price` ON (`kilo`.`name` = `price`.`name`)
WHERE `kilo`.`unit`='Kilo' AND `price`.`unit`='Price';
您可以使用
获得所有结果SELECT * FROM (
(SELECT * FROM your_table_name)
UNION
(SELECT r1.Name as Name, 'K/P', r1.Int/r2.Int
FROM your_table_name r1, your_table_name r2
WHERE r1.Name=r2.Name AND r1.Unit='Kilo' AND r2.Unit='Price'
)
) AS derived_table
ORDER BY Name, FIELD(Unit,'Kilo','Price','K/P');