如何在列中搜索值,并在单个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
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 =进行分组,然后对它们应用计数函数时,用户名的行搜索次数可以减少到该组中的行数
更多相关文章
- MYSQL中的普通索引,主健,唯一,全文索引区别
- 如何使用mysql 命令行 查看mysql表大小、数据大小、索引大小
- 确定mysql中索引的状态
- Atitit 数据库 标准库  sdk 函数库 编程语言 mysql oracle
- C标准库中的函数定义在哪里?
- 如何使用Angularjs显示函数的返回值
- JavaScript 字符串函数 之查找字符方法(一)
- 如何将对象作为参数传播给函数?
- 来自jQuery的Javascript中的等效函数