SELECT max(end_oil_cost) - min(start_oil_cost) into Result
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY d.terminal_id ORDER BY d.clct_date DESC) LEV,
count(0) over(partition by d.terminal_id) cnt,
d.*
FROM t_b_car_info car, t_b_car_terminal ct, t_o_day_info d
where car.id = ct.car_id
and ct.terminal_id = d.terminal_id
and d.clct_date >= nStartDateEx
and d.clct_date <= nEndDateEx
and d.clct_date between ct.terminal_install_date and
ct.terminal_uninstall_date
and car.id = nCarID
and d.end_oil_cost > 0
and d.start_oil_cost > 0)
WHERE LEV = 1
or LEV = cnt
ORDER BY clct_date DESC
————————————————————————————————————————————————————
对多数据进行分组排序后取每组第一条记录
Sql代码
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORDER BY DESC) LEV,
.*
FROM )
WHERE LEV = 1
ORDER BY DESC
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORDER BY DESC) LEV,
.*
FROM )
WHERE LEV = 1
ORDER BY DESC
-- 分组列
-- 排序列
-- 表名
-- 排序列
注:.* -- 一定要有表名,否则会报“缺失表达式”错误
==========================================================
分组时不仅想取每组的第一条记录,还想取每组共有几条记录
Sql代码
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY PK_FINANCE_ACCOUNT ORDER BY amassdate DESC) LEV,
count(0) over(partition by PK_FINANCE_ACCOUNT) cnt,
hx_amass_liquid.*
FROM
hx_amass_liquid
)
WHERE LEV = 1
ORDER BY PK_FINANCE_ACCOUNT DESC