Mysql对不同表格预订天价的计算和排序

Question

我在尝试从不同的表格中计算预订价格(基于日期范围)并使用结果排序时遇到了问题。所有的结果似乎都是一样的。我尝试了"group by“，如果有太多的记录，就会花费太多的时间。做到这一点的最佳解决方案是什么？谢谢。

Sql；

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `vehicles` (
  `id` int(11) NOT NULL,
  `title` varchar(300) DEFAULT NULL,
  `status` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `vehicles` (`id`, `title`, `status`) VALUES
(1, 'Renault Symbol', 1),
(2, 'Renault Clio', 1),
(3, 'Hyundai i20', 1),
(4, 'Hyundai i10', 1),
(5, 'Hyundai Accent', 1),
(6, 'Volkswagen up!', 0),
(7, 'Volkswagen Polo', 1),
(8, 'Ford Fiesta', 1),
(9, 'Fiat Panda', 1),
(10, 'Fiat Tipo', 1);

CREATE TABLE `vehicles_prices` (
  `id` int(11) NOT NULL,
  `vehicle_id` int(11) DEFAULT NULL,
  `day` date DEFAULT NULL,
  `price` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `vehicles_prices` (`id`, `vehicle_id`, `day`, `price`) VALUES
(1, 1, '2021-10-01', '111'),
(2, 1, '2021-10-02', '112'),
(3, 1, '2021-10-03', '113'),
(4, 1, '2021-10-04', '114'),
(5, 1, '2021-10-05', '115'),
(6, 1, '2021-10-06', '116'),
(7, 1, '2021-10-07', '117'),
(8, 1, '2021-10-08', '118'),
(9, 1, '2021-10-09', '119'),
(10, 1, '2021-10-10', '120'),
(11, 2, '2021-10-01', '121'),
(12, 2, '2021-10-02', '122'),
(13, 2, '2021-10-03', '123'),
(14, 2, '2021-10-04', '124'),
(15, 2, '2021-10-05', '125'),
(16, 2, '2021-10-06', '126'),
(17, 2, '2021-10-07', '127'),
(18, 2, '2021-10-08', '128'),
(19, 2, '2021-10-09', '129'),
(20, 2, '2021-10-10', '130'),
(21, 3, '2021-10-01', '131'),
(22, 3, '2021-10-02', '132'),
(23, 3, '2021-10-03', '133'),
(24, 3, '2021-10-04', '134'),
(25, 3, '2021-10-05', '135'),
(26, 3, '2021-10-06', '136'),
(27, 3, '2021-10-07', '137'),
(28, 3, '2021-10-08', '138'),
(29, 3, '2021-10-09', '139'),
(30, 3, '2021-10-10', '140'),
(31, 4, '2021-10-01', '100'),
(32, 4, '2021-10-02', '102'),
(33, 4, '2021-10-03', '103'),
(34, 4, '2021-10-04', '104'),
(35, 4, '2021-10-05', '105'),
(36, 4, '2021-10-06', '106'),
(37, 4, '2021-10-07', '107'),
(38, 4, '2021-10-08', '108'),
(39, 4, '2021-10-09', '109'),
(40, 4, '2021-10-10', '110'),
(41, 7, '2021-10-01', '201'),
(42, 7, '2021-10-02', '202'),
(43, 7, '2021-10-03', '203'),
(44, 7, '2021-10-04', '204'),
(45, 7, '2021-10-05', '205'),
(46, 7, '2021-10-06', '206'),
(47, 7, '2021-10-07', '207'),
(48, 7, '2021-10-08', '208'),
(49, 7, '2021-10-09', '209'),
(50, 7, '2021-10-10', '210'),
(51, 8, '2021-10-01', '301'),
(52, 8, '2021-10-02', '302'),
(53, 8, '2021-10-03', '303'),
(54, 8, '2021-10-04', '304'),
(55, 8, '2021-10-05', '305'),
(56, 8, '2021-10-06', '306'),
(57, 8, '2021-10-07', '307'),
(58, 8, '2021-10-08', '308'),
(59, 8, '2021-10-09', '309'),
(60, 8, '2021-10-10', '310'),
(61, 9, '2021-10-01', '80'),
(62, 9, '2021-10-02', '81'),
(63, 9, '2021-10-03', '82'),
(64, 9, '2021-10-04', '83'),
(65, 9, '2021-10-05', '84'),
(66, 9, '2021-10-06', '85'),
(67, 9, '2021-10-07', '86'),
(68, 9, '2021-10-08', '87'),
(69, 9, '2021-10-09', '88'),
(70, 9, '2021-10-10', '89'),
(71, 10, '2021-10-01', '91'),
(72, 10, '2021-10-02', '92'),
(73, 10, '2021-10-03', '93'),
(74, 10, '2021-10-04', '94'),
(75, 10, '2021-10-05', '95'),
(76, 10, '2021-10-06', '96'),
(77, 10, '2021-10-07', '97'),
(78, 10, '2021-10-08', '98'),
(79, 10, '2021-10-09', '99'),
(80, 10, '2021-10-10', '100');


ALTER TABLE `vehicles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`);

ALTER TABLE `vehicles_prices`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `vehicles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

ALTER TABLE `vehicles_prices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=81;
COMMIT;

查询；

SELECT
  *, (SELECT SUM(`price`) FROM `vehicles_prices` WHERE `day` BETWEEN '2021-10-02 00:00:00' AND '2021-10-05 23:59:59') AS `price_total`
FROM
  `vehicles`
WHERE
  `status` = 1
ORDER BY `price_total` DESC

结果；

id title          status price_total
 1 Renault Symbol      1        4628
 2 Renault Clio        1        4628 
 3 Hyundai i20         1        4628
 4 Hyundai i10         1        4628
 5 Hyundai Accent      1        4628
 7 Volkswagen Polo     1        4628
 8 Ford Fiesta         1        4628
 9 Fiat Panda          1        4628
10 Fiat Tipo           1        4628

测试：http://sqlfiddle.com/#!9/ce3475f/5

Answer 1

这个查询似乎起作用了；

SELECT
  `v`.`id`, `v`.`title`, SUM(`vp`.`price`) AS 'price_total'
FROM
  `vehicles` v
    LEFT JOIN `vehicles_prices` vp ON `v`.`id` = `vp`.`vehicle_id`
WHERE
  `status` = 1 AND `vp`.`day` BETWEEN '2021-10-02' AND '2021-10-05'
GROUP BY
  `v`.`id`
ORDER BY `price_total` ASC

id  title   price_total 
9   Fiat Panda  330 
10  Fiat Tipo   374 
4   Hyundai    i10  414 
1   Renault Symbol  454 
2   Renault Clio    494 
3   Hyundai i20 534    
7   Volkswagen Polo 814 
8   Ford Fiesta 1214

谨致问候