weixin_42498200
2012-04-18 10:18
浏览 1.0k

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

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

16条回答 默认 最新

  • MoneyEastSea 2012-04-18 15:15
    已采纳

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

    评论
    解决 无用
    打赏 举报
  • 查看更多回答(15条)

相关推荐 更多相似问题