SQL分组查询问题7

发布网友发布时间：2023-10-23 20:18

共5个回答

热心网友时间：2024-11-29 03:38

select t1.branch_no, t1.cnt, t2.usercode, count(t2.usercode)
from (select branch_no, count(1) as cnt from table2 group by branch_no) t1
LEFT JOIN table2 t2 on t1.branch_no = t2.branch_no and t2.usercode is not null
group by t1.branch_no, t1.cnt, t2.usercode
order by t1.branch_no, t1.cnt, count(t2.usercode) desc

-------testing
BRANCH_NO CNT USERCODE COUNT
001 4 zhang san 2
001 4 li si 1
002 1 wang wu 1

热心网友时间：2024-11-29 03:39

with c as( select count(*) as nums ,max(usercode) as code from a group by usercode)

select max(branch_no) as 机构名称, count(*) as 机构下的信息总数,max(b.usercode) as 经理名称,max(c.nums) as 客户经理下的信息总数 from c,b where c.code=b.usercode group by b.usercode

嗯，你可以试试上面的一条sql 语句， a 是你第一个表， b 是第二个表！祝你成功！

热心网友时间：2024-11-29 03:39

select
branch_no
,usercode
,(select count(*) from table2 where branch_no = table1.branch_no ) as 该机构下的信息总数
,,(select count(*) from table2 where branch_no = table1.branch_no and usercode = table1.usercode ) as 该客户经理下的信息总数
from table1

热心网友时间：2024-11-29 03:40

select distinct table2.branch_no,count(table2.branch_no),table2.usercode,count(table2.usercode) from table2 group by table2.usercode
这个试试

热心网友时间：2024-11-29 03:41

你是只用数据库查询呢还是程序中要求在页面显示呢