Дело, которое повлияло на то, где пункт ⇐ MySql

[Anonymous]

У меня есть таблица saa_prices (id, символ, цена, дата). И я создаю представление, чтобы запросить ежеквартальные цены. < /P>
CREATE OR REPLACE VIEW saa_quarterly_prices_final1 AS
WITH last_dates AS (
SELECT
symbol,
MAX(date) AS last_date,
YEAR(MAX(date)) AS report_year,
QUARTER(MAX(date)) AS report_quarter
FROM
saa.saa_prices
GROUP BY
symbol, YEAR(date), QUARTER(date)
)
SELECT
p.symbol,
ld.last_date AS date,
p.price,
ld.report_year AS year,
ld.report_quarter AS quarter,
CASE ld.report_quarter
WHEN 1 THEN CAST(CONCAT(ld.report_year, '-03-31') AS DATE)
WHEN 2 THEN CAST(CONCAT(ld.report_year, '-06-30') AS DATE)
WHEN 3 THEN CAST(CONCAT(ld.report_year, '-09-30') AS DATE)
WHEN 4 THEN CAST(CONCAT(ld.report_year, '-12-31') AS DATE)
END AS report_date
FROM
last_dates ld
JOIN
saa.saa_prices p ON ld.symbol = p.symbol AND ld.last_date = p.date;
< /code>
Запрос ： < /p>
SELECT p.* FROM saa.saa_quarterly_prices_final1 p
WHERE p.symbol IN ('600519') AND p.report_date > '2025-01-01';
< /code>
Результат запроса: < /p>



symbol < /th>
< /th> < /br /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> < /th> /> четверть < /th>
report_date < /th>
< /tr>
< /thead>


600519 < /td>
2025-03-31 < /td>
1561 < /td> 1561 < /td> 156-03-31 /> 2025 < /td>
1 < /td>
2020-03-31 < /td>
< /tr>

600519 < /td>
2025-06-30 600519 < /td>
2025-06-30 . />1409.52
2025 < /td>
2 < /td>
2020-03-31 < /td>


600519 < /td>

600519 < /td> /> 2025-07-31 < /td>
1421.67> 2025 < /td>
3 < /td>
2020-03-31 < /td>
< /tr>

/>
Почему год , Quarter рассчитывается правильно, но отчет_дат всегда является '2020-03-31' и конфликтует с тем, где оператор?SELECT p.*,
CASE p.quarter
WHEN 1 THEN CAST(CONCAT(p.year, '-03-31') AS DATE)
WHEN 2 THEN CAST(CONCAT(p.year, '-06-30') AS DATE)
WHEN 3 THEN CAST(CONCAT(p.year, '-09-30') AS DATE)
WHEN 4 THEN CAST(CONCAT(p.year, '-12-31') AS DATE)
END AS report_date1
FROM saa.saa_quarterly_prices_final1 p
WHERE p.symbol IN ('600519') AND p.report_date > '2025-01-01';
< /code>
Результат: < /p>



symbol < /th>
date < /th>
< /th> < /th> < /th> < /th> < /th>
/>quarter
report_date
report_date1




600519
2025-03-31
1561 < /td>
2025 < /td>
1 < /td>
2020-03-31 < /td>
2025-03-31 < /td>
< /tr>
/> 600519 < /td>
2025-06-30 < /td>
1409.52
2025 < /td>
2 < /td>
2020-03-31 < /td> < /td>
2020-03-31 < /td> < /td>
2020-03-31 < /td> < /td>
2020-03-31 < /> /> 2025-06-30 < /td>
< /tr>

600519 < /td>
2025-07-31 < /td>
1421.67> 202 < /td> 202 < /td> 202 < /td> 202 < /td> 202 < /td> 202 < /td> 202 /> 3 < /td>
2020-03-31 < /td>
2025-09-30 < /td>
< /tr>
< /tbody>
< /table> < /div>
mysql версия и Saa_prices: < /div>
mysql and rersion и saa_prices: < /p> p> p> p> p> p> p> < /br /> mysql and saa_prices: < /table> < /div>
mysql и saa_prices: < /table> < /div>
mySSELECT VERSION();
# VERSION()
'8.0.25'

show create table saa.saa_prices;
# Table, Create Table
'saa_prices', 'CREATE TABLE `saa_prices` (\n `id` int NOT NULL AUTO_INCREMENT,\n `symbol` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n `price` double NOT NULL,\n `date` date NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `symbol_date_UNIQUE` (`symbol`,`date`)\n) ENGINE=InnoDB AUTO_INCREMENT=5414885 DEFAULT CHARSET=utf8mb3'


Подробнее здесь: https://stackoverflow.com/questions/797 ... ere-clause