c只是字典,包括ID和企业名称,生产企业和销售企业都在里面。如果用两个字典,就写成: select c1.企业名称,c2.企业名称 from a,b,c where a.id=c1.id and b.id=c2.id 而两个都在C里,怎么写?
select c.id,ar.name,br.name from (select a.id as id,c.name as name from a,c where a.id=c.id ) as ar (select b.id as id,c.name as name from b,c where b.id=c.id ) as br (select id from c) as cr where ar.id=*cr.id and br.id=*cr.id
如果id是唯一的,也就是说一个id要么是生产企业,要么就是销售企业则写成这样就行了 select a.id as id,c.name as name from a,c where a.id=c.id union select b.id as id,c.name as name from b,c where b.id=c.id order by id
select c.id,ar.name,br.name from (select a.id as id,c.name as name from a,c where a.id=c.id ) as ar, (select b.id as id,c.name as name from b,c where b.id=c.id ) as br, (select id from c) as cr where ar.id=*cr.id and br.id=*cr.id 少了两个逗号:(
select c.企业名称 as 生产企业 from a,c inner join (select c.id,c.企业名称 as 销售企业 from b,c where b.id=c.id ) as tmp on a.id=c.id and c.id=tmp.id
这是我的测试: 如果你已经取得的约束放在d表中 其结构如下: id id1 id2 1 1 3 2 2 2 3 4 4 1(id为自动编号,起识别用)select d.id,ar.name1,br.name2 from (select d.id,c.name as name1 from d,c where d.id1=c.id) as ar, (select d.id,c.name as name2 from d,c where d.id2=c.id) as br, d where ar.id=d.id and br.id=d.id union select d.id,c.name as name1,'' as name2 from d,c where d.id1=c.id and d.id2 is null union select d.id,'' as name1,c.name as name2 from d,c where d.id2=c.id and d.id1 is null order by d.id上面就是我的查询语句,结果为 id name1 name2 1 n1 n3 2 n2 n2 3 n4 4 n1(所影响的行数为 4 行)
如果使用两个SQL指令,可以用: select name from c where a.id=c.id select name from c where b.id=c.id 然后合并成为一行:name name 这很简单,因为取得的结果是唯一的(A,B表组合的其他条件保证结果唯一) 我只是探讨是否能用一次查询得出结果。
修正一下我上面的语句: select ar.name,br.name from (select d.id as id ,c.name as name from c,d where d.id1=c.id) as ar FULL OUTER join (select d.id as id ,c.name as name from c,d where d.id2=c.id) as br on ar.id=br.id否则放在这里太没面子了:)
select c1.企业名称,c2.企业名称 from a,b,c
where a.id=c1.id and b.id=c2.id
而两个都在C里,怎么写?
(select a.id as id,c.name as name from a,c where a.id=c.id ) as ar
(select b.id as id,c.name as name from b,c where b.id=c.id ) as br
(select id from c) as cr
where ar.id=*cr.id and br.id=*cr.id
select a.id as id,c.name as name from a,c where a.id=c.id
union
select b.id as id,c.name as name from b,c where b.id=c.id
order by id
(select a.id as id,c.name as name from a,c where a.id=c.id ) as ar,
(select b.id as id,c.name as name from b,c where b.id=c.id ) as br,
(select id from c) as cr
where ar.id=*cr.id and br.id=*cr.id 少了两个逗号:(
一个id还是一批id?
你的记录是不是这样的:
a(id):1,2,4...
b(id):2,3
c(id,name):(1,n1),(2,n2),(3,n3),(4,n4)......
得到结果:
id name1 name2
1 n1
2 n2 n2
3 n3
4 n4 是不是啊?
A表里有生产企业的代码,B表里有销售企业的代码,C表是字典
我在其他条件约束下,取得了A,B表的唯一组合(全部记录),只是需要把两个代码换成两个企业名称(都是在C里,可能相同,也可能不同)
清楚了吗?
inner join
(select c.id,c.企业名称 as 销售企业 from b,c where b.id=c.id ) as tmp
on a.id=c.id and c.id=tmp.id
如果你已经取得的约束放在d表中
其结构如下:
id id1 id2
1 1 3
2 2 2
3 4
4 1(id为自动编号,起识别用)select d.id,ar.name1,br.name2 from
(select d.id,c.name as name1 from d,c where d.id1=c.id) as ar,
(select d.id,c.name as name2 from d,c where d.id2=c.id) as br,
d
where ar.id=d.id and br.id=d.id
union
select d.id,c.name as name1,'' as name2 from d,c where d.id1=c.id and d.id2 is null
union
select d.id,'' as name1,c.name as name2 from d,c where d.id2=c.id and d.id1 is null
order by d.id上面就是我的查询语句,结果为
id name1 name2
1 n1 n3
2 n2 n2
3 n4
4 n1(所影响的行数为 4 行)
select name from c where a.id=c.id
select name from c where b.id=c.id
然后合并成为一行:name name
这很简单,因为取得的结果是唯一的(A,B表组合的其他条件保证结果唯一)
我只是探讨是否能用一次查询得出结果。
select ar.name,br.name from
(select d.id as id ,c.name as name from c,d where d.id1=c.id) as ar FULL OUTER join
(select d.id as id ,c.name as name from c,d where d.id2=c.id) as br on ar.id=br.id否则放在这里太没面子了:)