DECLARE @a table(cateid INT,NAME VARCHAR(10),attribute int)
INSERT @a select 1 ,'全项目',null
union all select 1 ,'11', 0
union all select 1 ,'12', 1
union all select 1 ,'13', 2
union all select 1 ,'14', 12
union all select 2 ,'全项目',null
union all select 2 ,'21', 0
union all select 2 ,'22', 1
union all select 2 ,'23', 2
union all select 3 ,'全项目',null
union all select 3 ,'31', 0
union all select 3 ,'32', 1
union all select 3 ,'33', 12
union all select 4 ,'全项目',null
union all select 4 ,'41', 0
union all select 4 ,'42', 1
union all select 4 ,'43', 0
SELECT *,at=CASE WHEN NOT EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute<>0) THEN 0
WHEN EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=1) AND EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=2)
OR EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=12) THEN 12
WHEN EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=1) AND NOT EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=2)
AND NOT EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=12) THEN 1
WHEN EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=2) AND NOT EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=1)
AND NOT EXISTS(SELECT 1 FROM @a WHERE cateid=a.cateid AND attribute=12) THEN 2
END
FROM @a a
--result
/*cateid NAME attribute at
----------- ---------- ----------- -----------
1 全项目 NULL 12
1 11 0 12
1 12 1 12
1 13 2 12
1 14 12 12
2 全项目 NULL 12
2 21 0 12
2 22 1 12
2 23 2 12
3 全项目 NULL 12
3 31 0 12
3 32 1 12
3 33 12 12
4 全项目 NULL 1
4 41 0 1
4 42 1 1
4 43 0 1
（所影响的行数为 17 行）
*/
--> 测试数据: [项目表]
if object_id('[项目表]') is not null drop table [项目表]
go
create table [项目表] (cateid int,name varchar(6),attribute int)
insert into [项目表]
select 1,'全项目',null union all
select 1,'11',0 union all
select 1,'12',1 union all
select 1,'13',2 union all
select 1,'14',12 union all
select 2,'全项目',null union all
select 2,'21',0 union all
select 2,'22',1 union all
select 2,'23',2 union all
select 3,'全项目',null union all
select 3,'31',0 union all
select 3,'32',1 union all
select 3,'33',12
update [项目表]
set attribute=case when ( not exists(select * from [项目表] where attribute!=0 and cateid=t.cateid) ) then 0
when ( exists(select * from [项目表] where attribute=1 and cateid=t.cateid)) and
( not exists(select * from [项目表] where attribute=2 and cateid=t.cateid)) and
( not exists(select * from [项目表] where attribute=12 and cateid=t.cateid)) then 1
when ( exists(select * from [项目表] where attribute=2 and cateid=t.cateid)) and
( not exists(select * from [项目表] where attribute=1 and cateid=t.cateid)) and
( not exists(select * from [项目表] where attribute=12 and cateid=t.cateid)) then 2
else 12
end
from [项目表] t
where attribute is null
------------------------------------
select * from [项目表]
cateid name attribute
----------- ------ -----------
1 全项目 12
1 11 0
1 12 1
1 13 2
1 14 12
2 全项目 12
2 21 0
2 22 1
2 23 2
3 全项目 12
3 31 0
3 32 1
3 33 12
(13 行受影响)
update tb
set attribute = (case
when not exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute<>0)
then 0
when exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute=1)
and exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute=2)
or exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute=12)
then 12
when exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute=1)
and not exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute in (2,12))
then 1
when exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute=2)
and not exists (select * from tb where t.cateid=cateid and name<>'全项目' and attribute in (1,12))
then 2
end)
from tb t
where name='全项目';
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(cateid int, name varchar(8), attribute int)
insert into #
select 1, '全项目', null union all
select 1, '11', 0 union all
select 1, '12', 1 union all
select 1, '13', 2 union all
select 1, '14', 12 union all
select 2, '全项目', null union all
select 2, '21', 0 union all
select 2, '22', 1 union all
select 2, '23', 2 union all
select 3, '全项目', null union all
select 3, '31', 0 union all
select 3, '32', 1 union all
select 3, '33', 12
--> 如果attribute只是0、1、2、12这几个值的话，不用那么复杂
update # set attribute =
case (select sum(attribute) from # where cateid=t.cateid and name <> '全项目')
when 0 then 0
when 1 then 1
when 2 then 2
else 12
end
from # as t where name = '全项目'
select * from #
/*
cateid name attribute
----------- -------- -----------
1 全项目 12
1 11 0
1 12 1
1 13 2
1 14 12
2 全项目 12
2 21 0
2 22 1
2 23 2
3 全项目 12
3 31 0
3 32 1
3 33 12
*/
--> 测试数据：项目表
if object_id('项目表') is not null
drop table 项目表
---->建表
create table 项目表([cateid] int,[name] varchar(6),[attribute] int)
insert 项目表
select 1,'全项目',null union all
select 1,'11','0' union all
select 1,'12','1' union all
select 1,'13','2' union all
select 1,'14','12' union all
select 2,'全项目',null union all
select 2,'21','0' union all
select 2,'22','1' union all
select 2,'23','2' union all
select 3,'全项目',null union all
select 3,'31','0' union all
select 3,'32','1' union all
select 3,'33','12'
--> 查询结果
update 项目表
set attribute=n.attribute
from 项目表 m ,(select t.cateid,t.name,attribute=(select
case when max(attribute) = 0 then 0
when max(attribute) = 1 then 1
when max(attribute) = 2 and min(attribute) =2 then 2
when max(attribute) = 2 and min(attribute) =0 then 12
when max(attribute) = 2 and min(attribute) =1 then 12
when max(attribute) = 12 then 12 end
from 项目表 where cateid =t.cateid
group by cateid
) FROM 项目表 t)n
where m.cateid=n.cateid
and m.name ='全项目'
select * from 项目表
--> 删除表格
--DROP TABLE 项目表
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(cateid int, name varchar(8), attribute int)
insert into #
select 1, '全项目', null union all
select 1, '11', 0 union all
select 1, '12', 1 union all
select 1, '13', 2 union all
select 1, '14', 12 union all
select 2, '全项目', null union all
select 2, '21', 0 union all
select 2, '22', 1 union all
select 2, '23', 2 union all
select 3, '全项目', null union all
select 3, '31', 0 union all
select 3, '32', 1 union all
select 3, '33', 12
update # set attribute =
case (select sum(distinct attribute) from # where cateid=t.cateid and name <> '全项目')
when 0 then 0
when 1 then 1
when 2 then 2
else 12
end
from # as t where name = '全项目'
select * from #
/*
cateid name attribute
----------- -------- -----------
1 全项目 12
1 11 0
1 12 1
1 13 2
1 14 12
2 全项目 12
2 21 0
2 22 1
2 23 2
3 全项目 12
3 31 0
3 32 1
3 33 12
*/
--
if object_id ('项目表') is not null
drop table 项目表
go
create table 项目表 (cateid int,name nvarchar(10),attribute int)
insert into 项目表 select 1,'全项目',null
union all select 1,'11',0
union all select 1,'12',1
union all select 1,'13',2
union all select 1,'14',12
union all select 2,'全项目',null
union all select 2,'21',0
union all select 2,'22',1
union all select 2,'23',2
union all select 3,'全项目',null
union all select 3,'31',0
union all select 3,'32',1
union all select 3,'33',12
update 项目表 set attribute=case when name='00' then 0
when (charindex('1',name)>0 and charindex('2',name)>0)
then 12
when (charindex('1',name)>0 and (charindex('2',name)<0 or charindex('12',name)<0))
then 1
when (charindex('2',name)>0 and (charindex('1',name)<0 or charindex('12',name)<0))
then 2 else attribute end
select * from 项目表
