27,580
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-20 10:55:44
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[工程名称] nvarchar(6))
insert [A]
select 1,N'工程1' union all
select 2,N'工程2'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[工程ID] int,[结果] nvarchar(6))
insert [B]
select 1,1,N'满意' union all
select 2,1,N'满意' union all
select 3,1,N'不满意' union all
select 4,1,N'不满意' union all
select 5,1,N'不满意' union all
select 6,2,N'不满意' union all
select 7,2,N'不满意'
--------------生成数据--------------------------
select a.id,a.[工程名称],CAST(CAST(COUNT(CASE WHEN [结果]=N'满意' THEN 1 ELSE NULL END )*1.0/COUNT(1)*100 AS INT ) AS VARCHAR(2))+'%'满意度,
CAST(CAST(COUNT(CASE WHEN [结果]=N'不满意' THEN 1 ELSE NULL END )*1.0/COUNT(1)*100 AS INT ) AS VARCHAR(3))+'%'不满意度
from [A] INNER JOIN [B] ON a.id=b.[工程ID]
GROUP BY a.id,a.[工程名称]
----------------结果----------------------------
/*
id 工程名称 满意度 不满意度
----------- ------ ---- ----
1 工程1 40% 60%
2 工程2 0% 100%
*/