求一个SQL查询语句！先谢过了！

Question

本帖最后由 sp781022 于 2009-10-22 21:06:43 编辑

Answer 1

---没有测试
select 
   a.*,需求总量=a.总数量,b.库存,缺货量=a.总数量-b.库存
from
  (select 采购单号,行号,商品,sum(数量) as 总数量 from a group by 采购单号,行号,商品)a
left join
  (select 商品,库位,sum(库存) as 库存 from b group by 商品,库位)b
on
  a.商品=b.商品

Answer 2

if object_id('tb')is not null drop table tb
go
create table tb(采购单号 varchar(10), 行号 int,商品 varchar(5), 数量 int)
insert tb select
'A00001',      100 ,   'GOOD1',    2000 union all select
'A00001',      200 ,   'GOOD2',    4000 union all select
'A00001',      300 ,   'GOOD3',    7000 union all select
'A00002',      100 ,   'GOOD1',    3000 union all select
'A00002',      200 ,   'GOOD2',    3000 if object_id('tc')is not null drop table tc
go
create table tc( 商品 varchar(5),库位 varchar(5), 库存 int)
insert tc select 
'GOOD1',  'K01',    1000 union all select
'GOOD1',  'K02',    2000 union all select
'GOOD2',  'K01',    4000 
select 采购单号 , 行号,a.商品,数量,
c.需求总量 ,b.库存数量,缺货量= c.需求总量-b.库存数量
from tb a
left join
(select 商品,sum(数量) as 需求总量 from tb  group by 商品)c
on a.商品=c.商品
left join 
(select 商品,sum(库存 )as 库存数量 from tc group by 商品)b
on  a.商品=b.商品采购单号       行号          商品    数量          需求总量        库存数量        缺货量
---------- ----------- ----- ----------- ----------- ----------- -----------
A00001     100         GOOD1 2000        5000        3000        2000
A00001     200         GOOD2 4000        7000        4000        3000
A00001     300         GOOD3 7000        7000        NULL        NULL
A00002     100         GOOD1 3000        5000        3000        2000
A00002     200         GOOD2 3000        7000        4000        3000(5 行受影响)

Answer 3

create table [采购明细表]
(
[采购单号] varchar(10),
[行号] int,
[商品] varchar(10),
[数量] int
)
insert into [采购明细表]
select 'A00001',100,'GOOD1','2000' union all
select 'A00001',200,'GOOD2','4000' union all
select 'A00001',300,'GOOD3','7000' union all
select 'A00002',100,'GOOD1','3000' union all
select 'A00002',200,'GOOD2','3000' create table [库存表]
(
[商品] varchar(10),
[库位] varchar(3),
[库存] int
)insert into [库存表]
select 'GOOD1','K01',1000 union all
select 'GOOD1','K02',2000 union all
select 'GOOD2','K01',4000select a.* ,b.[需求总量],isnull(c.[库存数量],0) [库存数量],b.[需求总量]-isnull(c.[库存数量],0) [缺货量]
from [采购明细表] a
left join (select [商品],sum([数量]) [需求总量] from [采购明细表] group by [商品])b 
on a.[商品]=b.商品
left join (select [商品],sum([库存]) [库存数量] from [库存表] group by [商品]) c on a.[商品]=c.[商品]

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-10-22 21:10:01
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([采购单号] varchar(6),[行号] int,[商品] varchar(5),[数量] int)
insert [a]
select 'A00001',100,'GOOD1',2000 union all
select 'A00001',200,'GOOD2',4000 union all
select 'A00001',300,'GOOD3',7000 union all
select 'A00002',100,'GOOD1',3000 union all
select 'A00002',200,'GOOD2',3000
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([商品] varchar(5),[库位] varchar(3),[库存] int)
insert [b]
select 'GOOD1','K01',1000 union all
select 'GOOD1','K02',2000 union all
select 'GOOD2','K01',4000
--------------开始查询--------------------------
select 
  * 
from 
  a
left join
(select 
   a.商品,需求总量=a.总数量,isnull(b.库存,0) as 库存,缺货量=isnull((a.总数量-b.库存),0)
from
  (select 商品,sum(数量) as 总数量 from a group by 商品)a
left join
  (select 商品,sum(库存) as 库存 from b group by 商品)b
on
  a.商品=b.商品)b
on 
  a.商品=b.商品----------------结果----------------------------
/* 采购单号   行号          商品    数量          商品    需求总量        库存          缺货量
------ ----------- ----- ----------- ----- ----------- ----------- -----------
A00001 100         GOOD1 2000        GOOD1 5000        3000        2000
A00001 200         GOOD2 4000        GOOD2 7000        4000        3000
A00001 300         GOOD3 7000        GOOD3 7000        0           0
A00002 100         GOOD1 3000        GOOD1 5000        3000        2000
A00002 200         GOOD2 3000        GOOD2 7000        4000        3000(5 行受影响)*/

Answer 5

