主从表返回值主表: I_INSTORE
BILLID USERDEF1
1001 NULL
1002 NULL
从表: I_INSTORED
BILLID STOREID GOODSID QTY
1001 1 9001 300
1001 1 9002 500
1001 1 9003 40001002 2 9001 700
1002 3 9007 800
1002 2 9008 1000要求结果如下: (即主从表中同一BILLID时, 如果从表中的STOREID是全部相同时返回主表USERDEF1 的值为1)
主表: I_INSTORE
BILLID USERDEF1
1001 1
1002 NULL
BILLID USERDEF1
1001 NULL
1002 NULL
从表: I_INSTORED
BILLID STOREID GOODSID QTY
1001 1 9001 300
1001 1 9002 500
1001 1 9003 40001002 2 9001 700
1002 3 9007 800
1002 2 9008 1000要求结果如下: (即主从表中同一BILLID时, 如果从表中的STOREID是全部相同时返回主表USERDEF1 的值为1)
主表: I_INSTORE
BILLID USERDEF1
1001 1
1002 NULL
from I_INSTORE m left join
(select BILLID , count(distinct STOREID) cnt from I_INSTORED group by BILLID) n
on m.BILLID = n.BILLID
insert into I_INSTORE values(1001 , NULL )
insert into I_INSTORE values(1002 , NULL )
create table I_INSTORED(BILLID int, STOREID int, GOODSID int, QTY int)
insert into I_INSTORED values(1001 , 1 , 9001 , 300 )
insert into I_INSTORED values(1001 , 1 , 9002 , 500 )
insert into I_INSTORED values(1001 , 1 , 9003 , 4000)
insert into I_INSTORED values(1002 , 2 , 9001 , 700 )
insert into I_INSTORED values(1002 , 3 , 9007 , 800 )
insert into I_INSTORED values(1002 , 2 , 9008 , 1000)
go--search
select m.BILLID , USERDEF1 = (case when n.cnt = 1 then 1 end)
from I_INSTORE m left join
(select BILLID , count(distinct STOREID) cnt from I_INSTORED group by BILLID) n
on m.BILLID = n.BILLID
/*
BILLID USERDEF1
----------- -----------
1001 1
1002 NULL(所影响的行数为 2 行)
*/--update
update I_INSTORE
set USERDEF1 = (case when n.cnt = 1 then 1 end)
from I_INSTORE m left join
(select BILLID , count(distinct STOREID) cnt from I_INSTORED group by BILLID) n
on m.BILLID = n.BILLID
select * from I_INSTORE
/*
BILLID USERDEF1
----------- -----------
1001 1
1002 NULL(所影响的行数为 2 行)
*/drop table I_INSTORE,I_INSTORED
declare @I_INSTORE table (BILLID int,USERDEF1 int)
insert into @I_INSTORE
select 1001,null union all
select 1002,null
--> 测试数据: @I_INSTORED
declare @I_INSTORED table (BILLID int,STOREID int,GOODSID int,QTY int)
insert into @I_INSTORED
select 1001,1,9001,300 union all
select 1001,1,9002,500 union all
select 1001,1,9003,4000 union all
select 1002,2,9001,700 union all
select 1002,3,9007,800 union all
select 1002,2,9008,1000update @I_INSTORE
set USERDEF1=case when b.num=1 then 1 else null end
from @I_INSTORE a
left join (
select BILLID, num=count(distinct STOREID)
from @I_INSTORED
group by BILLID
)b
on a.BILLID=b.BILLIDselect * from @I_INSTOREBILLID USERDEF1
----------- -----------
1001 1
1002 NULL
-- Author: T.O.P
-- Create date: 2009/11/30
-- Version: SQL SERVER 2005
-- =============================================
declare @tb1 table([BILLID] int,[USERDEF1] sql_variant)
insert @tb1
select 1001,null union all
select 1002,nulldeclare @tb2 table([BILLID] int,[STOREID] int,[GOODSID] int,[QTY] int)
insert @tb2
select 1001,1,9001,300 union all
select 1001,1,9002,500 union all
select 1001,1,9003,4000 union all
select 1002,2,9001,700 union all
select 1002,3,9007,800 union all
select 1002,2,9008,1000
select a.[BILLID], (case when id=1 then 1 else null end) as USERDEF1
from @tb1 a inner join (select [BILLID],count(distinct [STOREID]) as id from @tb2 group by [BILLID]) b on a.[BILLID] = b.[BILLID]
--测试结果:
/*
BILLID USERDEF1
----------- -----------
1001 1
1002 NULL(2 row(s) affected)
*/
select a.BILLID,(case count(distinct(STOREID)) when 1 then 1 end)
from I_INSTORE a
inner join I_INSTORED b on a.BILLID=b.BILLID
group by a.BILLID