当我运行下面的代码时,ROWID总是1。对于具有相同信用值的每个项目,我需要ID从1开始。
;WITH CTETotal AS (SELECT
TranRegion
,TranCustomer
,TranDocNo
,SUM(TranSale) 'CreditValue'
FROM dbo.Transactions
LEFT JOIN customers AS C
ON custregion = tranregion
AND custnumber = trancustomer
LEFT JOIN products AS P
ON prodcode = tranprodcode
GROUP BY
TranRegion
,TranCustomer
,TranDocNo)
SELECT
r.RegionDesc
,suppcodedesc
,t.tranreason as [Reason]
,t.trandocno as [Document Number]
,sum(tranqty) as Qty
,sum(tranmass) as Mass
,sum(transale) as Sale
,cte.CreditValue AS 'Credit Value'
,RANK() OVER (PARTITION BY cte.CreditValue ORDER BY cte.CreditValue)AS ROWID
FROM transactions t
LEFT JOIN dbo.Regions AS r
ON r.RegionCode = TranRegion
LEFT JOIN CTETotal AS cte
ON cte.TranRegion = t.TranRegion
AND cte.TranCustomer = t.TranCustomer
AND cte.TranDocNo = t.TranDocNo
GROUP BY
r.RegionDesc
,suppcodedesc
,t.tranreason
,t.trandocno
,cte.CreditValue
ORDER BY CreditValue ASC
编辑
所有带有400的信用值都必须将ROWID设置为1。所有信用值为200的人必须将ROWID设置为2。等等。
你需要这样的东西吗?
with cte (item,CreditValue)
as
(
select 'a',8 as CreditValue union all
select 'b',18 union all
select 'a',8 union all
select 'b',18 union all
select 'a',8
)
select CreditValue,dense_rank() OVER (ORDER BY item)AS ROWID from cte
结果
CreditValue ROWID
----------- --------------------
8 1
8 1
8 1
18 2
18 2
在您的代码中替换
,RANK() OVER (PARTITION BY cte.CreditValue ORDER BY cte.CreditValue)AS ROWID
通过
,DENSE_RANK() OVER (ORDER BY cte.CreditValue)AS ROWID
您不必使用PARTITION
,只需使用DENSE_RANK() OVER (ORDER BY cte.CreditValue)
我认为问题出在RANK()OVER(PARTITION BY子句
您必须按项目而不是按CreditValue 对其进行分区
试试这个
RANK()OVER(按cte.CreditValue ORDER按cte.RegionDesc划分)为ROWID
编辑:这里的问题实际上不是子查询的嵌套,它可能是基于partition by
的列真正使每行唯一(或1)
而不是像这个那样在复杂的查询中排名
select
rank() over(partition by...),
*
from
data_source
join table1
join table2
join table3
join table4
order by
some_column
在生成的数据集上尝试rank()
或row_number()
,而不是在其中。
例如,使用上面的查询,删除rank()
并以这种方式实现:
select
rank() over(partition by...),
results.*
from (
select
*
from
data_source
join table1
join table2
join table3
join table4
order by
some_column
) as results