用sql语句实现下面的功能(50)

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-21

• #1

序号 父序号 产量1 产量21 0 ？ ？2 1 5 6 3 1 4 84 0 ？ ？ 5 4 15 10 6 4 23 22希望能通过一个sql语句实现：所有父序号相同的记录的产量1，产量2字段的合计值更新到对应的问号（？）位置，不知道能不能一条语句实现？注意，序号，父序号的层次数是不确定的。

 

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-21

• #2

没人看啊 ：（

 

Y

yh16008262

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-21

• #3

什么乱起八糟的，产量1和产量2都是谁

 

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-22

• #4

产量1，产量2 可以理解为字段1，字段2

 

F

fenghuo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-23

• #5

有点难度..

 

H

haidy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-23

• #6

Update 表 Set 产量1=IsNull(b.产量1,0), 产量2=IsNull(b.产量2,0)From 表 a, (Select 父序号, 产量1=Sum(产量1), 产量2=Sum(产量2) From 表 Group By 父序号)bWhere (a.序号=b.父序号) And (a.父序号=0)

 

X

xiaoli1740

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-23

• #7

update tablename A set field1 = (select field1sum from (select topindex, sum(field1) as field1sum from tablename group by topindex) where topindex = A.index), field2 = (select field2sum from (select topindex, sum(field2) as field2sum from tablename group by topindex) where topindex = A.index) where exists (select topindex from tablename where topindex = A.index);

 

L

liuyashao

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-23

• #8

update tablename A set field1 = (select field1sum from (select topindex, sum(field1) as field1sum from tablename group by topindex) where topindex = A.index), field2 = (select field2sum from (select topindex, sum(field2) as field2sum from tablename group by topindex) where topindex = A.index) where exists (select topindex from tablename where topindex = A.index);可以改写成update tablename A set field1 = (select sum(field1) from tablename where topindex = A.index), field2 = (select sum(field2) from tablename where topindex = A.index) where exists (select topindex from tablename where topindex = A.index);

 

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-26

• #9

haidy的答案稍微有一点瑕疵，最后一个Where (a.序号=b.父序号) And (a.父序号=0) 应该是Where (a.序号=b.父序号) And (a.父序号<>0)

 

W

wangminqi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-29

• #10

试试这个goCREATE TABLE [dbo].[test]( [No] [int] NULL, [pNo] [int] NULL, [count1] [real] NULL, [count2] [real] NULL) ON [PRIMARY]GOinsert into test select 1, 0, null, nullunionselect 2, 1, 5, 6unionselect 3, 1, 4, 8unionselect 4, 0, null, nullunionselect 5, 4, 15, 10unionselect 6, 4, 23, 22goupdate [test] set [count1]= c1,[count2]=c2from test as ajoin (SELECT [pNo] ,sum([count1]) as c1 ,sum([count2]) as c2 FROM [test] where pNo<>0group by pNo ) as b on a.No=b.pNo where a.[pNo]=0goselect * from test godrop table testgohttp://www.mybuffet.cn

 

H

haidy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-30

• #11

不对，要修改的是a表的产量1,产量2，所以a表的父序号=0是没问题的。要加也是加“And (b.父序号<>0)”，但其实如果(a.序号=b.父序号) And (a.父序号=0)，那么b.父序号就应该<>0，除非你的程序有误。我的执行脚本你试过了吗？试过有问题再说吧。

 

N

npcd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-02

• #12

除非你这张表是做为最终定稿的报表数据，否则这个表的设计上是有问题的，某个序号的产量一旦发生变化，父序号的产量就要更新。 应该是要分成两张表，一张表只存放序号和父序号的关系，一张表存放序号和产量的关系。要得到父序号的产量通过两张表的关联查询得到。

 

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-06

• #13

haidy说的对，应该是Where (a.序号=b.父序号) And (b.父序号<>0) 我试过了 怎么答复前弄错了 低级错误 ：（

 

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-06

• #14

wangminqi和haidy的答案都是正确的，本质是一样的。npcd的观点考虑过的，可是数据量很小，而且每个月才报表一次，就当冗余了。懒惰啊 ：）

 

Y

yuanlaqin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-06

• #15

多人接受答案了。