SQL 查询 - max(count()) - 无需 CTE 即可使用

Question

我目前正在为我的数据库系统考试而学习，在创建查询的练习中遇到了一些麻烦。

我有四张桌子：

1. 包含被指对象个人数据的参考表，
2. 带有课程数据的课程表（负责的引用作为外键），
3. 带有研讨会数据的研讨会表（以相应的课程作为外键），
管理预订的预订
4. 表（带有已预订为外键的相应研讨会）

我的练习是找出一个推荐人赚了多少钱（研讨会上有一个价格栏）

列出他每个车间赚了多少钱并不难;我创建此查询是为了向我显示：

SELECT  r.referentid,
    r.name,
    (SELECT COUNT(*) FROM g22_courses c WHERE c.responsiblerefid = r.referentid)*c.price AS income
FROM referent r
LEFT JOIN g22_courses c ON (c.responsiblerefid = r.referentid)
LEFT JOIN g22_workshop w ON (w.courseid = c.id)
LEFT JOIN g22_booking b ON (b.workshopid = w.id)
GROUP BY r.referentid, c.responsiblerefid

这将返回以下内容：

2;"Anna";0.60
4;"Ahmed";3.5
1;"Hans";
2;"Anna";13.20
3;"Wolfgang";

如您所见，它工作正常。

我现在有两行给

安娜（因为她负责两门课程......），并希望有一行包含两个表的总和。

不幸的是，执行此操作的唯一方法（正如我发现的那样）是使用公用表表达式 （CTE） - 使用 CTE 它可以工作：

WITH incomepercourse AS (
    SELECT  r.referentid,
        r.name,
        (SELECT COUNT(*) FROM g22_courses c WHERE c.responsiblerefid = r.referentid)*c.price AS income
    FROM referent r
    LEFT JOIN g22_courses c ON (c.responsiblerefid = r.referentid)
    LEFT JOIN g22_workshop w ON (w.courseid = c.id)
    LEFT JOIN g22_booking b ON (b.workshopid = w.id)
    GROUP BY r.referentid, c.responsiblerefid
)
SELECT  referentid, name, SUM(income) FROM incomepercourse GROUP BY referentid, name

这将返回：

3;"Wolfgang";
4;"Ahmed";3.50
2;"Anna";13.80
1;"Hans";

有什么方法可以避免CTE吗？

我的教授没有谈论CTE，也没有在他的讲义中 - 所以必须有其他更简单的方法。

有没有人知道实现这一目标的更好方法？

提前感谢！

Answer 1

您可以包装 CTE 部件并进行查询，如下所示。

SELECT  tbl1.referentid, tbl1.name, SUM(tbl1.income) FROM (
    SELECT  r.referentid,
            r.name,
            (SELECT COUNT(*) FROM g22_courses c WHERE c.responsiblerefid = r.referentid)*c.Preis AS income
        FROM referent r
        LEFT JOIN g22_courses c ON (c.responsiblerefid = r.referentid)
        LEFT JOIN g22_workshop w ON (w.courseid = c.id)
        LEFT JOIN g22_booking b ON (b.workshopid = w.id)
        GROUP BY r.referentid, c.responsiblerefid)tbl1      
GROUP BY tbl1.referentid, tbl1.name

Answer 2

如果您不使用，则无需加入研讨会和预订表。如果价格在您的第一个请求中所示在课程表中，我认为您的要求已通过此请求得到解答：

SELECT referent.referentid, referent.name, sum(price) 
FROM referent LEFT JOIN g22_courses ON g22_courses.responsiblerefid=referent.id
GROUP BY referent.referentid, referent.name

如果价格在车间表中，只需向此表添加一个联接即可。

Answer 3

好的，我解决了。

毕竟，这是我对问题的理解问题。_.

我知道设法提出了一个查询，就像a_horse_with_no_name在他的评论中所说的那样：

SELECT  r.referentid,
    r.name,
    SUM(c.price) AS income
FROM referent r
LEFT JOIN g22_courses c ON (c.responsiblerefid = r.referentid)
LEFT JOIN g22_workshop w ON (w.courseid = c.id)
LEFT JOIN g22_booking b ON (b.workshopid = w.id)
GROUP BY r.referentid
ORDER BY r.referentid

这完美地解决了我的问题，返回了正确的值。

谢谢！