邹建请进,请教一个MS sqlserver的 SQL语句,要求速度最快（50万行)。

Question

这样的设计怎么统计呢? 像Excel的处理

Answer 1

现在不究设计问题,
是账务凭证问题,求对方科目是哪些?
科目编号(kmbh)101的借方金额jfje=500,贷方dfje为0
而
科目编号(kmbh)121的贷方金额dfje=2000,借方jfje为0
科目编号(kmbh)123的贷方金额dfje=100,借方jfje为0
科目编号(kmbh)121的贷方金额dfje=2000,借方jfje为0
由于 后三行的sum(dfje)=第1行的jfje,所有会找出相应的对方科目
101的对方科目为后三行之和"121,123,103"
反之后三行121,123,103的对方科目都为"101"
不知道我说明白没有,
我已经用函数实现了需要,但速度上不去,

Answer 2

如果kmbh编号间没有联系，貌似无法靠SUM来确定唯一关系

Answer 3

你的科目编号没有联系吗？如果没有，你如何确定他们的对应？
假设数据如下时结果为何？
kmbh jfje  dfje    kmbhEx
101  5000   0.00   null
120  0.00   3000   null
121  0.00   2000   null
123  0.00   1000   null
102  0.00   2000   null
－－－－－－－－－－－－－
？？？

Answer 4

请教一个SQL语句:
在同张凭证条件下，jfje=sum(dfje),然后抽取对方科目编号：
同张凭证表示：pzrq,pzbh,pzlx组合在一起，全表中唯一。
如“20051231,12\89,,”表示一张凭证，
又如“20051231,12\92,,”表示另一张凭证
其它我想实现的功能是，在一同张凭证条件及jfje=sum(dfje)下，求字符串字段之和,
以下是原始数据：
凭证原始数据（逗号分隔）：表pzk
pzrq,pzbh,pzlx,zy,kmbh,jfje,dfje,kmbhex
20051231,12\89,,计提本月房屋租金,23101,0,24150,
20051231,12\89,,计提本月行车租金,23102,0,850.25,
20051231,12\89,,计提本月租金,40509,25000.25,0,
20051231,12\92,,结转在建工程,16901,0,3200,
20051231,12\92,,结转在建工程,16901,0,11800,
20051231,12\92,,结转在建工程,16901,0,32045.4,
20051231,12\92,,结转在建工程,16901,0,10300,
20051231,12\92,,结转在建工程,16101,57345.4,0,
20051231,12\103,,结转本月制造费用,40503,0,58639.78,
20051231,12\103,,结转本月制造费用,40504,0,50,
20051231,12\103,,结转本月制造费用,40505,0,92.5,
20051231,12\103,,结转本月制造费用,40507,0,65161.07,
20051231,12\103,,结转本月制造费用,40508,0,6352.45,
20051231,12\103,,结转本月制造费用,40509,0,25000.25,
20051231,12\103,,结转本月制造费用,40512,0,396034.79,
20051231,12\103,,结转本月制造费用,40516,0,79782.96
20051231,12\103,,结转本月制造费用,40103,631113.8,0
20051231,12\104,,结转本月管理费用,52101,0,6500
20051231,12\104,,结转本月管理费用,52103,0,1847.77
20051231,12\104,,结转本月管理费用,52105,0,12577.3
20051231,12\104,,结转本月管理费用,52106,0,6645.38
20051231,12\104,,结转本月管理费用,52107,0,10376.6
20051231,12\104,,结转本月管理费用,52108,0,565.5
20051231,12\104,,结转本月管理费用,52109,0,9095.22
20051231,12\104,,结转本月管理费用,52111,0,12750
20051231,12\104,,结转本月管理费用,52112,0,8499.39
20051231,12\104,,结转本月管理费用,52114,0,59404.69
20051231,12\104,,结转本月管理费用,52117,0,1142.87
20051231,12\104,,结转本月管理费用,52118,0,916.39
20051231,12\104,,结转本月管理费用,52124,0,7764.26
20051231,12\104,,结转本月管理费用,52130,0,13821.67
20051231,12\104,,结转本月管理费用,52131,0,1145.49
20051231,12\104,,结转本月管理费用,3210105,153052.53,0凭证最终结果数据：表pzkpzrq,pzbh,pzlx,zy,kmbh,jfje,dfje,kmbhex
20051231,12\89,,计提本月房屋租金,23101,0.0,24150.0,40509
20051231,12\89,,计提本月行车租金,23102,0.0,850.25,40509
20051231,12\89,,计提本月租金,40509,25000.25,0.0,23101、23102、
20051231,12\92,,结转在建工程,16901,0.0,3200.0,16101
20051231,12\92,,结转在建工程,16901,0.0,11800.0,16101
20051231,12\92,,结转在建工程,16901,0.0,32045.400000000001,16101
20051231,12\92,,结转在建工程,16901,0.0,10300.0,16101
20051231,12\92,,结转在建工程,16101,57345.400000000001,0.0,16901、
20051231,12\103,,结转本月制造费用,40503,0.0,58639.779999999999,40103
20051231,12\103,,结转本月制造费用,40504,0.0,50.0,40103
20051231,12\103,,结转本月制造费用,40505,0.0,92.5,40103
20051231,12\103,,结转本月制造费用,40507,0.0,65161.07,40103
20051231,12\103,,结转本月制造费用,40508,0.0,6352.4499999999998,40103
20051231,12\103,,结转本月制造费用,40509,0.0,25000.25,40103
20051231,12\103,,结转本月制造费用,40512,0.0,396034.78999999998,40103
20051231,12\103,,结转本月制造费用,40516,0.0,79782.960000000006,40103
20051231,12\103,,结转本月制造费用,40103,631113.80000000005,0.0,40503、40504、40505、40507、40508、40509、40512、40516、
20051231,12\104,,结转本月管理费用,52101,0.0,6500.0,3210105
20051231,12\104,,结转本月管理费用,52103,0.0,1847.77,3210105
20051231,12\104,,结转本月管理费用,52105,0.0,12577.299999999999,3210105
20051231,12\104,,结转本月管理费用,52106,0.0,6645.3800000000001,3210105
20051231,12\104,,结转本月管理费用,52107,0.0,10376.6,3210105
20051231,12\104,,结转本月管理费用,52108,0.0,565.5,3210105
20051231,12\104,,结转本月管理费用,52109,0.0,9095.2199999999993,3210105
20051231,12\104,,结转本月管理费用,52111,0.0,12750.0,3210105
20051231,12\104,,结转本月管理费用,52112,0.0,8499.3899999999994,3210105
20051231,12\104,,结转本月管理费用,52114,0.0,59404.690000000002,3210105
20051231,12\104,,结转本月管理费用,52117,0.0,1142.8699999999999,3210105
20051231,12\104,,结转本月管理费用,52118,0.0,916.38999999999999,3210105
20051231,12\104,,结转本月管理费用,52124,0.0,7764.2600000000002,3210105
20051231,12\104,,结转本月管理费用,52130,0.0,13821.67,3210105
20051231,12\104,,结转本月管理费用,52131,0.0,1145.49,3210105
20051231,12\104,,结转本月管理费用,3210105,153052.53,0.0,52101、52103、52105、52106、52107、52108、52109、52111、52112、52114、52117、52118、52124、52130、52131、大家可以导入MS sqlserver试试，谢谢各位。

Answer 5

update a
set a.jfje = sum(b.dfje)
from km a , km b
where left(a.kmbh,1)= left(a.kmbh.1)