22,199
社区成员
发帖
与我相关
我的任务
分享
--修改
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-19 14:20:46
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Id] int,[NameNo] varchar(2),[ColorNo] varchar(6),[StyleNo] varchar(11),[JobNo] varchar(11),[InAmount] int,[Place] varchar(2))
insert [a]
select 1,'AA','Red','S1\S2\S3','J1\J2\J3',100,'A1' union all
select 2,'AA','Red','S1\S2\S3','J1\J2\J3',100,'A1' union all
select 3,'BB','Bule','S1\S2\S3\S4','J1\J2\J3\J4',200,'A2' union all
select 4,'CC','Yellow','S1\S2\S3\S4','J1\J2\J3\J4',300,'A3'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([Id] int,[NameNo] varchar(2),[ColorNo] varchar(6),[StyleNo] varchar(6),[JobNo] varchar(6),[AtAmount] int,[Place] varchar(2))
insert [b]
select 1,'AA','Red','S1','J1',100,'A1' union all
select 1,'AA','Red','S1','J1',100,'A1' union all
select 3,'BB','Bule','S1\S2\','J1\J2\',200,'A2' union all
select 4,'CC','Yellow','S1\S2\','J1\J2\',300,'A3'
--------------开始查询--------------------------
select
A.NameNo,A.ColorNo,A.StyleNo,A.JobNo,A.Place,
Sum(InAmount) as InAmount,
(Select Sum(AtAmount)from b where NameNo=a.NameNo) as AtAmount
from
a
Group by
a.NameNo,a.ColorNo,a.StyleNo,a.JobNo,a.Place
----------------结果----------------------------
/* NameNo ColorNo StyleNo JobNo Place InAmount AtAmount
------ ------- ----------- ----------- ----- ----------- -----------
AA Red S1\S2\S3 J1\J2\J3 A1 200 200
BB Bule S1\S2\S3\S4 J1\J2\J3\J4 A2 200 200
CC Yellow S1\S2\S3\S4 J1\J2\J3\J4 A3 300 300
(3 行受影响)
*/
select b.NameNo
,b.ColorNo
,b.StyleNo
,b.JobNo
,sum(a.InAmount)
,sum(b.AtAmount)
,b.Place
from b
inner join a
on b.NameNo=a.NameNo
and b.ColorNo=a.ColorNo
and b.Place=a.Place
select A.NameNo,A.ColorNo,A.StyleNo,A.JobNo,A.Place,InAmount=Sum(InAmount),
AtAmount=(Select Sum(AtAmount)from B where NameNo=A.NameNo)
from A
Group by A.NameNo,A.ColorNo,A.StyleNo,A.JobNo,A.Place
select a.NameNo,a.ColorNo,a.StyleNo,a.JobNo,sum(a.InAmount) InAmount,sum(b.AtAmount) AtAmount,a.Place
from a,b
where a.id=b.id
group by a.NameNo,a.ColorNo,a.Place,a.StyleNoa.a.JobNo
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-19 14:20:46
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Id] int,[NameNo] varchar(2),[ColorNo] varchar(6),[StyleNo] varchar(11),[JobNo] varchar(11),[InAmount] int,[Place] varchar(2))
insert [a]
select 1,'AA','Red','S1\S2\S3','J1\J2\J3',100,'A1' union all
select 2,'AA','Red','S1\S2\S3','J1\J2\J3',100,'A1' union all
select 3,'BB','Bule','S1\S2\S3\S4','J1\J2\J3\J4',200,'A2' union all
select 4,'CC','Yellow','S1\S2\S3\S4','J1\J2\J3\J4',300,'A3'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([Id] int,[NameNo] varchar(2),[ColorNo] varchar(6),[StyleNo] varchar(6),[JobNo] varchar(6),[AtAmount] int,[Place] varchar(2))
insert [b]
select 1,'AA','Red','S1','J1',100,'A1' union all
select 1,'AA','Red','S1','J1',100,'A1' union all
select 3,'BB','Bule','S1\S2\','J1\J2\',200,'A2' union all
select 4,'CC','Yellow','S1\S2\','J1\J2\',300,'A3'
--------------开始查询--------------------------
select
a.NameNo,a.ColorNo,a.StyleNo,a.JobNo,
sum(a.InAmount) as InAmount,
sum(b.AtAmount) as AtAmount,
b.Place
from
a
join
b
on
a.NameNo=b.NameNo and a.ColorNo=b.ColorNo
group by
a.NameNo,a.ColorNo,a.StyleNo,a.JobNo,b.Place
----------------结果----------------------------
/* NameNo ColorNo StyleNo JobNo InAmount AtAmount Place
------ ------- ----------- ----------- ----------- ----------- -----
AA Red S1\S2\S3 J1\J2\J3 400 400 A1
BB Bule S1\S2\S3\S4 J1\J2\J3\J4 200 200 A2
CC Yellow S1\S2\S3\S4 J1\J2\J3\J4 300 300 A3
(3 行受影响)
*/
select
a.NameNo,a.ColorNo,a.StyleNo,a.JobNo,
sum(a.InAmount) as InAmount,
sum(b.AtAmount) as AtAmount,
b.Place
from
a,b
where
a.NameNo=b.NameNo and a.ColorNo=b.ColorNo
group by
a.NameNo,a.ColorNo,a.StyleNo,a.JobNo,b.Place
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-19 14:17:16
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Id] int,[NameNo] varchar(2),[ColorNo] varchar(6),[StyleNo] varchar(11),[JobNo] varchar(11),[InAmount] int,[Place] varchar(2))
insert [a]
select 1,'AA','Red','S1\S2\S3','J1\J2\J3',100,'A1' union all
select 2,'AA','Red','S1\S2\S3','J1\J2\J3',100,'A1' union all
select 3,'BB','Bule','S1\S2\S3\S4','J1\J2\J3\J4',200,'A2' union all
select 4,'CC','Yellow','S1\S2\S3\S4','J1\J2\J3\J4',300,'A3'
--------------开始查询--------------------------
select
NameNo,ColorNo,StyleNo,JobNo,sum(InAmount) as InAmount,Place
from
a
group by
NameNo,ColorNo,StyleNo,JobNo,Place
----------------结果----------------------------
/*
*/