如下图表中有一个memberId字段
第二个图中有一个id字段现在的要求是要把第一个表按照MemberId分组,求出每一组的Count,显示表2中的name还要加上条件Operation=Add和 ObjectName=Post
并且,Creatdate>'2010-1-1'
第一个表叫 WebLog
第二个表叫Member
第二个图中有一个id字段现在的要求是要把第一个表按照MemberId分组,求出每一组的Count,显示表2中的name还要加上条件Operation=Add和 ObjectName=Post
并且,Creatdate>'2010-1-1'
第一个表叫 WebLog
第二个表叫Member
select a.memberid,COUNT(1) as [count],max(b.name) as name from weblog a
join member b on a.memberid=b.id
where a.operation='add' and a.objectname='post' and a.createdate>'2010-01-01'
group by a.memberid
from
(select MemberId,count(1) as cnt from weblog
where Operation='Add' and ObjectName='Post'
and Creatdate>'2010-1-1'
group by MemberId
) a
join member b on a.MemberId=b.id
(select MemberId, count(1)cn from WebLog where Operation='Add' and ObjectName='Post' group by MemberId) b
on a.Id=b.MemberId
where a.Creatdate>'2010-1-1'
T1.MEMBERID,T2.NAME,T1.TOTAL
FROM (
SELECT MEMBERID,COUNT(1) AS TOTAL
FROM WEBLOG
WHERE Operation='Add' AND ObjectName='Post'
GROUP BY MEMBERID
) T1
LEFT JOIN [MEMBER] T2 ON T1.MEMBERID=T2.ID
max(b.name)问您一下这个写法会不会只出来一个name呢