日期:2014-05-18 浏览次数:20478 次
select a.name,sum(case when b.num = 1 then 1 when b.num = 2 then 3 else 0 end) + sum(case when b.Point= 1 then 1 when b.Point= 2 then 3 else 0 end) + sum(1) as 得分 from unit a,info b where b.unitid = a.id group by a.name
------解决方案--------------------
select a.name, sum(case when b.num = 1 then 1 when b.num = 2 then 3 else 0 end) + sum(case when b.Point= 1 then 1 when b.Point= 2 then 3 else 0 end) + sum(case when b.unitid is not null then 1 else 0 end) as 得分 from unit a left join info b on b.unitid = a.id group by a.name
------解决方案--------------------
use Tempdb go --> --> if not object_id(N'Tempdb..#Unit') is null drop table #Unit Go Create table #Unit([ID] int,[Name] nvarchar(50)) Insert #Unit select 1,N'中石油' union all select 2,N'中移动' union all select 3,N'电信' Go if not object_id(N'Tempdb..#Info') is null drop table #Info Go Create table #Info([ID] int,[UnitID] int,[Num] int,[Point] int) Insert #Info select 1,1,1,2 union all select 2,1,2,1 union all select 3,2,1,1 union all select 4,3,2,2 Go SELECT b.[Name], 得分=SUM(CASE WHEN a.[Num]=1 THEN 1 WHEN a.[Num]=2 THEN 3 ELSE 0 END+CASE WHEN a.[Point]=1 THEN 1 WHEN a.[Point]=2 THEN 3 ELSE 0 END)+COUNT(a.[UnitID]) FROM #Info AS a RIGHT JOIN #Unit AS b ON b.ID=a.[UnitID] GROUP BY b.[Name]
------解决方案--------------------
select t1.Uname Uname,sum((case when t2.Num=1 then 1 else 3)+count(t2.id)+ sum(case when t2.Point=1 then 1 else 2)) '得分' from Unit t1 inner join Info t2 on t1.id=t2.UnitID group by t1.Uname
------解决方案--------------------
select t1.Uname Uname,sum((case when t2.Num=1 then 1 else 3 end)+count(t2.id)+ sum(case when t2.Point=1 then 1 else 2 end)) '得分' from Unit t1 inner join Info t2 on t1.id=t2.UnitID group by t1.Uname
------解决方案--------------------
我也不知道