sql - 从返回值中过滤掉一行

Question

我目前正在创建按价值排名的前 5 名列表。我正在传递@InfoId = 12，它正在返回我所期望的值。但我不想显示该行@InfoId = @InformationId

例如，如果我将参数 12 作为InfoId; 我不想在下面的表格示例中显示第一行 `InformationId = 12。

这是我在运行选择查询时得到的返回示例

+------------------------------------+
| StateId InformationId CurrentValue |
+------------------------------------+
| 1             12           453231  |
| 1             10           394721  |
| 1             341          309425  |
| 1              21          308431  |
| 1              73          301342  |
+------------------------------------+

这是我所期待的一个例子：

+------------------------------------+
| StateId InformationId CurrentValue |
+------------------------------------+
| 1             10           394721  |
| 1             341          309425  |
| 1             21           309321  |
| 1             73           308431  |
| 1             62           301342  |
+------------------------------------+

这是我的代码：

    @StateId int
    , @InfoId int    
    , @CurrentValue int  -- My new parameter 
AS
BEGIN       
        select      top 5
                    i.StateId,
                    i.InformationId,
                    i.CurrentValue,

        from        dbo.tblInformation i

        inner join  dbo.tblInformationAnalysis ia
        on          ia.StateId= i.StateId
        and         ia.InformationId= i.InformationId
        and         ia.CurrentValue= i.CurrentValue

        inner join  (
                    select  
                                StateId,
                                InformationId,
                                CurrentValue,
                    from        dbo.tblInformationAnalysis
                    where       StateId = @StateID

                    group by    StateId,
                                InformationId,
                                CurrentValue,
                    ) via
        on          via.stateId= ia.stateid
        and         via.informationId= ia.informationId
        and         via.CurrentValue= ia.CurrentValue

        inner join  dbo.tblRetailData r
        on          r.stateId= i.stateid
        and         r.informationId= i.informationId
        and         r.CurrentValue= i.CurrentValue

        inner join  dbo.tblCounty c
        on          c.stateId= r.stateid
        and         c.informationId= r.informationId
        and         c.CurrentValue = 1

        inner join  dbo.tblRetailDataPublished rp
        on          rp.stateId= i.stateId
        and         rp.informationId= i.informationId
        and         rp.CurrentValue = i.CurrentValue 

        inner join  dbo.RetailCollection rc
        on          rc.stateId= i.stateId
        and         rc.informationId= i.informationId

        where       i.StateId= @StateId
        and         p.InformationId = @InfoId

END

标签： sqlsql-serverstored-procedures