求一个分组判断的sql

Question

测试数据 （sql2000）
create table  test1
(
  ta001 varchar(20),
  ta002 varchar(20),
  ta003 varchar(20),
  ta004 varchar(60),
  ta005 varchar(60),
  ta006 char(2)
)insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00101','IC','130R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00102','IC','132R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00103','PCBA','26VE','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00201','IC','130R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00202','IC','132R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00203','PCBA','25VE','N')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00204','IC','136R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00205','IC','133R','N')
........要求结果
    ta001       ta002        ta003       ta004       ta005       
  11010601      F001        F00101        IC         130R 
  11010601      F001        F00102        IC         132R  
  11010601      F001        F00103       PCBA        26VE
            11010601状态为    Y
  11010602      F002        F00201        IC         130R
  11010602      F002        F00201        IC         132R
  11010602      F002        F00201        PCBA       25VE
  11010602      F002        F00201        IC         136R
  11010602      F002        F00201        IC         133R
            11010602状态为    N 
     .
     .
     .ta006的值全为Y的才判定那个单号的状态为Y，只要有一个为N则单号的状态为N
请帮忙写一段sql

Answer 1

create table  test1
(
  ta001 varchar(20),
  ta002 varchar(20),
  ta003 varchar(20),
  ta004 varchar(60),
  ta005 varchar(60),
  ta006 char(2)
)insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00101','IC','130R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00102','IC','132R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00103','PCBA','26VE','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00201','IC','130R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00202','IC','132R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00203','PCBA','25VE','N')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00204','IC','136R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00205','IC','133R','N')
go
select ta001,ta002,ta003,ta004,ta005 from test1
union all
select distinct ta001,(case when not exists(select 1 from test1 where ta001=a.ta001 and ta006='N') then 'Y' else 'N' end),'','','' from test1 a
 order by ta001,ta002
go
drop table test1
/*
ta001                ta002                ta003                ta004                                                        ta005
-------------------- -------------------- -------------------- ------------------------------------------------------------ ------------------------------------------------------------
11010601             F001                 F00101               IC                                                           130R
11010601             F001                 F00102               IC                                                           132R
11010601             F001                 F00103               PCBA                                                         26VE
11010601             Y                                                                                                      
11010602             F002                 F00201               IC                                                           130R
11010602             F002                 F00202               IC                                                           132R
11010602             F002                 F00203               PCBA                                                         25VE
11010602             F002                 F00204               IC                                                           136R
11010602             F002                 F00205               IC                                                           133R
11010602             N                                                                                                      (10 行受影响)*/

Answer 2

create table  test1
(
  ta001 varchar(20),
  ta002 varchar(20),
  ta003 varchar(20),
  ta004 varchar(60),
  ta005 varchar(60),
  ta006 char(2)
)insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00101','IC','130R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00102','IC','132R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00103','PCBA','26VE','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00201','IC','130R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00202','IC','132R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00203','PCBA','25VE','N')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00204','IC','136R','Y')
insert into  test1(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00205','IC','133R','N')
go
select ta001,ta002,ta003,ta004,ta005 from test1
union all
select distinct ta001,(case when not exists(select 1 from test1 where ta001=a.ta001 and ta006='N') then '状态为Y' else '状态为N' end),'','','' from test1 a
 order by ta001,ta002
go
drop table test1
/*
ta001                ta002                ta003                ta004                                                        ta005
-------------------- -------------------- -------------------- ------------------------------------------------------------ ------------------------------------------------------------
11010601             F001                 F00101               IC                                                           130R
11010601             F001                 F00102               IC                                                           132R
11010601             F001                 F00103               PCBA                                                         26VE
11010601             状态为Y                                                                                                   
11010602             F002                 F00201               IC                                                           130R
11010602             F002                 F00202               IC                                                           132R
11010602             F002                 F00203               PCBA                                                         25VE
11010602             F002                 F00204               IC                                                           136R
11010602             F002                 F00205               IC                                                           133R
11010602             状态为N                                                                                                   (10 行受影响)*/

Answer 3

select
 ta001,ta002,ta003,ta004,ta005 
from
 test1
union all
select
 distinct ta001,(case when not exists(select 1 from test1 where ta001=t.ta001 and ta006='N') then '状态为Y' else '状态为N' end),'','','' from test1 t 
order by
 ta001,ta002

Answer 4

create table  #temp
(
  ta001 varchar(20),
  ta002 varchar(20),
  ta003 varchar(20),
  ta004 varchar(60),
  ta005 varchar(60),
  ta006 char(2)
)insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00101','IC','130R','Y')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00102','IC','132R','Y')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010601','F001','F00103','PCBA','26VE','Y')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00201','IC','130R','Y')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00202','IC','132R','Y')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00203','PCBA','25VE','N')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00204','IC','136R','Y')
insert into  #temp(ta001,ta002,ta003,ta004,ta005,ta006) values('11010602','F002','F00205','IC','133R','N')
go
select ta001,ta002,ta003,ta004,ta005 from #temp
union all
select ta001,case when sum(case when ta006 = 'N' then 1 else 0 end) > 0 then '状态为N' else '状态为Y' end ,'','','' from #temp a
group by ta001
 order by ta001,ta002
go
drop table #temp
/*
11010601 F001 F00101 IC 130R
11010601 F001 F00102 IC 132R
11010601 F001 F00103 PCBA 26VE
11010601 状态为Y
11010602 F002 F00201 IC 130R
11010602 F002 F00202 IC 132R
11010602 F002 F00203 PCBA 25VE
11010602 F002 F00204 IC 136R
11010602 F002 F00205 IC 133R
11010602 状态为N
*/