tb1Pid gender
1 Female
2 Female
3 Null(空值)
4 Null
5 Male
6 Maletb2eid Pid
aa 1
bb 2
cc 3我希望得到有多少Female,多少Male,多少Null
正确的返回是
2 Female
1 Null
0 Male
谢谢
1 Female
2 Female
3 Null(空值)
4 Null
5 Male
6 Maletb2eid Pid
aa 1
bb 2
cc 3我希望得到有多少Female,多少Male,多少Null
正确的返回是
2 Female
1 Null
0 Male
谢谢
解决方案 »
- 叶子,帮我测试下怎么做吧.........
- 如何让Select语句查询出来的Money类型格式化为货币表示形式(200.25元或¥200.25)?
- 恢复损坏文件的数据库信息。请大家帮帮帮超急超急超急
- 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004d01b
- 访问非本地数据库的地址格式是什么???
- 帮帮我
- 求助:在SQL中如何设置子查询的参数?
- sql修改标识的问题(在线等,急)
- 我是一个初学者请各位帮帮忙谢了。
- 关于锁的问题,帮帮我!谢谢!
- 请问哪里有比较好的安装包制作程序下载?
- 如何写一条语句得到一个表各个列的名称,类型,长度等!
create table #tb2 (eid varchar(100),pid int)insert into #tb1
select 1, 'Female' union all
select 2, 'Female' union all
select 3, Null union all
select 4, Null union all
select 5, 'Male' union all
select 6, 'Male'insert into #tb2
select 'aa', 1 union all
select 'bb', 2 union all
select 'cc', 3
select gender,sum(cnt) as 记录数
from
(
select gender,(select count(*) from #tb2 where pid=t1.pid) as cnt
from #tb1 as t1
) t2
group by genderdrop table #tb1
drop table #tb2
create table #tb2 (eid varchar(100),pid int)insert into #tb1
select 1, 'Female' union all
select 2, 'Female' union all
select 3, Null union all
select 4, Null union all
select 5, 'Male' union all
select 6, 'Male'insert into #tb2
select 'aa', 1 union all
select 'bb', 2 union all
select 'cc', 3
select gender,sum(cnt) as 记录数
from
(
select gender,(select count(*) from #tb2 where pid=t1.pid) as cnt
from #tb1 as t1
) t2
group by genderdrop table #tb1
drop table #tb2
--查询结果/*
gender 记录数
----------------
NULL 1
Female 2
Male 0*/
select sum(ss) 记录数,gender
from
(select (select count(*) from #tb2 where pid=#tb1.pid) as ss,gender
from #tb1)t
group by gender
order by sum(ss) desc
create table #tb2 (eid varchar(100),pid int)insert into #tb1
select 1, 'Female' union all
select 2, 'Female' union all
select 3, Null union all
select 4, Null union all
select 5, 'Male' union all
select 6, 'Male'insert into #tb2
select 'aa', 1 union all
select 'bb', 2 union all
select 'cc', 3--按记录数多少降序排列
select gender,sum(cnt) as 记录数
from
(
select gender,(select count(*) from #tb2 where pid=t1.pid) as cnt
from #tb1 as t1
) t2
group by gender
order by sum(cnt) descdrop table #tb1
drop table #tb2
--查询结果/*
gender 记录数
----------------
Female 2
NULL 1
Male 0*/
tb2换成db2.dbo.tb2不就行了