如何总结来自 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屋！