mysql - SQL将列添加到结果中，其中包含匹配查询的其他表中的行总和

Question

我正在尝试进行 SQL 查询，该查询将获取不同表中的行总和，其中 uuid 列与 中的 post 列匹配type 列并将该值附加到结果行。 votes 表中的 type 列具有 0 和 1 的值，0 表示得分总和为 -1，1 表示得分总和为 +1。一旦所有这些都完成了，我希望对 score 列中的结果进行排序，让最高分排在第一位。

如果您想要一个 SQL fiddle，链接在这里:http://sqlfiddle.com/#!9/cc490b/1/0

posts 表的表架构的简化版本是:

 uuid varchar(256)
 contents text
 author varchar(256)
 comment varchar(256)
 timestamp varchar(256)
 removed varchar(256)

votes 表的架构是:

 user varchar(256)
 post varchar(256)
 type int(1)

因为这是查询的一部分，所以这是 follows 表的架构:

 user varchar(256)
 target varchar(256)

我当前获取帖子提要的查询如下，我希望以此为基础:

SELECT * 
  FROM posts 
 WHERE timestamp <= ? 
   AND removed IS NULL 
   AND comment IS NULL 
   AND author IN (SELECT target 
                    FROM follows 
                   WHERE user = ? 
                   UNION 
                  SELECT ? AS target) 
 ORDER 
    BY timestamp DESC 
 LIMIT 25;

我期望的查询的最终结果如下:

| uuid | contents | author | comment | timestamp  | removed | score (DESC) |
| p12c | Hi!      | u7h34  | NULL    | 1560269397 | NULL    | 451          |
| p100 | Chicken  | u211f  | NULL    | 1560269417 | NULL    | 14           |

DDL:

DROP TABLE IF EXISTS `follows`;
DROP TABLE IF EXISTS `votes`;
DROP TABLE IF EXISTS `posts`;

CREATE TABLE `follows` (
  `user` varchar(256) NOT NULL,
  `target` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `follows` (`user`, `target`) VALUES
('user-uuid2', 'user-uuid1');

CREATE TABLE `votes` (
  `user` varchar(256) NOT NULL,
  `post` varchar(256) NOT NULL,
  `type` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `votes` (`user`, `post`, `type`) VALUES
('user-uuid2', 'post-uuid9', 0),
('user-uuid2', 'post-uuid9', 1),
('user-uuid2', 'post-uuid8', 1);

CREATE TABLE `posts` (
  `uuid` varchar(256) NOT NULL,
  `contents` text NOT NULL,
  `author` varchar(256) NOT NULL,
  `comment` varchar(256) DEFAULT NULL,
  `timestamp` varchar(256) NOT NULL,
  `removed` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `posts` (`uuid`, `contents`, `author`, `comment`, `timestamp`, `removed`) VALUES
('post-uuid1', 'Hello world1!', 'user-uuid1', NULL, '1560184505', NULL),
('post-uuid2', 'Hello world2!', 'user-uuid1', NULL, '1560184506', NULL),
('post-uuid3', 'Hello world3!', 'user-uuid1', NULL, '1560184507', NULL),
('post-uuid4', 'Hello world4!', 'user-uuid1', NULL, '1560184508', NULL),
('post-uuid5', 'Hello world5!', 'user-uuid1', NULL, '1560184509', NULL),
('post-uuid6', 'Hello world6!', 'user-uuid1', NULL, '1560184510', NULL),
('post-uuid7', 'Hello world7!', 'user-uuid1', NULL, '1560184511', NULL),
('post-uuid8', 'Hello world8!', 'user-uuid1', NULL, '1560184512', NULL),
('post-uuid9', 'Hello world9!', 'user-uuid1', NULL, '1560184513', NULL),
('post-uuid10', 'Hello world10!', 'user-uuid1', NULL, '1560184514', NULL),
('post-uuid11', 'Hello world11!', 'user-uuid1', NULL, '1560184515', NULL),
('post-uuid12', 'Hello world12!', 'user-uuid1', NULL, '1560184516', NULL);

Answer 1

除非我遗漏了什么，否则我认为您只需要 CASE 表达式的 SUM。

(我不得不注释掉 WHERE 子句，因为它会删除带投票的帖子。)

SELECT 
   p.uuid,
   p.contents,
   p.author,
   p.comment,
   p.timestamp,
   p.removed,
   SUM(COALESCE(CASE WHEN v.type = 0 THEN -1 ELSE v.type END,0)) as Score
  FROM posts p
  LEFT JOIN votes v
    ON v.post = p.uuid
-- WHERE timestamp <= '1560184509' 
   AND removed IS NULL 
   AND comment IS NULL 
   AND author IN (SELECT target 
                    FROM follows 
                   WHERE user = '2452be00-5b48-4c09-8abb-21d469bc8e11' 
                   UNION 
                  SELECT '2452be00-5b48-4c09-8abb-21d469bc8e11' AS target) 
 GROUP BY
   p.uuid,
   p.contents,
   p.author,
   p.comment,
   p.timestamp,
   p.removed
 ORDER 
    BY Score DESC 
 LIMIT 25;

你的 fiddle 回复给你:http://sqlfiddle.com/#!9/61068/8/0