运行环境: SqlServer 2008 要求如下: SQL语句如下: 结果如下: 另外还有工作中用的SQL语句,记录一下,以备以后使用。 将全年的所有拨付单据导出,包括负单据。查询一下每月的拨付人员明细,以202202为例。下表有10条数据,其中je>0的数据有7条,je<0的数据有3条
要求查询出4条记录(je正负抵消,不能抵消的负数也排除)
查询后结果会保留id为1,3,5,6,9的数据
WITH t1 AS (
SELECT
*,
ROW_NUMBER() over ( PARTITION BY je ORDER BY id ) vid,
DENSE_RANK() over (
ORDER BY
abs( je )) absid
FROM
[ ceshi ]) SELECT
*
FROM
t1 a
WHERE
je > 0
AND NOT EXISTS ( SELECT 1 FROM t1 WHERE absid = a.absid AND vid = a.vid AND je < 0 )
ORDER BY
id
WITH t1 AS (
SELECT
*,
ROW_NUMBER() over ( PARTITION BY 基础性养老金 ORDER BY id ) vid,
DENSE_RANK() over (
ORDER BY
abs(基础性养老金)) absid
FROM
[ Sheet1 ]
WHERE
发放年月 = '2022-05-01'
) SELECT
*
FROM
t1 a
WHERE
基础性养老金 >= 50
AND NOT EXISTS ( SELECT 1 FROM t1 WHERE absid = a.absid AND vid = a.vid AND 基础性养老金 < 0 )
ORDER BY
id