weixin_42498200
weixin_42498200
2012-04-18 10:18

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 MoneyEastSea 9年前

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

    点赞 评论 复制链接分享
  • iteye_12303 iteye_12303 9年前

    少写了个'!'
    [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]

    点赞 评论 复制链接分享
  • iteye_12303 iteye_12303 9年前

    没有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]

    点赞 评论 复制链接分享
  • Shabrave Shabrave 9年前

    不知道楼主是否要这样的结果,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, '')))

    点赞 评论 复制链接分享
  • librasama librasama 9年前

    [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去重复记录

    点赞 评论 复制链接分享
  • sunjinyujeep sunjinyujeep 9年前

    [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]
    效率不是很好,也许能实现你的功能

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

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

    点赞 评论 复制链接分享
  • MoneyEastSea MoneyEastSea 9年前

    [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]
    能实现你要的功能,,但是你这个表。。没有的字段,有的是无,有的是''。这种写法太恶心了吧

    点赞 评论 复制链接分享
  • zyn010101 zyn010101 9年前

    这个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)

    点赞 评论 复制链接分享
  • zyn010101 zyn010101 9年前

    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)

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

    不会吧,最多没过滤字段,那你看看这个,不过我用的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]

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

    主要是你这个无字
    [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]

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

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

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

    我咋说,我做了半天也不知道你的结果怎么来的
    他的这个不知道你用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]

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

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

    点赞 评论 复制链接分享
  • ll89308839 ll89308839 9年前

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

    点赞 评论 复制链接分享

相关推荐