oracle sql select 的问题

[zhangliang1982]

ieai_hand_rule 表里有
overtime setuser
88 sa
29 sa
我想得到一条记录
overtime1 overtime2 setuser
88 29 sa
怎么写sql？

[真的不知道取什么名]

--如果overtime里值少的话可以用下面的语句 多的话用过程
with tb as(
select 88 overtime,'sa' setuser from dual union all
select 29,'sa' from dual)
--以上为提供数据的语句
select max(decode(overtime,88,overtime,null)) overtime1,
       max(decode(overtime,29,overtime,null)) overtime2,
       setuser
from tb
group by setuser

 OVERTIME1  OVERTIME2 SE
---------- ---------- --
        88         29 sa

[心中的彩虹]

[Quote=引用楼主 zhangliang1982 的回复:]
ieai_hand_rule 表里有
overtime setuser
88 sa
29 sa
我想得到一条记录
overtime1 overtime2 setuser
88 29 sa
怎么写sql？
[/Quote]

--没描述清楚 猜下
select setuser,max(overtime) overtime1,min(overtime) overtime2
from tn
group by setuser

[Oraclefans_]

呵呵。。。真逗。。。

[「已注销」]

34 454 5675 767 345 88 29 sa 会出现吗，问题不清楚

[tangren]

--行数固定，可以采用如下类似方法：
WITH tmp AS
 (SELECT wm_concat(overtime) overtimes, setuser
    FROM ieai_hand_rule
   GROUP BY setuser)
SELECT SELECT substr(overtimes, 1, instr(overtimes, ',', 1, 1) - 1) overtime1,
              substr(overtimes, instr(overtimes, ',', 1, 1) + 1) overtime2,
              setuser
         FROM tmp;
--行数不定，编写存储过程来解决吧

[YY_MM_DD]

不写存储过程应该搞不出来，等大鸟指教。。

[物润声无]

[Quote=引用楼主 zhangliang1982 的回复:]
ieai_hand_rule 表里有
overtime setuser
88 sa
29 sa
我想得到一条记录
overtime1 overtime2 setuser
88 29 sa
怎么写sql？
[/Quote]
-- 如果只有两个字段overtime1， overtime2 的话可以这样写吧
select tab1. overtime overtime1, tab2.overtime overtime2 tab1.setuser
from ieai_hand_rule tab1, ieai_hand_rule tab2
where tab1.setuser = tab2.setuser
and tab1.overtime != tab2.overtime

[coolkisses]

-- 测试数据
drop table test_a_2;
create table test_a_2 as
select * from
(
select 11 a,10 b from dual union all
select 11 a,12 b from dual union all
select 12 a,13 b from dual union all
select 13 a,11 b from dual union all
select 13 a,12 b from dual union all
select 13 a,10 b from dual union all
select 14 a,1 b from dual
);

select * from test_a_2;

-- 列转行
-- 方法 1
-- lead() over() + row_number() over()
select a,b1,b2,b3 from (
select a
,b b1 -- 取第 1 个值
,lead(b,1) over (partition by a order by a) b2 -- 取第 2 个值 偏移 1 位
,lead(b,2) over (partition by a order by a) b3 -- 取第 3 个值 偏移 2 位
,row_number() over (partition by a order by a,b) a_rn -- 分组 序号 （每一个分组 A 下 ，有多少个序号）
from test_a_2
) where a_rn = 1 -- 只需要 第 1 个序号 最全的
;