---难道是这样
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-10-22 21:10:01
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([采购单号] varchar(6),[行号] int,[商品] varchar(5),[数量] int)
insert [a]
select 'A00001',100,'GOOD1',2000 union all
select 'A00001',200,'GOOD2',4000 union all
select 'A00001',300,'GOOD3',7000 union all
select 'A00002',100,'GOOD1',3000 union all
select 'A00002',200,'GOOD2',3000
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([商品] varchar(5),[库位] varchar(3),[库存] int)
insert [b]
select 'GOOD1','K01',1000 union all
select 'GOOD1','K02',2000 union all
select 'GOOD2','K01',4000
--------------开始查询--------------------------
select 
  * 
from 
  a
left join
(select 
   a.商品,需求总量=a.总数量,isnull(b.库存,0) as 库存,缺货量=isnull((a.总数量-b.库存),a.总数量)
from
  (select 商品,sum(数量) as 总数量 from a group by 商品)a
left join
  (select 商品,sum(库存) as 库存 from b group by 商品)b
on
  a.商品=b.商品)b
on 
  a.商品=b.商品----------------结果----------------------------
/* 采购单号   行号          商品    数量          商品    需求总量        库存          缺货量
------ ----------- ----- ----------- ----- ----------- ----------- -----------
A00001 100         GOOD1 2000        GOOD1 5000        3000        2000
A00001 200         GOOD2 4000        GOOD2 7000        4000        3000
A00001 300         GOOD3 7000        GOOD3 7000        0           7000
A00002 100         GOOD1 3000        GOOD1 5000        3000        2000
A00002 200         GOOD2 3000        GOOD2 7000        4000        3000(5 行受影响)*/

Answer 6

---修改下if object_id('tb')is not null drop table tb
go
create table tb(采购单号 varchar(10), 行号 int,商品 varchar(5), 数量 int)
insert tb select
'A00001',      100 ,   'GOOD1',    2000 union all select
'A00001',      200 ,   'GOOD2',    4000 union all select
'A00001',      300 ,   'GOOD3',    7000 union all select
'A00002',      100 ,   'GOOD1',    3000 union all select
'A00002',      200 ,   'GOOD2',    3000 if object_id('tc')is not null drop table tc
go
create table tc( 商品 varchar(5),库位 varchar(5), 库存 int)
insert tc select 
'GOOD1',  'K01',    1000 union all select
'GOOD1',  'K02',    2000 union all select
'GOOD2',  'K01',    4000 
select 采购单号 , 行号,a.商品,数量,
需求总量=isnull(c.需求总量,0) ,库存数量=isnull(b.库存数量,0),缺货量=isnull(c.需求总量,0)-isnull(b.库存数量,0)
from tb a
left join
(select 商品,sum(数量) as 需求总量 from tb  group by 商品)c
on a.商品=c.商品
left join 
(select 商品,sum(库存)as 库存数量 from tc group by 商品)b
on  a.商品=b.商品采购单号       行号          商品    数量          需求总量        库存数量        缺货量
---------- ----------- ----- ----------- ----------- ----------- -----------
A00001     100         GOOD1 2000        5000        3000        2000
A00001     200         GOOD2 4000        7000        4000        3000
A00001     300         GOOD3 7000        7000        0           7000
A00002     100         GOOD1 3000        5000        3000        2000
A00002     200         GOOD2 3000        7000        4000        3000(5 行受影响) 

Answer 7

declare @采购明细表 table(采购单号 varchar(10),行号 int,商品 varchar(10),数量 int)
insert into @采购明细表 select 'A00001',100,'GOOD1',2000
insert into @采购明细表 select 'A00001',200,'GOOD2',4000
insert into @采购明细表 select 'A00001',300,'GOOD3',7000
insert into @采购明细表 select 'A00002',100,'GOOD1',3000
insert into @采购明细表 select 'A00002',200,'GOOD2',3000declare @库存表 table(商品 varchar(10),库位 varchar(10),库存 int)
insert into @库存表 select 'GOOD1','K01',1000
insert into @库存表 select 'GOOD1','K02',2000
insert into @库存表 select 'GOOD2','K01',4000select 
a.采购单号,
a.行号,
a.商品,
a.数量,
a.数量2 as 需求总量,
isnull(b.库存2,0) as 库存数量,
isnull(a.数量2,0)-isnull(b.库存2,0) as 缺货量 
from (
select *,sum(数量) over(partition by 商品) as 数量2
from @采购明细表) a left join (
select distinct 商品,sum(库存) over(partition by 商品) as 库存2
from @库存表) b on a.商品=b.商品
A00001 100 GOOD1 2000 5000 3000 2000
A00002 100 GOOD1 3000 5000 3000 2000
A00002 200 GOOD2 3000 7000 4000 3000
A00001 200 GOOD2 4000 7000 4000 3000
A00001 300 GOOD3 7000 7000 0 7000

Answer 8

貌似楼主的最后一行结果有问题
---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-10-22 21:23:46
---------------------------------
 
