select a.id,count(1) from company as a,employee as b where a.id=b.employee_company_id group by a.id比如我要查出所有公司的员工数量 有些公司没有员工, 那我就只显示出 id count 1 20 4 10 其它的id不显示了。我想要没有员工的显示为0如何实现啊? id count 1 20 2 0 3 0 4 10谢谢。
显示全部 select a.id,count(b.count) as counts from company a inner join employee b on a.id=b.employee_company_id group by a.id 显示不为0的 select a.id,count(b.count) as counts from company a inner join employee b on a.id=b.employee_company_id where counts>0 group by a.id
显示0的 select a.id,count(1) from company a left join employee b on a.id=b.employee_company_id group by a.id
select a.id ,(select count(1) from employee where a.id=employee_company_id) as 总数 from company as a
不显示0的 select a.id,count(1) from company a inner join employee b on a.id=b.employee_company_id group by a.id
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([id] int,[count] int) insert [TB] select 1,20 union all select 4,10select number,[count]=isnull([count],0) from spt_values left join [TB] A on A.id=Number where type='p' and Number>0 and Number<=(select max(id)from TB)/* number count ----------- ----------- 1 20 2 0 3 0 4 10(所影响的行数为 4 行)*/drop table TB
select a.id,ISNULL(count(1) ,0)AS NUM from company as a LEFT JOIN employee as b ON a.id=b.employee_company_id group by a.id
select a.id,count(1) from company a left join employee b on a.id=b.employee_company_id group by a.id 你的显示0的。会出现1。
select a.id,ISNULL(count(1) ,0)AS NUM from company as a LEFT JOIN employee as b ON a.id=b.employee_company_id group by a.id 这个也是显示1哦。。
试试select a.id,sum(case when isnull(b.employee_company_id ,0) then 0 else 1 end) from company as a,employee as b where a.id=b.employee_company_id group by a.id
我测试了只有 select a.id ,(select count(1) from employee where a.id=employee_company_id) as 总数 from company as a 可以。
谢谢,太喜欢你们了,学到了很多东西经过测试的确是她 select a.id ,(select count(1) from employee where a.id=employee_company_id) as 总数 from company as a但效率实在是很低下,需5500ms,但用Group by 仅需47ms.但鱼与熊掌不能兼得嘛,还是很有用的
select a.id,count(b.count) as counts from company a inner join employee b on a.id=b.employee_company_id group by a.id
显示不为0的
select a.id,count(b.count) as counts from company a inner join employee b on a.id=b.employee_company_id where counts>0 group by a.id
select a.id,count(1) from company a left join employee b
on a.id=b.employee_company_id
group by a.id
select a.id
,(select count(1) from employee where a.id=employee_company_id) as 总数
from company as a
select a.id,count(1) from company a inner join employee b
on a.id=b.employee_company_id
group by a.id
如果是上面这样绑定的话 就这样写:
<%#Eval("字段").ToString().Length<0?Eval("字段").ToString().Substring(0,0)+"0":Eval("字段")%>如果是在后台绑定的话
那就要遍历了 然后赋值了
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[count] int)
insert [TB]
select 1,20 union all
select 4,10select number,[count]=isnull([count],0)
from spt_values left join [TB] A on A.id=Number
where type='p' and Number>0 and Number<=(select max(id)from TB)/*
number count
----------- -----------
1 20
2 0
3 0
4 10(所影响的行数为 4 行)*/drop table TB
from company as a
LEFT JOIN
employee as b
ON a.id=b.employee_company_id group by a.id
on a.id=b.employee_company_id
group by a.id 你的显示0的。会出现1。
from company as a
LEFT JOIN
employee as b
ON a.id=b.employee_company_id group by a.id 这个也是显示1哦。。
select a.id
,(select count(1) from employee where a.id=employee_company_id) as 总数
from company as a
可以。
因为count(1) 我统计的是行数,你用lfet join.就算是null 也不就是一行了吗。
select a.id
,(select count(1) from employee where a.id=employee_company_id) as 总数
from company as a但效率实在是很低下,需5500ms,但用Group by 仅需47ms.但鱼与熊掌不能兼得嘛,还是很有用的