postgresql - Postgresql Matches in 匹配两个表

Question

我有三个表：t_items、market、items_likes。

create table t_items (
item_id varchar PRIMARY KEY,
title varchar);

create table market(
market_id varchar PRIMARY KEY,
item_id varchar references t_items(item_id),
title varchar);

CREATE TABLE items_likes (
like_id varchar PRIMARY KEY,
item_id varchar references t_items(item_id),
user_id varchar);

insert into t_items values('it1', 'item1');
insert into t_items values('it2', 'item2');
insert into t_items values('it3', 'item3');
insert into t_items values('it4', 'item4');
insert into t_items values('it5', 'item5');

insert into market values('mar1', 'it1', 'market1');
insert into market values('mar2', 'it2', 'market2');
insert into market values('mar3', 'it3', 'market1');
insert into market values('mar4', 'it4', 'market1');
insert into market values('mar5', 'it5', 'market5');
insert into market values('mar6', 'it1', 'market5');
insert into market values('mar7', 'it2', 'market5');
insert into market values('mar8', 'it1', 'market2');
insert into market values('mar9', 'it1', 'market5');
insert into market values('mar10', 'it3', 'market3');

insert into items_likes values('li1', 'it1', 'user1');
insert into items_likes values('li2', 'it1', 'user1');
insert into items_likes values('li3', 'it2', 'user2');
insert into items_likes values('li4', 'it2', 'user2');
insert into items_likes values('li5', 'it3', 'user3');
insert into items_likes values('li6', 'it3', 'user3');
insert into items_likes values('li7', 'it4', 'user4');
insert into items_likes values('li8', 'it4', 'user4');
insert into items_likes values('li9', 'it5', 'user5');
insert into items_likes values('li0', 'it5', 'user5');

我需要一个显示 t_items 名称、市场名称和拥有此 t_items 的市场数量的请求。这是我的要求：

select
  t_items.item_id,
  t_items.title,
  market.market_id,
  market.title,
  count(distinct items_likes.like_id) as likes_count,  
  count(distinct market.market_id ) as market_count
from market
  left join t_items on market.item_id = t_items.item_id
  left join items_likes on t_items.item_id = items_likes.item_id
where market.title = 'market1'
group by t_items.item_id, market.market_id
order by t_items.title;

此查询返回数据而不计算市场数量。最后，我想得到一个这样的表：

    item_id title   market_id   title   likes_count market_count
1   it1 item1   mar1            market1         2   4
2   it3 item3   mar3            market1         2   2
3   it4 item4   mar4            market1         2   1

这是一个工作示例https://rextester.com/live/UMNBFA11091。

使用from market和market.title是强制性的。

标签： postgresql