IF OBJECT_ID('[tba]') IS NOT NULL 
    DROP TABLE [tba]
go
CREATE TABLE [tba] (采购单号 VARCHAR(6),行号 INT,商品 VARCHAR(5),数量 INT)
INSERT INTO [tba]
SELECT 'A00001',100,'GOOD1',2000 UNION ALL
SELECT 'A00001',200,'GOOD2',4000 UNION ALL
SELECT 'A00001',300,'GOOD3',7000 UNION ALL
SELECT 'A00002',100,'GOOD1',3000 UNION ALL
SELECT 'A00002',200,'GOOD2',3000
---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-10-22 21:24:00
---------------------------------
 
IF OBJECT_ID('[tbb]') IS NOT NULL 
    DROP TABLE [tbb]
go
CREATE TABLE [tbb] (商品 VARCHAR(5),库位 VARCHAR(3),库存 INT)
INSERT INTO [tbb]
SELECT 'GOOD1','K01',1000 UNION ALL
SELECT 'GOOD1','K02',2000 UNION ALL
SELECT 'GOOD2','K01',4000select tba.*,需求总量,isnull(库存,0),缺货量=isnull(需求总量,0)-isnull(库存,0)
from tba join (select 商品,需求总量=sum(数量) from tba group by 商品) a on tba.商品=a.商品
     left join (select 商品,库存=sum(库存) from tbb group by 商品) b on tba.商品=b.商品A00001 100 GOOD1 2000 5000 3000 2000
A00002 100 GOOD1 3000 5000 3000 2000
A00002 200 GOOD2 3000 7000 4000 3000
A00001 200 GOOD2 4000 7000 4000 3000
A00001 300 GOOD3 7000 7000 0 7000

Answer 9

declare @采购明细表 table (采购单号 varchar(6),行号 int,商品 varchar(5),数量 int)
insert into @采购明细表
select 'A00001',100,'GOOD1',2000 union all
select 'A00001',200,'GOOD2',4000 union all
select 'A00001',300,'GOOD3',7000 union all
select 'A00002',100,'GOOD1',3000 union all
select 'A00002',200,'GOOD2',3000declare @库存表 table (商品 varchar(5),库位 varchar(3),库存 int)
insert into @库存表
select 'GOOD1','K01',1000 union all
select 'GOOD1','K02',2000 union all
select 'GOOD2','K01',4000select 采购单号,行号, 商品,数量,需求总量,库存数量,需求总量-库存数量 as 缺货量 from 
(select a.*,isnull(b.库存,0) as 库存数量,c.需求总量  from @采购明细表 a 
left join 
(select 商品, sum(库存) as 库存 from @库存表  group by 商品)b on a.商品=b.商品
left join (select 商品,sum(数量) as 需求总量 from @采购明细表 group by 商品) c on a.商品=c.商品
)d/*
采购单号   行号          商品    数量          需求总量        库存数量        缺货量
------ ----------- ----- ----------- ----------- ----------- -----------
A00001 100         GOOD1 2000        5000        3000        2000
A00001 200         GOOD2 4000        7000        4000        3000
A00001 300         GOOD3 7000        7000        0           7000
A00002 100         GOOD1 3000        5000        3000        2000
A00002 200         GOOD2 3000        7000        4000        3000
*/

Answer 10

if object_id('tb')is not null drop table tb
go
create table tb(采购单号 varchar(10), 行号 int,商品 varchar(5), 数量 int)
insert tb select
'A00001',      100 ,   'GOOD1',    2000 union all select
'A00001',      200 ,   'GOOD2',    4000 union all select
'A00001',      300 ,   'GOOD3',    7000 union all select
'A00002',      100 ,   'GOOD1',    3000 union all select
'A00002',      200 ,   'GOOD2',    3000 if object_id('tc')is not null drop table tc
go
create table tc( 商品 varchar(5),库位 varchar(5), 库存 int)
insert tc select 
'GOOD1',  'K01',    1000 union all select
'GOOD1',  'K02',    2000 union all select
'GOOD2',  'K01',    4000 
go
select 采购单号 , 行号 ,商品,数量,需求总量, 库存数量, 缺货量 = 需求总量 - 库存数量
from
(
select 采购单号 , 行号 ,商品,数量,
需求总量 = isnull((select sum(数量) from tb where 商品 =t.商品) , 0),
库存数量 = isnull((select sum(库存) from tc where 商品 = t.商品) , 0)
from tb t
)a
----------------------------------------------
采购单号       行号          商品    数量          需求总量        库存数量        缺货量
---------- ----------- ----- ----------- ----------- ----------- -----------
A00001     100         GOOD1 2000        5000        3000        2000
A00001     200         GOOD2 4000        7000        4000        3000
A00001     300         GOOD3 7000        7000        0           7000
A00002     100         GOOD1 3000        5000        3000        2000
A00002     200         GOOD2 3000        7000        4000        3000