SQL 统计查询

Question

物品编号 批次    数量
100 1 10
100 2 20
100 3 30
100      4        10
200 1 10
200 2 20
200 3 50查询得到物品编号100，数量合计为大于等于 40的记录如下：100 1 10
100 2 20
100 3 30

Answer 1

declare @t table(物品编号 int,批次 int,数量 int)
insert into @t select 100,1,10 
insert into @t select 100,2,20 
insert into @t select 100,3,30 
insert into @t select 100,4,10 
insert into @t select 200,1,10 
insert into @t select 200,2,20 
insert into @t select 200,3,50 select
    t.*
from
    @t t
where
    t.物品编号=100
    and
    t.批次<=(select
                 top 1 批次 
             from 
                 @t n 
             where 
                 n.物品编号=t.物品编号 
                 and
                 (select sum(数量) from @t where 物品编号=n.物品编号 and 批次<=n.批次)>40
             order by
                 n.批次)/*
物品编号        批次          数量          
----------- ----------- ----------- 
100         1           10
100         2           20
100         3           30
*/

Answer 2

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-07-14 17:34:46
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (物品编号 INT,批次 INT,数量 INT)
INSERT INTO [tb]
SELECT 100,1,10 UNION ALL
SELECT 100,2,20 UNION ALL
SELECT 100,3,30 UNION ALL
SELECT 100,4,10 UNION ALL
SELECT 200,1,10 UNION ALL
SELECT 200,2,20 UNION ALL
SELECT 200,3,50select * from [tb]
;
with
wang1 as (select 物品编号,批次,合计=isnull((select sum(数量) from tb where 批次<t.批次),数量)
          from tb t)select * from wang1
where 物品编号=100 and 批次<=(select top 1 批次 from wang1 where 合计>40 order by 合计 )物品编号 批次 合计
100 1 10
100 2 20
100 3 60

Answer 3

修改一下
select * from tb
where 物品编号=100 and 批次<=(select top 1 批次 from wang1 where 合计>40 order by 合计 )
物品编号 批次 数量
100 1 10
100 2 20
100 3 30

Answer 4

--> 测试数据: @s
declare @s table (物品编号 int,批次 int,数量 int)
insert into @s
select 100,1,10 union all
select 100,2,20 union all
select 100,3,30 union all
select 100,4,10 union all
select 200,1,10 union all
select 200,2,20 union all
select 200,3,50select a.* from @s a,
(select top 1 * from
(select 物品编号,批次,数量=(select sum(数量) from @s where 物品编号=a.物品编号 and 批次<=a.批次) from @s a where 物品编号=100 )b
where 数量>=40)b
where a.物品编号=b.物品编号 and a.批次<=b.批次--结果：
物品编号        批次          数量
----------- ----------- -----------
100         1           10
100         2           20
100         3           30

Answer 5

declare @t table(物品编号 int,批次 int,数量 int)
insert into @t select 100,1,10 
insert into @t select 100,2,20 
insert into @t select 100,3,30 
insert into @t select 100,4,10 
insert into @t select 200,1,10 
insert into @t select 200,2,20 
insert into @t select 200,3,50 
;
with cte1 as
(
select top 1 批次 from 
(select 物品编号,批次, 总量=(select sum(数量) from @t where 物品编号=t.物品编号 and 批次<=t.批次 ) 
from @t t where 物品编号='100') tmp 
where 总量>=40 order by 总量
)
select t.物品编号,t.批次,t.数量 from @t t join cte1 c1 on t.批次<=c1.批次 where 物品编号='100'/* 
物品编号        批次          数量
----------- ----------- -----------
100         1           10
100         2           20
100         3           30(3 行受影响)*/

Answer 6

select 物品,编号,批次 from 表明 group by 物品 having SUM(批次)>=40 

Answer 7

declare @t table(物品编号 int,批次 int,数量 int)
insert into @t select 100,1,10 
insert into @t select 100,2,20 
insert into @t select 100,3,30 
insert into @t select 100,4,10 
insert into @t select 100,5,10 
insert into @t select 200,1,10 
insert into @t select 200,2,20 
insert into @t select 200,3,50 select * from @t where 批次<=any(
select 批次
from @t a where 物品编号=100 and (select sum(数量) from @t where 物品编号=a.物品编号 and 批次<=a.批次)>=40) 
and 物品编号=100/*物品编号        批次          数量
----------- ----------- -----------
100         1           10
100         2           20
100         3           30
100         4           10
100         5           10(5 行受影响)
*/

Answer 8

declare @t table (物品编号 int,批号 int,数量 int)
insert into @t select 100,1,10
     union all select 100,2,20
     union all select 100,3,30
     union all select 100,4,10
     union all select 200,1,10
     union all select 200,2,20
     union all select 200,3,50
 select * from @t a where not exists 
                    (select 1 from @t where 物品编号=a.物品编号 and 数量=a.数量 and 批号<a.批号)
                     and 物品编号=100 and 40<(select SUM(数量) from @t where 物品编号=100)物品编号        批号          数量
----------- ----------- -----------
100         1           10
100         2           20
100         3           30(3 行受影响)