TABLE A
ID CONTENT FLAG
1 TEST1 1
2 TEST2 1
3 TEST3 0TABLE B
ID F_ID NAME SUCCESS
1 1 A 1
2 1 B 1
3 1 C 0
4 2 A 0
5 2 D 1已知A.FLAG=1 求 GROUP BY F_ID 成功的个数,失败的个数,总个数,名字等信息
SQL 怎么写?
ID CONTENT FLAG
1 TEST1 1
2 TEST2 1
3 TEST3 0TABLE B
ID F_ID NAME SUCCESS
1 1 A 1
2 1 B 1
3 1 C 0
4 2 A 0
5 2 D 1已知A.FLAG=1 求 GROUP BY F_ID 成功的个数,失败的个数,总个数,名字等信息
SQL 怎么写?
解决方案 »
- SQL cte问题
- sql数据库错误日志回收要注意哪些?
- 使用Log explorer的问题,急求帮忙!
- 怎样建一个登录,使其只能读取某个数据库的某个表?
- sql查询问题,应该不难!我弄不出来,请大家帮忙!
- 请教一个在sqlserver2005中执行远程存储过程的问题
- 大侠们,两个数据库中的表能不能用一条sql语句关联起来??
- sql问题,可否实现数组的功能!
- sqlserver2000 做后台 ,连接人数一多,就变得很慢,怎么办?
- newyj(老鬼)来拿分,其他人莫入
- 本人材料专业高级工程师+高程(2005年考过)+系分(2006年考过),现想搞软件开发,没有资金,没有客户。哪里能找到投资渠道呢?哪里能找到客户呢?如何起步呢?
- 在sql2000里面创建一张表的一个字段是datetime,如何增加日期
,sum(case when b.success=0 then 1 else 0 end) 失败数
,count(*) 总数
from b inner join a on b.f_id=a.id
where a.flag=1
sum(case SUCCESS when 1 then 1 else 0 end) [成功的个数],
sum(case SUCCESS when 0 then 1 else 0 end) [失败的个数],
count(*) 总个数,
(select top 1 name from b where f_id = a.id) 名字
from a , b
where a.id = b.f_id and a.flag = 1
group by t.id , t.content , t.flag
(
select b.f_id
,成功个数 = sum(case when success = 1 then 1 else 0 end)
,失败个数 = sum(case when success = 0 then 1 else 0 end)
总个数 = count(success)
from b group by f_id
) T
join A on a.id = T.f_id
where a.flag = 1
create TABLE #1(ID int, CONTENT varchar(20), FLAG bit)
insert #1 values(1, 'TEST1', 1)
insert #1 values(2, 'TEST2', 1)
insert #1 values(3, 'TEST3', 0) Create TABLE #2(ID int, F_ID int, NAME varchar(20), SUCCESS bit )
insert #2 values(1, 1, 'A', 1)
insert #2 values(2, 1, 'B', 1)
insert #2 values(3, 1, 'C', 0)
insert #2 values(4, 2, 'A', 0)
insert #2 values(5, 2, 'D', 1)
select T.*,a.content as 姓名 from
(
select b.f_id
,成功个数 = sum(case when success = 1 then 1 else 0 end)
,失败个数 = sum(case when success = 0 then 1 else 0 end)
,总个数 = count(success)
from #2 b group by f_id
) T
join #1 a on a.id = T.f_id
where a.flag = 1/*
f_id 成功个数 失败个数 总个数 姓名
----------- ----------- ----------- ----------- --------------------
1 2 1 3 TEST1
2 1 1 2 TEST2(2 row(s) affected)*/
,sum(case when #b.success=0 then 1 else 0 end) 失败数
,count(*) 总数
from #b inner join #a on #b.f_id=#a.id where #a.flag=1
group by #b.namename 成功数
---------- -----------
A 3
B 3
C 3
A 3
D 3(5 行受影响)
insert into #A select 1, 'TEST1', 1
insert into #A select 2, 'TEST2', 1
insert into #A select 3, 'TEST3', 0 create TABLE #B (ID int, F_ID int , [NAME] varchar(10), SUCCESS int)
insert into #B select 1 , 1, 'A', 1
insert into #B select 2, 1, 'B', 1
insert into #B select 3, 1, 'C', 0
insert into #B select 4, 2, 'A', 0
insert into #B select 5, 2 , 'D', 1
select b.name ,A.成功数 ,C. 失败数,D.总数
from #B B cross apply ( select count(*) as 成功数 from #b inner join #A on #B.f_id=#A.id where #b.success = 1 and #A.flag=1 and #b.id= b.id) A
cross apply ( select count(*) as 失败数 from #b inner join #A on #B.f_id=#A.id where #b.success = 0 and #A.flag=1 and #b.id= b.id) C
cross apply ( select count(*) as 总数 from #b inner join #A on #B.f_id=#A.id where #A.flag=1 and #b.id= b.id) d
name 成功数 失败数 总数
---------- ----------- ----------- -----------
A 1 0 1
B 1 0 1
C 0 1 1
A 0 1 1
D 1 0 1(5 行受影响)
insert into #A select 1, 'TEST1', 1
insert into #A select 2, 'TEST2', 1
insert into #A select 3, 'TEST3', 0 create TABLE #B (ID int, F_ID int , [NAME] varchar(10), SUCCESS int)
insert into #B select 1 , 1, 'A', 1
insert into #B select 2, 1, 'B', 1
insert into #B select 3, 1, 'C', 0
insert into #B select 4, 2, 'A', 0
insert into #B select 5, 2 , 'D', 1
select A.[CONTENT] ,B.成功数 ,C. 失败数,D.总数
from #A A cross apply ( select count(*) as 成功数 from #b inner join #A on #B.f_id=#A.id where #b.success = 1 and #A.flag=1 and #b.f_id= A.id) B
cross apply ( select count(*) as 失败数 from #b inner join #A on #B.f_id=#A.id where #b.success = 0 and #A.flag=1 and #b.f_id= A.id) C
cross apply ( select count(*) as 总数 from #b inner join #A on #B.f_id=#A.id where #A.flag=1 and #b.f_id= A.id) d
CONTENT 成功数 失败数 总数
---------- ----------- ----------- -----------
TEST1 2 1 3
TEST2 1 1 2
TEST3 0 0 0(3 行受影响)