sql 쿼리를 선택하여 특정 사용자의 게시물, 팔로워 및 팔로워 수를 가져옵니다.

sql 쿼리를 선택하여 특정 사용자의 게시물, 팔로워 및 팔로워 수를 가져옵니다.

이름이 붙은 데이터베이스가 하나 있습니다.sdb그 안에는 테이블이 3개 있다.users,posts그리고.users_follow

다음 3개의 테이블에 대한 테이블 쿼리를 만듭니다.

-- users table

CREATE TABLE `users` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `uname` varchar(25) NOT NULL,
 `email` varchar(35) NOT NULL,
 `password` varchar(255) NOT NULL,
 `user_status` enum('active','inactive','remove') DEFAULT 'active',
 `signup_at` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

사용자 테이블 데이터

-- posts table

CREATE TABLE `posts` (
 `post_id` int(11) NOT NULL AUTO_INCREMENT,
 `title` tinytext NOT NULL,
 `content` text NOT NULL,
 `posted_at` datetime DEFAULT CURRENT_TIMESTAMP,
 `user_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`post_id`),
 KEY `posts_fk1` (`user_id`),
 CONSTRAINT `posts_fk1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

투고 테이블 데이터

-- users_follow table

CREATE TABLE `users_follow` (
 `follow_id` int(11) NOT NULL AUTO_INCREMENT,
 `follower_id` int(11) DEFAULT NULL,
 `following_id` int(11) NOT NULL,
 `follow_status` enum('active','blocked') DEFAULT 'active',
 PRIMARY KEY (`follow_id`),
 KEY `users_follow_fk1` (`follower_id`),
 KEY `users_follow_fk2` (`following_id`),
 CONSTRAINT `users_follow_fk1` FOREIGN KEY (`follower_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `users_follow_fk2` FOREIGN KEY (`following_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

users_follow 테이블 데이터

다음 쿼리를 시도합니다(user_id = 1인 사용자의 경우).

SELECT a.user_id,
   SUM(CASE WHEN f.following_id = 1 THEN 1 ELSE 0 END) AS count_followers,
   SUM(CASE WHEN f.follower_id = 1 THEN 1 ELSE 0 END) AS count_followings,
   SUM(CASE WHEN p.user_id = 1 THEN 1 ELSE 0 END) AS count_posts
FROM users_follow f
RIGHT JOIN users a
ON f.following_id = a.user_id
RIGHT JOIN users b
ON f.follower_id = b.user_id
LEFT JOIN posts p
ON p.user_id = a.user_id
where a.user_id = 1;

위의 쿼리에 대해 동일한 카운트를 반환한다.count_posts그리고.count_followers,count_following = 0

출력: 산출량

아마 이런 것들이 필요할 것 같은데

select a.user_id,
       (
          select count(*)
          from posts p
          where a.user_id = p.user_id
       ) count_posts,
       (
          select count(*)
          from users_follow f
          where f.follower_id = a.user_id
       ) count_followings,
       (
          select count(*)
          from users_follow f
          where f.following_id = a.user_id
       ) count_followers
from users a

다음을 사용하여 작성하려면GROUP BY인라인 서브쿼리로 만들기 위해 반드시 필요한 것보다 더 많은 외부 조인이 필요합니다.그래서 이런 거.

select a.user_id,
       p.count_posts,
       f1.count_followings,
       f2.count_followers
from users a
left join (
  select a.user_id, count(*)
  from posts p
  group by a.user_id
) p on p.user_id = a.user_id
left join 
(
      select f.follower_id, count(*) count_followings
      from users_follow f
      group by  f.follower_id
) f2
left join f2.follower_id = a.user_id
(
      select f.following_id, count(*) count_followers
      from users_follow f
      group by  f.following_id
) f2 on f2.following_id = a.user_id

퍼포먼스user_follow표를 개선할 수 있습니다.

CREATE TABLE `users_follow` (
 -- get rid of `follow_id`
 `follower_id` int(11) DEFAULT NULL,
 `following_id` int(11) NOT NULL,
 `follow_status` enum('active','blocked') DEFAULT 'active',
 PRIMARY KEY (follower_id, following_id),
 KEY `users_follow_fk2` (`following_id`),
 CONSTRAINT `users_follow_fk1` FOREIGN KEY (`follower_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `users_follow_fk2` FOREIGN KEY (`following_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

언급URL : https://stackoverflow.com/questions/54685175/sql-select-query-to-get-count-of-posts-followers-and-following-for-specific-use