求一SQL，在线等

[leotanyang]

field1,field2
A ,NULL
A ,FS
A ,US
B ,NULL
C ,NULL
D ,NULL
D ,FS
G ,US
G ,NULL
H ,PS

结果
A, PS
B, NULL
C, NULL
D, FS
G, US
H, PS
如果field1同一A中有不同的FS,US的话就要写入PS，急要在线等

[hyqwan11112]

[Quote=引用 7 楼 zhou968 的回复:]
SQL codedeclare@Ttable(f1nvarchar(1),f2nvarchar(2))Insert@TselectN'A',nullunionallselectN'A',N'FS'unionallselectN'A',N'US'unionallselectN'B',nullunionallselectN'C',nullunionallselectN'D',nullunionallselectN'D',N'FS'unionallselectN'G',N'US'unionallselectN'G',nullunionallselectN'H',N'PS'selectf1,casewhenminf2<>max_f2then'PS'elseminf2endfrom(selectf1,min(f2)asminf2,max(f2)asmax_f2from@Tgroupbyf1
)…
[/Quote]

[leotanyang]

没一个是对的

[leotanyang]

field1,field2
A ,NULL
A ,FS
A ,US
B ,''
C ,''
D ,''
D ,FS
G ,US
G ,''
H ,PS

结果
A, PS
B, NULL
C, NULL
D, FS
G, US
H, PS
如果field1同一A中有不同的FS,US的话就要写入PS，急要在线等

[leotanyang]

declare @T table([field1] nvarchar(1),[field2] nvarchar(2))
Insert @T
select N'A','' union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B','' union all
select N'C','' union all
select N'D','' union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G','' union all
select N'H',N'PS' union all
select N'H',''
select [field1],count(distinct isnull([field2],''))con from @T group by [field1]
Select
t.[field1],[field2]=case when t2.con>1 then 'PS' else max(t.[field2]) end
from
@T t
left join
(select [field1],count(distinct [field2])con from @T group by [field1]) t2 on t.[field1]=t2.[field1]
group by t.[field1],t2.con
order by t.[field1]

要是我将null变成为空就结果不对了
－－－－－－－－－－－－－－－

[中国风]

declare @T table([field1] nvarchar(1),[field2] nvarchar(2))
Insert @T
select N'A',null union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B','' union all
select N'C','' union all
select N'D','' union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G','' union all
select N'H',N'PS'


select [field1],
		case when count(distinct nullif(isnull([field2],''),''))>1 then 'PS' else max([field2]) end as [field2]
from @T 
group by  [field1]



field1 field2
------ ------
A      PS
B      
C      
D      FS
G      US
H      PS

[npkaida]

declare @T table([field1] nvarchar(1),[field2] nvarchar(2))
Insert @T
select N'A',null union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B',null union all
select N'C',null union all
select N'D',null union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G',null union all
select N'H',N'PS'

select [field1], 
 case when sum(case when [field2]='FS' then 1 when [field2]='US' then 2 when [field2]='PS' then 3 else 0 end) = 0 then null
      when sum(case when [field2]='FS' then 1 when [field2]='US' then 2 when [field2]='PS' then 3 else 0 end) = 1 then 'FS'
      when sum(case when [field2]='FS' then 1 when [field2]='US' then 2 when [field2]='PS' then 3 else 0 end) = 2 then 'US'
      else 'PS' end [field1]
 from @t
group by [field1]
/*
field1  field2
A	PS
B	NULL
C	NULL
D	FS
G	US
H	PS
*/

[sdxiong]

select f1,case when minf2<>maxf2 then 'PS' when minf2 is null then maxf2 else minf2 end
from (
select f1,min(f2) as minf2,max(f2) as max_f2
from test
group by f1
) a

[playwarcraft]

select field1, 
       case when field2='US' and exists(select 1 from test where field1=A.field1 and field2='PS')
       then 'PS' else field2 end as field2
from 
(
select field1,max(field2) as field2
from test
group by field1) A

[playwarcraft]

select field1, 
       case when field2='US' and exists(select 1 from test where field1=A.field1 and field2='FS')
       then 'PS' else field2 end as field2
from 
(
select field1,max(field2) as field2
from test
group by field1) A

[中国风]

declare @T table([field1] nvarchar(1),[field2] nvarchar(2))
Insert @T
select N'A',null union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B',null union all
select N'C',null union all
select N'D',null union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G',null union all
select N'H',N'PS'


Select 
	t.[field1],[field2]=case when t2.con>1 then 'PS' else max(t.[field2]) end
from 
	@T t
left join
	(select [field1],count(distinct [field2])con from @T group by [field1]) t2 on t.[field1]=t2.[field1]
group by t.[field1],t2.con
order by t.[field1]


field1 field2
------ ------
A      PS
B      NULL
C      NULL
D      FS
G      US
H      PS


(6 個資料列受到影響)

[zhou968]

select f1,case when minf2 <>maxf2 then 'PS' else minf2 end 
from ( 
      select f1,min(f2) as minf2,max(f2) as max_f2 
      from test 
      group by f1 
) a

[中国风]

declare @T table([field1] nvarchar(1),[field2] nvarchar(2))
Insert @T
select N'A',null union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B',null union all
select N'C',null union all
select N'D',null union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G',null union all
select N'H',N'PS'


select [field1],
case when count(distinct [field2])>1 then 'PS' else max([field2]) end as [field2]
from @T group by  [field1]


(10 個資料列受到影響)
field1 field2
------ ------
A      PS
B      NULL
C      NULL
D      FS
G      US
H      PS


(6 個資料列受到影響)

[zhou968]

declare @T table(f1 nvarchar(1),f2 nvarchar(2))
Insert @T
select N'A',null union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B',null union all
select N'C',null union all
select N'D',null union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G',null union all
select N'H',N'PS'

select f1,case when minf2 <>max_f2 then 'PS' else minf2 end 
from ( 
      select f1,min(f2) as minf2,max(f2) as max_f2 
      from @T
      group by f1 
) a

/*
A	PS
B	NULL
C	NULL
D	FS
G	US
H	PS
*/

[水族杰纶]

[Quote=引用 7 楼 zhou968 的回复:]
SQL code
declare @T table(f1 nvarchar(1),f2 nvarchar(2))
Insert @T
select N'A',null union all
select N'A',N'FS' union all
select N'A',N'US' union all
select N'B',null union all
select N'C',null union all
select N'D',null union all
select N'D',N'FS' union all
select N'G',N'US' union all
select N'G',null union all
select N'H',N'PS'

select f1,case when minf2 <>max_f2 then 'PS' else m…
[/Quote]