问按给定的卷量选择多行

EN

CREATE TABLE VolumePrice (
    id int not null,
    volume decimal(9,4) not null,
    price decimal(9,2) not null
);

insert VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),
       (2,0.2500,14251.10),
       (3,0.2500,14250.00),
       (4,0.0050,14200.00),
       (5,0.5000,14255.01);

-- create a temp table to hold the output
create table output (
    id int auto_increment not null primary key, 
    remainder decimal(9,2), 
    volume decimal(9,2)
);

DELIMITER //

create procedure do_insert()
begin
set @v = 1.75;
set @remainder = @v;

while @remainder > 0
do
    insert output(remainder, volume)
    select @remainder % max(volume)
         , max(volume) as volume
      from volumeprice
     where volume <= @remainder;

  set @remainder = (select remainder 
                      from output 
                  order by id desc
                     limit 1);
end while;
end;
//

DELIMITER ;    

CALL do_insert();

select v.*
  from output as o
  join (select volume, max(price) as max_price
          from volumeprice
      group by volume) as vp on vp.volume = o.volume
  -- I only link back to the volumeprice table here to obtain the id, 
  -- if you do't need the id you can remove this part and just select vp.* instead...
  join volumeprice as v 
    on v.volume = vp.volume 
   and v.price = vp.max_price;

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE (id <> t.id AND price > t.price) OR (id < t.id AND price = t.price) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.7500'
ORDER BY price DESC, id ASC;

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE volume > t.volume OR (id < t.id AND volume = t.volume) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

SELECT TOP (1048576)
    n = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.Numbers
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2
CROSS JOIN sys.columns AS c3;

CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Numbers (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

declare @input decimal(9,4)=1.7500 
if object_id('tempdb..#VolumePrice') is not null 
drop table #VolumePrice

create  table #VolumePrice (
    id tinyint  PRIMARY KEY NONCLUSTERED,
    volume decimal(9,4) not null,
    price decimal(9,2) not null,
     bit_value   AS 
                CONVERT
                (
                    integer, 
                    POWER(2, id - 1)
                )
                PERSISTED UNIQUE CLUSTERED
);

insert #VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),(2,0.2500,14251.10),
      (3,0.2500,14250.00),(4,0.0050,14200.00),(5,0.5000,14255.01);


DECLARE 
    @max integer = 
    POWER(2,
        (
            SELECT COUNT(*) 
            FROM #VolumePrice AS s
        )
    ) - 1;

;

WITH CTE
AS (
    SELECT n.n
        ,c.id
        ,c.volume
    FROM dbo.Numbers AS N
    CROSS APPLY (
        SELECT s.id
            ,s.volume
        FROM #VolumePrice AS s
        WHERE n.n & s.bit_value = s.bit_value
        ) c
    WHERE N.n BETWEEN 1
            AND @max
    )
    ,cte1
AS (
    SELECT n
        ,sum(volume) vol
        ,count(n) HavingLeastcount
    FROM cte
    GROUP BY n
    HAVING sum(volume) = @input
    )
SELECT *
FROM cte c
WHERE EXISTS (
        SELECT n
        FROM cte1 c1
        WHERE n = c.n
        )