表A有两列,name是显示的值,type是类型分为1,2,3,4
name type
职业 2
表B有两列,id对应上面的type
id text
2 作家
我现在想查询出name那列和type对应表b里的个数,如果type不等与2则个数为1,type为2则为实际个数,我写了不对
select name,ItemNum =case when type = 2 then count(id) else 1 end
from A inner join B on A.type = B.id
name type
职业 2
表B有两列,id对应上面的type
id text
2 作家
我现在想查询出name那列和type对应表b里的个数,如果type不等与2则个数为1,type为2则为实际个数,我写了不对
select name,ItemNum =case when type = 2 then count(id) else 1 end
from A inner join B on A.type = B.id
select
[name],
ItemNum =case when type = 2 then (select count(id) from B where B.id=A.id)
else 1
end
from A
[name],
ItemNum =case when type = 2 then (select count(id) from B where B.id=A.type)
else 1
end
from A
type =2 then (select count(*) from b where id=a.type) else 1 end
from a
你是要统计子表匹配母表记录条数不?
select
[name],[count]
from
A
inner join
(
select [id],count(*) [count] from B
)B on A.[type] = B.[id]
from tableA,
(select count(id) as ItemNum ,id
from tableB
where id = 2
group by id
) AS B
where tableA.type=B.id
union
select tableA.name,1 as ItemNum
from tableA
where tableA .type<>2