MYSQL嵌套查询

  • Z时代
  • 2024-01-10
  • 分类：问答

问这个问题的目的只是为了学习如何在MYSQL中进行嵌套查询。MYSQL嵌套查询

1）以下查询有什么问题？

"SELECT tblwriter.writer_alias, tblwriter.writer_first_name, COUNT(tblordersub.suborder_alias) AS totalOrders FROM tblwriter, tblordersub WHERE tblwriter.writer_isactive = 1 AND tblordersub.writer_alias = tblwriter.writer_alias"

2）对于下面的MYSQL查询嵌套有没有更好的解决方案？

$currentorders = $db->rawQuery("SELECT order_title,order_alias FROM tblorder WHERE company_id=? AND YEAR(order_date) = YEAR(CURDATE()) AND MONTH(order_date) = MONTH(CURDATE())",$params); 
$orderssummary = array(); 
if(!empty($currentorders)){ 
    foreach($currentorders as $corder){ 
     $param = array($corder["order_alias"]); 
     $oprice = $db->rawQuery("SELECT payment_amount FROM tblpayment WHERE payment_status = 1 AND writer_alias IS NULL AND order_alias=?",$param); 
     $itssuborders = $db->rawQuery("SELECT suborder_alias FROM tblordersub WHERE order_alias=?",$param); 
     $thesuborders = array_implode("",",",$itssuborders); 
     $cost = $db->rawQuery("SELECT SUM(payment_amount) AS total_subtotal FROM tblpayment WHERE writer_alias IS NOT NULL AND suborder_alias IN (".$thesuborders.")"); 
     $orderssummary[] = array("title"=>$corder["order_title"],"price"=>$oprice[0]["payment_amount"],"cost"=>$cost[0]["total_subtotal"]); 
    } 
}

3）有什么办法可以将以下三个查询合并为1吗？

"SELECT SUM(payment_amount) AS totalAmount FROM tblpayment WHERE company_id=? AND payment_status = 1 AND order_alias IS NOT NULL AND YEAR(payment_add_datetime) = YEAR(CURDATE()) AND MONTH(payment_add_datetime) = MONTH(CURDATE() - INTERVAL 2 MONTH)" 
"SELECT SUM(payment_amount) AS totalAmount FROM tblpayment WHERE company_id=? AND payment_status = 0 AND writer_alias IS NOT NULL AND YEAR(payment_add_datetime) = YEAR(CURDATE()) AND MONTH(payment_add_datetime) = MONTH(CURDATE() - INTERVAL 2 MONTH)" 
"SELECT SUM(payment_amount) AS totalAmount FROM tblpayment WHERE company_id=? AND payment_status = 1 AND writer_alias IS NOT NULL AND YEAR(payment_add_datetime) = YEAR(CURDATE()) AND MONTH(payment_add_datetime) = MONTH(CURDATE() - INTERVAL 2 MONTH)"

4）与第二个问题相同，有什么办法可以将以下查询合并到1个查询中吗？

$biggest_customers = $db->rawQuery("SELECT payment_user_id, SUM(payment_amount) AS totalEARNED FROM tblpayment WHERE writer_alias IS NULL AND order_alias IS NOT NULL GROUP BY payment_user_id ORDER BY totalEARNED DESC LIMIT 10"); 
for($i=0;$i<count($biggest_customers);$i++){ 
    $params = array($biggest_customers[$i]["payment_user_id"]); 
    $customerinformation = $db->rawQuery('SELECT customer_alias FROM tblcustomer WHERE user_id=?',$params); 
    $biggest_customers[$i]["customer_alias"] = $customerinformation[0]["customer_alias"]; 
    unset($biggest_customers[$i]["payment_user_id"]); 
}

希望学习一些东西！

回答：

  1. 见集合查询

  2. 请参见连接

  3. 见CASE语句/透视表查询

  4. 看到加入再次

回答：

你不能使用COUNT（）语句（聚集查询）无选择多列时的GROUP BY。您还应该使用连接语句从不同的表中选择值。事情是这样的，也许：

SELECT tblwriter.writer_alias, tblwriter.writer_first_name, COUNT(tblordersub.suborder_alias) AS totalOrders 
FROM tblwriter 
INNER JOIN tblordersub ON (tblordersub.writer_alias = tblwriter.writer_alias) 
WHERE tblwriter.writer_isactive = 1 
GROUP BY tblordersub.suborder_alias

只要使用FROM tblwriter, tblordersub在查询你正在创建一个交叉连接，这是你的表的数据cartesian product。你可能不希望这样。

以上是 MYSQL嵌套查询 的全部内容， 来源链接： utcz.com/qa/258317.html

回到顶部