求一个SQL语句

kerisyml 2010-10-20 03:08:55

有三张表a，a1，a2，内容如下：
SQL> select * from a;

DHHM BZ
--------------- --------------------
13311111111 test01
13322222222 test02
13333333333 test03

SQL> select * from a1;

DHHM1 XM1 XXK
--------------- ------------------------------ ----------------------------------------
13311111111 wangjj 电话库
13322222222 chenyy 电话库
13322222222 gogogo 电话库
13333333333 kkkkk 电话库

SQL> select * from a2;

DHHM2 XM2 XXK
--------------- ------------------------------ ----------------------------------------
13333333333 aaaaaa 人口库
13322222222 bbbbbb 人口库

要求提供一个SQL语句，查询结果如下：
13311111111 wangjj|电话库 test01
13322222222 chenyy|电话库 test02
13322222222 gogogo|电话库 test02
13322222222 bbbbbb|人口库 test02
13333333333 kkkkkk|电话库 test03
13333333333 aaaaaa|人口库 test03

要求提供一个SQL语句，查询结果如下：
13311111111 wangjj|电话库 test01
13322222222 chenyy|电话库,gogogo|电话库,bbbbbb|人口库 test02
13333333333 kkkkkk|电话库,aaaaaa|人口库 test03

请大家指教，该怎么写两个sql语句

...全文

121 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

心中的彩虹 2010-10-20

打赏
举报





select a.dhhm,a.bz,wm_concat(b.un) un

from 

(select dhhm,a1.xm1||'|'||a1.xxk un from a1

union all

select dhhm,xm2||'|'||xxk un from a2) b,a

where a.dhhm=b.dhhm

group by a.dhhm,a.bz

YY_MM_DD 2010-10-20

打赏
举报



Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 

Connected as topeng



SQL> 

SQL> with a as

  2  (

  3  select '13311111111'DHHM, 'test01' BZ from dual

  4  union all

  5  select '13322222222', 'test02' from dual

  6  union all

  7  select '13333333333', 'test03' from dual

  8  ),

  9  a1 as

 10  (

 11  select '13311111111' DHHM1, 'wangjj' XM1,  '电话库' XXK from dual

 12  union all

 13  select '13322222222' DHHM1, 'chenyy' XM1,  '电话库' XXK from dual

 14  union all

 15  select '13322222222' DHHM1, 'gogogo' XM1,  '电话库' XXK from dual

 16  union all

 17  select '13333333333' DHHM1, 'kkkkk' XM1,  '电话库' XXK from dual

 18  )

 19  ,a2 as

 20  (

 21  select '13333333333' DHHM2, 'aaaaaa' XM2,'人口库' XXK from dual

 22  union all

 23  select '13322222222' DHHM2, 'bbbbbb' XM2,'人口库' XXK from dual

 24  )

 25  select t.dhhm,wm_concat(temp),t.bz  from

 26  (

 27  select a.DHHM, a2.XM2||'|'||a2.XXK temp,a.BZ  from a,a2 where a.DHHM=a2.DHHM2

 28  union all

 29  select a.DHHM, a1.XM1||'|'||a1.XXK temp,a.BZ  from a,a1 where a.DHHM = a1.DHHM1

 30  ) t group by t.dhhm,t.BZ

 31  /



DHHM        WM_CONCAT(TEMP)                                                                  BZ

----------- -------------------------------------------------------------------------------- ------

13311111111 wangjj|电话库                                                                    test01

13322222222 bbbbbb|人口库,chenyy|电话库,gogogo|电话库                                        test02

13333333333 aaaaaa|人口库,kkkkk|电话库                                                       test03



SQL>

YY_MM_DD 2010-10-20

打赏
举报

修改下



with a as

(

select '13311111111'DHHM, 'test01' BZ from dual

union all

select '13322222222', 'test02' from dual 

union all

select '13333333333', 'test03' from dual

),

a1 as 

(

select '13311111111' DHHM1, 'wangjj' XM1,  '电话库' XXK from dual

union all

select '13322222222' DHHM1, 'chenyy' XM1,  '电话库' XXK from dual

union all

select '13322222222' DHHM1, 'gogogo' XM1,  '电话库' XXK from dual

union all

select '13333333333' DHHM1, 'kkkkk' XM1,  '电话库' XXK from dual

)

,a2 as

(

select '13333333333' DHHM2, 'aaaaaa' XM2,'人口库' XXK from dual

union all

select '13322222222' DHHM2, 'bbbbbb' XM2,'人口库' XXK from dual

)

select t.dhhm,wm_concat(temp),t.bz  from 

(

select a.DHHM, a2.XM2||'|'||a2.XXK temp,a.BZ  from a,a2 where a.DHHM=a2.DHHM2

union all

select a.DHHM, a1.XM1||'|'||a1.XXK temp,a.BZ  from a,a1 where a.DHHM = a1.DHHM1

) t group by t.dhhm,t.BZ

心中的彩虹 2010-10-20

打赏
举报

[Quote=引用楼主 kerisyml 的回复:]
有三张表a，a1，a2，内容如下：
SQL> select * from a;

