求sql语句

[atpains]

表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；

[百年树人]

一个id有多个state如何处理？

[王向飞]

update 表1 
set isdisplay = case when 表2.state = 3  then 1 else 0 end 
from 表1,表2
where 表2.id = 表1.ID

[linguojin11]

是不是不够详细？

[百年树人]

try

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

[飘零一叶]

IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA
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,3

update 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 a

SELECT * FROM TA
ID isdisplay
-- ---------
1	0
2	0
3	1

[billpu]

是全部都是3才更新的意思吧
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,NULL

INSERT 表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.ID

SELECT * FROM 表1

/*
1	0
2	0
3	1
*/

[coleling]

还可以这样写，效率相对高一点

--借用#6的演示数据

update a
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

[dawugui]

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[LJ_liujue]

俺是新手 有没有简单点的 明白点的？？