查询 Mysql 的唯一值 [英] Query Mysql For Unique Values
问题描述
我有一个燃料数据库表，其中包含以下详细信息
I have a database table for fuel with the following details
fID 是燃料 id，vID 是车辆 id，volume 是以升为单位的燃料量，price 是每升燃料的价格，date 是车辆加满的日期，type 是类型/等级的燃料.
The fID is the fuel id, vID is the vehicle id, volume is the amount of fuel in litres, price is the price of fuel per litre, date is the date of filling up the vehicle, type is the type/grade of fuel.
我想做一个查询，通过将每升燃油成本乘以加满燃油的体积来返回燃油成本，再加上另一个距离字段，该字段应该是前一个加注仪表与当前加注仪表之间的差值.最新仪表.表中会有很多车，所以我只想返回特定车辆的记录(vID会重复但fID是唯一的).
I wanted to make a query that would return the cost of fuel by multiplying the cost of fuel per litre with the volume of fuel filled up, plus another field for distance which should be the difference between the previous fill up meter and the latest meter. In the table, there will be a lot of cars, so I just want to return records of a specific vehicle (the vID will be repeated but the fID is unique).
到目前为止，我有以下查询，但如果数据库中只有一个燃料条目，它会返回空，而且我无法弄清楚如何计算 Mysql 中的成本.
I have the following query so far but it returns empty if there's only one fuel entry in the database, plus I can't figure out how to calculate the cost in Mysql.
Select
t1.*, t2.meter - t1.meter as distance
From fuel t1 left join fuel t2
on t1.date > t2.date
where t1.vID = t2.vID
order by t1.date desc
我应该如何以正确的方式去做?
How should I go about it the right way?
这是架构:
CREATE TABLE IF NOT EXISTS `fuel` (
`fID` int(11) NOT NULL AUTO_INCREMENT,
`vID` int(11) DEFAULT NULL,
`volume` varchar(100) DEFAULT NULL,
`price` varchar(100) DEFAULT NULL,
`meter` varchar(100) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`vendorID` int(11) DEFAULT NULL,
`notes` text,
`type` varchar(50) DEFAULT NULL,
PRIMARY KEY (`fID`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;
INSERT INTO `fuel` (`fID`, `vID`, `volume`, `price`, `meter`, `date`, `vendorID`, `notes`, `type`)
VALUES
(7, 28, '15', '800', '5000', '2018-05-27 05:53:00', NULL, 'Entry number one for this vehicle', 'Unleaded'),
(6, 27, '5', '1000', '2000', '2018-05-27 05:50:00', NULL, 'This is the second fill up for this vehicle', 'Leaded'),
(5, 27, '15', '1200', '1200', '2018-05-27 04:58:00', NULL, 'Hey there vendors!', 'Leaded'),
(9, 26, '25', '750', '4500', '2018-05-27 05:57:00', NULL, NULL, 'Leaded'),
(10, 26, '20', '750', '6000', '2018-05-27 05:58:00', NULL, NULL, 'Leaded');
这就是我想输出将要返回的数据的方式.此图以获取车辆 vID 27 的所有燃料历史记录为例.第一个条目应返回距离 0.第二个条目应从 vID 为 27(此处为 800)的先前记录中减去其当前仪表读数...任何想法如何实现这一目标?
This is how I want to output the data that will be returned. This picture takes an example of getting all fuel history logs for a vehicle vID 27. The first entry should return distance 0. The second one should subtract its current meter reading from the previous record that also has a vID of 27 (here its 800)... Any ideas how I can achieve this?
按照尼克的回答，我在 PHP 中实现了以下内容，但它抛出了一个错误.但是，当我在 mysql sql 命令中运行它时，它会按预期返回结果...
Following Nick's answer, I implemented the following in PHP, but it throws an error. However, when I run it in mysql sql command it returns the results as expected...
$vID = 27;
$pdo = $this -> con -> query("
select date_format(f1.date, '%y-%m-%d %H:%i:%s') as date,
f1.meter as mileage,
case when f2.meter is null then 0
else f1.meter - f2.meter end as distance,
f1.volume, f1.volume * f1.price as cost from fuel f1
left join fuel f2
on f2.date = (select max(date)
from fuel where fuel.vID = f1.vID and fuel.date < f1.date)
where f1.vID = ? order by f1.date ");
if($pdo -> execute([$vID]) && $pdo -> rowCount()) {
$res = $pdo -> fetchAll(5);
$this -> response($this -> json($res), 200); // send fuel logs
} else {
$this -> response('', 204); // If no records "No Content" status
}
这是我通过php执行代码后得到的错误.
Here is the error I get after executing the code through php.
推荐答案
此查询将为您提供所需的各个行.该查询的工作原理是使用当前加注日期之前的此车辆的最新加注日期将燃料与自身连接起来.如果之前没有填充日期，则 CASE 表达式为距离生成 0 结果.
This query will give you the individual rows that you want. The query works by joining fuel to itself using the latest fill-up date for this vehicle prior to the current fill-up date. If there is no prior fill-up date, the CASE expression produces a 0 result for distance.
SELECT DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date,
f1.meter AS mileage,
CASE WHEN f2.meter IS NULL THEN 0
ELSE f1.meter - f2.meter
END AS distance,
f1.volume,
f1.volume * f1.price AS cost
FROM fuel f1
LEFT JOIN fuel f2
ON f2.date = (SELECT MAX(date)
FROM fuel
WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
WHERE f1.vID = 27
ORDER BY f1.date
输出:
date mileage distance volume cost
18-05-27 04:58:00 1200 0 15 18000
18-05-27 05:50:00 2000 800 5 5000
如果您不想在 PHP 中对行进行求和，该查询可以生成一个汇总行，并对查询进行微小更改(添加聚合函数和 GROUP BY WITH ROLLUP 子句):
If you don't want to sum the rows in PHP, the query can produce a summary row with a minor change to the query (adding an aggregation function and a GROUP BY WITH ROLLUP clause):
SELECT DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date,
f1.meter AS mileage,
CASE WHEN f2.meter IS NULL THEN 0
ELSE f1.meter - f2.meter
END AS distance,
f1.volume,
SUM(f1.volume * f1.price) AS cost
FROM fuel f1
LEFT JOIN fuel f2
ON f2.date = (SELECT MAX(date)
FROM fuel
WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
WHERE f1.vID = 27
GROUP BY f1.date WITH ROLLUP
输出:
date mileage distance volume cost
18-05-27 04:58:00 1200 0 15 18000
18-05-27 05:50:00 2000 800 5 5000
(null) 2000 800 5 23000
您可以通过日期列为null 来检测PHP 中的摘要行.演示
You can detect the summary row in PHP by the fact that the date column is null. Demo
这篇关于查询 Mysql 的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
