查询出每篇文章的点赞数，和当前用户是否点赞

---

前言

---

有三张表，文章表，用户表，点赞表
用户可以给发布文章，给文章点赞
需要一个sql查询出每篇文章的点赞数，和当前用户是否点赞

drop table wt_msg_rec
create table wt_msg_rec(
  msg_rec_id int primary key identity(1,1),
  [user_id] int foreign key references usr_users([user_id]) not null,
  content varchar(500),
  like_count int default(0)
)
go


drop table wt_like_rec
create table wt_like_rec(
  like_rec_id int primary key identity(1,1),
  [user_id] int foreign key references usr_users([user_id]) not null,
  msg_rec_id int foreign key references wt_msg_rec(msg_rec_id) not null
)
go

查询sql

SELECT msg_rec_id, content
	, (
		SELECT count(1) AS cnt
		FROM wt_like_rec a
		WHERE a.msg_rec_id = c.msg_rec_id
	) AS is_like
	, (
		SELECT count(1) AS cnt
		FROM wt_like_rec a
		WHERE a.msg_rec_id = c.msg_rec_id
			AND user_id = 1
	) AS like_count
FROM wt_msg_rec c
go

结果
content：内容
is_like：是否点赞
like_count：点赞次数