This Is A FineDay

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  93 随笔 :: 0 文章 :: 69 评论 :: 0 Trackbacks

CREATE TABLE  a as (select 1 id ,'項目一' XMMC,'XMONE' CODE,1 RYID FROM DUAL
           UNION
           select 2 id ,'項目二' XMMC,'XMTWO' CODE,1 RYID FROM DUAL
           UNION
           select 3 id ,'項目三' XMMC,'XMTHREE' CODE,1 RYID FROM DUAL
           UNION
           select 4 id ,'項目一' XMMC,'XMONE' CODE,2 RYID FROM DUAL
           )
WITH B AS (SELECT 1 ID,'張三' NAME FROM DUAL
           UNION
           SELECT 2 ID,'李四' NAME FROM DUAL          
           )
SELECT C.RYID,
       B.NAME,
       MAX(DECODE(RN, 1, XMMC, NULL)) XMONE,
       MAX(DECODE(RN, 2, XMMC, NULL)) XMTWO,
       MAX(DECODE(RN, 3, XMMC, NULL)) XMTHREE
  FROM (SELECT RYID,
               XMMC,
               CODE,
               ROW_NUMBER() OVER(PARTITION BY RYID ORDER BY RYID) RN  --以RYID分组后内部的排序
          FROM A) C,
       B
 WHERE C.RYID = B.ID
 GROUP BY C.RYID, B.NAME
 ORDER BY C.RYID

--     RYID NAME XMONE  XMTWO  XMTHREE
---------- ---- ------ ------ -------
--         1 張三 項目一 項目二 項目三
--         2 李四 項目一        

posted on 2008-05-19 12:25 Peter Pan 阅读(204) 评论(0)  编辑  收藏 所属分类: DB

只有注册用户登录后才能发表评论。


网站导航: