从表中：
Name Tlb div Result
N-1 1 1 Pass
N-1 1 2
N-1 1 3 f000
N-1 1 4 f123
N-1 2 1
N-1 2 2
N-2 1 1 Pass
N-2 1 2 Pass
N-2 1 3 Pass
N-2 1 4 Pass
得出结果:
Name Tlb Result
N-1 1 Fail
N-1 2
N-2 1 Pass
规则:
Name和Tlb是唯一行，Result的结果从表中得到:
同一Name，Tlb中如果Result
1.含有除空值和‘Pass’值外的值，则为‘Fail'
2.全部为"Pass"，则为'Pass'
3.否则为空
这个过程要怎么写？
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-21 11:26:43
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Name] varchar(3),[Tlb] int,[div] int,[Result] varchar(4))
insert [huang]
select 'N-1',1,1,'Pass' union all
select 'N-1',1,2,null union all
select 'N-1',1,3,'f000' union all
select 'N-1',1,4,'f123' union all
select 'N-1',2,1,null union all
select 'N-1',2,2,null union all
select 'N-2',1,1,'Pass' union all
select 'N-2',1,2,'Pass' union all
select 'N-2',1,3,'Pass' union all
select 'N-2',1,4,'Pass'
--------------开始查询--------------------------
;WITH cte AS (
select DISTINCT name,tlb,'pass'result
from [huang] a
WHERE EXISTS (SELECT 1 FROM huang b WHERE a.NAME=b.NAME AND a.tlb=b.tlb AND b.result='pass')
AND NOT exists (SELECT 1 FROM huang b WHERE a.NAME=b.NAME AND a.tlb=b.tlb AND b.result<>'pass')
UNION ALL
select DISTINCT name,tlb,NULL result
from [huang] a
WHERE EXISTS (SELECT 1 FROM huang b WHERE a.NAME=b.NAME AND a.tlb=b.tlb AND b.result IS NULL )
AND NOT exists (SELECT 1 FROM huang b WHERE a.NAME=b.NAME AND a.tlb=b.tlb AND b.result IS NOT NULL ))
SELECT * FROM cte
UNION ALL
SELECT DISTINCT name,tlb,'Fail'
FROM huang a
WHERE NOT EXISTS (SELECT 1 FROM cte b WHERE a.NAME=b.NAME AND a.tlb=b.tlb)
----------------结果----------------------------
/*
name tlb result
---- ----------- ------
N-2 1 pass
N-1 2 NULL
N-1 1 Fail
*/
------解决方案--------------------
是这样吗：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Name] varchar(3),[Tlb] int,[div] int,[Result] varchar(4))
insert [tb]
select 'N-1',1,1,'Pass' union all
select 'N-1',1,2,null union all
select 'N-1',1,3,'f000' union all
select 'N-1',1,4,'f123' union all
select 'N-1',2,1,null union all
select 'N-1',2,2,null union all
select 'N-2',1,1,'Pass' union all
select 'N-2',1,2,'Pass' union all
select 'N-2',1,3,'Pass' union all
select 'N-2',1,4,'Pass'
go
select Name,Tlb,
case when c = pass_c then 'pass'
when not_pass_c > 0 then 'fail'
else null
end Result
from