如何将具有不同WHERE/LIKE条件的两个查询组合在一起 - How can I combine two queries with different WHERE / LIKE conditions?

How can I combine two queries with different WHERE / LIKE conditions?

我的两个查询：

$compareTotals1 = mysqli_query($con,"
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts
    FROM        transaction
    WHERE       paid_on LIKE '%2014%'
");
$compareTotals2 = mysqli_query($con,"
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts
    FROM        transaction
    WHERE       paid_on LIKE '%2015%'
");

我如何输出结果：

if ($row = mysqli_fetch_array($compareTotals1)) {
    echo CURRENCY.number_format($row['total'],2);
    echo CURRENCY.number_format($row['latefees'],2);
    echo CURRENCY.number_format($row['discounts'],2);
} else { 
    echo "No Records."; 
}
if ($row = mysqli_fetch_array($compareTotals2)) {
    echo CURRENCY.number_format($row['total'],2);
    echo CURRENCY.number_format($row['latefees'],2);
    echo CURRENCY.number_format($row['discounts'],2);
} else { 
    echo "No Records."; 
}

paid_on LIKE '% %'是由一个下拉框和一些javascript动态生成的。这是唯一改变的部分。

如何将其压缩为一个查询，以便只需要使用一个mysqli_fetch_array？

假设您想要多行，联合可能是最干净的

$compareTotals1 = mysqli_query($con,"
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts,
                '2014' as Yr
    FROM        transaction
    WHERE       paid_on LIKE '%2014%'
    UNION ALL
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts,
                '2015' as Yr
    FROM        transaction
    WHERE       paid_on LIKE '%2015%'
");

您可以将where组合到OR中，并确保拼写出一个分组，否则您将不知道哪一个是2015年或2014年，除非*包含此类详细信息。

$compareTotals1 = mysqli_query($con,"
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts,
                case when paid_on like '%2014%' then '2014' 
                     when paid_on like '%2015%' then '2015' end as yr
    FROM        transaction
    WHERE       paid_on LIKE '%2014%'
       OR       paid_on LIKE '%2015%'
   --GROUP BY    all fields from select relevant to group by... without structure and sample data from table can't figure out.
   -- This might work though I'd be concerned all the * columns could be returning improper results.
   GROUP BY case when paid_on  like '%2014%' then '2014' when paid_on like '%2015%' then '2015' end
");

也许。。。group by case when paid_on like '%2014%' then '2014' when paid_on like '%2015%' then '2015' end，但这是非常具体的。

我们也许可以按paid_on分组，但这似乎不仅仅是一年。。。所以你每年可能会得到多行。。。因此，如果没有样本数据，结构就无法确定该怎么办。

或者你想交叉连接更多的列。。。没有更多的行。。。

$compareTotals1 = mysqli_query($con,"
    Select * from (
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts
    FROM        transaction
    WHERE       paid_on LIKE '%2014%') CROSS JOIN
    (SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts
    FROM        transaction
    WHERE       paid_on LIKE '%2015%') B
");

为什么不在WHERE中使用OR？

$compareTotals1 = mysqli_query($con,"
    SELECT *,   (SUM(rent_amount)+SUM(late_fees)-SUM(discount_amount)) AS total,
                SUM(late_fees) AS latefees,
                SUM(discount_amount) AS discounts
    FROM        transaction
    WHERE       paid_on LIKE '%2014%'
        OR      paid_on LIKE '%2015%'
    GROUP BY    YEAR(paid_on) -- is `paid_on` a date?
");