mysql - 使用 mysqlQuery 重复某些值时未获得预期结果

Question

我正在尝试从 4 个表中获取数据，并且我编写了一个查询

SELECT
  c.id,
  c.name,
  c.price,
  c.duration,
  c.period,
  c.practical_classes,
  c.theory_classes,
  e.id,e.created_at,
  u.id,
  u.registration_no,
  i.id,
  i.amountpaid,
  (i.amount - i.amountpaid) as balanceamount
FROM
  courses c,
  coursesenrolled e,
  users u,
  invoices i
WHERE e.student = "11"
  AND e.course = c.id
  AND c.delete_status = "0"
  AND c.status = "Available"
  AND u.id = "11"
  AND i.student = e.student 

我得到的结果如下图所示，这里原始数据是重复的。如果我在查询中添加 GROUP BY，最后 3 列会得到错误的数据

如果我添加分组依据，我会得到以下结果

下面我附上表格格式1.“类(class)”， 2.“coursesenrolled”正在处理哪个类(class)分配给哪个用户的数据 3.“用户” 4.发票，为用户办理发票

Answer 1

我尝试理解这个问题，所以你想获得 id=11 用户的结果，对吗？试试这个

SELECT DISTINCT c.id,c.name,c.price,c.duration,c.period,c.practical_classes,c.theory_classes,e.id,e.created_at,u.id,u.registration_no,i.id,i.amountpaid,(i.amount-i.amountpaid) as balanceamount FROM courses AS c INNER JOIN coursesenrolled AS e ON e.course=c.id INNER JOIN users AS u ON u.id=e.student INNER JOIN invoices AS i ON i.student=u.id  WHERE u.id='11' AND c.delete_status='0' AND c.status='Available';