27,580
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-26 11:11:54
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Id] nvarchar(8),[app_cd1] nvarchar(10),[app_nm1] nvarchar(4),[app_cd2] nvarchar(10),[app_nm2] nvarchar(4))
insert [A]
select 'A001','XA121',N'张三',null,null union all
select 'A002','XA111',N'黄二','XA001',N'李四' union all
select 'A002','XA101',N'王五','XA002',N'赵六'
--------------生成数据--------------------------
DECLARE @name VARCHAR(10)
SET @name='XA001'
SELECT ISNULL(a.id,b.id)id,ISNULL(a.app_cd1,b.app_cd2)app_cd,ISNULL(a.app_nm1,b.app_nm2)appnm
FROM
(select id,app_cd1,app_nm1
from [A]
WHERE app_cd1=@name) a FULL JOIN (select id,app_cd2,app_nm2
from [A]
WHERE app_cd2=@name)b ON a.id=b.id
----------------结果----------------------------
/*
id app_cd appnm
-------- ---------- -----
A002 XA001 李四
*/
create table A表
(Id varchar(10),app_cd1 varchar(10),app_nm1 varchar(10),app_cd2 varchar(10),app_nm2 varchar(10))
insert into A表
select 'A001','XA121','张三',null,null union all
select 'A002','XA111','黄二','XA001','李四' union all
select 'A002','XA101','王五','XA002','赵六'
-- 传入“XA101”时取出王五
declare @x varchar(10)
select @x='XA101'
select top 1 app_nm
from
(select app_cd1 'app_cd',app_nm1 'app_nm' from A表
union all
select app_cd2 'app_cd',app_nm2 'app_nm' from A表) t
where app_cd=@x
/*
app_nm
----------
王五
(1 row(s) affected)
*/
-- 传入“XA001”时取出李四
declare @x varchar(10)
select @x='XA001'
select top 1 app_nm
from
(select app_cd1 'app_cd',app_nm1 'app_nm' from A表
union all
select app_cd2 'app_cd',app_nm2 'app_nm' from A表) t
where app_cd=@x
/*
app_nm
----------
李四
(1 row(s) affected)
*/
select nm
from (select app_cd1 cd,app_nm1 nm
from a
union all
select app_cd2,app_nm2
from a ) t
where cd=“XA101”