表1
字段1:id int identity(1,1) not null
字段2:isdisplay bit not null
......表2
字段1:id int not null
字段2:state tinyint not null
......一对多 关系update 表1 set isdisplay = 表2.id = 表1.ID 如记录都 state = 3 ,isdisplay = 1;否则 isdisplay =0;
字段1:id int identity(1,1) not null
字段2:isdisplay bit not null
......表2
字段1:id int not null
字段2:state tinyint not null
......一对多 关系update 表1 set isdisplay = 表2.id = 表1.ID 如记录都 state = 3 ,isdisplay = 1;否则 isdisplay =0;
update 表1
set isdisplay = case when 表2.state = 3 then 1 else 0 end
from 表1,表2
where 表2.id = 表1.ID
update a
set a.isdisplay=
case
when
not exists(select 1 from tb where id=b.id and state!=3)
then 1
else 0
end
from ta a,tb b
where a.id=b.id
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TA
(
ID INT IDENTITY,
isdisplay INT
)
INSERT INTO TA
SELECT 0 UNION ALL
SELECT 0 UNION ALL
SELECT 0 CREATE TABLE TB
(
ID INT,
STATE INT
)INSERT INTO TB
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 3,3update a
set a.isdisplay=
case
when
not exists(select 1 from tb b where a.id=b.id and state<>3)
then 1
else 0
end
from ta aSELECT * FROM TA
ID isdisplay
-- ---------
1 0
2 0
3 1
CREATE TABLE 表1(id INT,isdisplay bit)
CREATE TABLE 表2(id INT,state TINYINT)INSERT 表1 SELECT 1,NULL UNION ALL
SELECT 2,NULL UNION ALL
SELECT 3,NULLINSERT 表2 SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3
update 表1
set isdisplay = case when NOT EXISTS (SELECT 1 FROM 表2 WHERE state <>3 AND id=b.ID) then 1 else 0 end
from 表1 b ,表2 a
where a.id = b.IDSELECT * FROM 表1/*
1 0
2 0
3 1
*/
set a.isdisplay=(b.isdisplay+1)%2
from ta a, (select id, isdisplay = sum(distinct case state when 3 then 0 else 1 end) from tb group by id) b
where a.id=b.id
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281