求SQL 语句

[fcfd86]

有一个表

ID A B C D
------------------------
1 1 1 true p
2 1 1 False o
3 2 2 true i
4 3 3 False u
... ...

ID列是主键int 类型, C列类型为 bit 非空 默认为 false, A,B,D 为nvarchar(50) 可以为空.

我现在需要数据 A,B,C 并且 将 A,B 相同的组合起来(group by A,B), 组合后, 如果C列 只存在 true 则显示 Y 只存在 False 则显示 N ,有true和false 则显示 P
即, 查询后我想得到的数据是:
A B C1
---------------
1 1 P
2 2 Y
3 3 N
... ...

求SQL 语句

[coleling]

create table tb(id int, A varchar(50), B varchar(50), C bit, D varchar(50))
insert tb
select 1, '1', '1', 1, 'p' union all
select 2, '1', '1', 0, 'o' union all
select 3, '2', '2', 1, 'i' union all
select 4, '3', '3', 0, 'u'

select A,B,c1 = case sum(distinct C*1) when 0 then 'N' else case count(distinct C) when 1 then 'Y' else 'P' end end 
from tb group by A,B

/*
A                                                  B                                                  c1
-------------------------------------------------- -------------------------------------------------- ----
1                                                  1                                                  P
2                                                  2                                                  Y
3                                                  3                                                  N

(3 行受影响)
*/

楼主，记得结贴哦

[chen8410]

declare @tb table(id int,a nvarchar(50),b nvarchar(50),c bit,d nvarchar(50))
insert @tb
select 1 ,'1', '1', 'true' ,'p'
union all select 2 ,'1', '1', 'False' ,'o'
union all select 3 ,'2', '2', 'true' ,'i'
union all select 4 ,'3', '3', 'False' ,'u'

select a,b,case SUM(c*1) when 0 then 'N' when COUNT(1) then 'Y' else 'P' end   AS c1
from @tb
group by a,b

--结果
a	b	c1
1	1	P
2	2	Y
3	3	N

[dawugui]

create table tb(id int, A int, B int, C varchar(10), D varchar(1))
insert tb
select 1, 1, 1, 'true', 'p' union all
select 2, 1, 1, 'False', 'o' union all
select 3, 2, 2, 'true', 'i' union all
select 4, 3, 3, 'False', 'u'

select t.A , t.B , C1 = 'Y' from tb t where not exists(select 1 from tb where a = t.a and b = t.b and c = 'false')
union all
select t.A , t.B , C1 = 'N' from tb t where not exists(select 1 from tb where a = t.a and b = t.b and c = 'true')
union all
select distinct t.A , t.B , C1 = 'P' from tb t where exists(select 1 from tb where a = t.a and b = t.b and c = 'false') and exists(select 1 from tb where a = t.a and b = t.b and c = 'true')
order by a , b

/*
A           B           C1   
----------- ----------- ---- 
1           1           P
2           2           Y
3           3           N

（所影响的行数为 3 行）
*/

drop table tb

[dawugui]

create table tb(id int, A int, B int, C varchar(10), D varchar(1))
insert tb
select 1, 1, 1, 'true', 'p' union all
select 2, 1, 1, 'False', 'o' union all
select 3, 2, 2, 'true', 'i' union all
select 4, 3, 3, 'False', 'u'

select distinct t.A , t.B , C1 = 'Y' from tb t where not exists(select 1 from tb where a = t.a and b = t.b and c = 'false')
union all
select distinct t.A , t.B , C1 = 'N' from tb t where not exists(select 1 from tb where a = t.a and b = t.b and c = 'true')
union all
select distinct t.A , t.B , C1 = 'P' from tb t where exists(select 1 from tb where a = t.a and b = t.b and c = 'false') and exists(select 1 from tb where a = t.a and b = t.b and c = 'true')
order by a , b

/*
A           B           C1   
----------- ----------- ---- 
1           1           P
2           2           Y
3           3           N

（所影响的行数为 3 行）
*/

drop table tb

[dawugui]

应该都加上distinct，我3楼写漏了，4楼补上了。