两张表合并统计

来源：互联网发布：分布式 java 编辑：程序博客网时间：2024/05/17 01:31

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

USE [GPSClient]GO/****** 对象:  StoredProcedure [dbo].[StatisticsContruction]    脚本日期: 01/13/2016 10:13:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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    ASBEGIN-------------------------------------------------------------------------with temp1as (select ##realtimedata.clientserial ,##realtimedata.clientStutas,construction from ##realtimedata full join server_synchro_info on            ##realtimedata.clientserial = server_synchro_info.clientserialwhere ##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 temp1group by constructionset @Reccount = @@ROWCOUNT END测试方法：exec statisticsContruction '200023'

0 0