DHHM BZ
--------------- --------------------
13311111111 test01
13322222222 test02
13333333333 test03

SQL> select * from a1;

DHHM1 XM1 XXK
------……
[/Quote]
可以在你的外面在用wm_concat套用

YY_MM_DD 2010-10-20

打赏
举报

---try it! good luck



Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 

Connected as topeng



SQL> 

SQL> with a as

  2  (

  3  select '13311111111'DHHM, 'test01' BZ from dual

  4  union all

  5  select '13322222222', 'test02' from dual

  6  union all

  7  select '13333333333', 'test03' from dual

  8  ),

  9  a1 as

 10  (

 11  select '13311111111' DHHM1, 'wangjj' XM1,  '电话库' XXK from dual

 12  union all

 13  select '13322222222' DHHM1, 'chenyy' XM1,  '电话库' XXK from dual

 14  union all

 15  select '13322222222' DHHM1, 'gogogo' XM1,  '电话库' XXK from dual

 16  union all

 17  select '13333333333' DHHM1, 'kkkkk' XM1,  '电话库' XXK from dual

 18  )

 19  ,a2 as

 20  (

 21  select '13333333333' DHHM2, 'aaaaaa' XM2,'人口库' XXK from dual

 22  union all

 23  select '13322222222' DHHM2, 'bbbbbb' XM2,'人口库' XXK from dual

 24  )

 25  select t.dhhm,wm_concat(temp)  from

 26  (

 27  select a.DHHM, a2.XM2||'|'||a2.XXK temp from a,a2 where a.DHHM=a2.DHHM2

 28  union all

 29  select a.DHHM, a1.XM1||'|'||a1.XXK temp from a,a1 where a.DHHM = a1.DHHM1

 30  ) t group by t.dhhm

 31  /



DHHM        WM_CONCAT(TEMP)

----------- --------------------------------------------------------------------------------

13311111111 wangjj|电话库

13322222222 bbbbbb|人口库,chenyy|电话库,gogogo|电话库

13333333333 aaaaaa|人口库,kkkkk|电话库



SQL>

真的不知道取什么名 2010-10-20

打赏
举报

--1

select DHHM,XM1||'|'||XXK col2,BZ

from a,a1

where a.DHHM=a1.DHHM1

union all

select DHHM,XM2||'|'||XXK col2,BZ 

from a,a2

where a.DHHM=a2.DHHM2

--2.先自己写过函数然后再语句中调用

--创建个视图

create or replace view test

as

select DHHM,XM1||'|'||XXK col2,BZ

from a,a1

where a.DHHM=a1.DHHM1

union all

select DHHM,XM2||'|'||XXK col2,BZ 

from a,a2

where a.DHHM=a2.DHHM2



create or replace function my_concat(var_col varchar2)

return varchar2

is

v_col varchar2(4000);

begin 

for cur in (select col2 from test where dhhm=var_col)

loop

v_col:=v_col||cur.col2||';';

end loop;

v_col:=rtrim(v_col,',');

return v_col;

end;



--最后从视图中读取数据

select DHHM,my_concat(col2),BZ

FROM TEST;

minitoy 2010-10-20

打赏
举报

select a.dhhm,b.xx,a.bz

  from a,

(select DHHM1,wm_concat(xm1||'|'||xxk) xx from (

select DHHM1, XM1, XXK from a1

union all

select DHHM2 DHHM1, XM2 XM1, XXK from a2)

group by DHHM1) b

where a.dhhm=b.dhhm

kerisyml 2010-10-20

打赏
举报

第一个sql语句可以用如下：
(select a.dhhm,a1.xm1||a1.xxk,a.bz from a,a1 where a.dhhm=a1.dhhm1) union (select a.dhhm,a2.xm2||a2.xxk,a.bz from a,a2 where a.dhhm=a2.dhhm2);

不过加入字段多，数据多，且如果包括a1,a2,a3,a4,a5,a6很多字表，是不是速度很慢呢，有没有更好的sql语句呢

SQL语句基础教程

sql学习必备，内含sql一些常用语句的例子，有图有表，通过结果来理解sql语句的实现与结果。

您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】七年SQL语句编写经验。满满的干货！在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题，但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。

最近要做一个软件升级，其中涉及到数据库表字段的变动(新增或删除或修改)，所有的关于数据库的变动的sql语句都是存放在Sqlupdate.sql文件中，每次升级的时候都需要执行一次Sqlupdate.sql里的所有sql语句，这就涉及到执行多条语句的问题了。软件在CodeIgniter框架上开发的，CodeIgniter有封装好的执行sql语句的函数：$this->db->query('...

PLSQL执行SQL语句 1、执行中量SQL语句 当执行SQL语句较大时，除运行SQL语句外可以点击工具------->导入表--------->（出现界面如下图所示）点击导入就会出现执行语句的窗口如下图所示，把语句粘贴进去，自动执行，执行完毕，提交事务，导入成功！也可以直接打开命令行方式：点击新增SQL窗口直接执行。 ...