获取所需的sql查询结果

mysql> select calendar.calId,calendar.cdate,lsUsers.userId,
              apptActual.OPDID,count(*) 
        from calendar 
        LEFT JOIN apptActual on calendar.calId=apptActual.calendarId
        LEFT JOIN lsUsers on lsUsers.userId=apptActual.OPDID 
        group by 1,2,3,4 order by 3
        desc limit 10;

Result is:

+-------+------------+--------+-------+----------+
| calId | cdate      | userId | OPDID | count(*) |
+-------+------------+--------+-------+----------+
|    76 | 2016-03-16 |     11 |    11 |        1 |
|    75 | 2016-03-15 |     11 |    11 |        1 |
|    77 | 2016-03-17 |      9 |     9 |        1 |
|    75 | 2016-03-15 |      8 |     8 |        1 |
|    75 | 2016-03-15 |      1 |     1 |        1 |
|  1279 | 2019-07-02 |   NULL |  NULL |        1 |
|  1287 | 2019-07-10 |   NULL |  NULL |        1 |
|  1295 | 2019-07-18 |   NULL |  NULL |        1 |
|  1303 | 2019-07-26 |   NULL |  NULL |        1 |
|  1311 | 2019-08-03 |   NULL |  NULL |        1 |
+-------+------------+--------+-------+----------+
10 rows in set (0.34 sec)

By using above query I am getting result as above, but I need result result like below (all userId's has to come.)

通过使用上面的查询我得到的结果如上,但我需要结果如下(所有userId必须来。)

+-------+------------+--------+-------+----------+
| calId | cdate      | userId | OPDID | count(*) |
+-------+------------+--------+-------+----------+
|    76 | 2016-03-16 |     11 |    11 |        1 |
|    75 | 2016-03-15 |     11 |    11 |        1 |
|    77 | 2016-03-17 |      9 |     9 |        1 |
|    75 | 2016-03-15 |      8 |     8 |        1 |
|    75 | 2016-03-15 |      1 |     1 |        1 |
|  1279 | 2019-07-02 |      4 |  NULL |        1 |
|  1287 | 2019-07-10 |      21|  NULL |        1 |
|  1295 | 2019-07-18 |      3 |  NULL |        1 |
|  1303 | 2019-07-26 |      7 |  NULL |        1 |
|  1311 | 2019-08-03 |      5 |  NULL |        1 |
+-------+------------+--------+-------+----------+

Please suggest me how to get result as above!

请建议我如何获得如上所述的结果!

1 个解决方案

#1

If you join lsUsers on userId = appActual.OPDID you will get what you ask for: usedId (NULL) = OPDID (NULL). Is there any other way to join lsUsers?

如果你在userId = appActual.OPDID上加入lsUsers,你将得到你所要求的:usedId(NULL)= OPDID(NULL)。有没有其他方式加入lsUsers?