27,579
社区成员
发帖
与我相关
我的任务
分享
select distinct A, B= (CASE WHEN EXISTS(select 1 FROM tb E where E.A = F.A and E.B = 'Y') THEN 'Y' ELSE 'N' END)
, C = (CASE WHEN EXISTS(select 1 FROM tb E where E.A = F.A and E.C = 'Y') THEN 'Y' ELSE 'N' END)
, D = (CASE WHEN EXISTS(select 1 FROM tb E where E.A = F.A and E.D = 'Y') THEN 'Y' ELSE 'N' END)
FROM tb F
DECLARE @TB TABLE (A VARCHAR(10),B VARCHAR(10),C VARCHAR(10),D VARCHAR(10))
INSERT INTO @TB SELECT 'abc','Y','N','N'
UNION ALL SELECT 'abc', 'N', 'N' , 'N'
UNION ALL SELECT 'abc', 'N', 'Y' , 'N'
SELECT A,B=MAX(B),C=MAX(C),D=MAX(D) FROM @TB GROUP BY A
/*
A B C D
---------- ---------- ---------- ----------
abc Y Y N
(所影响的行数为 1 行)
*/
------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-08-08 15:16:23
------------------------------------
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(字段A nvarchar(3),字段B nvarchar(1),字段C nvarchar(1),字段D nvarchar(1))
Go
Insert into ta
select 'abc','Y','N','N' union all
select 'abc','N','N','N' union all
select 'abc','N','Y','N'
Go
--Start
select 字段A,max( 字段B)字段B,max( 字段C) 字段c,max( 字段D ) 字段d
from ta
group by 字段A
--Result:
/*
字段A 字段B 字段c 字段d
---- ---- ---- ----
abc Y Y N
(所影响的行数为 1 行)
*/
--End