drwxg62286
drwxg62286
2018-11-01 22:18

一个查询的多个求和查询

已采纳

I would like to make a collection according to some fields in the product information and product history table in the products table how do I do this in a single query?

my sample code:

    <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

Thanks.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • duanqiang6501 duanqiang6501 3年前

    You can use conditional aggregation to get these results from one query:

    $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'");
    

    You could actually merge both your queries into 1 using a LEFT JOIN:

    $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";
    

    If the only value of interest from product is pid (for the purposes of matching against the pid value in product_history) you should change the p.* in the query to p.pid. Otherwise you should ideally enumerate the specific fields whose values you want. See Why is SELECT * considered harmful?

    点赞 评论 复制链接分享
  • dongmi5015 dongmi5015 3年前

    I guess you want to know all colors and not only blue? In that case, I'd go with a LEFT JOIN

    SELECT p.pid, p.name, SUM(ph.price) AS total, ph.color, ph.status 
    FROM product p
    LEFT JOIN product_history ph ON (p.pid = ph.pid)
    WHERE p.status = 'ACTIVE'
    GROUP BY p.pid, p.name, ph.status, ph.color
    

    FYI: You should really use prepared statements to handle the context change and to avoid any kind of problems and vulnerabilities.

    点赞 评论 复制链接分享

为你推荐