ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 17201|回复: 18

[SQL] 请教一个SQL的写法

[复制链接]
论坛徽章:
1
复活蛋
日期:2012-05-21 14:39:53
发表于 2017-2-19 09:57 | 显示全部楼层 |阅读模式
本帖最后由 kangpengjie 于 2017-2-19 20:39 编辑

有这样的几张表
create table a(id varchar2(10),item_id varchar2(10));

create table a1(id varchar2(10),value varchar2(20));
create table a2(id varchar2(10),value varchar2(20));
create table a3(id varchar2(10),value varchar2(20));

a表的item_id 与 a1,a2,a3的id是对应的。
数据准备如下:
insert into a(id,item_id) values('1','11');
insert into a(id,item_id) values('1','22');
insert into a(id,item_id) values('1','33');
insert into a(id,item_id) values('2','111');insert into a(id,item_id) values('3','2222');
insert into a(id,item_id) values('3','3333');
insert into a(id,item_id) values('4','11111');
insert into a(id,item_id) values('4','22222');

insert into a1(id,value) values('11','a1');
insert into a2(id,value) values('22','a2');
insert into a3(id,value) values('33','a3');
insert into a1(id,value) values('111','a1');
insert into a2(id,value) values('2222','a2');
insert into a3(id,value) values('3333','a3');
insert into a1(id,value) values('11111','a1');
insert into a2(id,value) values('22222','a2');

现在想查出以下这样的效果,
1、a包含a1,并且a包含(a2或a3),出来的结果是 a(id='1', id='4')。
2、a包含a1,或者a包含(a2并且a3),出来的结果是 a(id ='1' id= '2' id='3')

根据a包含a1,a2,a3的与或关系,写出对应的SQL。
比如给出一个表达式: a contains a1 and contains(a2 or a3)  或 a contains a1 or contains(a2 and a3)
根据这个表达式写出对应的SQL语句,这个表达式是多变的,请教如何写出一个通用的SQL

认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-19 10:30 | 显示全部楼层
方法比较多,提供几下几种方法抛砖引玉。针对各表不同的数据量和数据分布特征,其性能是有差异的。
1、select a.*
        from a
        where a.item_id in (select id from a1)
            and a.item_id in (select id form a2 union all select id from a3);

2、select a.*
        from a,a1
        where a.item_id=a1.id
            and (exists (select 1 from a2 where a2.id=a.item_id) or
                   exists (select 1 from a3 where a3.id=a.item_id)
                   );
    注: 如果A的item_id与a1.id存在一对多的关系,要加 distinct ,即 select distinct a.* .....
3、 select t.*
         from (select a.*,case when a2.id is not null or a3.id is not null then 1 else 0 end flag
                     from a join a1 on a.item_id=a1.id
                              left join a2 on a.item_id=a2.id
                              left join a3 on a.item_id=a3.id
                ) t
         where t.flag=1;
       注: 如果A的item_id与a1.id、a2.id、a3.id中任一存在一对多的关系,要加 distinct ,即 select distinct t.* .....

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-19 10:31 | 显示全部楼层
本帖最后由 bfc99 于 2017-2-19 10:39 编辑

回的贴子被吞了?再写一遍。
方法比较多,提供以下几个抛砖引玉。当这几个表的数据量和数据分布特征不同时,以下几种方法的性能是有差异的:
1、select a.*
         from a
         where a.item_id in (select id from a1)
             and a.item_id in (select id from a2 union all select id from a3);

2、select a.*
         from a,a1
        where a.item_id=a1.id
            and (exists (select 1 from a2 where a.item_id=a2.id) or
                    exists (select 1 from a3 where a.item_id=a3.id)
                  );
     注:当a.item_id与a1.id存在一对多的关系时,要加DISTINCT,即 select distinct a.* ......

3、select t.id,t.item_id
        from (select a.*,case when a2.id is not null or a3.id is not null then 1 else 0 end flag
                     from a join a1 on a.item_id=a1.id
                               left join a2 on a.item_id=a2.id
                              left join a3 on a.item_id=a3.id
                  ) t
       where t.flag=1;
      注:当a.item_id与a1.id、a2.id、a2.id中任一个,存在一对多的关系时,要加DISTINCT,即 select distinct t.id,t.item_id ......

