17,088
社区成员
发帖
与我相关
我的任务
分享
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
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 DISTINCT
科目,
FIRST_VALUE(期初数) OVER (PARTITION BY 科目
ORDER BY 时间) AS 期初数,
FIRST_VALUE(期末数) OVER (PARTITION BY 科目
ORDER BY 时间 DESC) AS 期末数
FROM table1
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
[/q
第一行的期初数与最后一行的期末数哦 。 你这样写可以吗?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