sql - 自联接表并仅使用一次行 - DENSE_RANK() 的正确使用

Question

首先，一些虚构的数据：

create table #testm
(
    transdate date,
    item nvarchar(20),
    qty int,
    whse nvarchar(10),
    loc nvarchar(10)
)
insert into #testm (transdate, item, qty, whse, loc)
values ('20180601', '123', 100, 'main', 'qc'),
       ('20180602', '123', -100, 'main', 'qc'), 
       ('20180603', '123', 100, 'main', 'qc'),
       ('20180604', '123', -100, 'main', 'qc'), 
       ('20180602', '1234', 100, 'main', 'qc'), 
       ('20180602', '1234', -100, 'main', 'notqc')

场景：我有一张表，其中记录了我的所有交易，如股票变动和其他交易。我需要生成一个报告，它将显示#testm输入特定项目的数据（表格字段）loc，然后退出它。但是，我只需要计算每个“出口”一次，因此它不会加入与项目和数量匹配的每个其他“条目”。然后，我需要查看该项目花费了多少时间loc。特殊情况是当项目还没有离开时loc，它应该在查询的第二部分显示空值datediff()from getdate()。

在上面的insert语句中，预期的结果是将第 1 行与 2、3 与 4、5 与任何内容匹配（因为还没有出口，第 6 行不正确loc）并跳过第 6 行，因为loc不是我要找的.

结果应该是这样的

     transdate  item qty whse loc td2        it2  qt2   wh2     lo2 datediffhour
     2018-06-01 123  100 main qc  2018-06-02 123  -100  main    qc   24
     2018-06-02 1234 100 main qc  NULL       NULL NULL  NULL    NULL 1056
     2018-06-03 123  100 main qc  2018-06-04 123  -100  main    qc   24

这是我的尝试：

select * from
(
    select tin.transdate, tin.item, tin.qty, tin.whse, tin.loc, DENSE_RANK() over(partition by tin.transdate order by tout.transdate) as firstrank,
    DENSE_RANK() over (partition by tout.transdate order by tin.item) as secondrank, tout.transdate as td2, tout.item as it2, tout.qty as qt2, tout.whse as wh2, tout.loc as lo2,
    datediff(hour, tin.transdate, isnull(tout.transdate, getdate())) as datediffhour
    from #testm as tin
    left join #testm as tout on tin.item = tout.item and tin.whse = tout.whse and tin.transdate <= tout.transdate and tin.qty = -1*tout.qty
where tin.loc = 'qc' and tout.loc = 'qc'
) as t
where (firstrank = secondrank or t.td2 is null) and t.qty > 0
order by t.transdate

2个问题：

它跳过第 5 行

secondrank这只是猜测，firstrank因为我并不熟悉DENSE_RANK- 我什至使用它对吗？

标签： sqlsql-serversql-server-2008

Answer 1

我用row_number()而不是dense_rank(). 这里只需要一个。请注意，我们有一个t_in.transdate < t_out.transdate条件而不是<=。

显示没有退出匹配的第 5 行的关键是将条件t_out.loc = 'qc'作为左连接条件而不是WHERE子句，因为在这里它的行为就像您正在应用INNER JOIN.

select 
  transdate, item, qty, whse, loc,
  td2, it2, qt2, wh2, lo2, datediffhour
from (
    select 
      t_in.transdate, t_in.item, t_in.qty, t_in.whse, t_in.loc,
      t_out.transdate as td2, t_out.item as it2, t_out.qty as qt2, t_out.whse as wh2, t_out.loc as lo2,
      datediff(hour, t_in.transdate, isnull(t_out.transdate, getdate())) as datediffhour,
      row_number() over (partition by t_in.item, t_in.transdate order by t_in.transdate) as rn
    from #testm t_in
    left join #testm t_out on
      t_in.item = t_out.item
      and t_in.whse = t_out.whse
      and t_in.qty = -1 * t_out.qty
      and t_in.transdate < t_out.transdate
      and t_out.loc = 'qc' 
    where 
      t_in.qty > 0
      and t_in.loc = 'qc'     
) t 
where rn = 1 -- pick up only first matching "exit"
order by transdate