使用道具 举报

回复
论坛徽章:
1
复活蛋
日期:2012-05-21 14:39:53
 楼主| 发表于 2017-2-19 11:24 | 显示全部楼层
bfc99 发表于 2017-2-19 10:31
回的贴子被吞了?再写一遍。
方法比较多,提供以下几个抛砖引玉。当这几个表的数据量和数据分布特征不同时 ...

感谢回复。但你的第一个方法里, a.item_id in (select id from a1) 这个条件就把a里item_id 是22,33的过滤了,你再加  and a.item_id in (select id from a2 union all select id from a3); 这个条件也没用了

第二个方法 a.item_id=a1.id 也是把item_id 是22,33的过滤了

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-19 11:31 | 显示全部楼层
kangpengjie 发表于 2017-2-19 11:24
感谢回复。但你的第一个方法里, a.item_id in (select id from a1) 这个条件就把a里item_id 是22,33的过 ...

你的需求是:“a包含a1,并且包含(a2或a3)”
我的理解是:
“a包含a1”的只有以下两条满足条件:
insert into a(id,item_id) values('1','11');
insert into a(id,item_id) values('2','111');

进一步,“并且包含((a2或a3)”的,则上述两条中没有任一条满足。

使用道具 举报

回复
论坛徽章:
1
复活蛋
日期:2012-05-21 14:39:53
 楼主| 发表于 2017-2-19 11:43 | 显示全部楼层
本帖最后由 kangpengjie 于 2017-2-19 11:45 编辑
bfc99 发表于 2017-2-19 11:31
你的需求是:“a包含a1,并且包含(a2或a3)”
我的理解是:
“a包含a1”的只有以下两条满足条件:

可能我没表达清楚,以我给的用例来说,id为1的a,他包含a1(11),a2(22),a3(33), id为2的a,他只含有a1(111),所以 a包含a1,并且包含(a2或a3) 出来的结果就是 id为1的a。这里的并且包含是指a 又包含

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-19 11:51 | 显示全部楼层
kangpengjie 发表于 2017-2-19 11:43
可能我没表达清楚,以我给的用例来说,id为1的a,他包含a1(11),a2(22),a3(33), id为2的a,他只含有a1(111 ...

这样说来,只要a表的item_id出现在a2或a3表的 id列中就可以了。至于是否出现在A1表的ID列中,是无意义的。

使用道具 举报

回复
论坛徽章:
1
复活蛋
日期:2012-05-21 14:39:53
 楼主| 发表于 2017-2-19 11:58 | 显示全部楼层
bfc99 发表于 2017-2-19 11:51
这样说来,只要a表的item_id出现在a2或a3表的 id列中就可以了。至于是否出现在A1表的ID列中,是无意义的 ...

不是,如果有个a(3),他包含a2(222),a3(333),这个就不满足。因为我的条件是 a包含a1,并且包含。这里是个 and的关系

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-19 12:05 | 显示全部楼层
kangpengjie 发表于 2017-2-19 11:43
可能我没表达清楚,以我给的用例来说,id为1的a,他包含a1(11),a2(22),a3(33), id为2的a,他只含有a1(111 ...

或者说,你的意思是要说,由于A表的一个ID有多个ITEM_ID值, 将a表中的item_id的值出现在A1表中,且item_id的值还出现在A2或A3表中的A表中的ID值找出来?

如果是这样的话,可以用下面的方法:
select id
    from a
    where a.item_id in (select id from a1)
intersect
select id
   from a
   where a.item_id in (select id from a2)
         or a.item_id in (select id from a3);

使用道具 举报

回复
论坛徽章:
0
发表于 2017-2-19 16:51 | 显示全部楼层
楼主是这个意思吗?
with a10 as (select distinct a.id from a where a.item_id in (select id from a1))
,a11 as (select distinct a.id from a where a.item_id in (select id from a2 union select id from a3))
select a.* from a
inner join a10 on a.id=a10.id
inner join a11 on a.id=a11.id

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票7.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表