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忙，实在想不到该怎么查询了，先谢谢大家了。a表b表想要的查询结果 问题补充：kidding87 写道

解决方案

SELECT a.xianzhong,CASE WHEN a.chepai IN ('暂未上牌','')THEN b.chepaiELSE a.chepaiEND AS chepai,CASE WHEN a.fadongji IN ('无','')THEN b.fadongjiELSE a.fadongjiEND AS fadongji,CASE WHEN a.chejiahao IN ('无','')THEN b.chejiahaoELSE a.chejiahaoEND AS chejiahaoFROM 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!=''))上面是我的。。写的有点错了。。改下另外，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不能合并'无'字的
解决方案二：
少写了个'!'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
解决方案三：
没有oracle，也没有mysql ，在sqlserver下测试下，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
解决方案四：
不知道楼主是否要这样的结果，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, '')))
解决方案五：
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) )加个distinct去重复记录
解决方案六：
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!='';unionselect b.* from a,b where a.xianzhong=b.xianzhong and a.chejiahao=b.chejiahao and a.chejiahao!='无' and a.chejiahao!='';效率不是很好，也许能实现你的功能
解决方案七：
楼上的和我写的一样的。。。看来oracle带的函数还是比较好用的。。建议什么“无” 这些都update 为null判断起来舒服多了
解决方案八：
SELECT a.xianzhong,CASE a.chepai WHEN '暂未上牌' OR ''THEN b.chepaiELSE a.chepaiEND AS chepai,CASE a.fadongji WHEN '无' OR ''THEN b.fadongjiELSE a.fadongjiEND AS fadongji,CASE a.chejiahao WHEN '无' OR ''THEN b.chejiahaoELSE a.chejiahaoEND AS chejiahaoFROM 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!=''))能实现你要的功能，，但是你这个表。。没有的字段，有的是无，有的是''。这种写法太恶心了吧
解决方案九：
这个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)
解决方案十：
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)
解决方案十一：
不会吧，最多没过滤字段，那你看看这个，不过我用的oracle写的，不知道mysql支持不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 != '无'))
解决方案十二：
主要是你这个无字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!='无'))
解决方案十三：
哥哥，我就想问，你的结果到底要算的是什么。只要a表的险种与b表的险种相同，并且a表的车牌，发动机号，车大架号与b表的车牌，发动机号，车大架号只要3个中的一个相同，则显示出来相同的记录。不相同的咋整呢
解决方案十四：
我咋说，我做了半天也不知道你的结果怎么来的他的这个不知道你用mysql运行过结果没我没有mysql，用的oracle应该就是下面的这个意思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)
解决方案十五：
这个结果是怎么得到的呢？
其他方案：
想了半天也不知道楼主要的是什么结果。。。