日记表:userid,content(日记内容)
用户表:userid,username
部门表:userid,orgid
部门信息表:orgid,orgname查询每人发表的日记数量,查询效果如下
orgname username count
一年级 小米 5
五年级 小莉 10求一句sql语句查询,多谢了。
用户表:userid,username
部门表:userid,orgid
部门信息表:orgid,orgname查询每人发表的日记数量,查询效果如下
orgname username count
一年级 小米 5
五年级 小莉 10求一句sql语句查询,多谢了。
with t
as(
select
a.userid,
orgname
from
部门表 a
inner join
部门信息表 b
on
a.orgid=b.orgid
)
select
t.orgname,
u.username,
COUNT(c.content) as [count]
from
t
inner join
用户表 u
on
t.userid=u.userid
inner join
日记表 c
on
t.userid=content.userid
group by
t.orgname,
u.username
SELECT di.orgname ,
u.username ,
COUNT(1) [count]
FROM 用户表 u
INNER JOIN 部门表 D ON u.userid = d.userid
INNER JOIN 部门信息表 DI ON d.orgid = di.orgid
INNER JOIN 日记表 l ON u.userid = l.userid
GROUP BY di.orgname ,
u.username
select d.orgname,b.username,COUNT(*) as count from
日记表 a inner join 用户表 b on a.userid=b.userid
inner join 部门表 c on c.userid=b.userid
inner join 部门信息表 d on d.orgid=c.orgid
group by d.orgname,b.username
select d.orgname,b.username,COUNT(userid) as count from 日记表 as a , 用户表 as b , 部门表 as c , 部门信息表 as d where d.orgid=c.orgid , c.userid=b.userid, a.userid=b.userid group by d.orgname,b.username
bmxsb a,bmb c,yhb b where a.orgid=c.orgid and b.userid=c.userid
group by a.orgname,b.username