代码之家 › 专栏 › 技术社区 › Sandeepan Nath

MySQL查询帮助

join sum conditional count mysql

Sandeepan Nath · 技术社区 · 14 年前

我的系统由两种用户类型组成-学生和导师。
-导师可以创建课程和课程包
-学生和导师都可以购买课程和背包

以下是涉及的表格

Groups
Users -包含两种用户类型的公用字段
Tutor_Details -导师特定领域
WebClasses -导师创建的课程
Learning_Packs -导师创建的包 Orders -每次购买一条记录
Order_Details -每次采购的多个记录-采购的项目数
Payments

下面的查询生成所有用户(学生和导师)的列表,并显示3个字段-用户名, orders -采购物品数量和 topics -创建的类和包总数-

SELECT u.name,
       COUNT(DISTINCT( o.id_order ))                                         AS
       orders,
       ( ( COUNT(DISTINCT( wc.id_wc )) ) + ( COUNT(DISTINCT( lp.id_lp )) ) ) AS
       topics
FROM   users AS u
       LEFT JOIN tutor_details AS td
         ON u.id_user = td.id_user
       INNER JOIN groups AS g
         ON u.id_group = g.id_group
       LEFT JOIN webclasses AS wc
         ON td.id_tutor = wc.id_author
       LEFT JOIN learning_packs AS lp
         ON td.id_tutor = lp.id_author
       LEFT JOIN orders AS o
         ON ( u.id_user = o.id_user )
       LEFT JOIN order_details AS od
         ON ( o.id_order = od.id_order )
       LEFT JOIN payments AS p
         ON ( o.id_order = p.id_order )
WHERE  IF(o.id_order != 0, o.order_status = 'paid', 1)
       AND IF(p.id_payment != 0, p.payment_status = 'success', 1)
GROUP  BY u.id_user
ORDER  BY u.id_user ASC

需要帮助
现在,我想向 话题 计数。只应计算wc.status=1或lp.status=1的主题。我希望在一个查询中也这样做。请注意,不能在主where块(组块之前)中添加条件,因为查询必须仍然显示ID_状态为0的学生(未参加任何课程)和导师。

这个 话题 count应该只考虑classes/packs条件,这就是我想要的。查询仍应显示当前查询显示的所有用户。

谢谢,
桑迪桑

1 回复 | 直到 14 年前

Sandeepan Nath 14 年前

好吧,我在别人面前找到了解决办法:)

SELECT u.id_user,
       ( Concat(u.name, ' ', u.surname) )                                AS name
       ,
       u.login,
       u.status,
       u.email,
       g.name                                                            AS TYPE
       ,
       u.joined,
       COUNT(DISTINCT( o.id_order ))                                     AS
       orders,
       ( COUNT(DISTINCT( IF(wc.id_status = 1, wc.id_wc, NULL) )) +
           COUNT(DISTINCT( IF(lp.id_status = 1, lp.id_lp, NULL) )) ) AS
       topics
FROM   users AS u
       LEFT JOIN tutor_details AS td
         ON u.id_user = td.id_user
       INNER JOIN groups AS g
         ON u.id_group = g.id_group
       LEFT JOIN webclasses AS wc
         ON td.id_tutor = wc.id_author
       LEFT JOIN learning_packs AS lp
         ON td.id_tutor = lp.id_author
       LEFT JOIN orders AS o
         ON ( u.id_user = o.id_user )
       LEFT JOIN order_details AS od
         ON ( o.id_order = od.id_order )
       LEFT JOIN payments AS p
         ON ( o.id_order = p.id_order )
WHERE  IF(o.id_order != 0, o.order_status = 'paid', 1)
       AND IF(p.id_payment != 0, p.payment_status = 'success', 1)
GROUP  BY u.id_user
ORDER  BY u.id_user ASC