如何从一个表中选择2次记录? [英] How Do I Select 2 Times Records From One Table?
问题描述
我有2张如下表格
PostList:post_id(PK,Identity)
LikeList :like_id(PK,身份),post_id(FK)
LikeList中的每个帖子都很受欢迎。
这个任务就像在FaceBook上发布
我从.cs传递2个值,当DataList绑定时哪个post_id和当前用户ID(mid)
计算每个帖子如。
如果当前用户alredy喜欢,则更改按钮文本不像
i创建查询,如< br $>
SELECT mid
,( SELECT COUNT(post_id) FROM LikeList WHERE post_id = @ sp_post_id) as like_count
FROM LikeList WHERE (post_id = @ sp_post_id AND mid = @sp_mid)
GROUP BY mid
i得到post_id计数。
但如果过去post_id = 27并且
用户,则在下面的情况下/ mid = 2然后显然我们没有找到记录。
我想要的:检查帖子喜欢计数,检查用户是否喜欢这篇文章。 (我如何检查计数和当前用户是否喜欢?)
PostList
post_id fid mid post_img post_msg post_time
1 1 1 Tulips.jpg来自user1的帖子。 2015-05-26
3 3 2 Lighthouse.jpg来自user2的这篇文章。 2015-05-26
5 1002 3 road1.jpg来自user3的这篇文章。 2015-05-26
6 3 2 map1.jpg来自user2的这2条信息。 2015-05-27
7 1 1这是文章来自用户1 2015-05-27
26 1 1 globe.jpg 2015-05-28
27 1003 5 Gujarat.jpg这篇文章来自user5,再次。 2015-05-29
30 3 2 Location2.jpg来自user2的帖子2015-05-29
LikeList
like_id post_id mid like_status like_time
1 27 1 1 2015-05-29
2 30 1 1 2015-05 -29
3 6 1 1 2015-05-29
4 30 2 1 2015-05-29
9 6 2 1 2015-05-29
post_id:30次赞2次(在线用户)也喜欢)
当在线用户:mid = 2
post_id:27次赞1次(在线等)用户不喜欢它)
检查一下:
SELECT mid,COUNT(post_id) AS like_count
FROM LikeList
WHERE post_id = @ sp_post_id AND mid = @ sp_mid
GROUP BY mid
< span class =code-keyword> SELECT
post_id,
COUNT(*) AS LikeCount,
MAX ( CASE mid WHEN 3 THEN ' True' ELSE ' False' END ) AS UserLiked
FROM LikeList
WHERE post_id = 6
GROUP BY post_id
http://stackoverflow.com/questions/30567818/select-2-time-records-from-one-table/30568019#30568019 [ ^ ]
I have 2 table like below
PostList: post_id(PK,Identity)
LikeList: like_id(PK,Identity), post_id(FK)
Every entry of post in LikeList, is liked.
This task like post on FaceBook
I am passing 2 value from .cs, when DataList Binding Whichever post_id and Current User id(mid)
Count every post like.
if current user alredy liked, then change button text Unlike
i create query like,
SELECT mid
,(SELECT COUNT(post_id) FROM LikeList WHERE post_id=@sp_post_id) as like_count
FROM LikeList WHERE (post_id=@sp_post_id AND mid=@sp_mid)
GROUP BY mid
i got post_id count.
but in below case if past post_id=27 and
user/mid=2 then obviously we not found record.
What i want: Check post likes count , check if user like this post or not. (how i check counts and current user like or not?)
PostList
post_id fid mid post_img post_msg post_time
1 1 1 Tulips.jpg this post from user1. 2015-05-26
3 3 2 Lighthouse.jpg this post from user2. 2015-05-26
5 1002 3 road1.jpg this post from user3. 2015-05-26
6 3 2 map1.jpg this 2 post from user2. 2015-05-27
7 1 1 This is text Post From User1 2015-05-27
26 1 1 globe.jpg 2015-05-28
27 1003 5 Gujarat.jpg this post from user5, again. 2015-05-29
30 3 2 Location2.jpg post from user2 2015-05-29
LikeList
like_id post_id mid like_status like_time
1 27 1 1 2015-05-29
2 30 1 1 2015-05-29
3 6 1 1 2015-05-29
4 30 2 1 2015-05-29
9 6 2 1 2015-05-29
What will happen: When user online: mid=1
post_id:30 likes 2 times (online user like too)
When user online: mid=2
post_id:27 likes 1 time (online user not like it)
Check this:
SELECT mid, COUNT(post_id) AS like_count FROM LikeList WHERE post_id=@sp_post_id AND mid=@sp_mid GROUP BY mid
SELECT post_id, COUNT(*) AS LikeCount, MAX(CASE mid WHEN 3 THEN 'True' ELSE 'False' END) AS UserLiked FROM LikeList WHERE post_id=6 GROUP BY post_id
http://stackoverflow.com/questions/30567818/select-2-time-records-from-one-table/30568019#30568019[^]
这篇关于如何从一个表中选择2次记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!