一个sql查询问题，实在没办法解决啊，只有50分了，麻烦帮帮忙····

[繁花似锦剩下青春忧伤]

数据库表结构如图：

我要查询的结构如图：


上面截图已经简要的说明了我要查询的东西，但是我sql太差不知道怎么写...............


有两张表A表和B表 ，2张表存在主外建关系，图中能看到，

我现在要按照第二张图的查询，来查出我要的结果......


希望各位大哥大姐帮忙看看应该怎么写语句，，，，，，，，，

实在想不出来，就这么多分 不好意思了，，，，，，，，

[yanyuchonglou]

该下班了，明天再看吧。

[繁花似锦剩下青春忧伤]

[Quote=引用 1 楼 yanyuchonglou 的回复:]
该下班了，明天再看吧。
[/Quote]
.......................................大哥晚上有时间给看看呗

[-晴天]

select name,
(select count(*) from a where name=t.name)namecount,
(select count(*) from a where newname=t.name)newnamecount,
count(*) countName,
(select count(*) from a innner join b on a.id=b.id and a.name=t.name and b.type=1)type1name,
(select count(*) from a innner join b on a.id=b.id and a.newname=t.name and b.type=1)type1name,
(select count(*) from b where type=1)counttype1,
(select count(*) from a innner join b on a.id=b.id and a.name=t.name and b.type=0)type0name,
(select count(*) from a innner join b on a.id=b.id and a.newname=t.name and b.type=0)type0name,
(select count(*) from b where type=0)counttype0
from( 
select name from a
union
select newname from a
)t

[快溜]

count(*) countName不对哦

[快溜]

countName=(select count(*) from a where name=t.name or newname=t.name)

[繁花似锦剩下青春忧伤]

[Quote=引用 5 楼 ssp2009 的回复:]
countName=(select count(*) from a where name=t.name or newname=t.name)
[/Quote]

大哥能给个全的吗

[繁花似锦剩下青春忧伤]

select [name],
(select count(*) from a where [name]=t.[name])namecount,
(select count(*) from a where newname=t.[name])newnamecount,
countName=(select count(*) from a where name=t.name or newname=t.name),
(select count(*) from a innner join b on a.id=b.aid and a.[name]=t.[name] and b.[type]=1)type1name,
(select count(*) from a innner join b on a.id=b.aid and a.newname=t.[name] and b.[type]=1)type1name,
(select count(*) from b where [type]=1)counttype1,
(select count(*) from a innner join b on a.id=b.aid and a.[name]=t.[name] and b.[type]=0)type0name,
(select count(*) from a innner join b on a.id=b.aid and a.newname=t.[name] and b.[type]=0)type0name,
(select count(*) from b where [type]=0)counttype0
from( 
select [name] from a
union
select newname from a
)t

我大概修改了下 但是还不行么····

[繁花似锦剩下青春忧伤]

select [name],
(select count(*) from a where [name]=t.[name])namecount,
(select count(*) from a where newname=t.[name])newnamecount,
countName=(select count(*) from a where name=t.name or newname=t.name),
(select count(*) from a innner join b on a.id=b.aid and a.[name]=t.[name] and b.[type]=1)type1name,
(select count(*) from a innner join b on a.id=b.aid and a.newname=t.[name] and b.[type]=1)type1name,
(select count(*) from b where [type]=1)counttype1,
(select count(*) from a innner join b on a.id=b.aid and a.[name]=t.[name] and b.[type]=0)type0name,
(select count(*) from a innner join b on a.id=b.aid and a.newname=t.[name] and b.[type]=0)type0name,
(select count(*) from b where [type]=0)counttype0
from( 
select [name] from a
union
select newname from a
)t

我大概修改了下 但是还不行啊········

[-晴天]

修正:

create table a(id int,name varchar(10),newname varchar(10),prjname varchar(10))
insert into a select 1,'zs','ls','prj1'
insert into a select 2,'zs','ww','prj2'
insert into a select 3,'ls','ww','prj3'
insert into a select 4,'ls','zs','prj4'
insert into a select 5,'ww','zs','prj5'
insert into a select 6,'zs','wmz','prj6'
create table b(id int,aid int,type int)
insert into b select 1,1,0
insert into b select 2,2,1
insert into b select 3,1,1
insert into b select 4,2,0
insert into b select 5,1,1
insert into b select 6,2,0
insert into b select 7,3,1
insert into b select 8,4,1
insert into b select 9,5,0
insert into b select 10,6,1
go

select name,
(select count(*) from a where name=t.name)namecount,
(select count(*) from a where newname=t.name)newnamecount,
(select count(*) from a where name=t.name)+(select count(*) from a where newname=t.name) countName,
(select count(*) from a inner join b on a.id=b.aid and a.name=t.name and b.type=1)type1name,
(select count(*) from a inner join b on a.id=b.aid and a.newname=t.name and b.type=1)type1newname,
(select count(*) from a inner join b on a.id=b.aid and a.name=t.name and b.type=1)+
(select count(*) from a inner join b on a.id=b.aid and a.newname=t.name and b.type=1)counttype1,
(select count(*) from a inner join b on a.id=b.aid and a.name=t.name and b.type=0)type0name,
(select count(*) from a inner join b on a.id=b.aid and a.newname=t.name and b.type=0)type0name,
(select count(*) from a inner join b on a.id=b.aid and a.name=t.name and b.type=0)+
(select count(*) from a inner join b on a.id=b.aid and a.newname=t.name and b.type=0)counttype0
from( 
select name from a
union
select newname from a
)t group by name
/*
name       namecount   newnamecount countName   type1name   type1newname counttype1  type0name   type0name   counttype0
---------- ----------- ------------ ----------- ----------- ------------ ----------- ----------- ----------- -----------
ls         2           1            3           2           2            4           0           1           1
wmz        0           1            1           0           1            1           0           0           0
ww         1           2            3           0           2            2           1           2           3
zs         3           2            5           4           1            5           3           1           4

(4 行受影响)

*/
go
drop table a,b

[快溜]

哪里不对？

[繁花似锦剩下青春忧伤]

谢谢楼上几位帮忙，我去好好研究下，等下结贴··················

[繁花似锦剩下青春忧伤]

[Quote=引用 10 楼 ssp2009 的回复:]
哪里不对？
[/Quote]

我正在拿第一次发的和第二次的对比 我自己去看看············我数据库太差了··········

[繁花似锦剩下青春忧伤]

[Quote=引用 10 楼 ssp2009 的回复:]
哪里不对？
[/Quote]

谢谢 好了····