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

Question

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]
想要的查询结果

Answer 1

[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不能合并'无'字的