gpt4 book ai didi

php - 使用一个查询进行多个求和查询

转载 作者:行者123 更新时间:2023-11-29 06:37:37 25 4
gpt4 key购买 nike

我想根据产品表中的产品信息和产品历史表中的某些字段进行集合,如何在单个查询中执行此操作?

我的示例代码:

    <table> 
<thead>
<tr>
<th>Product</th>
<th>Model</th>
<th>Actıve Total</th>
<th>Pasıve Total</th>
<th>Color Total</th>
</tr>
</thead>
<tbody>
<?php

$product = $db->get_results("SELECT * FROM product WHERE (status='ACTIVE')");


foreach ($product as $p ){

$active_total = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='AKTIVE'");
$pasive_total = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='PASIVE'");
$color_total = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='ACTIVE' AND color='BLUE'");
?>
<tr>
<td><?php echo $p->name; ?></td>
<td><?php echo $p->model; ?></td>
<td><?php echo $active_total; ?></td>
<td><?php echo $pasive_total; ?></td>
<td><?php echo $color_total; ?></td>
</tr>
<?php } ?>
</tbody>
</table>

enter image description here

enter image description here

谢谢。

最佳答案

您可以使用条件聚合从一个查询中获取这些结果:

$sql = "SELECT
SUM(CASE WHEN status='AKTIVE' THEN price ELSE 0 END) AS aktive,
SUM(CASE WHEN status='PASIVE' THEN price ELSE 0 END) AS pasive,
SUM(CASE WHEN status='AKTIVE' AND color='BLUE' THEN price ELSE 0 END) AS color,
FROM product_history
WHERE pid='$p->pid'");

您实际上可以使用LEFT JOIN将两个查询合并为1。 :

$sql = "SELECT p.*,
SUM(CASE WHEN h.status='AKTIVE' THEN h.price ELSE 0 END) AS aktive,
SUM(CASE WHEN h.status='PASIVE' THEN h.price ELSE 0 END) AS pasive,
SUM(CASE WHEN h.status='AKTIVE' AND h.color='BLUE' THEN h.price ELSE 0) AS color
FROM product p
LEFT JOIN product_history h ON h.pid = p.pid
WHERE p.status='ACTIVE'
GROUP BY p.pid";

如果唯一感兴趣的值来自 productpid (为了匹配 pid 中的 product_history 值)您应该更改 p.*在查询 p.pid 中。否则,理想情况下您应该枚举您想要其值的特定字段。参见 Why is SELECT * considered harmful?

关于php - 使用一个查询进行多个求和查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53110160/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com