求sql

Question

----------------------------------------------------------------
-- 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
*/

Answer 1

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'
goselect 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_NOdrop 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
*/

Answer 2

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

Answer 3

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

Answer 4

--> 测试数据：[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 行受影响)*/