时间戳

首页 > 解决方案 > 态度是多对多。我的参与。PostgreSQL

sql - 态度是多对多。我的参与。PostgreSQL

问题描述

我有三个表t_items，t_users，items_likes。

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

create table t_users(
user_id varchar PRIMARY KEY,
title varchar);

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

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 t_items values('it6', 'item6');
insert into t_items values('it7', 'item7');
insert into t_items values('it8', 'item8');
insert into t_items values('it9', 'item9');
insert into t_items values('it0', 'item0');

insert into t_users values('user1', 'name1');
insert into t_users values('user2', 'name2');
insert into t_users values('user3', 'name3');
insert into t_users values('user4', 'name4');
insert into t_users values('user5', 'name5');

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

我需要一个请求来显示用户喜欢的总次数。在这里，这样的请求只给出了我喜欢的数量。我需要喜欢的总数。

select
    t_items.item_id,
    t_items.title,
  count(distinct items_likes.like_id) as likes_count
from t_items
LEFT OUTER join items_likes on t_items.item_id = items_likes.item_id and items_likes.user_id = 'user3'
where 
items_likes.user_id = 'user3'
group by t_items.item_id, items_likes.user_id;

我有错误的结果：

  item_id title likes_count
1  it1    item1     1
2  it3    item3     1

我需要结果：

  item_id title likes_count
1  it1    item1     3
2  it3    item3     2

如何查看我参与的点赞总数？

工作示例： https ://rextester.com/live/TZUAAE31639

标签： sqlpostgresql

解决方案

select
    t_items.item_id,
    min(t_items.title) as title,
    count(case when item_likes.user_id = 'user3' then 1 else null end) as my_likes
    count(distinct items_likes.like_id) as total_likes
from t_items left outer join items_likes on t_items.item_id = items_likes.item_id
group by t_items.item_id, items_likes.user_id
having count(case when item_likes.user_id = 'user3' then 1 else null end) > 0;

推荐阅读