A表:
aid,name其中aid为主键
内容如下:
1 aaa1
2 aaa2
3 aaa3 B表:
bid,aid,bep 其中bid为主键,aid与A表中的aid对应(有重复)
内容如下:
1 1 bep1
2 1 bep2
3 2 bep3C表:
cid,aid,cep 其中cid为主键,aid与A表中的aid对应(有重复)
内容如下:
1 1 cep1
2 2 cep2
3 2 cep3现希望得到如下查询结果:
aid name bep cep
1 aaa1 bep1
1 aaa1 bep2
1 aaa1 cep1
2 aaa2 bep3
2 aaa2 cep2
2 aaa2 cep3
3 aaa3请问如何用sql 查询语句实现?我就差一点!可怎么也实现不了,最好能实现对每个字段的模糊查询和组合查询!遗憾的是只能给100分
aid,name其中aid为主键
内容如下:
1 aaa1
2 aaa2
3 aaa3 B表:
bid,aid,bep 其中bid为主键,aid与A表中的aid对应(有重复)
内容如下:
1 1 bep1
2 1 bep2
3 2 bep3C表:
cid,aid,cep 其中cid为主键,aid与A表中的aid对应(有重复)
内容如下:
1 1 cep1
2 2 cep2
3 2 cep3现希望得到如下查询结果:
aid name bep cep
1 aaa1 bep1
1 aaa1 bep2
1 aaa1 cep1
2 aaa2 bep3
2 aaa2 cep2
2 aaa2 cep3
3 aaa3请问如何用sql 查询语句实现?我就差一点!可怎么也实现不了,最好能实现对每个字段的模糊查询和组合查询!遗憾的是只能给100分
select *
into tb_a
from (
select '1' as aid,'aaa1' as nm
union all
select '2' as aid,'aaa2' as nm
union all
select '3' as aid,'aaa3' as nm
) as z--你的b表
select *
into tb_b
from(
select '1' as bid,'1' as aid,'bep1' as bep
union all
select '2' as bid,'1' as aid,'bep2' as bep
union all
select '3' as bid,'2' as aid,'bep3' as bep
) AS Z--你的c表
select *
into tb_c
from(
select '1' as cid,'1' as aid,'cep1' as cep
union all
select '2' as cid,'2' as aid,'cep2' as cep
union all
select '3' as cid,'2' as aid,'cep3' as cep
) as z--联合记录,保存到临时表,也得到了总行数,这里是关键,你看我保存了些什么
select *
into #Tmp_Table
from(
select tb_b.aid,tb_a.nm as [name],tb_b.bid as iid, tb_b.bep as exp1,'b' as exp2,Convert(Varchar(255),'') as bep,Convert(Varchar(255),'') as cep from tb_b left join tb_a on tb_b.aid=tb_a.aid
union all
select tb_c.aid,tb_a.nm as [name],tb_c.cid as iid, tb_c.cep as exp1,'c' as exp2,Convert(Varchar(255),'') as bep,Convert(Varchar(255),'') as cep from tb_c left join tb_a on tb_c.aid=tb_a.aid
) as z
order by z.aid--现在可以查它了
select aid,
[name],
bep=(case when exp2='b' then exp1 else '' end),
cep=(case when exp2='c' then exp1 else '' end)
from #tmp_Table
order by aid--这里删除了刚才建立的那些表
drop table #tmp_Table
drop table tb_a
drop table tb_b
drop table tb_c/** 结果和你的一样
aid name bep cep
1 aaa1 bep1
1 aaa1 bep2
1 aaa1 cep1
2 aaa2 bep3
2 aaa2 cep2
2 aaa2 cep3
3 aaa3
**/
(select a.aid,a.name,b.bep,null cep from a left join b on a.aid=b.aid
union
select a.aid,a.name,null bep,c.cep from a left join c on a.aid=c.aid
)
order by aid