postgre我应该执行什么SQL查询才能按预期获取结果集

Question

我在获得所需结果时遇到问题：/这些是我的平板电脑。在Postgresql上

       table: logins                              table: users
+------------------------+                 +------------------+
|   iduser  | date       |                 | iduser | name    |
|------------------------|                 |------------------|
|     1     |'2017-06-06'|                 |   1    | Joe     |
|------------------------|                 |------------------|
|     1     |'2017-06-06'|                 |   2    | Jane    |       
|------------------------|                 |------------------|
|     2     |'2017-06-07'|                 |   3    | Mary    |
|------------------------|                 +------------------+
|     3     |'2017-06-07'|
|------------------------|
|     3     |'2017-06-07'|
|------------------------|
|     3     |'2017-06-07'|
+------------------------+

我使用此查询：

SELECT name, date, count(*) FROM logins l
LEFT JOIN users u
ON u.iduser= l.iduser
GROUP BY 
u.name,l.date
ORDER BY 
l.date

这就是我得到的：

+-----------------------------------+
|  name    |    date      |   count |
|-----------------------------------|
| Joe      | '2017-06-06' |    2    |
|-----------------------------------|
| Jane     | '2017-06-07' |    1    |
|-----------------------------------|
| Mary     | '2017-06-07' |    3    |
+-----------------------------------+

但我真正需要从结果中得到的是：

+-----------------------------------+
|  name    |    date      |   count |
|-----------------------------------|
| Joe      | '2017-06-06' |    2    |
|-----------------------------------|
| Jane     | '2017-06-06' |    0    |
|-----------------------------------|
| Mary     | '2017-06-06' |    0    |
|-----------------------------------|
| Joe      | '2017-06-07' |    0    |
|-----------------------------------|
| Jane     | '2017-06-07' |    1    |
|-----------------------------------|
| Mary     | '2017-06-07' |    3    |
+-----------------------------------+

我该怎么办？请帮忙!!非常感谢！^^

Answer 1

在SQL Server和Postgres中：

获取 date 和 users 的所有组合，然后left join login：

select 
    d.date
  , u.name
  , count(l.iduser) as login_count
from (select distinct date from logins) d
  cross join users u 
  left join logins l 
    on l.iduser=u.iduser 
   and l.date=d.date
group by d.date, u.name

Rextester demo (SQL Server(： http://rextester.com/THJE85313

Rextester demo (postgres(： http://rextester.com/BNHE97192

返回：

+---------------------+------+-------------+
|        date         | name | login_count |
+---------------------+------+-------------+
| 2017-06-06 00:00:00 | Jane |           0 |
| 2017-06-07 00:00:00 | Jane |           1 |
| 2017-06-06 00:00:00 | Joe  |           2 |
| 2017-06-07 00:00:00 | Joe  |           0 |
| 2017-06-06 00:00:00 | Mary |           0 |
| 2017-06-07 00:00:00 | Mary |           3 |
+---------------------+------+-------------+