如何获取指定的数据？
从表中：
NameTlbdivResult
N-111Pass
N-112
N-113f000
N-114f123
N-121
N-122
N-211Pass
N-212Pass
N-213Pass
N-214Pass
得出结果:
NameTlbResult
N-11Fail
N-12
N-21Pass
规则:
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
(
select Name,Tlb,
COUNT(*) c,
COUNT(case when [Result] = 'pass' then 1 else null end) pass_c,
COUNT(case when [Result] IS not null or
[Result] <> 'pass' then 1 else null end) not_pass_c
from tb
group by name,tlb
)t
order by Name
/*
NameTlbResult
N-11fail
N-12NULL
N-21pass
*/
IF OBJECT_ID('[kiss huang]') is not null DROP TABLE [kiss huang]
go
CREATE TABLE [kiss huang]([Name] VARCHAR(3),[Tlb] INT,[div] INT,[Result] VARCHAR(4))
insert [kiss 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'
SELECT Name,Tlb,CASE WHEN SUM(Result)<0 THEN 'Fail'
WHEN SUM(Result)=0 THEN 'PASS' ELSE '' END
FROM (
SELECT Name,Tlb,CASE WHEN Result NOT IN('Pass','') THEN -100000000
WHEN Result = 'Pass' THEN 0 ELSE 0.1 END Result
FROM [kiss huang]
) T
GROUP BY Name,Tlb
ORDER BY NAME,Tlb
create table #temp([Name] varchar(3),[Tlb] int,[div] int,[Result] varchar(4))
insert #temp
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'
SELECT [Name],[Tlb],
CASE
WHEN EXISTS(SELECT * FROM #temp WHERE [Name]=a.[Name] AND [Tlb]=a.[Tlb] AND NOT (Result IS NULL OR Result='Pass')) THEN 'Fail'
WHEN NOT EXISTS(SELECT * FROM #temp WHERE [Name]=a.[Name] AND [Tlb]=a.[Tlb] AND (Result IS NULL OR Result<>'Pass')) THEN 'Pass'
ELSE ''
END Fail
FROM #temp a
GROUP BY [Name],[Tlb]