有A表与B表如下:
A表
ID BH FLAG
1 a01
2 a02
3 a03
B表
BH LOCK
a01 1
a01 0
a02 1
a02 1
a03 0
a03 0
根据B表,更新A表FLAG属性,期望结果:
ID BH FLAG
1 a01 2
2 a02 1
3 a03 0
A表
ID BH FLAG
1 a01
2 a02
3 a03
B表
BH LOCK
a01 1
a01 0
a02 1
a02 1
a03 0
a03 0
根据B表,更新A表FLAG属性,期望结果:
ID BH FLAG
1 a01 2
2 a02 1
3 a03 0
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 09:52:22
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @a
declare @a table (ID int,BH varchar(3),FLAG sql_variant)
insert into @a
select 1,'a01',null union all
select 2,'a02',null union all
select 3,'a03',null
--> 测试数据: @b
declare @b table (BH varchar(3),LOCK int)
insert into @b
select 'a01',1 union all
select 'a01',0 union all
select 'a02',1 union all
select 'a02',1 union all
select 'a03',0 union all
select 'a03',0update @a
set flag=b.lock
from @a a,
(select bh,lock=sum(lock) from @b group by bh)b
where a.bh=b.bhselect * from @a
ID BH FLAG
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a01 1
2 a02 2
3 a03 0(3 行受影响)
A表
ID BH FLAG
1 a01
2 a02
3 a03 B表
BH LOCK
a01 1
a01 0
a01 1
a01 1
a02 1
a02 1
a03 0
a03 0 根据B表,更新A表FLAG属性,期望结果:
ID BH FLAG
1 a01 2 (a01在B表中LOCK包含1和0两种情况,返回2)
2 a02 1 (a02在B表中LOCK包含1一种情况,返回1)
3 a03 0 (a03在B表中LOCK包含0一种情况,返回0)
我没有讲清楚,三楼理解错了,不是求和
那这个为什么是0?---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 10:00:59
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @a
declare @a table (ID int,BH varchar(3),FLAG sql_variant)
insert into @a
select 1,'a01',null union all
select 2,'a02',null union all
select 3,'a03',null
--> 测试数据: @b
declare @b table (BH varchar(3),LOCK int)
insert into @b
select 'a01',1 union all
select 'a01',0 union all
select 'a01',1 union all
select 'a01',1 union all
select 'a02',1 union all
select 'a02',1 union all
select 'a03',0 union all
select 'a03',0update @a
set flag=b.co
from @a a,
(select bh,co=count(distinct lock) from @b group by bh)b
where a.bh=b.bhselect * from @a
ID BH FLAG
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a01 2
2 a02 1
3 a03 1(3 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-02 10:01:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[BH] varchar(3),[FLAG] sql_variant)
insert [A]
select 1,'a01',null union all
select 2,'a02',null union all
select 3,'a03',null
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([BH] varchar(3),[LOCK] int)
insert [B]
select 'a01',1 union all
select 'a01',0 union all
select 'a02',1 union all
select 'a02',1 union all
select 'a03',0 union all
select 'a03',0
--------------开始查询--------------------------
update
a
set
flag=b.lock
from
a,
(select bh,count(distinct lock) as lock from b group by bh)b
where
a.bh=b.bh
select * from a
----------------结果----------------------------
/* ID BH FLAG
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a01 2
2 a02 1
3 a03 1(3 行受影响)*/
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 10:00:59
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @a
declare @a table (ID int,BH varchar(3),FLAG sql_variant)
insert into @a
select 1,'a01',null union all
select 2,'a02',null union all
select 3,'a03',null
--> 测试数据: @b
declare @b table (BH varchar(3),LOCK int)
insert into @b
select 'a01',1 union all
select 'a01',0 union all
select 'a01',1 union all
select 'a01',1 union all
select 'a02',1 union all
select 'a02',1 union all
select 'a03',0 union all
select 'a03',0update @a
set flag=b.co
from @a a,
(select bh,co=case when count(distinct lock)>1 then count(distinct lock) else max(lock) end from @b group by bh)b
where a.bh=b.bhselect * from @a
ID BH FLAG
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a01 2
2 a02 1
3 a03 0(3 行受影响)
declare @a table (ID int,BH varchar(3),FLAG sql_variant)
insert into @a
select 1,'a01',null union all
select 2,'a02',null union all
select 3,'a03',null
--> 测试数据: @b
declare @b table (BH varchar(3),LOCK int)
insert into @b
select 'a01',1 union all
select 'a01',0 union all
select 'a01',1 union all
select 'a01',1 union all
select 'a02',1 union all
select 'a02',1 union all
select 'a03',0 union all
select 'a03',0
select [ID],[BH],count(*) as flag
from
(
select distinct [ID],A.[BH],B.lock from @a as a
left outer join @b as b on a.[BH]=b.[BH]
) as t
group by [ID],[BH]