Create Table #Test(FID int, FNumber varchar(200),FBillNo varchar(200),FAmount decimal(18,4))
insert into #Test(FID,FNumber,FBillNo)
select distinct t1.FID,t1.FCustomer,t2.FExplanation
from A t1 left join B t2 on t1.FContactID=t2.FID
where t1.FType=4
update t1 set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) from #Test t1
select * from #Test
drop table #Test
错误提示:列 't1.FID' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
我不太明白,请高手赐教!个人愚见UPDATE语句中无需GROUP BY子句。
insert into #Test(FID,FNumber,FBillNo)
select distinct t1.FID,t1.FCustomer,t2.FExplanation
from A t1 left join B t2 on t1.FContactID=t2.FID
where t1.FType=4
update t1 set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) from #Test t1
select * from #Test
drop table #Test
错误提示:列 't1.FID' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
我不太明白,请高手赐教!个人愚见UPDATE语句中无需GROUP BY子句。
select
distinct t1.FID,--列 't1.FID' 在选择列表中无效
t1.FCustomer,t2.FExplanation
from
A t1
left join
B t2 on t1.FContactID=t2.FID
where
t1.FType=4
go
Create Table #Test(FID int, FNumber varchar(200),FBillNo varchar(200),FAmount decimal(18,4))
go
insert into #Test(FID,FNumber,FBillNo)
select distinct t1.FID,t1.FCustomer,t2.FExplanation
from A t1 left join B t2 on t1.FContactID=t2.FID
where t1.FType=4
update t1
set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID )
from #Test t1
select * from #Test
drop table #Test
select distinct t1.FID,t1.FCustomer,t2.FExplanation
from A t1 left join B t2 on t1.FContactID=t2.FID
where t1.FType=4 两个不 一样
insert into #Test(FID,FNumber,FBillNo) 这条能用? 这就有问题 FID应该插入 数字 ,FNumber 插入字符串
要取的就是表T1中的FID字段
而表t1和t2是通过t1.FContactID=t2.FID关联的
提示有错的是UPDATE语句中的t1.FID啊
--恩。估计跟二楼说的一样
select distinct t1.FID,t1.FCustomer,t2.FExplanation
from A t1 left join B t2 on t1.FContactID=t2.FID
where t1.FType=4 看A表中是否有FID列
PS:3楼的什么意思?写的代码和我的没什么区别啊
insert into #Test(FID,FNumber,FBillNo)
select distinct t1.FID,t1.FCustomer,t2.FExplanation
from A t1 left join B t2 on t1.FContactID=t2.FID
where t1.FType=4
/* 第2句 */ update t1 set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) from #Test t1
/* 第3句 */ select * from #Test
/* 第4句 */ drop table #Test -- 第2句 语法错误:
/* 第2句 */ update #Test t1 set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) -- from #Test t1
提示错误在UPDATE语句中的FID
/* 第2句 */ update #Test t1 set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) -- from #Test t110楼的朋友,你写的这个语句也有语法错误
我刚才测试过了
--try
update #Test set FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) from #Test t1
主要是那个insert 列名有点不对劲
我把UPDATE语句去掉,完全没有错误啊
说了错误在UPDATE语句上
--try
update #Test set FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID) from #Test t1
Create Table #Test(FID int, FNumber varchar(200),FBillNo varchar(200),FAmount decimal(18,4))
create table a (ftype int,fid int,famount int)
goupdate t1
set t1.FAmount=(select sum(FAmount) from A where FType=6 and FID=t1.FID)
from #Test t1 drop table #test,a/*
(所影响的行数为 0 行)
*/
(select sum(FAmount) from A where FType=6 and FID=t1.FID)這里,
這一句你調用了sum聚合函數,而且後面你又用了FID=t1.FID這一條件,而
t1.FID的值并不是唯一的,所以建議在
select sum(FAmount) from A where FType=6 and FID=t1.FID group by FID,
沒有試過不知道對不對.