问题

以下是您的代码的中文翻译：

我有2个表，我想通过使用group by和case来获得结果，就像这样

| title | ViewersCount | EditorsCount
|-------|------------- |-------------
|Recog  | 6            | 6
|Mile   | 4            | 2

而不是得到这个结果

| title | ViewersCount | EditorsCount
|-------|------------- |-------------
|Recog  | null         | 6
|Recog  |6             |null
|Mile   | null         | 2
|Mile   | 4            | null

我的查询如下

```sql
select distinct
  (db.title),
  case when da.type = "VIEW" then count(da.user_id) END as  ViewersCount,
  case when da.type = "EDIT" then count(da.user_id) End as EditorsCount
from dashboard db 
left join dashboard_access da 
  on db.id = da.dashboard_id
group by db.title, da.type;

select distinct
  (db.title),
  case when da.type ="VIEW" then count(da.user_id) END as  ViewersCount,
  case when da.type ="EDIT" then count(da.user_id) End as EditorsCount
from dashboard db 
left join dashboard_access da 
  on db.id = da.dashboard_id
group by db.title,da.type;

答案1

得分: 0

Group by title only in order to get one result row per title. Put the conditions inside COUNT then. This is called conditional aggregation and gets the result you are after.

select 
  db.title,
  count(case when da.type = 'VIEW' then 1 end) as viewerscount,
  count(case when da.type = 'EDIT' then 1 end) as editorscount
from dashboard db 
left join dashboard_access da on db.id = da.dashboard_id
group by db.title
order by db_title;

select 
  db.title,
  count(case when da.type = 'VIEW' then 1 end) as viewerscount,
  count(case when da.type = 'EDIT' then 1 end) as editorscount
from dashboard db 
left join dashboard_access da on db.id = da.dashboard_id
group by db.title
order by db_title;