34,590
社区成员
发帖
与我相关
我的任务
分享
create table #tb1(WARD_NO varchar(10),PRO_NO varchar(10),EVA_LEVEL char(1),评价人次 int)
insert into #tb1
select '10206','30','A','75'
union all select '10206','30','B','5'
union all select '10207','31','A','100'
union all select '10207','31','B','50'
union all select '10207','31','c','25'
create table #tb2(PRO_NO varchar(10),EVA_LEVEL char(1))
insert into #tb2
select '30','A'
union all select '30','B'
union all select '30','C'
union all select '31','A'
union all select '31','B'
union all select '31','C'
union all select '31','D'
go
select ISNULL(b.WARD_NO,c.WARD_NO) as WARD_NO,a.*,isnull(b.评价人次,0) as 评价人次
from #tb2 a
left join #tb1 b on a.PRO_NO=b.PRO_NO and a.EVA_LEVEL=b.EVA_LEVEL
left join (select distinct WARD_NO,PRO_NO from #tb1)c on a.PRO_NO=c.PRO_NO
drop table #tb1,#tb2
/*
WARD_NO PRO_NO EVA_LEVEL 评价人次
10206 30 A 75
10206 30 B 5
10206 30 C 0
10207 31 A 100
10207 31 B 50
10207 31 C 25
10207 31 D 0
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-13 10:31:58
-- 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]([WARD_NO] int,[PRO_NO] int,[EVA_LEVEL] varchar(1),[评价人次] int)
insert [A]
select 10206,30,'A',75 union all
select 10206,30,'B',5 union all
select 10207,31,'A',100 union all
select 10207,31,'B',50 union all
select 10207,31,'c',25
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([PRO_NO] int,[EVA_LEVEL] varchar(1))
insert [B]
select 30,'A' union all
select 30,'B' union all
select 30,'C' union all
select 31,'A' union all
select 31,'B' union all
select 31,'C' union all
select 31,'D'
--------------开始查询--------------------------
select t.WARD_NO ,t2.PRO_NO,t2.EVA_LEVEL,ISNULL(T1.评价人次,0)评价人次
from B t2
inner join
(
select distinct WARD_NO,PRO_NO--,评价人次
from a
)t
on t.PRO_NO = t2.PRO_NO
left join A t1
on t2.PRO_NO = t1.PRO_NO
and t2.EVA_LEVEL = t1.EVA_LEVEL
----------------结果----------------------------
/* WARD_NO PRO_NO EVA_LEVEL 评价人次
----------- ----------- --------- -----------
10206 30 A 75
10206 30 B 5
10206 30 C 0
10207 31 A 100
10207 31 B 50
10207 31 C 25
10207 31 D 0
*/
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([WARD_NO] int,[PRO_NO] int,[EVA_LEVEL] varchar(1),[评价人次] int)
insert [A]
select 10206,30,'A',75 union all
select 10206,30,'B',5 union all
select 10207,31,'A',100 union all
select 10207,31,'B',50 union all
select 10207,31,'c',25
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([PRO_NO] int,[EVA_LEVEL] varchar(1))
insert [B]
select 30,'A' union all
select 30,'B' union all
select 30,'C' union all
select 31,'A' union all
select 31,'B' union all
select 31,'C' union all
select 31,'D'
--------------------------------------查询-----------------------------------------------
;WITH t AS
(
SELECT a.WARD_NO,ISNULL(a.评价人次,0) 评价人次,b.PRO_NO,b.EVA_LEVEL FROM a RIGHT JOIN b
ON a.PRO_NO=b.PRO_NO
AND a.EVA_LEVEL=b.EVA_LEVEL
)
SELECT c.WARD_NO AS WARD_NO,c.PRO_NO AS PRO_NO,t.EVA_LEVEL AS EVA_LEVEL,t.评价人次 AS 评价人次 FROM t,
(SELECT WARD_NO,PRO_NO FROM a GROUP BY WARD_NO,PRO_NO) c WHERE t.PRO_NO=c.PRO_NO
/*
WARD_NO PRO_NO EVA_LEVEL 评价人次
----------- ----------- --------- -----------
10206 30 A 75
10206 30 B 5
10206 30 C 0
10207 31 A 100
10207 31 B 50
10207 31 C 25
10207 31 D 0
(7 行受影响)
*/
select t.WARD_NO ,t2.PRO_NO,t2.EVA_LEVEL,isnull(评价人次,0) as 评价人次
from 表2 t2
inner join
(
select distinct WARD_NO,PRO_NO
from 表1
)t
on t.PRO_NO = t2.PRO_NO
left join 表1 t1
on t2.PRO_NO = t1.PRO_NO
and t2.EVA_LEVEL = t1.EVA_LEVEL
select isnull(A.WARD_NO,C.WARD_NO) as WARD_NO,B.PRO_NO,B.EVA_LEVEL,ISNULL(A.[评价人次],0) as [评价人次] from B
left join A
on B.PRO_NO=A.PRO_NO and B.EVA_LEVEL=A.EVA_LEVEL
inner join (select distinct [WARD_NO],[PRO_NO] from A) C
on B.PRO_NO = C.PRO_NO