怎么判断是否相同的值?

[sankyqiu]

怎么判断是否相同的值?谢谢
主表 INSTORE
细表 INSTORED


INSTORE 主表
BILLID USERDEF1
100001
100002

INSTORED 细表
BILLID ITEMNO GOODSID STORE QTY USERDEF2
100001 1 货品A 仓库1 100 NULL
100001 2 货品A 仓库K 200 NULL
100001 3 货品A 仓库A 300 NULL
100001 4 货品A 仓库1 100 NULL

100002 1 货品A 仓库1 100 NULL
100002 2 货品A 仓库1 200 NULL
100002 3 货品A 仓库1 300 NULL
100002 4 货品A 仓库1 100 NULL


请问表 INSTORED 的STORE列内容如果相同时返回1，有不同时返回0
结果需如下显示：
BILLID ITEMNO GOODSID STORE QTY USERDEF2
100001 1 货品A 仓库1 100 0
100001 2 货品A 仓库K 200 0
100001 3 货品A 仓库A 300 0
100001 4 货品A 仓库1 100 0

100002 1 货品A 仓库1 100 1
100002 2 货品A 仓库1 200 1
100002 3 货品A 仓库1 300 1
100002 4 货品A 仓库1 100 1

[百年树人]

try--

select
  ID,
  ITEMNO,
  GOODSID,
  STORE,
  QTY,
  USERDEF2=case when exits(select 1 from INSTORED where BILLID=t.BILLID and STORE!=t.STORE) then 0 else 1 end
from
  INSTORED t

[百年树人]

[Quote=引用 1 楼 josy 的回复:]
try--
SQL codeselect
ID,
ITEMNO,
GOODSID,
STORE,
QTY,
USERDEF2=case when exits(select 1 from INSTORED where BILLID=t.BILLID and STORE!=t.STORE) then 0 else 1 end
from
INSTORED t
[/Quote]

---测试数据---
if object_id('[INSTORED]') is not null drop table [INSTORED]
go
create table [INSTORED]([BILLID] int,[ITEMNO] int,[GOODSID] varchar(5),[STORE] varchar(5),[QTY] int,[USERDEF2] int)
insert [INSTORED]
select 100001,1,'货品A','仓库1',100,null union all
select 100001,2,'货品A','仓库K',200,null union all
select 100001,3,'货品A','仓库A',300,null union all
select 100001,4,'货品A','仓库1',100,null union all
select 100002,1,'货品A','仓库1',100,null union all
select 100002,2,'货品A','仓库1',200,null union all
select 100002,3,'货品A','仓库1',300,null union all
select 100002,4,'货品A','仓库1',100,null
 
---查询---
select
  BILLID,
  ITEMNO,
  GOODSID,
  STORE,
  QTY,
  USERDEF2=case when exists(select 1 from INSTORED where BILLID=t.BILLID and STORE!=t.STORE) then 0 else 1 end
from
  INSTORED t

---结果---
BILLID      ITEMNO      GOODSID STORE QTY         USERDEF2    
----------- ----------- ------- ----- ----------- ----------- 
100001      1           货品A     仓库1   100         0
100001      2           货品A     仓库K   200         0
100001      3           货品A     仓库A   300         0
100001      4           货品A     仓库1   100         0
100002      1           货品A     仓库1   100         1
100002      2           货品A     仓库1   200         1
100002      3           货品A     仓库1   300         1
100002      4           货品A     仓库1   100         1

（所影响的行数为 8 行）

[chuifengde]

DECLARE @a TABLE(BILLID CHAR(6), ITEMNO INT,  GOODSID VARCHAR(20),  STORE VARCHAR(20),  QTY INT,  USERDEF2 INT) 
insert @a select '100001',  1      ,'货品A',    '仓库1',  100,    NULL 
UNION ALL SELECT '100001',  2      ,'货品A',    '仓库K',  200,    NULL    
UNION ALL SELECT '100001',  3      ,'货品A',    '仓库A',  300,    NULL 
UNION ALL SELECT '100001',  4      ,'货品A',    '仓库1',  100,    NULL 
UNION ALL SELECT '100002',  1      ,'货品A',    '仓库1',  100,    NULL    
UNION ALL SELECT '100002',  2      ,'货品A',    '仓库1',  200,    NULL 
UNION ALL SELECT '100002',  3      ,'货品A',    '仓库1',  300,    NULL 
UNION ALL SELECT '100002',  4      ,'货品A',    '仓库1',  100,    NULL 


