有条件地统计MYSQL列行(多个数据库)，并通过PHP输出数据

Question

我试图运行一个查询，并从同一台服务器上的多个数据库(使用UNION子句)获取结果，但这对我不起作用。所有数据库中的表名和列名都是相同的(相同的杯子，相同的咖啡，不同的“商店”:D)。下面是我的sql语句：

 $result = mysql_query("SELECT MONTH(ScanDate) AS sdmonth, YEAR(ScanDate) AS sdyear,  
              COUNT(Investigation) AS Investigation, COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END) AS MaleCount, 
              COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) AS FemaleCount, SUM(InvestigationAmount) AS investigationAmount, SUM(AmountDue) AS AmountDue   
                    FROM `database1`.`table_name`
                    UNION
                    SELECT MONTH(ScanDate) AS sdmonth, YEAR(ScanDate) AS sdyear,  
              COUNT(Investigation) AS Investigation, COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END) AS MaleCount, 
              COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) AS FemaleCount, SUM(InvestigationAmount) AS investigationAmount, SUM(AmountDue) AS AmountDue   
                    FROM `database2`.`table_name` 
                    WHERE (ScanDate BETWEEN '{$ScanDate1}' AND '{$ScanDate2}')
UNION
SELECT MONTH(ScanDate) AS sdmonth, YEAR(ScanDate) AS sdyear,  
              COUNT(Investigation) AS Investigation, COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END) AS MaleCount, 
              COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) AS FemaleCount, SUM(InvestigationAmount) AS investigationAmount, SUM(AmountDue) AS AmountDue   
                    FROM `database3`.`table_name` 
                    WHERE (ScanDate BETWEEN '{$ScanDate1}' AND '{$ScanDate2}')");


    //-create  while loop and loop through result set
    while($row=mysql_fetch_array($result)){ 
    $month_doe=$row['sdmonth']; 
    $year_doe=$row['sdyear'];
    $si=$row['Investigation'];
    $male=$row['MaleCount'];
    $female=$row['FemaleCount'];
    $sum_investigation=number_format($si);
    $sia=$row['investigationAmount'];
    $sum_investigationamount=number_format($sia);
    $srd=$row['AmountDue'];
    $sum_rebatedue=number_format($srd);
    }

我肯定是在我的头上，做了很多错误的事情，因为这只输出第二个数据库的结果。谁能给我指个方向？谢谢。

万一我的连接参数也有问题，下面是我的连接详细信息：

<?php  
    $conn = mysql_connect('localhost', 'root', '');
     if (!$conn)
    {
     die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("", $conn);
?>

Answer 1

有几件事值得一看。首先，默认情况下，MySQL中的UNION作为DISTINCT运行，丢弃它认为相等的行。

如果您想返回所有行，则需要使用UNION all而不仅仅是UNION。

我会试着和UNION一起使用。

如果这仍然不起作用，那么您为所有变量提供别名的方式可能会破坏它们的返回方式。

我将对其进行修改，删除第二个和第三个select语句的别名，如下所示：

"SELECT MONTH(ScanDate) AS sdmonth, YEAR(ScanDate) AS sdyear,  
          COUNT(Investigation) AS Investigation, COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END) AS MaleCount, 
          COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) AS FemaleCount, SUM(InvestigationAmount) AS investigationAmount, SUM(AmountDue) AS AmountDue   
                FROM `database1`.`table_name`
                UNION ALL
                SELECT MONTH(ScanDate), YEAR(ScanDate),  
          COUNT(Investigation), COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END), 
          COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END), SUM(InvestigationAmount), SUM(AmountDue)   
                FROM `database2`.`table_name` 
                WHERE (ScanDate BETWEEN '{$ScanDate1}' AND '{$ScanDate2}')
UNION ALL
SELECT MONTH(ScanDate), YEAR(ScanDate),  
          COUNT(Investigation), COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END), 
          COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END), SUM(InvestigationAmount), SUM(AmountDue)
                FROM `database3`.`table_name` 
                WHERE (ScanDate BETWEEN '{$ScanDate1}' AND '{$ScanDate2}')"

希望这能有所帮助

Answer 2

它终于起作用了。简单的答案有时很难看出来。我一直需要做的SQL语句是：

SELECT COUNT(Investigation) AS Investigation, COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END) 
                        AS MaleCount, COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) AS FemaleCount, 
                        SUM(InvestigationAmount) AS investigationAmount, SUM(AmountDue) AS AmountDue   
                        FROM 
                        (SELECT * FROM `database1`.`table_name` WHERE ScanDate BETWEEN '{$ScanDate1}' 
                        AND '{$ScanDate2}'
                        UNION ALL
                        SELECT * FROM `database2`.`table_name` WHERE ScanDate BETWEEN '{$ScanDate1}' 
                        AND '{$ScanDate2}') table_alias_here

感谢你们的投入。非常感谢。