sql - 有没有办法在左连接中实现计数

Question

我正在使用一个简单的数据库设置一个聊天组应用程序。我想知道是否有办法让我查看喜欢/不喜欢某个帖子的人，该帖子可以附有图片或视频。我想知道的重要信息是帖子ID，帖子的消息，帖子的日期，我要发布的图片的url，视频的url，以及发布该帖子的用户的id。我正在使用左连接来查看所有这些信息，但如果帖子有喜欢或不喜欢，我会丢失。

我已经尝试使用聚合计数来查看每个帖子的喜欢和不喜欢，但我不知道如何将此查询与以前的信息混合。

这是我查看上面列出的信息的查询：

select P.post_ID, P.post_msg, P.post_date, F.photo_url,V.video_url, P.user_ID
from ((Post as P left join Photo as F on P.post_ID = F.post_ID ) left join Video as V 
on P.post_ID = V.post_ID)
where P.chat_ID = 1

这是查询的输出：

这是我想要的输出示例：

上面的查询显示了我正在寻找的所有内容，除了每个帖子的喜欢和不喜欢。反正有没有看到这些信息与我想要的混合在一起？

这是数据库的结构：

create table Login(login_ID serial primary key, login_Date DATE NOT NULL DEFAULT CURRENT_DATE, user_name varchar(20) NOT NULL);

create table Users(user_ID serial primary key, user_name varchar(20) NOT NULL, user_password varchar(20) NOT NULL);

create table Contact_List(contactlist_ID serial primary key, user_name varchar(20) NOT NULL,user_ID integer references Users(user_ID) on delete cascade);

create table Person(person_ID serial primary key, person_name varchar(20) NOT NULL, person_lastname varchar(30) NOT NULL, person_phone varchar(12), person_email varchar(50) NOT NULL, user_ID INTEGER references Users(user_ID) on delete cascade);

create table Admin(admin_ID serial primary key, user_ID INTEGER references Users(user_ID) on delete cascade);

create table Chat_Group(chat_ID serial primary key, chat_name varchar(50) NOT NULL, admin_Id INTEGER references Admin(admin_ID) on delete cascade);

create table Group_List(user_ID INTEGER references Users(user_ID) on delete cascade, chat_ID integer references Chatgroup(chat_id) on delete cascade, primary key(user_id, chat_id));

create table Post(post_ID serial primary key, post_msg varchar(280), post_date DATE NOT NULL DEFAULT CURRENT_DATE, user_ID INTEGER references Users(user_ID) on delete cascade, chat_ID INTEGER references Chatgroup(chat_ID) on delete cascade);

create table Video(video_ID serial primary key, video_url varchar(280) NOT NULL, post_ID INTEGER references Post(post_ID) on delete cascade);

create table Photo(photo_ID serial primary key, photo_url varchar(280) NOT NULL, post_ID INTEGER references Post(post_ID) on delete cascade);

create table Reply(reply_ID serial primary key, reply_msg varchar(280) NOT NULL, reply_Date DATE NOT NULL DEFAULT CURRENT_DATE, post_ID INTEGER references Post(post_ID) on delete cascade, user_ID INTEGER references Users(user_ID) on delete cascade);

create table HashTag(hashtag_ID serial primary key, hashtag_msg varchar(200) NOT NULL,post_ID INTEGER references Post(post_ID) on delete cascade NOT NULL, user_ID INTEGER references Users(user_ID) on delete cascade);

create table Reaction(reaction_ID serial primary key,reaction_date DATE NOT NULL DEFAULT CURRENT_DATE, reaction_like BOOLEAN, reaction_dislike BOOLEAN, post_ID INTEGER references Post(post_ID) on delete cascade, user_ID INTEGER references users(user_ID) on delete cascade);

create table React_to(reaction_ID INTEGER references Reaction(reaction_ID) on delete cascade, reply_ID INTEGER references Reply(reply_ID) on delete cascade, primary key(reaction_ID,reply_ID));

请原谅我的英语不好，英语不是我的主要语言。

标签： sqldatabasepostgresqlpgadmin

Answer 1

一种方法是使用相关子查询。

SELECT p.post_id,
       p.post_msg,
       p.post_date,
       f.photo_url,
       v.video_url,
       p.user_id,
       (SELECT count(*)
               FROM reaction r
               WHERE r.post_id = p.post_id
                     AND r.reaction_like) likes,
       (SELECT count(*)
               FROM reaction r
               WHERE r.post_id = p.post_id
                     AND r.reaction_dislike) dislikes
       FROM post p
            LEFT JOIN photo f
                      ON p.post_id = f.post_id
            LEFT JOIN video v 
                      ON p.post_id = v.post_id
       WHERE p.chat_id = 1;

但是我想知道一个反应是否可以同时是喜欢和不喜欢。至少你的数据模型允许这样的事情......