UPDATE @a SET USERDEF2 = CASE WHEN NOT EXISTS(SELECT 1 FROM @a WHERE BILLID=a.BILLID AND STORE<>a.STORE) THEN 1 ELSE 0 END 
FROM @a a 
SELECT * FROM @a

--result
/*BILLID ITEMNO      GOODSID              STORE                QTY         USERDEF2    
------ ----------- -------------------- -------------------- ----------- ----------- 
100001 1           货品A                  仓库1                  100         0
100001 2           货品A                  仓库K                  200         0
100001 3           货品A                  仓库A                  300         0
100001 4           货品A                  仓库1                  100         0
100002 1           货品A                  仓库1                  100         1
100002 2           货品A                  仓库1                  200         1
100002 3           货品A                  仓库1                  300         1
100002 4           货品A                  仓库1                  100         1

（所影响的行数为 8 行）
*/

[usher_gml]

UP

[feixianxxx]

if object_id('[INSTORED]') is not null drop table [INSTORED]
go
create table [INSTORED]([BILLID] int,[ITEMNO] int,[GOODSID] varchar(5),[STORE] varchar(5),[QTY] int,[USERDEF2] int)
insert [INSTORED]
select 100001,1,'货品A','仓库1',100,null union all
select 100001,2,'货品A','仓库K',200,null union all
select 100001,3,'货品A','仓库A',300,null union all
select 100001,4,'货品A','仓库1',100,null union all
select 100002,1,'货品A','仓库1',100,null union all
select 100002,2,'货品A','仓库1',200,null union all
select 100002,3,'货品A','仓库1',300,null union all
select 100002,4,'货品A','仓库1',100,null
go
update [INSTORED]
set USERDEF2 =1
from INSTORED a
where not exists (select * from INSTORED where BILLID=a.BILLID and STORE<>a.STORE)
go
update [INSTORED]
set USERDEF2 =0
where USERDEF2 is null
go
select * from [INSTORED]
/*
100001 1 货品A 仓库1 100 0
100001 2 货品A 仓库K 200 0
100001 3 货品A 仓库A 300 0
100001 4 货品A 仓库1 100 0
100002 1 货品A 仓库1 100 1
100002 2 货品A 仓库1 200 1
100002 3 货品A 仓库1 300 1
100002 4 货品A 仓库1 100 1
*/

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

if object_id('[INSTORED]') is not null drop table [INSTORED] 
go 
create table [INSTORED]([BILLID] int,[ITEMNO] int,[GOODSID] varchar(5),[STORE] varchar(5),[QTY] int,[USERDEF2] int) 
insert [INSTORED] 
select 100001,1,'货品A','仓库1',100,null union all 
select 100001,2,'货品A','仓库K',200,null union all 
select 100001,3,'货品A','仓库A',300,null union all 
select 100001,4,'货品A','仓库1',100,null union all 
select 100002,1,'货品A','仓库1',100,null union all 
select 100002,2,'货品A','仓库1',200,null union all 
select 100002,3,'货品A','仓库1',300,null union all 
select 100002,4,'货品A','仓库1',100,null 
go 
update [INSTORED] 
set USERDEF2 =1 
from INSTORED a 
where not exists (select * from INSTORED where BILLID=a.BILLID and STORE <>a.STORE) 
go 
update [INSTORED] 
set USERDEF2 =0 
where USERDEF2 is null 
go 
select * from  [INSTORED] 
/* 
100001 1 货品A 仓库1 100 0 
100001 2 货品A 仓库K 200 0 
100001 3 货品A 仓库A 300 0 
100001 4 货品A 仓库1 100 0 
100002 1 货品A 仓库1 100 1 
100002 2 货品A 仓库1 200 1 
100002 3 货品A 仓库1 300 1 
100002 4 货品A 仓库1 100 1 
*/

[bennyyyyy]

IF EXISTS(SELECT ........)