Postgresql 匹配中的匹配数

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', 'market3');
insert into market values('mar4', 'it4', 'market4');
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', 'market5');
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 t_items.title = 'item3'
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 | it3     | item3 | mar10     | market3 |      2      |     2               
2 | it3     | item3 | mar3      | market3 |      2      |     2

这是一个工作示例 https://rextester.com/EAA9855 .

必须使用来自市场。

谢谢。

Answer 1

我希望这能奏效，而且它正是您所需要的。这样做相当仓促。

select  t_items.item_id,
        t_items.title,
        c.market_id,
        c.title,
        c.market_count,
        count(distinct items_likes.like_id) as likes_count 

from (select item_id, market_id, title, (select count(distinct market_id) as market_count
                                         from market a
                                         where a.item_id = b.item_id)
      from market b
      group by item_id, market_id, title) c

left join t_items on c.item_id = t_items.item_id
left join items_likes on t_items.item_id = items_likes.item_id
where t_items.title = 'item2'
group by t_items.item_id, t_items.title, c.market_id, c.title, c.market_count;


 item_id | title | market_id |  title  | market_count | likes_count
---------+-------+-----------+---------+--------------+-------
 it3     | item3 | mar10     | market3 |            2 |     2
 it3     | item3 | mar3      | market3 |            2 |     2
(2 Zeilen)

for item 2

 item_id | title | market_id |  title  | market_count | likes_count
---------+-------+-----------+---------+--------------+-------
 it2     | item2 | mar2      | market2 |            2 |     2
 it2     | item2 | mar7      | market5 |            2 |     2
(2 Zeilen)

```