使用Group By 查询两个表,但有两个字段只需A表的,,不需要Group By...

cookies10wen 2009-10-19 02:12:45
a:
Id NameNo ColorNo StyleNo JobNo InAmount Place
1 AA Red S1\S2\S3 J1\J2\J3 100 A1
2 AA Red S1\S2\S3 J1\J2\J3 100 A1
3 BB Bule S1\S2\S3\S4 J1\J2\J3\J4 200 A2
4 CC Yellow S1\S2\S3\S4 J1\J2\J3\J4 300 A3


b:
Id NameNo ColorNo StyleNo JobNo AtAmount Place
1 AA Red S1 J1\ 100 A1
1 AA Red S1 J1\ 100 A1
3 BB Bule S1\S2\ J1\J2\ 200 A2
4 CC Yellow S1\S2\ J1\J2\ 300 A3


查询得出结果:
NameNo ColorNo StyleNo JobNo InAmount AtAmount Place
AA Red S1\S2\S3 J1\J2\J3 200 200 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



用NameNo\ColorNo\Place进行汇总,,而StyleNo\JobNo要a表的数据...

...全文
265 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
纸纸纸纸飞飞 2009-10-19
  • 打赏
  • 举报
回复
if object_id(N'tablea') is not null drop table tablea
create table tablea(
NameNo varchar(10),
ColorNo varchar(20),
StyleNo varchar(100),
JobNo varchar(100),
InAmount int,
Place varchar(10)
)
insert into tablea
select 'AA', 'Red', 'S1\S2\S3', 'J1\J2\J3', 100, 'A1' union all
select 'AA', 'Red', 'S1\S2\S3', 'J1\J2\J3', 100, 'A1' union all
select 'BB', 'Blue', 'S1\S2\S3\S4', 'J1\J2\J3\J4', 200, 'A2' union all
select 'CC', 'Yellow', 'S1\S2\S3\S4', 'J1\J2\J3\J4', 300, 'A3'

if object_id(N'tableb') is not null drop table tableb
create table tableb(
NameNo varchar(10),
ColorNo varchar(20),
StyleNo varchar(100),
JobNo varchar(100),
AtAmount int,
Place varchar(10)
)
insert into tableb
select 'AA', 'Red', 'S1', 'J1', 100, 'A1' union all
select 'AA', 'Red', 'S1', 'J1', 100, 'A1' union all
select 'BB', 'Blue', 'S1\S2\', 'J1\J2\', 200, 'A2' union all
select 'CC', 'Yellow', 'S1\S2\', 'J1\J2\', 300, 'A3'

select * from tablea
select * from tableb

/*
NameNo ColorNo StyleNo JobNo InAmount Place
---------- -------------------- ---------------- -------------------- ----------- ----------
AA Red S1\S2\S3 J1\J2\J3 100 A1
AA Red S1\S2\S3 J1\J2\J3 100 A1
BB Blue S1\S2\S3\S4 J1\J2\J3\J4 200 A2
CC Yellow S1\S2\S3\S4 J1\J2\J3\J4 300 A3

(4 row(s) affected)

NameNo ColorNo StyleNo JobNo AtAmount Place
---------- -------------------- ---------------- -------------------- ----------- ----------
AA Red S1 J1 100 A1
AA Red S1 J1 100 A1
BB Blue S1\S2\ J1\J2\ 200 A2
CC Yellow S1\S2\ J1\J2\ 300 A3

(4 row(s) affected)
*/

select a.NameNo, a.ColorNo, a.StyleNo, a.JobNo, a.InAmount, b.AtAmount, b.Place
from (select NameNo, ColorNo, StyleNo, JobNo, sum(InAmount) as InAmount from tablea group by NameNo, ColorNo, StyleNo, JobNo) a
inner join (select NameNo, ColorNo, StyleNo, Place, sum(AtAmount) as AtAmount from tableb group by NameNo, ColorNo, StyleNo, Place) b on a.NameNo = b.NameNo and a.ColorNo = b.ColorNo

/*
NameNo ColorNo StyleNo JobNo InAmount AtAmount Place
---------- -------------------- ---------------- ----------------- ----------- ----------- ----------
AA Red S1\S2\S3 J1\J2\J3 200 200 A1
BB Blue 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 row(s) affected)
*/

drop table tablea
drop table tableb
jwdream2008 2009-10-19
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 fredrickhu 的回复:]
SQL code--修改
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-19 14:20:46
-- Version:
-- Microsoft SQL Server 2005 - 9.00.40¡­
[/Quote]
顶!
--小F-- 2009-10-19
  • 打赏
  • 举报
回复
--修改
----------------------------------------------------------------
-- 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 行受影响)


*/
jwdream2008 2009-10-19
  • 打赏
  • 举报
回复
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
bancxc 2009-10-19
  • 打赏
  • 举报
回复

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
navy887 2009-10-19
  • 打赏
  • 举报
回复
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
--小F-- 2009-10-19
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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 行受影响)

*/
sgtzzc 2009-10-19
  • 打赏
  • 举报
回复
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
--小F-- 2009-10-19
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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
----------------结果----------------------------
/*
*/
guguda2008 2009-10-19
  • 打赏
  • 举报
回复
简单的JOIN连接而已
guguda2008 2009-10-19
  • 打赏
  • 举报
回复
我猜我回个帖再一刷新答案就出来了
--小F-- 2009-10-19
  • 打赏
  • 举报
回复
哦 看错
--小F-- 2009-10-19
  • 打赏
  • 举报
回复
根本不需要B表啊
cookies10wen 2009-10-19
  • 打赏
  • 举报
回复
a:
Id NameNo ColorNo StyleNo JobNo InAmount Place
1 AA Red S1\S2\S3 J1\J2\J3 100 A1
2 AA Red S1\S2\S3 J1\J2\J3 100 A1
3 BB Bule S1\S2\S3\S4 J1\J2\J3\J4 200 A2
4 CC Yellow S1\S2\S3\S4 J1\J2\J3\J4 300 A3


b:
Id NameNo ColorNo StyleNo JobNo AtAmount Place
1 AA Red S1 J1 100 A1
1 AA Red S1 J1 100 A1
3 BB Bule S1\S2\ J1\J2\ 200 A2
4 CC Yellow S1\S2\ J1\J2\ 300 A3


查询得出结果:
NameNo ColorNo StyleNo JobNo InAmount AtAmount Place
AA Red S1\S2\S3 J1\J2\J3 200 200 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

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