求条查询语句 。。。。。。

[yinxiaoqi]

大概有6张表。
表a

ID    name     sfjk   b_id
1      a        0      10
2      b        0      10
3      c        1      11
4      d        1      11
5      e        2      12

表b

bid   name   c_id
10    123     1
11    456     2

表c

cid   name
 1     ccc
 2     bbb

想要的结果是：

bid  name  c_id  cid  name  ID    name     sfjk   b_id     c0   c1   c2   c3
10   123     1    1   ccc   1      a        0      10      2     0    0    0
11   456     2    2   bbb   3      c        1      11      0     2    0    0

大家可以看下，co,c1,c2,c3 是sfjk字段出现的次数，0对应c0，1对应c1,2对应c2,3对应c3,
a.b_id=b.bid
b.c_id=c.cid
这是条件。

[百年树人]

name 为什么取a c ？

[yinxiaoqi]

[Quote=引用 1 楼 josy 的回复:]
name 为什么取a c ？
[/Quote]
是因为 不显示重复的记录 所以 去第一次出现的记录。所以是 a.name 是 a 和c

[SQLCenter]

--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(ID int, name varchar(8), sfjk int, b_id int)
insert into #a
select 1, 'a', 0, 10 union all
select 2, 'b', 0, 10 union all
select 3, 'c', 1, 11 union all
select 4, 'd', 1, 11 union all
select 5, 'e', 2, 12
--> 测试数据：#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(bid int, name int, c_id int)
insert into #b
select 10, 123, 1 union all
select 11, 456, 2
--> 测试数据：#c
if object_id('tempdb.dbo.#c') is not null drop table #c
create table #c(cid int, name varchar(8))
insert into #c
select 1, 'ccc' union all
select 2, 'bbb'

select
	b.bid,
	min(b.name)name,
	min(b.c_id)c_id,
	min(c.cid)cid,
	min(c.name)name,
	min(a.ID)ID,
	min(a.name)name,
	min(a.sfjk)sfjk,
	c0 = count(case a.sfjk when 0 then 1 end),
	c1 = count(case a.sfjk when 1 then 1 end),
	c2 = count(case a.sfjk when 2 then 1 end),
	c3 = count(case a.sfjk when 3 then 1 end)
from
	#a a, #b b, #c c
where 
	a.b_id = b.bid and b.c_id = c.cid
group by
	b.bid
/*
bid  name  c_id  cid  name  ID    name     sfjk   b_id     c0   c1   c2   c3
10   123     1    1   ccc   1      a        0      10      2     0    0    0
11   456     2    2   bbb   3      c        1      11      0     2    0    0
*/

[feixianxxx]

[Quote=引用 3 楼 sqlcenter 的回复:]

SQL code
--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(ID int, name varchar(8), sfjk int, b_id int)
insert into #a
select 1, 'a', 0, 10 union all
select 2, 'b',……
[/Quote]
膜拜