如何在列中搜索值，并在单个SQL查询中获取列的计数

I have a table of following structure in MySQL 5.7:

我在MySQL 5.7中有一个以下结构表:

CREATE TABLE `post_like` (
  `post_title_id` varchar(255) NOT NULL,
  `user_name` varchar(50) NOT NULL,
  PRIMARY KEY (`post_title_id`,`user_name`),
) ENGINE=InnoDB;

and I have the following data

我有以下数据

post_title_id    user_name

new-story        mani27
new-story        manipal
some-story       manipal

I am trying to get the count of likes for a particular story and also if a particular user has liked the story in a function likeStatus(user_name, post_title_id)

我试图得到特定故事的喜欢数量,以及特定用户是否喜欢像函数中的故事(user_name,post_title_id)

Suppose likeStatus(mani27, new-story) would result in:

假设likeStatus(mani27,new-story)会导致:

count    status
2        1

I am using the following query right now and it works right:

我现在使用以下查询,它的工作正常:

SELECT COUNT(user_name) AS count,
COUNT(CASE WHEN `user_name` = ? THEN 1 ELSE null END) as status
FROM post_like WHERE post_title_id = ?

But this would execute the case function on all the rows in the table rather than searching the indexed column user_name.

但是这将在表中的所有行上执行case函数,而不是搜索索引列user_name。

I could use two different queries to get count and status of username liking a post, but that would take much more time than this. So, is there an optimised way to get this output?

我可以使用两个不同的查询来获取用户喜欢帖子的计数和状态,但这需要比这更多的时间。那么,有没有一种优化的方法来获得这个输出?

3 个解决方案

#1

I didn't check the query but this should give you an idea. Try Group By

我没有检查查询,但这应该给你一个想法。尝试分组

SELECT COUNT(user_name) AS count,
COUNT(CASE WHEN `user_name` = ? THEN 1 ELSE null END) as status
FROM post_like GROUP BY post_title_id HAVING post_title_id=?

But this would execute the case function on all the rows in the table rather than searching the indexed column user_name

但是这将在表中的所有行上执行case函数,而不是搜索索引列user_name

When you group by basing on post_title_id= and then applying count functions on them, number of row searches for username can be reduced to rows in that group

当您基于post_title_id =进行分组,然后对它们应用计数函数时,用户名的行搜索次数可以减少到该组中的行数

#2

Add your condition inside CASE not in WHERE, then make sure you use DISTINCT to avoid duplicates:

在CASE中添加条件不在WHERE中,然后确保使用DISTINCT以避免重复:

SELECT COUNT(DISTINCT user_name) AS count,
COUNT(CASE WHEN `user_name` = ? AND post_title_id = ? THEN 1 ELSE null END) as status
FROM post_like

#3

You don't have to scan all the records to see if a user liked the post. Just use a subquery on the select list. That should use the primary key index. Something like this should work

您不必扫描所有记录以查看用户是否喜欢该帖子。只需在选择列表中使用子查询。那应该使用主键索引。这样的事情应该有效

SELECT COUNT(*), 
    (SELECT COUNT(*) FROM post_like WHERE postid=? AND userid=?)
WHERE postid=?

#1