昨天没人应，今天再问，希望有高手帮忙，谢谢

[li_guang]

select
[SH].* ,
[StaClip] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] ),
[StaPoints] =(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] ),
[StaClip_1] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=1),
[StaPoints_1]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=1),
[StaClip_2] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=2),
[StaPoints_2]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=2),
[StaClip_3] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=3),
[StaPoints_3]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=3),
[StaClip_4] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=4),
[StaPoints_4]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=4),
[StaClip_5] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=5),
[StaPoints_5]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=5),
[StaClip_6] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=6),
[StaPoints_6]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=6),
[StaClip_7] =(select count([ID]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=7),
[StaPoints_7]=(select sum([points]) from [SHT_All] where [ID]=[SH].[ID] and [Type]=7)
from [SHTrans] [SH]

[SHTrans] [SHT_All] 都是视图来的
运行结果:
服务器: 消息 4408，级别 16，状态 1，行 1
查询和其中的视图或函数超过了 256 个表的限制。

我应该怎样去优化，谢谢

[zjcxc]

select
[SH].* ,
[StaClip] =count(b.[ID]),
[StaPoints] =sum(b.[points]),
[StaClip_1] =sum(case when b.[Type]=1 then 1 else 0 end),
[StaPoints_1]=sum(case when b.[Type]=1 then b.[points] else 0 end),
[StaClip_2] =sum(case when b.[Type]=2 then 1 else 0 end),
[StaPoints_2]=sum(case when b.[Type]=2 then b.[points] else 0 end),
[StaClip_3] =sum(case when b.[Type]=3 then 1 else 0 end),
[StaPoints_3]=sum(case when b.[Type]=3 then b.[points] else 0 end),
[StaClip_4] =sum(case when b.[Type]=4 then 1 else 0 end),
[StaPoints_4]=sum(case when b.[Type]=4 then b.[points] else 0 end),
[StaClip_5] =sum(case when b.[Type]=5 then 1 else 0 end),
[StaPoints_5]=sum(case when b.[Type]=5 then b.[points] else 0 end),
[StaClip_6] =sum(case when b.[Type]=6 then 1 else 0 end),
[StaPoints_6]=sum(case when b.[Type]=6 then b.[points] else 0 end),
[StaClip_7] =sum(case when b.[Type]=7 then 1 else 0 end),
[StaPoints_7]=sum(case when b.[Type]=7 then b.[points] else 0 end)
from [SHTrans] [SH]
left join [SHT_All] b on b.[ID]=[SH].[ID]
group by [SH].字段1,[SH].字段2 -----这里写上[SH]的所有字段列表

[laoQ]

晕。。。
学习中…………
帮楼主UP

[AntingZ]

Select a.*,b.[StaClip],b.[StaPoints],b.[StaClip_1],b.[StaPoints_1], b.[StaClip_2],b.[StaPoints_2],b.[StaClip_3],b.[StaPoints_3],b.[StaClip_4],b.[StaPoints_4],b.[StaClip_5],b.[StaPoints_5],b.[StaClip_6],b.[StaPoints_6],b.[StaClip_7],b.[StaPoints_7]
From
[SHTrans] a left join
(
Select [ID],
[StaClip] =sum(c),
[StaPoints] =sum(s),
[StaClip_1] =sum(case when [Type]=1 then c else 0 end) ,
[StaPoints_1]=sum(case when [Type]=1 then s else 0 end) ,
[StaClip_2] =sum(case when [Type]=2 then c else 0 end) ,
[StaPoints_2]=sum(case when [Type]=2 then s else 0 end) ,
[StaClip_3] =sum(case when [Type]=3 then c else 0 end) ,
[StaPoints_3]=sum(case when [Type]=3 then s else 0 end) ,
[StaClip_4] =sum(case when [Type]=4 then c else 0 end) ,
[StaPoints_4]=sum(case when [Type]=4 then s else 0 end) ,
[StaClip_5] =sum(case when [Type]=5 then c else 0 end) ,
[StaPoints_5]=sum(case when [Type]=5 then s else 0 end) ,
[StaClip_6] =sum(case when [Type]=6 then c else 0 end) ,
[StaPoints_6]=sum(case when [Type]=6 then s else 0 end) ,
[StaClip_7] =sum(case when [Type]=7 then c else 0 end) ,
[StaPoints_7]=sum(case when [Type]=7 then s else 0 end)
From
(select [ID],[Type],c=count(*),s=sum([points]) from [SHT_All] group by [ID],[Type]) a
group by a.[ID]
) b
on a.[ID]=b.[ID]

[li_guang]

谢谢你们，结贴