MySQL中使用PHP的多维数组和聚合函数? [英] Multidimensional array and aggregate functions in MySQL using PHP?
本文介绍了MySQL中使用PHP的多维数组和聚合函数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
已更新
如何在u2旁边再显示1列，称为CUMULATIVE TOTAL
它应该显示基于咨询顾问的学生总数，应付账款总额和应付账款总额.
How to display 1 more column next to that u2 called CUMULATIVE TOTAL
it should display total number of students total payable total paid and total due based on counsellors.
考虑我有c1，c2，c3，c4作为顾问，而u1，u2作为大学 假设c1在每所大学中有5名学生，那么在这种情况下，累计总数"列应显示学生总数"列为[c1] [学生人数] = 10，[c1] [应付] =某个值，[c1] [已付费] =一些值，[c1] [平衡] =一些值
Consider i have c1,c2,c3,c4 as counsellors and u1,u2 as universities say c1 has 5 student in each university in that case CUMULATIVE TOTAL column should show total number of students column as [c1][No of students]=10, [c1][Payable]=some value, [c1][Paid]=some value, [c1][Balence]=some value
请检查以下代码，让我知道有什么方法可以在SUM聚合函数或任何替代解决方案内编写选择查询，因为我希望wll_invoice.total_payable应按customer_id分组.
Please check the following code and let me know is there any way to write select query inside that SUM aggregate function or any alternate solution because i want wll_invoice.total_payable should group by customer_id.
<?php
define('DB_MAIN', 'localhost|user|passowd|database');
class my_db{
private static $databases;
private $connection;
public function __construct($connDetails){
if(!is_object(self::$databases[$connDetails])){
list($host, $user, $pass, $dbname) = explode('|', $connDetails);
$dsn = "mysql:host=$host;dbname=$dbname";
self::$databases[$connDetails] = new PDO($dsn, $user, $pass);
}
$this->connection = self::$databases[$connDetails];
}
public function fetchAll($sql){
$args = func_get_args();
array_shift($args);
$statement = $this->connection->prepare($sql);
$statement->execute($args);
return $statement->fetchAll(PDO::FETCH_OBJ);
}
}
$db = new my_db(DB_MAIN);
$universities = $db->fetchAll('SELECT distinct customer_university FROM wll_customer');
$counselors = $db->fetchAll('SELECT distinct customer_counselor FROM wll_customer');
$payments_ = $db->fetchAll('SELECT
customer_counselor,
customer_university,
COUNT(DISTINCT customer_name) AS \'no of students\',
SUM(DISTINCT wll_invoice.total_payable) AS payable,**//I want to make total_payable should GROUP BY customer_id**
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM
wll_customer
LEFT JOIN
wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor,customer_university;');
$payments = [];
foreach ($payments_ as $payment)
$payments[$payment->customer_counselor][$payment->customer_university] = $payment;
?>
<table id="table_id" class='display table-bordered'>
<thead>
<tr>
<td rowspan="2">Sl</td>
<td rowspan="2" >counselor</td>
<?php
foreach ($universities as $key => $university){ ?>
<td colspan="4" ><?=$university->customer_university ?> </td>
<?php } ?>
</tr>
<tr>
<?php foreach ( $universities as $university){?>
<td>no of students</td>
<td>payable</td>
<td>paid</td>
<td>balance</td>
<?php } ?>
</tr>
</thead>
<tbody>
<tr>
<?php foreach ( $counselors as $counselor){?>
<?php foreach ( $universities as $key => $university){
$payment = $payments[$counselor->customer_counselor][$university->customer_university];
?> <?php if(!$key){?>
<td></td>
<td><?=$counselor->customer_counselor?></td>
<?php } ?>
<td><?=(int)$payment->{'no of students'}?></td>
<td><?=number_format($payment->payable,0,',','')?></td>
<td><?=number_format($payment->paid,0,',','')?></td>
<td><?=number_format($payment->balance,0,',','')?></td>
<?php } ?>
</tr>
<?php } ?>
</tbody>
</table>
推荐答案
我希望这是您要关注的代码:
I hope this is the code you are looking after:
<?php
define('DB_MAIN', 'localhost|user|password|database');
class my_db{
private static $databases;
private $connection;
public function __construct($connDetails){
if(!is_object(self::$databases[$connDetails])){
list($host, $user, $pass, $dbname) = explode('|', $connDetails);
$dsn = "mysql:host=$host;dbname=$dbname";
self::$databases[$connDetails] = new PDO($dsn, $user, $pass);
}
$this->connection = self::$databases[$connDetails];
}
public function fetchAll($sql){
$args = func_get_args();
array_shift($args);
$statement = $this->connection->prepare($sql);
$statement->execute($args);
return $statement->fetchAll(PDO::FETCH_OBJ);
}
}
$db = new my_db(DB_MAIN);
$universities = $db->fetchAll('SELECT distinct customer_university FROM wll_customer order by customer_university');
/**
* Adding Cummulative university
*/
$cumulativeUniversity = new StdClass();
$cumulativeUniversity->customer_university = "CUMULATIVE TOTAL";
$universities[] = $cumulativeUniversity;
$counselors = $db->fetchAll('SELECT distinct customer_counselor FROM wll_customer order by customer_counselor');
$payments_ = $db->fetchAll('(SELECT
customer_counselor,
customer_university,
COUNT(distinct wll_invoice.customer_id) AS \'no of students\',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor, customer_university
order by `customer_counselor`, `customer_name`)
UNION
(SELECT
customer_counselor,
"CUMULATIVE TOTAL" as university,
COUNT(distinct wll_invoice.customer_id) AS \'no of students\',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor
ORDER BY `customer_counselor`)');
$payments = [];
foreach ($payments_ as $payment)
$payments[$payment->customer_counselor][$payment->customer_university] = $payment;
?>
<table id="table_id" class='display table-bordered' border="1">
<thead>
<tr>
<td rowspan="2" >Counselor</td>
<?php
foreach ($universities as $key => $university): ?>
<td colspan="4" ><?=$university->customer_university ?> </td>
<?php endforeach ?>
</tr>
<tr>
<?php foreach ( $universities as $university): ?>
<td>no of students</td>
<td>payable</td>
<td>paid</td>
<td>balance</td>
<?php endforeach ?>
</tr>
<?php foreach ( $counselors as $counselor):?>
<tr>
<td>
<?php echo $counselor->customer_counselor;?>
</td>
<?php foreach ( $universities as $key => $university):
$payment = isset($payments[$counselor->customer_counselor][$university->customer_university]) ? $payments[$counselor->customer_counselor][$university->customer_university] : null;
if($payment):?>
<td><?=(int)$payment->{'no of students'}?></td>
<td><?=number_format($payment->payable,0,',','')?></td>
<td><?=number_format($payment->paid,0,',','')?></td>
<td><?=number_format($payment->balance,0,',','')?></td>
<?php else:?>
<td colspan="4"></td>
<?php endif?>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</thead>
</table>
我使用了以下查询，其中我正在使用Union来补充咨询师正在寻找的总体数据.另外，如果您在代码中注意到了，我会在大学列表中附加一个累积的大学对象，以处理相同的循环.
I have used following query where I am using Union to append overall data by counsellor as well that you are looking for. Also if you have noticed in code, there I have appended a cumulative university object to the university list to process it same loop.
(SELECT
customer_counselor,
customer_university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor, customer_university
ORDER BY `customer_counselor`, `customer_name`)
UNION
(SELECT
customer_counselor,
"CUMULATIVE TOTAL" AS university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor
ORDER BY `customer_counselor`)
尝试使用此查询获取不同的值，但是您确实需要更新架构.这只是一个临时解决方案:
Try using this query for distinct value, but you really need to update your schema. This is just a temporary solution:
(SELECT
customer_counselor,
customer_university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(final_pay) AS paid,
SUM(wll_invoice.total_payable - final_pay) AS balance
FROM wll_customer
LEFT JOIN (SELECT MAX(id) max_id, customer_id, SUM(total_pay) final_pay FROM `wll_invoice`
GROUP BY customer_id, `total_payable`) AS wll_unique ON wll_unique.customer_id = wll_customer.`customer_id`
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_unique.customer_id AND `wll_invoice`.id = wll_unique.max_id
GROUP BY customer_counselor, customer_university
ORDER BY `customer_counselor`, `customer_name`)
UNION
(SELECT
customer_counselor,
"CUMULATIVE TOTAL" AS university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(final_pay) AS paid,
SUM(wll_invoice.total_payable - final_pay) AS balance
FROM wll_customer
LEFT JOIN (SELECT MAX(id) max_id, customer_id, SUM(total_pay) final_pay FROM `wll_invoice`
GROUP BY customer_id, `total_payable`) AS wll_unique ON wll_unique.customer_id = wll_customer.`customer_id`
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_unique.customer_id AND `wll_invoice`.id = wll_unique.max_id
GROUP BY customer_counselor
ORDER BY `customer_counselor`)
这篇关于MySQL中使用PHP的多维数组和聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
