求一高效查询

Question

--求一高效查询,数据量比较大,尽可能考虑性能
--示例表结构(item_code 产品编码,tran_date 交易日期,tran_qty 交易数量)
--求一查询,返回同一产品编码+交易日期下交易数量最小的行,(有多行时取id最小)
create table test(id int identity(1,1) ,item_code varchar(20) null,tran_date datetime null,tran_qty int null)
insert into test(item_code,tran_date,tran_qty)
select 'A001','2010-03-15',20
union all
select 'A001','2010-03-16',25
union all
select 'A001','2010-03-15',60
union all
select 'A001','2010-03-15',20
union all
select 'A002','2010-03-18',50
union all
select 'A002','2010-03-18',60
union all
select 'A002','2010-03-19',100
union all
select 'A002','2010-03-20',100
--求一查询,同一item_code + tran_date的记录中,
--tran_qty最小的一行记录,有多个tran_qty相同时,最id最小的一行记录
--如示例数据,返回结果集为:(第1行及第5行的记录,其中item_code = 'A001' and tran_date = '2010-03-15' and tran_qty = 20的记录有两条,取id小的)
id      item_code  tran_date   tran_qty
1 A001 2010-03-15 00:00:00.000 20
5 A002 2010-03-18 00:00:00.000 50

Answer 1

select * from (
select *,row_number() over(partition by item_code  order by tran_qty,id) as rn from test
) a where rn=1         id item_code            tran_date                  tran_qty                   rn
----------- -------------------- ----------------------- ----------- --------------------
          1 A001                 2010-03-15 00:00:00.000          20                    1
          5 A002                 2010-03-18 00:00:00.000          50                    1(2 行受影响)

Answer 2

select
  *
from
  tb t
where
  id=(select min(id) from tb where item_code=t.item_code,tran_date=t.tran_date,tran_qty=t.tran_qty)

Answer 3

select
  *
from
  tb t
where
  id=(select min(id) from tb where item_code=t.item_code and tran_date=t.tran_date and tran_qty=t.tran_qty)

Answer 4

select * from test a
where not exists (select 1 from test where item_code=a.item_code and tran_qty<a.tran_qty) 
and not exists ( select 1 from test where item_code=a.item_code and tran_qty=a.tran_qty and id<a.id)

Answer 5

SELECT * 
FROM 
Test AS a 
WHERE 
NOT EXISTS (SELECT 1 FROM Test WHERE item_code=a.item_code AND Tran_date=a.tran_date AND (tran_qty<a.tran_qty OR (tran_qty=a.tran_qty AND ID<a.ID)))

Answer 6

--或者用in
select * from test a
where tran_qty in (select min(tran_qty) from test where item_code=a.item_code) 
and id in ( select min(id) from test where item_code=a.item_code and tran_qty=a.tran_qty)

Answer 7

--错了，没看还有个条件tran_date加上这个

Answer 8

--1、
select * from test a
where not exists (select 1 from test where item_code=a.item_code and tran_date=a.tran_date and tran_qty<a.tran_qty) 
and not exists ( select 1 from test where item_code=a.item_code and tran_date=a.tran_date and tran_qty=a.tran_qty and id<a.id)--2、
select * from test a
where tran_qty in (select min(tran_qty) from test where item_code=a.item_code and tran_date=a.tran_date) 
and id in ( select min(id) from test where item_code=a.item_code and tran_date=a.tran_date and tran_qty=a.tran_qty)--3、
select * from test a
where not exists (select 1 from test where item_code=a.item_code and tran_date=a.tran_date and (tran_qty<a.tran_qty or (tran_qty=a.tran_qty and id<a.id))) 

Answer 9

select * from (
select *,(select count(*) from test b where a.item_code=b.item_code and (a.tran_qty>b.tran_qty 
or (a.tran_qty=b.tran_qty and a.id>=b.id))) as rn 
from test a
) a where rn=1         id item_code            tran_date                  tran_qty          rn
----------- -------------------- ----------------------- ----------- -----------
          1 A001                 2010-03-15 00:00:00.000          20           1
          5 A002                 2010-03-18 00:00:00.000          50           1(2 行受影响)