如何在MySQL查询中排除当前年份？PHP+AJAX实现遇阻

看起来你已经通过UNION整合了四个表的年份数据，但还没正确加上排除当前年份的过滤条件。我帮你调整查询语句，同时优化类型匹配避免潜在问题：

方法1：外层过滤已整合的年份（推荐写法） #

把所有UNION的结果作为子查询，在外层统一排除当前年份，写法简洁且能利用UNION的去重特性：

<?php
session_start();
require_once 'connection.php';
$output = "";
// 修改后的查询语句
$query = "SELECT year FROM (
    SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_flood_info`
    UNION
    SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_fire_info`
    UNION
    SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_accident_info`
    UNION
    SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_crime_info`
) AS all_years
WHERE year != YEAR(CURDATE())
ORDER BY year DESC";

$result = mysqli_query($connection, $query);
$numrows = mysqli_num_rows($result);
$x = 0;
if ($numrows > 0) {
    while($row = mysqli_fetch_assoc($result)){
        $output .= "<option>" .$row['year']. "</option>";
        $x++;
    }
}
echo $output;
mysqli_close($connection);
?>

方法2：子查询内提前过滤（性能优化） #

如果四个表的数据量较大，在每个子查询里就过滤掉当前年份的数据，可以减少UNION需要处理的数据量。这里用YEAR()函数直接提取年份（返回数字类型），和YEAR(CURDATE())类型一致，避免字符串与数字比较的潜在问题：

<?php
session_start();
require_once 'connection.php';
$output = "";
// 子查询内提前过滤的写法
$query = "(SELECT DISTINCT YEAR(`date_happened`) AS year FROM `tbl_flood_info` WHERE YEAR(`date_happened`) != YEAR(CURDATE()))
UNION
(SELECT DISTINCT YEAR(`date_happened`) AS year FROM `tbl_fire_info` WHERE YEAR(`date_happened`) != YEAR(CURDATE()))
UNION
(SELECT DISTINCT YEAR(`date_happened`) AS year FROM `tbl_accident_info` WHERE YEAR(`date_happened`) != YEAR(CURDATE()))
UNION
(SELECT DISTINCT YEAR(`date_happened`) AS year FROM `tbl_crime_info` WHERE YEAR(`date_happened`) != YEAR(CURDATE()))
ORDER BY year DESC";

$result = mysqli_query($connection, $query);
$numrows = mysqli_num_rows($result);
$x = 0;
if ($numrows > 0) {
    while($row = mysqli_fetch_assoc($result)){
        $output .= "<option>" .$row['year']. "</option>";
        $x++;
    }
}
echo $output;
mysqli_close($connection);
?>

AJAX小优化（可选） #

如果页面可能多次调用这个接口，建议先清空下拉框再添加选项，避免重复内容：

$.ajax({
    type: "post",
    url: "../get_year.php",
    success: function(data) {
        $("#selectYear").empty(); // 先清空原有选项
        $("#selectYear").append(data.trim());
        getFilter();
    }
});

这样修改后，下拉框就只会显示2015-2019中排除当前年份的选项了。

内容的提问来源于stack exchange，提问作者Nyotnoti