日期:2014-05-16 浏览次数:20510 次
create table 客户通话清单
(CUST_MOBILE varchar(20),CUST_NAME varchar(10))
insert into 客户通话清单
select '1381234567','张三' union all
select '1301234567','李四'
create table 监控名单
(MOBILE varchar(20))
insert into 监控名单
select '1381234567' union all
select '1301111111'
select a.CUST_MOBILE,a.CUST_NAME,
case when b.MOBILE is not null then '监控中' else '无' end 'FLAG'
from 客户通话清单 a
left join 监控名单 b on a.CUST_MOBILE=b.MOBILE
/*
CUST_MOBILE CUST_NAME FLAG
-------------------- ---------- ------
1381234567 张三 监控中
1301234567 李四 无
(2 row(s) affected)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-02-28 10:35:33
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([CUST_MOBILE] int,[CUST_NAME] varchar(4))
insert [A]
select 1381234567,'张三' union all
select 1301234567,'李四'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([MOBILE] int)
insert [B]
select 1381234567 union all
select 1301111111
--------------开始查询--------------------------
SELECT
*,CASE WHEN EXISTS(SELECT 1 FROM b WHERE MOBILE=a.CUST_MOBILE) THEN '监控中' ELSE '无' END AS FLAG
FROM
a
----------------结果----------------------------
/* CUST_MOBILE CUST_NAME FLAG
----------- --------- ------
1381234567 张三 监控中
1301234567 李四 无
(2 行受影响)
*/