求一SQL语句.谢谢
具体要求为:产品表
goods code , style
01 A
02 B
03 C
04 C
05 A
06 B进仓纪录表
instock
Code lines goods qty
jc01 1 01 50
jc01 2 02 50
jc02 1 01 50
jc02 2 03 50
jc02 3 04 50
jc03 1 01 50
jc03 2 02 50
jc03 3 03 50
jc03 4 04 50
jc04 1 05 50
jc04 2 06 50要求统计为:style qty
A 200
B 150
C 200
具体要求为:产品表
goods code , style
01 A
02 B
03 C
04 C
05 A
06 B进仓纪录表
instock
Code lines goods qty
jc01 1 01 50
jc01 2 02 50
jc02 1 01 50
jc02 2 03 50
jc02 3 04 50
jc03 1 01 50
jc03 2 02 50
jc03 3 03 50
jc03 4 04 50
jc04 1 05 50
jc04 2 06 50要求统计为:style qty
A 200
B 150
C 200
from goods a,instock b where a.code=b.goods
group by a.style
from goods a,instock b
where a.codes=b.goods
group by a.style
-- Author :SQL77(只为思齐老)
-- Date :2010-03-06 16:00:11
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#goods
if object_id('tempdb.dbo.#goods') is not null drop table #goods
go
create table #goods([code] varchar(2),[style] varchar(1))
insert #goods
select '01','A' union all
select '02','B' union all
select '03','C' union all
select '04','C' union all
select '05','A' union all
select '06','B'
--> 测试数据:#instock
if object_id('tempdb.dbo.#instock') is not null drop table #instock
go
create table #instock([Code] varchar(4),[lines] int,[goods] varchar(2),[qty] int)
insert #instock
select 'jc01',1,'01',50 union all
select 'jc01',2,'02',50 union all
select 'jc02',1,'01',50 union all
select 'jc02',2,'03',50 union all
select 'jc02',3,'04',50 union all
select 'jc03',1,'01',50 union all
select 'jc03',2,'02',50 union all
select 'jc03',3,'03',50 union all
select 'jc03',4,'04',50 union all
select 'jc04',1,'05',50 union all
select 'jc04',2,'06',50
--------------开始查询--------------------------select
G.style,
SUM(I.qty)qty
from
#goods G,#instock I
WHERE
G.code=I.goods
GROUP BY G.style----------------结果----------------------------
/* (所影响的行数为 6 行)
(所影响的行数为 11 行)style qty
----- -----------
A 200
B 150
C 200(所影响的行数为 3 行)
*/
go
create table [goods]([code] varchar(2),[style] varchar(1))
insert [goods]
select '01','A' union all
select '02','B' union all
select '03','C' union all
select '04','C' union all
select '05','A' union all
select '06','B'
if object_id('[instock]') is not null drop table [instock]
go
create table [instock]([Code] varchar(4),[lines] int,[goods] varchar(2),[qty] int)
insert [instock]
select 'jc01',1,'01',50 union all
select 'jc01',2,'02',50 union all
select 'jc02',1,'01',50 union all
select 'jc02',2,'03',50 union all
select 'jc02',3,'04',50 union all
select 'jc03',1,'01',50 union all
select 'jc03',2,'02',50 union all
select 'jc03',3,'03',50 union all
select 'jc03',4,'04',50 union all
select 'jc04',1,'05',50 union all
select 'jc04',2,'06',50
select a.style,sum(b.qty) as qty from goods a,instock b where a.code=b.goods group by a.style/**
style qty
----- -----------
A 200
B 150
C 200(所影响的行数为 3 行)**/
style qty
A 150
A 50
B 100
B 50
C 100
C 100
declare @goods table([code] varchar(2),[style] varchar(1))
insert @goods
select '01','A' union all
select '02','B' union all
select '03','C' union all
select '04','C' union all
select '05','A' union all
select '06','B'--> 测试数据:@instock
declare @instock table([Code] varchar(4),[lines] int,[goods] varchar(2),[qty] int)
insert @instock
select 'jc01',1,'01',50 union all
select 'jc01',2,'02',50 union all
select 'jc02',1,'01',50 union all
select 'jc02',2,'03',50 union all
select 'jc02',3,'04',50 union all
select 'jc03',1,'01',50 union all
select 'jc03',2,'02',50 union all
select 'jc03',3,'03',50 union all
select 'jc03',4,'04',50 union all
select 'jc04',1,'05',50 union all
select 'jc04',2,'06',50select a.style,sum(b.[qty]) as [qty] from @goods a ,@instock b where a.code=b.[goods]
group by a.style
/*
style qty
----- -----------
A 200
B 150
C 200(3 行受影响)
*/