php - 在 PHP 中合并条件循环的第二个总计数查询

Question

我有一个带有脚本的 PHP 页面，该脚本根据所选的“年份”显示行。这是由 URL 字符串中的 $_GET 执行的，然后在我的 MySQL 查询中使用一个变量。

$jID = (int)$_GET['id'];

SELECT lh.*, c.country AS c, c.flag, MAX(countv) countv, MAX(counta) counta
FROM (
      SELECT vg.vertrekluchthaven AS code, count(*) as countv, 0 as counta
      FROM tbl_vluchtgegevens vg
      WHERE year(vg.vertrekdatum2) = '$jID'
      GROUP BY vg.vertrekluchthaven

    UNION  

      SELECT vg.aankomstluchthaven AS code, 0 as countv, count(*) as counta
      FROM tbl_vluchtgegevens vg  
      WHERE year(vg.vertrekdatum2) = '$jID'
      GROUP BY vg.aankomstluchthaven
   ) vg

LEFT JOIN tbl_luchthaven lh
ON  lh.luchthavenID = vg.code

LEFT JOIN tbl_countries c
ON lh.countryID = c.countryID 

GROUP BY vg.code
ORDER BY lh.luchthavencode;

?id=2018 可能会产生

| Code | Name                       | # Arrival | # Depart |
| AMS  | Amsterdam                  | 3         | 2        |
| MSP  | Minneapolis                | 5         | 5        |
| TLL  | Tallinn                    | 1         | 1        |

我还想计算所有行，然后在循环中使用粗体和红色(或其他格式)的条件，如果现在 <= 的整个表的计数 <= 0 $_GET 值。

是否有一种方法可以在其中添加子查询，以将 UNION 查询的总数作为列提供，例如:

| Code | Name                       | # Arrival | # Depart | # Total |
| AMS  | Amsterdam                  | 3         | 2        | 64      |
| MSP  | Minneapolis                | 5         | 5        | 122     |
| TLL  | Tallinn                    | 1         | 1        | 1       |


FROM (
          SELECT vg.vertrekluchthaven AS code, count(*) as countv, 0 as counta
          FROM tbl_vluchtgegevens vg
          WHERE year(vg.vertrekdatum2) <= '$jID'
          GROUP BY vg.vertrekluchthaven

        UNION  

          SELECT vg.aankomstluchthaven AS code, 0 as countv, count(*) as counta
          FROM tbl_vluchtgegevens vg  
          WHERE year(vg.vertrekdatum2) <= '$jID'
          GROUP BY vg.aankomstluchthaven
       ) vg

然后我可以在 PHP 中使用 if ($row['totalcount'] >0)/else 子句进行条件格式化。

Answer 1

尽管没有示例数据很难确定，但根据您的评论，我认为您可以通过在所有日期运行子查询 <= $jID 来实现您想要的效果然后使用条件聚合来获取您感兴趣的特定年份的值:

SELECT lh.*, c.country AS c, c.flag, 
       SUM(CASE WHEN vg.year = '$jID' THEN countv END) AS countv, 
       SUM(CASE WHEN vg.year = '$jID' THEN counta END) AS counta,
       SUM(counta)+SUM(countv) AS total
FROM (
      SELECT vg.vertrekluchthaven AS code, year(vg.vertrekdatum2) AS year, count(*) as countv, 0 as counta
      FROM tbl_vluchtgegevens vg
      WHERE year(vg.vertrekdatum2) <= '$jID'
      GROUP BY code, year

    UNION  

      SELECT vg.aankomstluchthaven AS code, year(vg.vertrekdatum2) AS year, 0 as countv, count(*) as counta
      FROM tbl_vluchtgegevens vg  
      WHERE year(vg.vertrekdatum2) <= '$jID'
      GROUP BY code, year
   ) vg

LEFT JOIN tbl_luchthaven lh
ON  lh.luchthavenID = vg.code

LEFT JOIN tbl_countries c
ON lh.countryID = c.countryID 

GROUP BY vg.code
ORDER BY lh.luchthavencode;