select memberID,sum(case when r.memberID is null then 0 else 0 end) as total from member as m left join reply as r on m.memberID=r.memberID group by m.memberID
select memberID,sum(case when r.memberID is null then 0 else 1 end) as total from member as m left join reply as r on m.memberID=r.memberID group by m.memberID
SELECT COUNT(*)记录条数,M.memberID FROM member M WHERE EXISTS(SELECT 1 FROM reply WHERE memberID=M.memberID) GROUP BY M.memberID
select a.memberID,count(*) from member a left join reply b on a.memberID=b.memberID
select a.memberID,count(*) from member a left join reply b on a.memberID=b.memberID group by a.memberID
有两个表:member: memberID,memberName,sex,shcool reply表: replyID,memberID 求一个SQL语句,查出member表中的每个memberID在reply表中的记录条数 SELECT A.MEMBERID,COUNT(REPLYID) AS 记录条数 FROM MEMBERE A, REPLY B WHERE A.MEMBERID = B.MEMBERID GROUP BY A.MEMBERID
select memberID,sum(case when r.memberID is null then 0 else 1 end) as total from member as m left join reply as r on m.memberID=r.memberID group by m.memberID正确
select a.memberID,memberName,sex,school,count(reply) as 个数 from member a,reply b where a.memberID=b.memberID group by a.memberID,memberName,sex,school
create table member (memberid int identity(1,1), membername varchar(100), sex varchar(10), school varchar(10) ) alter table member alter column memberid int create select * from member insert into member select '12','男','大学' union all select '2','女','小学' union all select '34','女','小学' union all select '45','女','初中' union all select '122','男','高中' union all select '456','男','小学' union allcreate table reply (replyid int identity(1,1), memberid int ) insert into reply select '1' union all select '2' union all select '3' union all select '1' union all select '2' union all select '12' union all select '2' union all select '1' union all select '3' select a.memberid,a.membername,a.sex,a.school,sum(b.memberid) as number from member a join reply b on a.memberid=b.memberid group by a.memberid,a.membername,a.sex,a.school
select memberID,memberName,sex,school,replyCount=(select count(1) from reply where memberId=member.memberId) from member
这个肯定有错,memberId 不在聚合函数或 GROUP BY 子句中
with cte_reply ( select memberID , replyCount = COUNT(replyID) from reply group by memberID ) SELECT m.*, replyCount = ISNULL(r.replyCount, 0) FROM member m LEFT JOIN cte_reply r ON r.memberID = m.memberID ORDER BY m.memberID练习一下
select memberID, memberName, sex, shcool, (select num from(select distinct count(replyID) as num,memberID group by memberID) as b where a.memberID=b.memberID) from member as a
from member as m left join reply as r
on m.memberID=r.memberID
group by m.memberID
from member as m left join reply as r
on m.memberID=r.memberID
group by m.memberID
WHERE EXISTS(SELECT 1 FROM reply WHERE memberID=M.memberID)
GROUP BY M.memberID
select a.memberID,count(*)
from member a left join reply b
on a.memberID=b.memberID
select a.memberID,count(*)
from member a left join reply b
on a.memberID=b.memberID
group by a.memberID
memberID,memberName,sex,school,replyCount
memberID,memberName,sex,shcool reply表:
replyID,memberID 求一个SQL语句,查出member表中的每个memberID在reply表中的记录条数
SELECT A.MEMBERID,COUNT(REPLYID) AS 记录条数
FROM MEMBERE A, REPLY B
WHERE A.MEMBERID = B.MEMBERID
GROUP BY A.MEMBERID
from member as m left join reply as r
on m.memberID=r.memberID
group by m.memberID正确
group by a.memberID,memberName,sex,school
(memberid int identity(1,1),
membername varchar(100),
sex varchar(10),
school varchar(10)
)
alter table member alter column memberid int
create
select * from member
insert into member
select '12','男','大学' union all
select '2','女','小学' union all
select '34','女','小学' union all
select '45','女','初中' union all
select '122','男','高中' union all
select '456','男','小学' union allcreate table reply
(replyid int identity(1,1),
memberid int
)
insert into reply
select '1' union all
select '2' union all
select '3' union all
select '1' union all
select '2' union all
select '12' union all
select '2' union all
select '1' union all
select '3'
select a.memberid,a.membername,a.sex,a.school,sum(b.memberid) as number
from member a join reply b on a.memberid=b.memberid
group by a.memberid,a.membername,a.sex,a.school
select memberID,memberName,sex,school,replyCount=(select count(1) from reply where memberId=member.memberId)
from member
这个肯定有错,memberId 不在聚合函数或 GROUP BY 子句中
(
select memberID
, replyCount = COUNT(replyID)
from reply
group by memberID
)
SELECT m.*, replyCount = ISNULL(r.replyCount, 0)
FROM member m
LEFT JOIN cte_reply r
ON r.memberID = m.memberID
ORDER BY m.memberID练习一下
select
memberID,
memberName,
sex,
shcool,
(select num from(select distinct count(replyID) as num,memberID group by memberID) as b where a.memberID=b.memberID)
from member as a