两张表合并统计

两张表:一张存储设备编号、状态；另一张存储设备编号、所属工地。要求：统计出每个工地设备在线和离线的数目

USE [GPSClient]
GO
/****** 对象:  StoredProcedure [dbo].[StatisticsContruction]    脚本日期: 01/13/2016 10:13:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<主页使用,获取全部工地,及在线离线设备数目>
-- =============================================
ALTER PROCEDURE [dbo].[StatisticsContruction]
	-- Add the parameters for the stored procedure here
    @CustomID varchar(10),
    @recCount int=0 output	
    
AS
BEGIN
-------------------------------------------------------------------------

with temp1
as 
(
	select ##realtimedata.clientserial ,##realtimedata.clientStutas,construction 
	from ##realtimedata 
	full join server_synchro_info on 
           ##realtimedata.clientserial = server_synchro_info.clientserial
	where ##realtimedata.defaultTag2='200023' 
	group by construction,construction,##realtimedata.clientStutas,
                 ##realtimedata.clientserial
)

select 
isNULL(construction,'未指定工地砂浆罐') as  'construction'
,
count(
   case clientStutas
   when 1 then '在线'
   end
)as 'OnLineCount'
,
count(
   case clientStutas
   when 0 then '离线'
   end
)as 'OffLineCount'
from temp1
group by construction

set @Reccount = @@ROWCOUNT 

END

测试方法：
exec statisticsContruction '200023'