错了,改下Select *, (Select SUM(kmfs) from XXGL_GRAGE Where ryid=A.ryid And pzid=A.pzid) As zf from XXGL_GRAGE A
select ryid,pzid,sum(kmfs) as ss into #t from 表 group by ryid,pzid update 表 set zf=a.zf from #t a where ryid=a.ryid and pzid=a.pziddrop table #t
oracle:select a.ryid,a.pzid,a.kmfs,b.zf from XXGL_GRAGE a, (select ryid,pzid,sum(kmfs) "zf" from XXGL_GRAGE group by ryid,pzid) b where a.ryid=b.ryid and a.pzid=b.pzid ;
那用这个吧,效率高些Select A.*,B.zf from XXGL_GRAGE A Left Join (Select SUM(kmfs) from XXGL_GRAGE Group By ryid,pzid) B On A.ryid=B.ryid And A.pzid=B.pzid
不好意思,有点小错误。Select A.*,B.zf from XXGL_GRAGE A Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B On A.ryid=B.ryid And A.pzid=B.pzid
若数据量大时,且ryid,pzid上建立有索引的话,个人认为Select A.*,B.zf from XXGL_GRAGE A Left Join (Select SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B On A.ryid=B.ryid And A.pzid=B.pzid比 Select A.*,B.zf from XXGL_GRAGE A Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B On A.ryid=B.ryid And A.pzid=B.pzid效率高
晕,真糊涂了,个人认为:Select a.*, (Select SUM(kmfs) As zf from XXGL_GRAGE Where ryid=a.ryid And pzid=a.pzid) As zf from XXGL_GRAGE a比 Select A.*,B.zf from XXGL_GRAGE A Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B On A.ryid=B.ryid And A.pzid=B.pzid效率高
--建立測試環境 Create Table myTable ( ryid int, pzid int, kmfs int, ) insert into myTable select 1,12,73 Union select 1,12,90 Union select 1,5361,100 Union select 1,5361,98 union select 1,15246,100 union select 1,15246,96 union select 1,22285,96 union select 1,22285,97 union select 2,29,96 union select 2,29,90 union select 2,29,104 union select 2,29,84 union select 2,29,67 union select 2,29,49 union select 2,29,64--查詢 select b.*,a.zf from (select ryid,pzid, sum(kmfs) as zf from myTable group by ryid,pzid) a ,myTable b where a.ryid=b.ryid and a.pzid=b.pzid--結果 ryid pzid kmfs zf 1 12 73 163 1 12 90 163 1 5361 98 198 1 5361 100 198 1 15246 96 196 1 15246 100 196 1 22285 96 193 1 22285 97 193 2 29 49 554 2 29 64 554 2 29 67 554 2 29 84 554 2 29 90 554 2 29 96 554 2 29 104 554
*,
(Select SUM(kmfs) from XXGL_GRAGE Where ryid=A.ryid And pzid=A.pzid) As zf
from XXGL_GRAGE A
into #t
from 表
group by ryid,pzid
update 表 set zf=a.zf from #t a where ryid=a.ryid and pzid=a.pziddrop table #t
from XXGL_GRAGE a,
(select ryid,pzid,sum(kmfs) "zf" from XXGL_GRAGE group by ryid,pzid) b
where a.ryid=b.ryid and a.pzid=b.pzid ;
from XXGL_GRAGE A
Left Join (Select SUM(kmfs) from XXGL_GRAGE Group By ryid,pzid) B
On A.ryid=B.ryid And A.pzid=B.pzid
列名 'zf' 无效。
服务器: 消息 209,级别 16,状态 1,行 7
列名 'ryid' 不明确。
服务器: 消息 209,级别 16,状态 1,行 7
列名 'pzid' 不明确。有这么多错误??
----------------------
服务器: 消息 8155,级别 16,状态 2,行 1
没有为第 1 列(属于 'B')指定列。
from XXGL_GRAGE A
Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B
On A.ryid=B.ryid And A.pzid=B.pzid
Create Table XXGL_GRAGE
(ryid Int,
pzid Int,
kmfs Int)
--插入数据
Insert XXGL_GRAGE Values(1, 12, 73)
Insert XXGL_GRAGE Values(1, 12, 90)
Insert XXGL_GRAGE Values(1, 5361, 100)
Insert XXGL_GRAGE Values(1, 5361, 98)
Insert XXGL_GRAGE Values(1, 15246, 100)
Insert XXGL_GRAGE Values(1, 15246, 96)
Insert XXGL_GRAGE Values(1, 22285, 96)
Insert XXGL_GRAGE Values(1, 22285, 97)
Insert XXGL_GRAGE Values(2, 29, 96)
Insert XXGL_GRAGE Values(2, 29, 90)
Insert XXGL_GRAGE Values(2, 29, 104)
Insert XXGL_GRAGE Values(2, 29, 84)
Insert XXGL_GRAGE Values(2, 29, 67)
Insert XXGL_GRAGE Values(2, 29, 49)
Insert XXGL_GRAGE Values(2, 29, 64)
--测试
Select A.*,B.zf
from XXGL_GRAGE A
Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B
On A.ryid=B.ryid And A.pzid=B.pzid
--删除测试环境
Drop Table XXGL_GRAGE
--结果
/*
ryid pzid kmfs zf
1 12 73 163
1 12 90 163
1 5361 100 198
1 5361 98 198
1 15246 100 196
1 15246 96 196
1 22285 96 193
1 22285 97 193
2 29 96 554
2 29 90 554
2 29 104 554
2 29 84 554
2 29 67 554
2 29 49 554
2 29 64 554
*/
from XXGL_GRAGE A
Left Join (Select SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B
On A.ryid=B.ryid And A.pzid=B.pzid比
Select A.*,B.zf
from XXGL_GRAGE A
Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B
On A.ryid=B.ryid And A.pzid=B.pzid效率高
a.*,
(Select SUM(kmfs) As zf from XXGL_GRAGE Where ryid=a.ryid And pzid=a.pzid) As zf
from XXGL_GRAGE a比
Select A.*,B.zf
from XXGL_GRAGE A
Left Join (Select ryid,pzid,SUM(kmfs) As zf from XXGL_GRAGE Group By ryid,pzid) B
On A.ryid=B.ryid And A.pzid=B.pzid效率高
Create Table myTable
(
ryid int,
pzid int,
kmfs int,
)
insert into myTable
select 1,12,73
Union
select 1,12,90
Union
select 1,5361,100
Union
select 1,5361,98
union
select 1,15246,100
union
select 1,15246,96
union
select 1,22285,96
union
select 1,22285,97
union
select 2,29,96
union
select 2,29,90
union
select 2,29,104
union
select 2,29,84
union
select 2,29,67
union
select 2,29,49
union
select 2,29,64--查詢
select b.*,a.zf
from (select ryid,pzid, sum(kmfs) as zf from myTable group by ryid,pzid) a ,myTable b
where a.ryid=b.ryid and a.pzid=b.pzid--結果
ryid pzid kmfs zf
1 12 73 163
1 12 90 163
1 5361 98 198
1 5361 100 198
1 15246 96 196
1 15246 100 196
1 22285 96 193
1 22285 97 193
2 29 49 554
2 29 64 554
2 29 67 554
2 29 84 554
2 29 90 554
2 29 96 554
2 29 104 554