如何在保留查询结果的同时从查询中检索MAX（）？

Question

我想在数据库中制作一些统计数据的简单条形图。更具体地说：这是一棵家谱，我想显示每月的出生频率。我设法创建了SQL查询，以获取带有相应出生日期的月份列表。现在，我要添加一个彩色的条，其中最大的数字是100％的宽度，其他数字是成比例的。

要计算百分比，我需要从查询结果中获取最大的数字。但是，我不需要显示它。我只需要数字就可以用它来计算条的宽度。

我用MAX（）尝试了不同的解决方案，但是所有这些解决方案都设置为仅显示COUNT（）的最大值。如前所述，我想显示完整结果，并且只需要内部使用MAX（）进行计算。我不需要显示它。

通过按COUNT（）进行SELECT（）查询排序并限制为第一个结果，可以很容易地检索数字。由于所需的MAX（）结果只有一个数字，因此我无法与其他SELECT查询（每个月获取我的数字）进行UNION。第二个问题是，要显示的结果显然不是按COUNT（）排序的，而是按月排序的。因此，此时MAX（）数将是第三个结果，尽管当我将其添加到数据库时，它可能会改变。

<?php

require_once(ABSPATH . 'wp-settings.php');

$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);

mysqli_select_db($connection, DB_NAME);

$query = "SELECT

    MONTHNAME(birthdatetr) AS Month_of_Birth,

    MONTH(birthdatetr) AS number_of_month_of_birth,

    COUNT(*) AS Total

FROM

    tng_people

WHERE

    MONTH(birthdatetr) > 0

GROUP BY

    number_of_month_of_birth

ORDER BY

    number_of_month_of_birth";

$result = mysqli_query($connection, $query);

$barwidth = floor($Total / $birthmax * 100);

?>

<?php foreach ($result as $row){    

echo "{$row['Month_of_Birth']} ({$row['Total']})

<td class=\"bar-holder\"><div style=\"width:{$barwidth}%;\" class=\"bar rightround\"></div></td>";

}

    ?>

这是我必须显示月份列表的代码。我已将max-variable命名为“ birthmax”，但我只是不知道如何为其分配值。该值应该是“总计”的最大结果。“ Barwidth”是我要显示的彩条的计算大小。照原样编写代码，因此缺少birthmax的值，会显示条，月和数字，但条为全角，表示分配给barwidth的值不是数字（NAN）。

Answer 1

如果您使用的是MYSQL 8.0及更高版本，则可以WITH用来创建两个表，即已创建的表和仅具有最大值的表。然后交叉连接两个表以将最大值添加到每一行。您也可以选择Total / max_total以获得介于0和1之间的百分比。WITH query_data AS (&nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; MONTHNAME(birthdatetr) AS Month_of_Birth,&nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) AS number_of_month_of_birth,&nbsp; &nbsp; &nbsp; &nbsp; COUNT(*) AS Total&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; &nbsp; tng_people&nbsp; &nbsp; WHERE&nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) > 0&nbsp; &nbsp; GROUP BY&nbsp; &nbsp; &nbsp; &nbsp; number_of_month_of_birth),max_count AS (&nbsp; &nbsp; SELECT MAX(Total) AS max_total FROM query_data)SELECT query_data.*, max_count.max_totalFROM query_data CROSS JOIN max_countORDER BY&nbsp; &nbsp; &nbsp; &nbsp; query_data.number_of_month_of_birth对于mysql <8.0，您可以像这样连接两个表：SELECT query_data.*, max_count.max_totalFROM (&nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; MONTHNAME(birthdatetr) AS Month_of_Birth,&nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) AS number_of_month_of_birth,&nbsp; &nbsp; &nbsp; &nbsp; COUNT(*) AS Total&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; &nbsp; tng_people&nbsp; &nbsp; WHERE&nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) > 0&nbsp; &nbsp; GROUP BY&nbsp; &nbsp; &nbsp; &nbsp; number_of_month_of_birth) query_dataCROSS JOIN (&nbsp; &nbsp; SELECT MAX(tmp_max.Total) AS max_total FROM&nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTHNAME(birthdatetr) AS Month_of_Birth,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) AS number_of_month_of_birth,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT(*) AS Total&nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tng_people&nbsp; &nbsp; &nbsp; &nbsp; WHERE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) > 0&nbsp; &nbsp; &nbsp; &nbsp; GROUP BY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; number_of_month_of_birth&nbsp; &nbsp; ) tmp_max) max_countORDER BY&nbsp; &nbsp; query_data.number_of_month_of_birth

Answer 2

我不知道我的想法是否正确，但我相信您可以使用子查询&nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; MONTHNAME(birthdatetr) AS Month_of_Birth,&nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) AS number_of_month_of_birth,&nbsp; &nbsp; &nbsp; &nbsp; COUNT(*) AS Total,(select count(*) FROM tng_people&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE month(birthdatetr) > 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GROUP BY month(birthdatetr)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ORDER BY count(*) DESC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;limit 1) as birthmax&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; &nbsp; tng_people&nbsp; &nbsp; WHERE&nbsp; &nbsp; &nbsp; &nbsp; MONTH(birthdatetr) > 0&nbsp; &nbsp; GROUP BY&nbsp; &nbsp; &nbsp; &nbsp; number_of_month_of_birth&nbsp; &nbsp; ORDER BY&nbsp; &nbsp; &nbsp; &nbsp; number_of_month_of_birth

Answer 3

一种解决方案是子查询，该查询的计数最高。SELECT monthname(birthdatetr) month_of_birth,&nbsp; &nbsp; &nbsp; &nbsp;month(birthdatetr) number_of_month_of_birth,&nbsp; &nbsp; &nbsp; &nbsp;count(*) total,&nbsp; &nbsp; &nbsp; &nbsp;(SELECT count(*)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM tng_people&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE month(birthdatetr) > 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GROUP BY month(birthdatetr)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ORDER BY count(*) DESC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LIMIT 1) overall_total&nbsp; &nbsp; &nbsp; &nbsp;FROM tng_people&nbsp; &nbsp; &nbsp; &nbsp;WHERE month(birthdatetr) > 0&nbsp; &nbsp; &nbsp; &nbsp;GROUP BY month(birthdatetr)&nbsp; &nbsp; &nbsp; &nbsp;ORDER BY month(birthdatetr);