mysql两表联合查询，两个表结构一样，多个字段中的3个字段，只要这3个字段有重复的就罗列出来

a表与b表字段都是相同的，字段为：
险种，车牌，发动机号，车大架号
现在想实现的是：
只要a表的险种与b表的险种相同，并且a表的车牌，发动机号，车大架号与b表的车牌，发动机号，车大架号只要3个中的一个相同，则显示出来相同的记录。
a表创建及记录：
DROP TABLE IF EXISTS a;
CREATE TABLE a (
xianzhong varchar(30) DEFAULT NULL,
chepai varchar(20) DEFAULT NULL,
fadongji varchar(30) DEFAULT NULL,
chejiahao varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO a VALUES ('DAA','吉B3120挂','无','');
INSERT INTO a VALUES ('DZA','吉B3120挂','无','');
INSERT INTO a VALUES ('DAA','暂未上牌','ABCD','无');
INSERT INTO a VALUES ('DAA','吉B3146挂','无','');
INSERT INTO a VALUES ('DZA','吉B3146挂','无','');

b表创建及记录：
DROP TABLE IF EXISTS b;
CREATE TABLE b (
xianzhong varchar(30) DEFAULT NULL,
chepai varchar(20) DEFAULT NULL,
fadongji varchar(30) DEFAULT NULL,
chejiahao varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO b VALUES ('DAA','吉B3120挂','无','LH9B3G40090HHT174');
INSERT INTO b VALUES ('DZA','吉B3120挂','无','LH9B3G40090HHT174');
INSERT INTO b VALUES ('DAA','吉B12345','','1345');
INSERT INTO b VALUES ('DAA','吉B3146挂','无','LH9B3G40990HHT173');
INSERT INTO b VALUES ('DZA','吉B3146挂','无','LH9B3G40990HHT173');

a表及b表及想要的结果图片如下，请大家bangbang忙，实在想不到该怎么查询了，先谢谢大家了。

[img]http://dl.iteye.com/upload/attachment/0066/7629/962e1a43-686d-3d95-8ac2-6e40d518db81.jpg[/img]
a表

[img]http://dl.iteye.com/upload/attachment/0066/7631/5a3e82d5-8875-3009-9669-eddad06e4bbe.jpg[/img]
b表

[img]http://dl.iteye.com/upload/attachment/0066/7633/08c4bb48-fe27-3018-92fa-5c8e66800f1e.jpg[/img]
想要的查询结果

[code="java"]SELECT a.xianzhong,
CASE WHEN a.chepai IN ('暂未上牌','')
THEN b.chepai
ELSE a.chepai
END AS chepai,
CASE WHEN a.fadongji IN ('无','')
THEN b.fadongji
ELSE a.fadongji
END AS fadongji,
CASE WHEN a.chejiahao IN ('无','')
THEN b.chejiahao
ELSE a.chejiahao
END AS chejiahao
FROM a,b WHERE a.xianzhong=b.xianzhong
AND( (a.chepai=b.chepai AND a.chepai!='无' AND a.chepai!='') OR (a.fadongji=b.fadongji AND a.fadongji!='无' AND a.fadongji!='') OR (a.chejiahao=b.chejiahao AND a.chejiahao!='无' AND a.chejiahao!=''))[/code]
上面是我的。。写的有点错了。。改下
另外，select distinct b.*

from a

, b

where a.xianzhong = b.xianzhong and( a.chepai = b.chepai or a.fadongji = b.fadongji or
a.chejiahao = b.chejiahao)

这们兄弟写的。。你把a表中的任意一个fadongji改成不是无。。你就错了。。可以试下，distinct不能合并'无'字的

想了半天也不知道楼主要的是什么结果。。。

这个结果是怎么得到的呢？

我咋说，我做了半天也不知道你的结果怎么来的
他的这个不知道你用mysql运行过结果没
我没有mysql，用的oracle
应该就是下面的这个意思
[code="sql"]
select b.*
from a
left join b
on a.xianzhong = b.xianzhong
where (a.chepai = b.chepai) or (a.fadongji = b.fadongji) or
(a.chejiahao = b.chejiahao)

[/code]

哥哥，我就想问，你的结果到底要算的是什么。
只要a表的险种与b表的险种相同，并且a表的车牌，发动机号，车大架号与b表的车牌，发动机号，车大架号只要3个中的一个相同，则显示出来相同的记录。
不相同的咋整呢

主要是你这个无字
[code="sql"]
select * from a,b where a.xianzhong=b.xianzhong and (
(a.chepai=b.chepai and a.chepai !='无') or
(a.fadongji = b.fadongji and a.fadongji!='无') or
(a.chejiahao=b.chejiahao and a.chejiahao!='无')
)
[/code]

不会吧，最多没过滤字段，那你看看这个，不过我用的oracle写的，不知道mysql支持不
[code="sql"]
select a.xianzhong as xianzhong,
nvl(a.chepai,b.chepai) as chepai ,
nvl(a.fadongji,b.fadongji) as fadongji,
nvl(a.chejiahao,b.chejiahao) as chejiahao
from a, b
where a.xianzhong = b.xianzhong
and ((a.chepai = b.chepai and a.chepai != '无') or
(a.fadongji = b.fadongji and a.fadongji != '无') or
(a.chejiahao = b.chejiahao and a.chejiahao != '无'))
[/code]

select b.*

from a

, b

where a.xianzhong = b.xianzhong and( a.chepai = b.chepai or a.fadongji = b.fadongji or

a.chejiahao = b.chejiahao)

这个sql可以满足你需要的结果:分数拿来，哈哈！
select distinct b.*

from a

, b

where a.xianzhong = b.xianzhong and( a.chepai = b.chepai or a.fadongji = b.fadongji or

a.chejiahao = b.chejiahao)

[code="java"]SELECT a.xianzhong,
CASE a.chepai WHEN '暂未上牌' OR ''
THEN b.chepai
ELSE a.chepai
END AS chepai,
CASE a.fadongji WHEN '无' OR ''
THEN b.fadongji
ELSE a.fadongji
END AS fadongji,
CASE a.chejiahao WHEN '无' OR ''
THEN b.chejiahao
ELSE a.chejiahao
END AS chejiahao
FROM a,b WHERE a.xianzhong=b.xianzhong
AND( (a.chepai=b.chepai AND a.chepai!='无' AND a.chepai!='') OR (a.fadongji=b.fadongji AND a.fadongji!='无' AND a.fadongji!='') OR (a.chejiahao=b.chejiahao AND a.chejiahao!='无' AND a.chejiahao!=''))[/code]
能实现你要的功能，，但是你这个表。。没有的字段，有的是无，有的是''。这种写法太恶心了吧

楼上的和我写的一样的。。。
看来oracle带的函数还是比较好用的。。
建议什么“无” 这些都update 为null判断起来舒服多了

[code="java"]
select b.* from a,b where a.xianzhong=b.xianzhong and a.chepai=b.chepai and a.chepai!='无' and a.chepai!='';
union
select b.* from a,b where a.xianzhong=b.xianzhong and a.fadongji=b.fadongji and a.fadongji!='无' and a.fadongji!='';
union
select b.* from a,b where a.xianzhong=b.xianzhong and a.chejiahao=b.chejiahao and a.chejiahao!='无' and a.chejiahao!='';
[/code]
效率不是很好，也许能实现你的功能

[code="java"]
select distinct b.* from a, b where b.xianzhong = a.xianzhong and (
(a.chepai = b.chepai) OR
(a.fadongji = b.fadongji) OR
(a.chejiahao = b.chejiahao)
)
[/code]
加个distinct去重复记录

不知道楼主是否要这样的结果，Oracle的，自己看看mysql怎么用：
1将“无”当空处理：
SELECT DISTINCT b.*
FROM a, b
WHERE b.xianzhong = a.xianzhong
AND ((nvl(a.chepai, '无') = nvl(b.chepai, '无')) OR (nvl(a.fadongji, '无') = nvl(b.fadongji, '无')) OR
(nvl(a.chejiahao, '无') = nvl(b.chejiahao, '无')))

2“无”和空不一样：

SELECT DISTINCT b.*
FROM a, b
WHERE b.xianzhong = a.xianzhong
AND ((nvl(a.chepai, '') = nvl(b.chepai, '')) OR (nvl(a.fadongji, '') = nvl(b.fadongji, '')) OR
(nvl(a.chejiahao, '') = nvl(b.chejiahao, '')))

没有oracle，也没有mysql ，在sqlserver下测试下，
[code="java"]
select b.*
from a left join b on(a.xianzhong=b.xianzhong)
where (a.chepai=b.chepai and (a.chepai!='暂未上牌' or b.chepai!='暂未上牌'))
OR (a.fadongji=b.fadongji and (a.fadongji!='无' or b.fadongji!='无'))
OR (a.chejiahao=b.chejiahao and (a.chejiahao='无' or b.chejiahao='无' ))
group by b.chepai,b.fadongji,b.chejiahao,b.xianzhong[/code]

少写了个'!'
[code="java"]
select b.*
from a left join b on(a.xianzhong=b.xianzhong)
where (a.chepai=b.chepai and (a.chepai!='暂未上牌' or b.chepai!='暂未上牌'))
OR (a.fadongji=b.fadongji and (a.fadongji!='无' or b.fadongji!='无'))
OR (a.chejiahao=b.chejiahao and (a.chejiahao!='无' or b.chejiahao!='无' ))
group by b.chepai,b.fadongji,b.chejiahao,b.xianzhong
[/code]