时间戳

首页 > 解决方案 > 如何将变量中一列的值输出到

sql - 如何将变量中一列的值输出到

问题描述

有一个查询以这种形式返回数据： 

| ID |
+----+
| 25 |
| 28 |

请求总是会返回 2 个值 比如我需要把数据 (25,28) 的值拉到一个变量中，也就是拉到 Request 中： 

select  ID  from table1
where CODE in ('bonus', 'Pnlty') and dcl_id := iId

我尝试通过枢轴 

with BaseData as (select  ID, CODE  from table1
where CODE in ('bonus', 'Pnlty') and dcl_id := iId)
select * from  BaseData 
PIVOT
(
  MAX(ID)
  FOR CODE in ('bonus', 'Pnlty')
)

但是如何将 id on 的含义推导出到这里呢？
我需要这样的查询： 

select  ID as id1,
        ID as id2
        into bId, pID
into  from table1
    where CODE in ('bonus', 'Pnlty') and dcl_id := iId

标签： sqloracle

解决方案

每个代码一个变量。使用两个查询或使用条件聚合。

两个查询

create procedure p(iid integer) as
  bid integer;
  pid integer;
begin
  select id into bid 
  from table1 
  where code = 'bonus' and dcl_id = iid;

  select id into pid 
  from table1 
  where code = 'Pnlty' and dcl_id = iid;
end;

条件聚合

create procedure p(iid integer) as
  bid integer;
  pid integer;
begin
  select
    max(case when code = 'bonus' then id end),
    max(case when code = 'Pnlty' then id end)
  into bid, pid
  from table1 
  where code in ('bonus', 'Pnlty') and dcl_id = iid;
end;

推荐阅读