SQL子查询统计

Question

2张表A和B A是主表 关联字段 A:visit_id B:visitId
2表数据如下：
A：
visit_id
1
2
3
B:
visitId typeId
1 65
1 14
2 65
2 14
3 65
3 14
想要得到结果集：
visit_id次数 14出现次数 65出现次数
3 3 3

Answer 1

select
count(distinct(b.visit_id))'visit_id次数',

count(case when typeid=14 then 1 else 0)'14出现次数',
count(case when typeid=65 then 1 else 0)'65出现次数' from a
inner join b on a.visit_id=b.visitId

Answer 2

把楼上的代码改下 如果那个typeId 字段是int就把case中的引号去掉
select count(distinct(b.visitId)) 'visit_id次数' ,sum(case b.typeId when '14' then 1 else 0 end ) '14出现次数',
sum(case b.typeId when '65' then 1 else 0 end ) '65出现次数' from A a inner join B b on a.visit_id =b.typeId

Answer 3

select visitid,sum(cast typedid when 14 then 1else 0 end ),
sum(cast typedid when 14 then 1else 0 end )
from b

Answer 4

三列数值需要三次查询，合并结果集应该可以吧