sql中如何用select 语句实现跨表计数查询？

2024-12-18 16:52:50

推荐回答（5个）

回答1：

正常的要用join。join分inner join，outter join，left join，right join。
这种情况用inner join，但根据不同的数据库软件，inner join有时候直接用join代替，所以你自己试一下该不该有inner。
select 单位名称,count(*),sum(case when 性别='女' then 1 else 0 end ),sum(case when 性别='男' then 1 else 0 end) from a join b on a.单位代码=b.单位代码

分步考虑，先不管b表：
select
`单位代码`,
count(*) as '单位人数',
sum(case when 性别='女' then 1 else 0 end) as '女性人数',

sum(case when 性别='男' then 1 else 0 end) as '男性人数'
from `a` group by `单位代码`;

然后加b表进去：
select `单位名称`, `单位人数`, `女性人数`, `男性人数` from

(select
`单位代码`,
count(a.*) as '单位人数',
sum(case when 性别='女' then 1 else 0 end) as '女性人数',

sum(case when 性别='男' then 1 else 0 end) as '男性人数'
from `a` group by `单位代码`
) as c
join b on c.`单位代码` = b.`单位代码`;

回答2：

select td.单位名称,ta.Num as 单位人数,tc.nvnum as 女生人数,tb.mannum as 男人数
from
(select 单位名称,单位代码 from 表b) as td ,
(select 单位代码,count(姓名) as Num from 表a ) as ta ,
(select 单位代码,count(姓名) as nanNum from 表a where 性别='男') as tb,
(select 单位代码,count(姓名) as nvNum from 表a where 性别='女') as tc
WHERE td.单位代码=ta.单位代码 and td.单位代码=tb.单位代码 and td.单位代码=tc.单位代码

回答3：

select b.单位名称, count(1) 单位人数,
sum(case when a.性别 = '女' then 1 when a.性别 = '男' then 0 end) as 女性人数,
sum(case when a.性别 = '男' then 1 when a.性别 = '女' then 0 end) as 男性人数
from a, b
where a.单位代码 = b.单位代码
group by b.单位名称

回答4：

name:a表中姓名,sex:a表中性别,dno:单位代码
select b.dname,
count(1) total,
sum(decode(a.sex,1,1,0)) male,
sum(decode(a.sex,2,1,0)) female
from a,b
where b.dno=a.dno
group by b.dname;

十四级的那位答案是正确的，最佳答案有一些小错误。

回答5：

select 单位名称,count(*),sum(case when 性别='女' then 1 else 0 end ),sum(case when 性别='男' then 1 else 0 end) from a,b where a.单位代码=b.单位代码 group by 单位名称