表A
字段1 字段2 字段3
A AA 2012-01-30
A AB 2012-01-30
B BB 2012-01-30
B BC 2012-01-30
C CA 2012-01-30
C CB 2012-01-30
C CC 2012-01-30
表B
A AA 2012-01-30
B BB 2012-01-30
B BC 2012-01-30
C CA 2012-01-30
C CC 2012-01-30
最后结果
字段1 字段2 字段3 新字段
A AA 2012-01-30 OK
A AB 2012-01-30 NG
B BB 2012-01-30 OK
B BC 2012-01-30 OK
C CA 2012-01-30 OK
C CB 2012-01-30 NG
C CC 2012-01-30 NG
以表A为准,结果需要新增一个字段。如果表B中字段1和字段2数据相同,则新字段为OK,没有或者不相同为NG。
这个用SQL怎么实现?
谢谢
字段1 字段2 字段3
A AA 2012-01-30
A AB 2012-01-30
B BB 2012-01-30
B BC 2012-01-30
C CA 2012-01-30
C CB 2012-01-30
C CC 2012-01-30
表B
A AA 2012-01-30
B BB 2012-01-30
B BC 2012-01-30
C CA 2012-01-30
C CC 2012-01-30
最后结果
字段1 字段2 字段3 新字段
A AA 2012-01-30 OK
A AB 2012-01-30 NG
B BB 2012-01-30 OK
B BC 2012-01-30 OK
C CA 2012-01-30 OK
C CB 2012-01-30 NG
C CC 2012-01-30 NG
以表A为准,结果需要新增一个字段。如果表B中字段1和字段2数据相同,则新字段为OK,没有或者不相同为NG。
这个用SQL怎么实现?
谢谢
解决方案 »
- 为什么我找不到数据库下的节点
- 使用语句从mssql导到paradox中,提示 未能处理对象 'select * from zc_xmmx'。OLE DB 提供程序 'microsoft.jet.oledb.4.0' 指出该对象中没有任何列。 查了一个晚上,还是不知道原因。
- 为什么用临时表不见效率提高?
- 菜菜提问
- 一个比较复杂的查询,考验真功夫的时候到了,感兴趣的朋友请进!!!
- 去面试遇上的一个题目,居然做不出来,丢脸啊
- 求助:函数中sql语句写法
- SqlServer调试问题。急急急!
- 请问float类型如何设置小数位数?
- 关于存储过程参数的问题
- 关于SQL2005 APPLY运算符的疑惑
- 一个简单的C++问题,求大虾帮忙。
--> 测试数据: @表A
declare @表A table (字段1 varchar(1),字段2 varchar(2),字段3 varchar(10))
insert into @表A
select 'A','AA','2012-01-30' union all
select 'A','AB','2012-01-30' union all
select 'B','BB','2012-01-30' union all
select 'B','BC','2012-01-30' union all
select 'C','CA','2012-01-30' union all
select 'C','CB','2012-01-30' union all
select 'C','CC','2012-01-30'
--> 测试数据: @表B
declare @表B table (字段1 varchar(1),字段2 varchar(2),字段3 varchar(10))
insert into @表B
select 'A','AA','2012-01-30' union all
select 'B','BB','2012-01-30' union all
select 'B','BC','2012-01-30' union all
select 'C','CA','2012-01-30' union all
select 'C','CC','2012-01-30'select a.*,新字段=isnull(replace(b.字段1,b.字段1,'OK'),'NG')
from @表A a left join @表B b on a.字段1=b.字段1
and a.字段2=b.字段2 and a.字段3=b.字段3/*
字段1 字段2 字段3 新字段
---- ---- ---------- --------------
A AA 2012-01-30 OK
A AB 2012-01-30 NG
B BB 2012-01-30 OK
B BC 2012-01-30 OK
C CA 2012-01-30 OK
C CB 2012-01-30 NG
C CC 2012-01-30 OK
*/
go
create table [A]([col1] varchar(1),[col2] varchar(2),[col3] datetime)
insert [A]
select 'A','AA','2012-01-30' union all
select 'A','AB','2012-01-30' union all
select 'B','BB','2012-01-30' union all
select 'B','BC','2012-01-30' union all
select 'C','CA','2012-01-30' union all
select 'C','CB','2012-01-30' union all
select 'C','CC','2012-01-30'
GO
if object_id('[B]') is not null drop table [B]
go
create table [B]([col1] varchar(1),[col2] varchar(2),[col3] datetime)
insert [B]
select 'A','AA','2012-01-30' union all
select 'B','BB','2012-01-30' union all
select 'B','BC','2012-01-30' union all
select 'C','CA','2012-01-30' union all
select 'C','CC','2012-01-30'
GOSELECT A.*,CASE WHEN B.COL1 IS NULL THEN 'NG' ELSE 'OK' END AS NEWCOL
FROM A
LEFT JOIN B ON A.COL1=B.COL1 AND A.COL2=B.COL2
/**
col1 col2 col3 NEWCOL
---- ---- ----------------------- ------
A AA 2012-01-30 00:00:00.000 OK
A AB 2012-01-30 00:00:00.000 NG
B BB 2012-01-30 00:00:00.000 OK
B BC 2012-01-30 00:00:00.000 OK
C CA 2012-01-30 00:00:00.000 OK
C CB 2012-01-30 00:00:00.000 NG
C CC 2012-01-30 00:00:00.000 OK(7 行受影响)
**/
表A
字段1 字段2 字段3
A AA 2012-01-30
A AB 2012-01-30
B BB 2012-01-30
B BC 2012-01-30
C CA 2012-01-30
C CB 2012-01-30
C CC 2012-01-30
表B
A AA 2012-01-30
B BB 2012-01-30
B BC 2012-01-30
C CA 2012-01-30
C CC 2012-01-30
最后结果
字段1 字段2 字段3 新字段
A AA 2012-01-30 OK
A AB 2012-01-30 NG
B BB 2012-01-30 OK
B BC 2012-01-30 OK
C CA 2012-01-30 OK
C CB 2012-01-30 NG
C CC 2012-01-30 NG
以表A为准,结果需要新增一个字段。如果表B中字段1和字段2数据相同,则新字段为OK,没有或者不相同为NG。
这个用SQL怎么实现?
谢谢
*/
go
if object_id('tbl1') is not null
drop table tbl1
go
create table tbl1(
one varchar(2),
two varchar(4),
three datetime
)
go
insert tbl1
select 'A','AA','2012-01-30' union all
select 'A','AB','2012-01-30' union all
select 'B','BB','2012-01-30' union all
select 'B','BC','2012-01-30' union all
select 'C','CA','2012-01-30' union all
select 'C','CB','2012-01-30' union all
select 'C','CC','2012-01-30'go
if object_id('tbl2') is not null
drop table tbl2
go
create table tbl2(
one varchar(2),
two varchar(3),
three datetime
)
insert tbl2
select 'A','AA','2012-01-30' union all
select 'B','BB','2012-01-30' union all
select 'B','BC','2012-01-30' union all
select 'C','CA','2012-01-30' union all
select 'C','CC','2012-01-30'select tbl1.*,
case
when
tbl1.one=tbl2.one and tbl1.two=tbl2.two
--tbl2.one Is null
then 'OK' else 'NG' end
from tbl1
left join
tbl2 on tbl1.one=tbl2.one and tbl1.two=tbl2.two请问楼上大侠,when后面的两种写法有什么区别??
结果是一样的,但是tbl1.one=tbl2.one and tbl1.two=tbl2.two
在on后面已经有了,所以根据这个条件得不到值的就是null的。
用 is null 相对简洁。