如何总结来自 MySQL 数据库的一组结果并避免在 PHP 中出现重复项? [英] How to sum a set of results from a MySQL database and avoid getting duplicates in PHP?
问题描述
我的用户可以为某些课程单独付款.我有一张用户表、一张付款表、一张已验证付款表 (pagosVerificados)、一张课程表以及用户已参加的课程.每门课程都包含一个课程价格字段.
I have users that can make individual payments for some courses. I have a table for users, a table for payments, a table for verified Payments (pagosVerificados), a table for courses, and for courses that the users have taken. Each course includes a field for the course's price.
我正在尝试执行一个显示每个用户的所有付款的数据库查询.每笔付款都附加到特定课程.
I'm trying to do a database query that shows all the payments of each user. Each payment is attached to a specific course.
现在,问题是,如果我列出付款清单,而有些用户为一门课程支付了不止一次付款,则课程会在查询中重复.
Now, the problem is that if I do a list of payments and some users make more than one payment for a single course the course gets repeated on the query.
那么我如何使用该查询来获取每门课程的总金额,然后从每笔付款中获得休息?
So how may I use that query to get the total amount of each course and then rest from that each individual payment?
CREATE TABLE cursos (
cursoID int unsigned not null auto_increment primary key,
nombreCurso char(100) not null,
cursoPrecio int(10) null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE cursosUsuarios (
cursosUsuariosID int unsigned not null auto_increment primary key,
userID int not null,
cursoID int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE pagos (
pagoID int unsigned not null auto_increment primary key,
userID int not null,
pagoMonto int null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE pagosVerificados (
pagosVerificadosID int unsigned not null auto_increment primary key,
userID int not null,
pagoID int not null,
cursoID int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
这是查询:
SELECT pagosVerificados.pagoID AS pagoid, pagos.pagoMonto,
cursosUsuarios.userID,
cursos.cursoID, cursos.nombreCurso, cursos.cursoPrecio
FROM pagos JOIN pagosVerificados
ON pagos.pagoID = pagosVerificados.pagoID
LEFT JOIN cursosUsuarios
ON pagosVerificados.cursoID = cursosUsuarios.cursoID
JOIN cursos
ON cursosUsuarios.cursoID = cursos.cursoID
WHERE cursosUsuarios.userID = 16
这是我得到的结果:
pagoID --- pagoMonto ---- userID -- cursoPrecio -- cursoID - nombreCurso
3 -------- 200 ---------- 16 ------ 5000 --------- 15 ------ alfa
11 ------- 25 ----------- 16 ------ 5000 --------- 15 ------ alfa
12 ------- 50 ----------- 16 ------ 8000 --------- 18 ------ zeta
现在,如果我想使用 PHP 来执行此操作:
Alfa
5000
-200
-25
======
4775
我该怎么做?
推荐答案
我不会撒谎,我不知道你的表名或列是什么意思,但这是我最好的猜测.将其用作子查询,然后加入其他表以获取课程名称和课程总价.
I'm not going to lie, I have no idea what your table names or columns mean but this is my best guess. Use this below as a subquery then join to your other tables to get the course name and total price of the course.
select sum(A.pagoMonto), A.userID, B.cursoID
from pagos A inner join pagosVerificados B
on ON A.pagoID = B.pagoID
Where B.userID = 16
GROUP BY B.cursoID,A.userID
这篇关于如何总结来自 MySQL 数据库的一组结果并避免在 PHP 中出现重复项?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!