如何写这个sql语句?

jlandzpa

收藏。

rebecca_xt

SQL> select deptno, dname, emps
2 from (
3 select d.deptno, d.dname, rtrim(e.ename ||', '||
4 lead(e.ename,1) over (partition by d.deptno
5 order by e.ename) ||', '||
6 lead(e.ename,2) over (partition by d.deptno
7 order by e.ename) ||', '||
8 lead(e.ename,3) over (partition by d.deptno
9 order by e.ename) ||', '||
10 lead(e.ename,4) over (partition by d.deptno
11 order by e.ename) ||', '||
12 lead(e.ename,5) over (partition by d.deptno
13 order by e.ename),', ') emps,
14 row_number () over (partition by d.deptno
15 order by e.ename) x
16 from emp e, dept d
17 where d.deptno = e.deptno
18 )
19 where x = 1
20 /

这是什么意思？

myhuaer

先create function get_a2;
create or replace function get_a2( tmp_a1 number)
return varchar2
is
  Col_a2 varchar2(4000);
begin
  Col_a2:='';
  for cur in (select a2 from unite_a where a1=tmp_a1)
  loop
     Col_a2=Col_a2||cur.a2;   
  end loop;
  return Col_a2;
end get_a2;  

select distinct a1 ,get_a2(a1) from unite_a
1        ABC
2        EFG
3        KMN

flyerchen2000

佩服佩服.
自己也没少看了书, 但怎么就是想不起来呢.
老外地解法是用到了分析函数, 详情参见 <<oracle专家高级编程>>

biti_rainy

最初由 xallan 发布
[B]不能不承认，还是老外厉害。

我在otn上也问了同样的问题。
很快有人说用plsql写个存储过程，后来我又问纯粹用sql行不行，有个牛人很快回答了：

Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.

This example uses a max of 6, and would need more cut n pasting to do more than that.


SQL> select deptno, dname, emps
  2  from (
  3  select d.deptno, d.dname, rtrim(e.ename ||', '||
  4     lead(e.ename,1) over (partition by d.deptno
  5             order by e.ename) ||', '||
  6     lead(e.ename,2) over (partition by d.deptno
  7             order by e.ename) ||', '||
  8     lead(e.ename,3) over (partition by d.deptno
  9             order by e.ename) ||', '||
10     lead(e.ename,4) over (partition by d.deptno
11             order by e.ename) ||', '||
12     lead(e.ename,5) over (partition by d.deptno
13             order by e.ename),', ') emps,
14     row_number () over (partition by d.deptno
15             order by e.ename) x
16  from emp e, dept d
17  where d.deptno = e.deptno
18  )
19  where x = 1
20  /

DEPTNO DNAME       EMPS
------- ----------- ------------------------------------------
     10 ACCOUNTING  CLARK, KING, MILLER
     20 RESEARCH    ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH
     30 SALES       ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

--------------------
打算踏踏实实看看书. [/B]

无法应付一个部门人数超过5个人或者几百人不定的时候
除非你知道最多人数为N
然后采用 lead   N  来解决

所以 pl/sql 才是根本之道

我的眼里只有我

自定义函数也不行, 因为你必须将所有的查询参数PASS进去才能拿到你想要的行集. 并且如果我的查询参数是动态变化的(即有时很多, 有时一个也没有), 那应该怎样在自定义函数的SELECT语句中拿到你想要的行集呢?
我用的是SQL SERVER库, LEAD用法不一样. 我正在学习中......

csqno1

ding

hwhone

老外，是很牛，但不太好理解，也太麻烦，我是赞成用函数

accelerator

[PHP]
select deptno,list(Ename)  from emp group by deptno
  
      10    CLARK,KING,MILLER
      20    SMITH,FORD,ADAMS,SCOTT,JONES
      30    ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD



select object_type,list(to_char(created,'yyyy-mm-dd')) from user_objects
group by object_type


FUNCTION        2004-11-25
INDEX        2002-05-12,2002-05-12
TABLE        2002-05-12,2002-05-12,2002-05-12,2004-11-12,2004-10-28,2002-05-12
TYPE        2004-11-25
TYPE BODY        2004-11-25
[/PHP]

参考
http://www.itpub.net/293596.html

wiwei

經典