A表:
AID AName
1 a
2 b
3 c
4 d
5 E
B表
BID AID
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
10 4想通过SQL语句查询出如下表样式的结果:
AID AName count
1 a 4
2 b 3
3 c 2
4 d 1
5 e 0count是统计B表中有AID的数量的,这样的SQL语句怎么写?!求指导!!!!!!!!!!!!!!!!!!!
AID AName
1 a
2 b
3 c
4 d
5 E
B表
BID AID
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
10 4想通过SQL语句查询出如下表样式的结果:
AID AName count
1 a 4
2 b 3
3 c 2
4 d 1
5 e 0count是统计B表中有AID的数量的,这样的SQL语句怎么写?!求指导!!!!!!!!!!!!!!!!!!!
解决方案 »
- 请问asp.net程序怎么测试?就是测试代码的效率?
- 如何在asp.NET webservices中设断点?
- 这SQL Update语句没报错有什么更新不了
- 请问大家如何用正则表达式来过滤下面的标记
- 在datagrid中如何从radiobuttonlist取值出来更新数据库?
- 取日期问题
- 为什么无法用“Page”取得页面中的控件的名称和数量?不知道错在哪里。代码在正文里。
- 做一个淘宝这样得网站(支付宝那块除外),.NET做适合吗? 大概要多少人/天(3年以上.NET熟手)?
- 框架问题急~~~~~~~
- 建虚拟目录奇怪的问题,请大家解答
- ASP.NET怎么将EXCEL表格中的数据导入到MYSQL数据库中
- 求一个 jquery 无刷新分页例子 asp.net
BID AID step
1 1 1
2 1 1
3 1 1
4 1 1
5 2 1
6 2 1
7 2 1
8 3 1
9 3 1
10 4 1
11 1 2
12 1 2
13 2 2
14 3 2
用Step控制 count1是step=1的,count2是step=2查询出来的表:
AID AName count1 count2
1 a 4 2
2 b 3 1
3 c 2 1
4 d 1 0
5 e 0 0
这样写不会显示出count为0的
select a.aid,a.aname,count(b.aid) c from #tempA a left join #tempB b on a.aid=b.aid group by a.aid,a.aname
/*
aid aname c
1 a 4
2 b 2
3 c 2
4 d 1
5 e 0
*/
go
if OBJECT_ID('A') is not null drop table A
go
if OBJECT_ID('B') is not null drop table B
go
create table A
(
AID int,
AName nvarchar(20)
)
create table B
(
BID int,
AID int,
step int
)
insert into A
select 1,'A' union all
select 2,'B'
insert into B
select 1,1,1 union all
select 2,1,2--第一个
select A.AID,A.AName,COUNT(*) as count from A inner join B
on A.AID=B.AID group by A.AID,A.AName--第二个
select A.AID,A.AName,sum(case B.step when 1 then 1 else 0 end ) as count1,
sum(case B.step when 2 then 1 else 0 end ) as count2 from A inner join B
on A.AID=B.AID group by A.AID,A.AName
go
if OBJECT_ID('A') is not null drop table A
go
if OBJECT_ID('B') is not null drop table B
go
create table A
(
AID int,
AName nvarchar(20)
)
create table B
(
BID int,
AID int,
step int
)
insert into A
select 1,'A' union all
select 2,'B'
insert into B
select 1,1,1 union all
select 2,1,2--第一个
select A.AID,A.AName,COUNT(B.BID) as count from A left join B
on A.AID=B.AID group by A.AID,A.AName--第二个
select A.AID,A.AName,sum(case B.step when 1 then 1 else 0 end ) as count1,
sum(case B.step when 2 then 1 else 0 end ) as count2 from A left join B
on A.AID=B.AID group by A.AID,A.AName