62,269
社区成员
发帖
与我相关
我的任务
分享select 编号,父编号,停产=(
select isnull(min(停产),'否') from test where 父编号=a.编号 and 类别='系列') ,类别
from test a where 类别='品牌'select 编号,父编号,停产=(
select isnull(min(停产),'是') from test where 父编号=a.编号 and 类别='系列') ,类别
from test a where 类别='品牌'--建立测试环境
set nocount on
create table test(编号 varchar(20),父编号 varchar(20),停产 varchar(20),类别 varchar(20))
insert into test select '311','576','否','品牌'
insert into test select '341','1245','是','系列'
insert into test select '342','326','否','系列'
insert into test select '343','1245','否','系列'
insert into test select '344','1245','否','系列'
insert into test select '345','326','否','系列'
insert into test select '346','1245','否','系列'
insert into test select '347','1245','是','系列'
insert into test select '348','326','否','系列'
insert into test select '349','326','否','系列'
insert into test select '350','2','NULL','厂商'
insert into test select '351','326','否','系列'
insert into test select '352','1248','否','系列'
insert into test select '353','1248','是','系列'
insert into test select '354','1248','否','系列'
insert into test select '355','326','否','系列'
insert into test select '356','1248','否','系列'
insert into test select '357','1248','否','系列'
insert into test select '358','326','是','系列'
insert into test select '359','1248','否','系列'
insert into test select '360','1248','否','系列'
insert into test select '361','1248','是','系列'
insert into test select '362','1248','否','系列'
insert into test select '363','1248','否','系列'
insert into test select '366','1230','否','系列'
insert into test select '367','2','NULL','厂商'
insert into test select '368','2','NULL','厂商'
insert into test select '369','1252','是','系列'
insert into test select '370','1232','否','系列'
insert into test select '371','1252','是','系列'
insert into test select '372','1252','是','系列'
insert into test select '373','1232','否','系列'
insert into test select '374','1252','是','系列'
insert into test select '375','1252','是','系列'
insert into test select '376','1252','是','系列'
insert into test select '377','2','NULL','厂商'
insert into test select '378','2','NULL','厂商'
insert into test select '379','1235','否','系列'
insert into test select '380','1253','是','系列'
insert into test select '381','1253','是','系列'
insert into test select '382','1235','否','系列'
insert into test select '383','1253','是','系列'
insert into test select '384','1235','否','系列'
insert into test select '385','1235','否','系列'
insert into test select '402','2','NULL','厂商'
insert into test select '403','1241','否','系列'
insert into test select '404','1260','否','系列'
insert into test select '405','1257','是','系列'
insert into test select '406','1241','否','系列'
insert into test select '408','1241','否','系列'
insert into test select '409','2','NULL','厂商'
insert into test select '410','1241','否','系列'
insert into test select '411','1261','否','系列'
insert into test select '430','1262','否','系列'
insert into test select '413','1261','否','系列'
insert into test select '414','1261','否','系列'
insert into test select '431','1262','是','系列'
insert into test select '432','2','NULL','厂商'
insert into test select '433','2','NULL','厂商'
insert into test select '434','1231','否','系列'
insert into test select '435','2305','否','系列'
insert into test select '436','2305','否','系列'
insert into test select '437','2305','是','系列'
insert into test select '438','2305','否','系列'
insert into test select '439','2305','是','系列'
insert into test select '440','2305','否','系列'
insert into test select '441','2','NULL','厂商'
insert into test select '442','2','NULL','厂商'
insert into test select '443','1277','否','系列'
insert into test select '444','1277','否','系列'
insert into test select '445','1278','否','系列'
insert into test select '446','1277','否','系列'
insert into test select '447','1277','是','系列'
insert into test select '448','1278','否','系列'
insert into test select '460','1280','是','系列'
insert into test select '461','1288','是','系列'
insert into test select '462','1288','是','系列'
insert into test select '463','1280','是','系列'
insert into test select '464','1288','否','系列'
insert into test select '465','2','NULL','厂商'
insert into test select '466','1288','是','系列'
insert into test select '467','1281','是','系列'
insert into test select '468','1288','否','系列'
insert into test select '469','1281','否','系列'
insert into test select '470','2','NULL','厂商'
insert into test select '471','1289','否','系列'
insert into test select '201','1','NULL','厂商'
insert into test select '206','205','否','品牌'
insert into test select '207','206','否','系列'
insert into test select '245','239','否','品牌'
insert into test select '231','1839','否','系列'
insert into test select '232','1185','否','系列'
insert into test select '246','1837','是','系列'
insert into test select '234','1848','否','系列'
insert into test select '235','1838','否','系列'
insert into test select '247','1313','否','系列'
insert into test select '249','1','NULL','厂商'
insert into test select '250','1849','是','系列'
insert into test select '251','249','否','品牌'
insert into test select '252','249','否','品牌'
insert into test select '253','249','否','品牌'
insert into test select '254','1','NULL','厂商'
insert into test select '255','249','否','品牌'
insert into test select '265','1','NULL','厂商'
insert into test select '266','261','否','品牌'
insert into test select '267','261','否','品牌'
insert into test select '268','261','否','品牌'
insert into test select '269','268','否','系列'
insert into test select '270','1','NULL','厂商'
insert into test select '271','265','否','品牌'
insert into test select '272','270','否','品牌'
insert into test select '273','270','否','品牌'
insert into test select '274','270','否','品牌'
insert into test select '275','270','否','品牌'
insert into test select '277','1','NULL','厂商'
insert into test select '292','1','NULL','厂商'
insert into test select '294','292','否','品牌'
insert into test select '295','1','NULL','厂商'
insert into test select '296','295','否','品牌'
insert into test select '297','1','NULL','厂商'
insert into test select '303','1','NULL','厂商'
insert into test select '304','303','否','品牌'
insert into test select '305','1','NULL','厂商'
insert into test select '306','305','否','品牌'
insert into test select '312','311','是','系列'
insert into test select '313','311','是','系列'
insert into test select '314','311','否','系列'
insert into test select '315','311','是','系列'
insert into test select '316','311','否','系列'
insert into test select '317','311','否','系列'
insert into test select '318','311','否','系列'
insert into test select '319','311','否','系列'
insert into test select '320','311','否','系列'
insert into test select '327','1229','是','系列'
insert into test select '328','326','是','系列'
insert into test select '329','326','是','系列'
insert into test select '330','2','NULL','厂商'
insert into test select '331','1228','否','系列'
insert into test select '332','1228','否','系列'
insert into test select '333','326','否','系列'
insert into test select '334','1228','否','系列'
insert into test select '335','326','否','系列'
insert into test select '336','1228','是','系列'
insert into test select '337','326','否','系列'
insert into test select '364','1248','是','系列'
insert into test select '365','2','NULL','厂商'
insert into test select '449','2','NULL','厂商'
insert into test select '472','1289','否','系列'
insert into test select '473','1289','是','系列'
insert into test select '480','1282','否','系列'
go
--测试
select 编号,父编号,停产=
case when(
select count(*) from test where 父编号=a.编号 and 类别='系列' and 停产='否'
) >0 then '否' else '是' end ,类别
from test a where 类别='品牌'
--删除测试环境
drop table test
set nocount off
/*编号 父编号 停产 类别
-------------------- -------------------- ---- --------------------
311 576 否 品牌
206 205 否 品牌
245 239 是 品牌
251 249 是 品牌
252 249 是 品牌
253 249 是 品牌
255 249 是 品牌
....
*/