php - 检索两个独立列的总和 - 使用嵌套选择语句

问题描述

本质上，我试图在书店内显示书籍的 ID、标题、总数量和总销售价格。目前我能够显示 ID、标题和总数量 - 或者 ID、标题和总销售价格，但是我无法弄清楚如何从该表中同时检索总数量和销售价格.

此查询成功地为我提供了 ID、标题和数量：

SELECT ID,Title,(SELECT Sum(Quantity) from Sale where BookId = Book.id ) as Quantity from Book;

此查询成功地为我提供了 ID、标题和销售价格：

SELECT ID,Title,(SELECT Sum(UnitPrice) from Sale where BookId = Book.id ) as Price from Book;

那么如何在我们的查询中同时检索 Quantity 和 Sales Price 呢？这两个值都在同一个表中，但这里使用了两个表。

这是我当前用来循环并显示查询结果的 HTML：

<!DOCTYPE html>
<html lang="en">
    <!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" href="new.css">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>

    <nav class="navbar navbar-default navbar navbar-inverse">
        <div class="container">
        <h2> <a class="head" href="base.php" style="text-decoration: none;">Database Project</a> <a class="btn btn-danger btn-sm main" href="login.php" role="button">Sign Out</a></h2>
        </div>
    </nav>

    <div class="container">
    <div class="jumbotron">
        <h1>Book Report:</h1><br>
        <table>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Quantity</th>
                <th>Total Sale Price</th>
    
            </tr>
            <?php
            $conn = mysqli_connect("localhost","root","","Zion");
            if ($conn-> connect_error){
                die("Connection Failed:". $conn-> connect_error);
            }
            $sql = "SELECT ID,Title,(SELECT Sum(Quantity) from Sale where BookId = Book.id ) as Quantity,(SELECT Sum(UnitPrice) from Sale where BookId = Book.id ) as Price from Book";
            $result = $conn-> query($sql);
            if ($result = $conn->query($sql)) {
                while($row = $result->fetch_assoc()) {
                    echo "<tr><td>". $row["ID"] . "</td><td>". $row["Title"]."</td><td>". $row["Quantity"]. "</td><td>". "$".$row["Price"]. "</td></tr>";
                }
                echo"</table>";
            }
            $conn-> close()
            ?>
            <!-- Generate a report that for each book, displays the book id, the book title, 
            the quantity and the total sales price, sorts by the book id in increasing order; -->

</div>

</body>
</html>

谢谢！

标签： phphtmlmysqlsql

php - 检索两个独立列的总和 - 使用嵌套选择语句

问题描述

解决方案

推荐阅读