求一SQL更新语句，各位帮忙~~~~~~~~~~~

[lishery]

项目表(cateid,name,attribute)

cateid name attribute
1 全项目 <null>
1 11 0
1 12 1
1 13 2
1 14 12
2 全项目 <null>
2 21 0
2 22 1
2 23 2
3 全项目 <null>
3 31 0
3 32 1
3 33 12

项目表中，每个cateid都有一条name为“全项目”的记录，现在想更新其 attribute属性，
根据数据库中相同cateid的其他记录的attribute值来确定：
若全为0，则其值为0；
若存在1和2，或者12，则其值为12；
若存在1，不存在2和12，则其值为1；
若存在2，不存在1和12，则其值为2。

[chuifengde]

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 行受影响)

[xman_78tom]

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='全项目';

[SQLCenter]

--> 测试数据：#
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
*/

[SQLCenter]

[Quote=引用 4 楼 sqlcenter 的回复:]

SQL code
--> 测试数据：#
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 u……
[/Quote]

我短路了，忽略这个回复。

[王向飞]

--> 测试数据：项目表
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 项目表

[lishery]

多谢各位

[SQLCenter]

[Quote=引用 5 楼 sqlcenter 的回复:]

[/Quote]

没完全短路，sum(distinct attribute) 没问题。

--> 测试数据：#
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
*/

[ChinaJiaBing]

--


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 项目表