34,571
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([cid] int,[tat] int,[val] int)
insert [A]
select 12,1,null union all
select 13,1,40
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B](cid int,[tat] int,[val] int)
insert [B]
select 12,2,30 union all
select 14,1,50
--------------开始查询--------------------------
select ISNULL(a.cid,b.cid)cid,CASE WHEN a.tat IS NOT NULL AND b.tat IS NOT NULL THEN 3 ELSE ISNULL(a.tat,b.tat) END tat,ISNULL(a.val,b.val)val
from [A] full JOIN [B]ON a.cid=b.cid
/*
cid tat val
----------- ----------- -----------
12 3 30
13 1 40
14 1 50
*/
if object_id('tempdb..#b')is not null
drop table #a
go
create table #a
(
cid int,
tat int,
val int
)
insert into #a
select 12,1 ,null union all
select 13,1, 40
if object_id('tempdb..#b')is not null
drop table #b
go
create table #b
(
cid int,
tat int,
val int
)
insert into #b
select 12,2 ,30 union all
select 14,1, 50
------------ 查询如下
;with t as
(
select * ,row_number() over(partition by cid
order by case when tat=2 then 0 else 1 end) as nu
from (select * from #a union select * from #b)a
)
select cid ,tat,val from t where nu=1
if object_id('tempdb..#b')is not null
drop table #a
go
create table #a
(
cid int,
tat int,
val int
)
insert into #a
select 12,1 ,null union all
select 13,1, 40
if object_id('tempdb..#b')is not null
drop table #b
go
create table #b
(
cid int,
tat int,
val int
)
--------查询如下
;with t as
(
select * ,row_number() over(partition by cid
order by case when tat=2 then 0 else 1 end) as nu
from (select * from #a union select * from #b)a
)
select cid ,tat,val from t where nu=1
if object_id('[A]') is not null drop table [A]
go
create table [A]([cid] int,[tat] int,[val] int)
insert [A]
select 12,1,null union all
select 13,1,40
if object_id('[B]') is not null drop table [B]
go
create table [B]([cid] int,[tat] int,[val] int)
insert [B]
select 12,2,30 union all
select 14,1,50
go
;with t
as
(
select * from A
union all
select * from B
)
select distinct
t1.cid,
case when exists(select 1 from t t2
where t2.cid = t1.cid and t2.tat = 2)
then 2
else t1.tat
end as tat,
case when exists(select 1 from t t2
where t2.cid = t1.cid and t2.tat = 2)
then (select val from t t2
where t2.cid = t1.cid and t2.tat = 2)
else t1.val
end as tat
from t t1
--group by t.cid
/*
cid tat tat
12 2 30
13 1 40
14 1 50
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([cid] int,[tat] int,[val] int)
insert [A]
select 12,1,null union all
select 13,1,40
if object_id('[B]') is not null drop table [B]
go
create table [B]([cid] int,[tat] int,[val] int)
insert [B]
select 12,2,30 union all
select 14,1,50
go
select distinct a.cid,
case when b.tat = 2
then 2
else a.tat
end as tat,
case when b.tat = 2
then b.val
else a.val
end as val
from A
left join B
on a.cid = b.cid
/*
cid tat val
12 2 30
13 1 40
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-18 17:49:20
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([cid] int,[tat] int,[val] int)
insert [A]
select 12,1,null union all
select 13,1,40
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([客户号] int,[状态] int,[值] int)
insert [B]
select 12,2,30 union all
select 14,1,50
--------------开始查询--------------------------
SELECT cid,MAX(tat) tat,MAX( val )val
FROM (
select * from [A]
UNION ALL
select * from [B])a
GROUP BY cid
----------------结果----------------------------
/*
cid tat val
----------- ----------- -----------
12 2 30
13 1 40
14 1 50
*/