循环通过表格，看看价格是否已经改变 [英] Looping through the table and see if the price has been changed

问题描述

我有一张桌子A

I have a table A

table A 
 Auditid | ReqId | Payment
 1       | 123   | 0.00
 2       | 123   | 30
 3       | 123   | 20
 4       | 123   | 20
 5       | 123   | 20
 6       | 123   | 20
 7       | 456   | 40
 8       | 456   | 40
 9       | 789   | 0
 10      | 789   | 20

现在我需要一个查询来检查每个reqid并查看表格并检查金额是否已更改。

因此，对于reqid 123，它将采用最大审计ID：6并将该价格与审计ID 5进行比较，它等于所以它将转到4，它也是相同的，它将会去到2，它将返回该req id。

for reqid 456：它将检查审计id 8与7并且它相等，所以它不会返回任何内容。对于req id 786，它将返回reqid，因为价格已更改。



输出：

Now I need a query that will check each reqid and go through the table to and check if the amount was changed.
So, for reqid 123, it will take the max audit id: 6 and compare that price with audit id 5, its equal so it will go to the 4, that equal as well, it will go to 2 and it will return that req id.
for reqid 456: it will check audit id 8 with 7 and its equal so it will not return anything. for req id 786, it will return the reqid because the price was changed.

Output:

Reqid
123   
789

我的尝试：

What I have tried:

select Payment from [table A]  a
where Auditid = (select Auditid 
from (select ROW_NUMBER() over (order by Auditid desc) as 'rowNum', Auditid from [table A] where  ReqId = '11115' ) withRowNum
where rowNum = 2)

推荐答案

尝试：

Try:

SELECT
    Reqid,
    COUNT(DISTINCT Payment) As PriceCount,
    MIN(Payment) As MinimumPayment,
    MAX(Payment) As MaximumPayment
FROM
    [table A]
GROUP BY
    Reqid
HAVING
    COUNT(DISTINCT Payment) > 1
;

Declare @table table (Auditid int,ReqId int,Payment int)
insert into @table values 
 (1, 123, 0),
 (2, 123, 30 ),
 (3, 123, 20 ),
 (4, 123, 20 ),
 (5, 123, 20 ),
 (6, 123, 20 ),
 (7, 456, 40 ),
 (8, 456, 40 ),
 (9, 789, 0	 ),
 (10, 789, 20)
 ;with cte as (
 select *,Rank() over(partition by ReqId order by Payment)Rn from @table) 
 select ReqId from cte where rn>1 group by ReqId

这篇关于循环通过表格，看看价格是否已经改变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！