SELECT DISTINCT 科目, FIRST_VALUE(期初数) OVER (PARTITION BY 科目 ORDER BY 时间 ROWS UNBOUNDED PRECEDING) AS 期初数, LAST_VALUE(期末数) OVER (PARTITION BY 科目 ORDER BY 时间 ROWS UNBOUNDED PRECEDING) AS 期末数 FROM table1
--正确的应该 SELECT DISTINCT 科目, FIRST_VALUE(期初数) OVER (PARTITION BY 科目 ORDER BY 时间) AS 期初数, FIRST_VALUE(期末数) OVER (PARTITION BY 科目 ORDER BY 时间 DESC) AS 期末数 FROM table1
with ls as (SELECT DISTINCT 科目, FIRST_VALUE(期初数) OVER (PARTITION BY 科目 ORDER BY 时间) AS 期初数, FIRST_VALUE(期末数) OVER (PARTITION BY 科目 ORDER BY 时间 DESC) AS 期末数 FROM table1) select * from table1 s where exists ( select 1 from ls where ls.科目=s.科目 and (ls.期初数=s.期初数 or ls.期末数=s.期末数))
group by科目,然后取max(期末数) 和 min(期初数)不行?
select 去跑代码啊。 如果结果不符合预期,把你预想的结果贴出来。 用数据说话。 select decode(row_number() over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC), 1, to_char(TRIM(TO_CHAR(c.begin_balance, '999,999,999,999.99'))), '') begin_balance, /*期初数*/ decode(row_number() over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC), 1, to_char(TRIM(TO_CHAR(c.end_balance, '999,999,999,999.99'))), '') end_balance /*期末数*/ from CUX_mul_subsidiary_ledger c这是我写的 结果是
去跑代码啊。 如果结果不符合预期,把你预想的结果贴出来。 用数据说话。 select decode(row_number() over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC), 1, to_char(TRIM(TO_CHAR(c.begin_balance, '999,999,999,999.99'))), '') begin_balance, /*期初数*/ decode(row_number() over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC), 1, to_char(TRIM(TO_CHAR(c.end_balance, '999,999,999,999.99'))), '') end_balance /*期末数*/ from CUX_mul_subsidiary_ledger c这是我写的 结果是 就是想根据 科目 开窗后 每个组的第一行 和最后一行 显示 数字 其他行 不显示不知道 你还有其他好的写法吗?
select c.segment3,sequence_value, first_value(c.begin_balance) over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC) begin_balance,/*期初数*/ last_value(c.end_balance) over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC) end_balance, /*期末数*/ from CUX_mul_subsidiary_ledger c
select c.segment3,sequence_value, first_value(c.begin_balance) over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC) begin_balance,/*期初数*/ last_value(c.end_balance) over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC) end_balance /*期末数*/ from CUX_mul_subsidiary_ledger c
科目,
FIRST_VALUE(期初数) OVER (PARTITION BY 科目
ORDER BY 时间
ROWS UNBOUNDED PRECEDING) AS 期初数,
LAST_VALUE(期末数) OVER (PARTITION BY 科目
ORDER BY 时间
ROWS UNBOUNDED PRECEDING) AS 期末数
FROM table1
不明白你自己去查 FIRST_VALUE()、LAST_VALUE() 的帮助。
--正确的应该
SELECT DISTINCT
科目,
FIRST_VALUE(期初数) OVER (PARTITION BY 科目
ORDER BY 时间) AS 期初数,
FIRST_VALUE(期末数) OVER (PARTITION BY 科目
ORDER BY 时间 DESC) AS 期末数
FROM table1
with ls as (SELECT DISTINCT
科目,
FIRST_VALUE(期初数) OVER (PARTITION BY 科目
ORDER BY 时间) AS 期初数,
FIRST_VALUE(期末数) OVER (PARTITION BY 科目
ORDER BY 时间 DESC) AS 期末数
FROM table1)
select * from table1 s where exists ( select 1
from ls where ls.科目=s.科目 and (ls.期初数=s.期初数 or ls.期末数=s.期末数))
如果结果不符合预期,把你预想的结果贴出来。
用数据说话。
select decode(row_number()
over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC),
1,
to_char(TRIM(TO_CHAR(c.begin_balance, '999,999,999,999.99'))),
'') begin_balance, /*期初数*/
decode(row_number()
over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC),
1,
to_char(TRIM(TO_CHAR(c.end_balance, '999,999,999,999.99'))),
'') end_balance /*期末数*/
from CUX_mul_subsidiary_ledger c这是我写的 结果是
如果结果不符合预期,把你预想的结果贴出来。
用数据说话。
select decode(row_number()
over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC),
1,
to_char(TRIM(TO_CHAR(c.begin_balance, '999,999,999,999.99'))),
'') begin_balance, /*期初数*/
decode(row_number()
over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC),
1,
to_char(TRIM(TO_CHAR(c.end_balance, '999,999,999,999.99'))),
'') end_balance /*期末数*/
from CUX_mul_subsidiary_ledger c这是我写的 结果是
就是想根据 科目 开窗后 每个组的第一行 和最后一行 显示 数字 其他行 不显示不知道 你还有其他好的写法吗?
select c.segment3,sequence_value, first_value(c.begin_balance)
over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC) begin_balance,/*期初数*/
last_value(c.end_balance)
over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC) end_balance, /*期末数*/
from CUX_mul_subsidiary_ledger c
select c.segment3,sequence_value, first_value(c.begin_balance) over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value ASC) begin_balance,/*期初数*/ last_value(c.end_balance) over(PARTITION BY c.segment3 /*科目*/ ORDER BY sequence_value DESC) end_balance /*期末数*/ from CUX_mul_subsidiary_ledger c