有2个表如下,table2的ID是table1 ID的外键:table1
ID Title
1 你好啊
2 你好啊
3 你好啊table2
tID ID Sate
1 1 True
2 1 False
3 1 True
4 2 False
5 2 False
要求统计出的数据如下,Count为table2中ID外键为N的总和,TrueCountCount为table2中ID外键为N,并且Sate为True的总和:ID Title Count TrueCount
1 你好啊 3 2
2 你好啊 2 0
3 你好啊 0 0
ID Title
1 你好啊
2 你好啊
3 你好啊table2
tID ID Sate
1 1 True
2 1 False
3 1 True
4 2 False
5 2 False
要求统计出的数据如下,Count为table2中ID外键为N的总和,TrueCountCount为table2中ID外键为N,并且Sate为True的总和:ID Title Count TrueCount
1 你好啊 3 2
2 你好啊 2 0
3 你好啊 0 0
解决方案 »
- js里求字体变粗的代码
- System.NullReferenceException: 未将对象引用设置到对象的实例。
- 求教:在GridView的模板列中如何使用超链接控件
- 网站推荐人
- DataList简单问题
- 在treeview中,如何点击节点获得点击该节点的值。
- 急问:帮我看看程序错在什么地方?
- 找不到类型或命名空间名称“index”(
- 弹出窗体能控制不显示,最大化,最小化的按钮吗?
- 请问如何在Repeater中获得RadioButton的点击
- 如果你的公司可以做网页美工的请留言,我们公司现在要做美工,是用.net作的,一定要在南京哦,个人的不行,一定要使公司,因为要开发票的
- calendar能实现年的切换吗?怎样切换?
SELECT ID,Title,
(SELECT COUNT(*) FROM table2 b WHERE b.ID=a.ID) AS Count,
(SELECT COUNT(*) FROM table2 c WHERE c.ID=a.ID) AS TrueCount
FROM table1 a
(SELECT COUNT(*) FROM table2 b WHERE b.ID=a.ID) AS Count,
(SELECT COUNT(*) FROM table2 c WHERE Sate='True' and c.ID=a.ID) AS TrueCount
FROM table1 a
sum(case b.sata when 'true' then 1 else 0 end) as truecount
from table1 a left outer join table2 b
on a.id=b.id group by a.id,a.title自己试过了 呵呵 结贴把~~~~~~~~~
SELECT ID,Title,
(SELECT COUNT(*) FROM table2 b WHERE b.ID=a.ID) AS Count,
(SELECT COUNT(*) FROM table2 c WHERE Sate= 'True ' and c.ID=a.ID) AS TrueCount
FROM table1 a
1。内联接:
select a.*,(select count(1) from table2 where id=a.id)[count],
(select count(1) from table2 where id=a.id and sate='true')[truecount] from table1 a2、左外联接
select a.*,isnull(b.[count],0) [count],isnull(c.[truecount],0) [truecount] from table1 a left join (select id,count(1)[count] from table2 group by id)b on a.id=b.id
left join (select id,count(1)[truecount] from table2 where sate='true' group by id)c
on a.id=c.id第一种写法效率高于第二种
测试环境:
create table table1(id int,title varchar(20))
insert into table1 select 1,'你好啊'
insert into table1 select 2,'你好啊'
insert into table1 select 3,'你好啊'create table table2(tid int,id int,Sate varchar(20))
insert into table2 select 1,1,'True'
insert into table2 select 2,1,'False'
insert into table2 select 3,1,'True'
insert into table2 select 4,2,'False'
insert into table2 select 5,2,'False'