[求助]SQL语句的问题 - 编程论坛

#2

棉花糖ONE2007-01-10 12:18

没看懂

#3

上炕不脱鞋2007-01-10 12:40

表a 表b 表c
ID a1 a2 ID b1 b2 ID c1 c2
001 100 150 001 200 250 001 10 20
002 150 200 002 150 200 002 20 30
001 50 60 002 100 100 003 30 40
003 10 10 003 50 50 003 50 50
ID=表D的ID的查询出来
结果像这样：
ID a1 a2 b1 b2 c1 c2
001 150 210 200 250 10 20
002 150 200 250 300 20 30
003 20 20 50 50 80 90

这回应该很清楚了

[此贴子已经被作者于2007-1-10 13:07:45编辑过]

#4

accpfriend2007-01-10 13:06

ID a1 a2 b1 b2 c1 c2
001 250 210 200 250 10 20
002 150 200 250 300 20 30
003 20 20 50 50 80 90
红色的是１５０吧，

#5

上炕不脱鞋2007-01-10 13:08

不好意思，写错了，谢谢提醒！
你知道应该怎么写吗？

#6

accpfriend2007-01-10 13:09

create table a (ID int,a1 int,a2 int) --表a
insert into a select 001,100,150
insert into a select 002,150,200
insert into a select 001,50,60
insert into a select 003,10,10

create table b (ID int,b1 int,b2 int) --表b
insert into b select 001,200,250
insert into b select 002,150,200
insert into b select 002,100,100
insert into b select 003,50,50

drop table c
create table c (ID int,c1 int,c2 int) --表c
insert into c select 001,10,20
insert into c select 002,20,30
insert into c select 003,30,40
insert into c select 003,50,60

drop table d
create table d(ID int) --表d
insert into d select 001
union all select 002
union all select 003

select sum(a.a1) as a1 from a group by a.ID
select d.ID,sum(a.a1) as a1,sum(a.a2) as a2, sum(b.b1) as b1,sum(b.b2) as b2,
sum(c.c1) as c1,sum(c.c2) as c2 from a,b,c,d where a.ID = d.ID
and b.ID = d.ID and c.ID = d.ID group by d.ID

#7

棉花糖ONE2007-01-10 13:09

if object_id('a') is not null
drop table a
go
create table a(id varchar(3),a1 int,a2 int)
insert a select '001',100,150
union all
select '002',150,200
union all
select '001',50,60
union all
select '003',10,10
select * from a

if object_id('b') is not null
drop table b
go
create table b (id int,b1 int,b2 int)
insert b select '001',200,250
union all
select '002',150,200
union all
select '002',100,100
union all
select '003',50,50

if object_id('c') is not null
drop table c
go
create table c (id int,c1 int,c2 int)
insert c select '001',10,20
union all
select '002',20,30
union all
select '003',30,40
union all
select '003',50,50

select aa.id,aa.a1,aa.a2,bb.b1,bb.b2,cc.c1,cc.c2 from (select id,sum(a1) as a1 ,sum(a2) as a2 from a group by id) as aa,(select id,sum(b1) as b1 ,sum(b2) as b2 from b group by id) as bb ,
(select id,sum(c1) as c1 ,sum(c2) as c2 from c group by id) as cc where aa.id=bb.id and aa.id=cc.id

结果
id a1 a2 b1 b2 c1 c2
---- ----------- ----------- ----------- ----------- ----------- -----------
001 150 210 200 250 10 20
002 150 200 250 300 20 30
003 10 10 50 50 80 90

（所影响的行数为 3 行）

#8

上炕不脱鞋2007-01-10 13:53

select ItemInfo.ItemID,ItemInfo.ItemName,aa.SePaMo,aa.SeInMo,aa.SeFaMo,bb.StoPaMo,bb.StoInMo,bb.StoCoMo,ItemInfo.Itemys,bb.StoPaMo,cc.CoDeSt,cc.CoStock,bb.StoEsMo,cc.CoCost from ItemInfo,
(select SeItem,sum(SePaMo) as SePaMo ,sum(SeInMo) as SeInMo,sum(SeFaMo) as SeFaMo from SePactInfo group by SeItem) as aa,
(select StoItem,sum(StoPaMo) as StoPaMo ,sum(StoInMo) as StoInMo,sum(StoCoMo) as StoCoMo,sum(StoEsMo) as StoEsMo from StoPactInfo group by StoItem) as bb ,
(select CoItem,sum(CoDeSt) as CoDeSt ,sum(CoStock) as CoStock,sum(CoCost) as CoCost from CoSellInfo group by CoItem) as cc where ItemInfo.ItemName=aa.SeItem and ItemInfo.ItemName=bb.StoItem and ItemInfo.Item=cc.CoItem

大哥我照你那个改的可是为什么查不出数据来啊，是不是那里错了阿？

#9

棉花糖ONE2007-01-10 13:57

你的是四个表啊．具体我不清楚，ItemInfo这个表的结构给出来看看

#10

上炕不脱鞋2007-01-10 14:02

ItemInfo有5个字段Item,ItemID,ItemName,Principal,Itemys
我写的语法还没有错啊

[此贴子已经被作者于2007-1-10 14:03:31编辑过]

#11

棉花糖ONE2007-01-10 14:07

不清楚什么问题

#12

上炕不脱鞋2007-01-10 14:12

#13

棉花糖ONE2007-01-10 14:13

是输出不符合你的要求吗

#14

上炕不脱鞋2007-01-10 14:16

select ItemInfo.ItemID,ItemInfo.ItemName,aa.SePaMo,aa.SeInMo,aa.SeFaMo,bb.StoPaMo,bb.StoInMo,bb.StoCoMo,ItemInfo.Itemys,bb.StoPaMo,cc.CoDeSt,cc.CoStock,bb.StoEsMo,cc.CoCost from ItemInfo,
(select SeItem,sum(SePaMo) as SePaMo ,sum(SeInMo) as SeInMo,sum(SeFaMo) as SeFaMo from SePactInfo group by SeItem) as aa,
(select StoItem,sum(StoPaMo) as StoPaMo ,sum(StoInMo) as StoInMo,sum(StoCoMo) as StoCoMo,sum(StoEsMo) as StoEsMo from StoPactInfo group by StoItem) as bb ,
(select CoItem,sum(CoDeSt) as CoDeSt ,sum(CoStock) as CoStock,sum(CoCost) as CoCost from CoSellInfo group by CoItem) as cc where ItemInfo.ItemName=aa.SeItem and ItemInfo.ItemName=bb.StoItem and ItemInfo.Item=cc.CoItem

这里错了，应该是ItemName

大哥谢谢你了

还有你的头像可真强