sql 小问题，速度给分！

[Small__Wolf]

declare @datetemp table(classid int)--表变量
insert into @datetemp select distinct(enterprisecolumnclass.classid)
from enterprisecolumnclass,enterprisecolumn
where status=0 and enterpriseid=137 and enterprisecolumn.id=enterprisecolumnclass.enterprisecolumnid

declare @infocount int
set @infocount=0
select @infocount=@infocount+count(ProductTemp.id) from ProductTemp,ProductTemp_class,@datetemp t
where ProductTemp.name<>'' and ProductTemp.name is not null and ProductTemp_class.classid=t.classid and ProductTemp.id=ProductTemp_class.ProductTempid
and datediff(d,getdate(),ProductTemp.createtime)=0

select @infocount

这条sql用@infocount=@infocount+count(ProductTemp.id) 这种方式查询需要2秒左右
直接用infocount=count(ProductTemp.id) 效率就高多了，但是这样满足不了我的逻辑，有什么方式可以把@infocount值累加起来，效率还高？

[sdhdy]

--不太明白你说的。？？
sum(count(ProductTemp.id))  

[dawugui]

请给出表结构，测试数据，具体算法和结果，谢谢。

[arrow_gx]

select @infocount =sum(count(ProductTemp.id)) from ProductTemp,ProductTemp_class,@datetemp t 
where ProductTemp.name <>'' and ProductTemp.name is not null and ProductTemp_class.classid=t.classid and ProductTemp.id=ProductTemp_class.ProductTempid 
and datediff(d,getdate(),ProductTemp.createtime)=0 

select @infocount 

[sdhdy]

count(ProductTemp.id) 不就是表里id 的个数吗？为什么还要@infocount=@infocount+count(ProductTemp.id)？？？

[ChinaJiaBing]

declare @datetemp table(classid int)--表变量 
insert into  @datetemp select distinct(enterprisecolumnclass.classid) 
from enterprisecolumnclass,enterprisecolumn 
where status=0 and enterpriseid=137 and enterprisecolumn.id=enterprisecolumnclass.enterprisecolumnid 

declare @infocount int 
set @infocount=0 
select @infocount=count(ProductTemp.id) from ProductTemp,ProductTemp_class,@datetemp t 
where ProductTemp.name <>'' and ProductTemp.name is not null and ProductTemp_class.classid=t.classid and ProductTemp.id=ProductTemp_class.ProductTempid 
and datediff(d,getdate(),ProductTemp.createtime)=0 
select @infocount

[Small__Wolf]

declare @infocount int
set @infocount=0
select @infocount=@infocount+count(id) from table1
select @infocount=@infocount+count(id) from table2
select @infocount=@infocount+count(id) from table3
select @infocount=@infocount+count(id) from table4
select @infocount=@infocount+count(id) from table5
select @infocount


其实就是统计多个表中的一个数，把这个数加到一个 @infocount中，表可能非常多！

[等不到来世]

再加个变量中转一下。

declare @infocount int,@cnt int
select @infocount=0,@cnt=0
select @cnt=count(id) from table1 
set @infocount=@infocount+@cnt
select @cnt=count(id) from table2
set @infocount=@infocount+@cnt
select @infocount