Mysql 选择不同表中的两列 - Mysql Select two column in different table

如下所示，我选择具有相同表和条件的两个不同列（count_group和group_total）。我觉得这种方法会使页面变慢。有什么建议吗？

$sql3 = "SELECT SUM(mr.payment_amount) as total ,
COUNT(mr.order_id) as count_personal , 
(SELECT COUNT(DISTINCT mr.group_order_id) 
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.group_order_id != '' ) as count_group ,
(SELECT SUM(mr.payment_amount) 
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.group_order_id != '' ) as group_total 
FROM event_mgmt_registration mr 
WHERE mr.event_nid =".$nid." 
AND mr.status='completed'  
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.order_id !=''";

为了获得更好的性能，您最好做一个 UNION 以获得三个结果，每个结果都是一行：

SELECT 
SUM(mr.payment_amount) as total ,
COUNT(mr.order_id) as count_personal
FROM event_mgmt_registration mr 
WHERE mr.event_nid =".$nid." 
AND mr.status='completed'  
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$molpaySum['latest_date']."' 
AND mr.order_id !=''
UNION ALL
(SELECT NULL, COUNT(DISTINCT mr.group_order_id)
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$molpaySum['latest_date']."' 
AND mr.group_order_id != '' )
UNION ALL
(SELECT NULL, SUM(mr.payment_amount)
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.group_order_id != '' )

为什么需要对每个参数进行查询？我认为你可以用一个查询来做到这一点

试试这个

$sql3 = "
    SELECT 
        SUM(mr.payment_amount) as total ,
        COUNT(mr.order_id) as count_personal , 
        COUNT(DISTINCT mr.group_order_id) as count_group,
        SUM(mr.payment_amount) as group_total
    FROM event_mgmt_registration mr 
    WHERE 
        mr.event_nid =".$nid." AND 
        mr.status='completed'  AND 
        DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= 
            '".$molpaySum['latest_date']."' AND 
        mr.order_id !=''
";

请注意，在group_total中使用$Sum['latest_date']而不是$molpaySum['latest_date'] 。如果不是打印错误，则应保留此查询，如下所示：

    $sql3 = "
    SELECT 
        SUM(mr.payment_amount) as total ,
        COUNT(mr.order_id) as count_personal , 
        COUNT(DISTINCT mr.group_order_id) as count_group,
        (SELECT SUM(mr.payment_amount) 
         FROM event_mgmt_registration mr 
         WHERE mr.event_nid = ".$nid." 
             AND mr.status='completed' 
             AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <=
                 '".$Sum['latest_date']."' 
             AND mr.group_order_id != '' 
        ) as group_total 
    FROM event_mgmt_registration mr 
    WHERE 
        mr.event_nid =".$nid." AND 
        mr.status='completed'  AND 
        DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= 
            '".$molpaySum['latest_date']."' AND 
        mr.order_id !=''
";