select a.WARD_NO,b.PRO_NO,a.EVA_LEVEL,ISNULL(b.评价人次,0) as 评价人次
from tb2 a
left join tb1 b on a.WARD_NO=b.WARD_NO and a.EVA_LEVEL=b.EVA_LEVEL
from tb2 a
left join tb1 b on a.WARD_NO=b.WARD_NO and a.EVA_LEVEL=b.EVA_LEVEL
insert into #tb1
select 10206,30,'A',73
union all select 10206,30,'B',4create table #tb2(PRO_NO int,PRO_NAME varchar(100),EVA_LEVEL char(1),LEVEL_NAME varchar(10))
insert into #tb2
select 30,'您目前身体恢复情况如何?','A','很好'
union all select 30,'您目前身体恢复情况如何?','B','一般'
union all select 30,'您目前身体恢复情况如何?','C','不好'select isnull(b.WARD_NO,c.WARD_NO) as WARD_NO,a.PRO_NO,a.EVA_LEVEL,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/*
WARD_NO PRO_NO EVA_LEVEL 评价人次
---------------------------------------
10206 30 A 73
10206 30 B 4
10206 30 C 0
*/
if object_id('[a]') is not null drop table [a]
go
create table [a]([WARD_NO] int,[PRO_NO] int,[EVA_LEVEL ] varchar(2),[评价人次] int)
insert [a]
select 10206,30,'A',73 union all
select 10206,30,'B',4--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([PRO_NO] int,[PRO_NAME]varchar(30), [EVA_LEVEL ] varchar(2),[LEVEL_NAME] varchar(4))
insert [b]
select 30,'您目前身体恢复情况如何?','A','很好' union all
select 30,'您目前身体恢复情况如何?','B','一般' union all
select 30,'您目前身体恢复情况如何?','C','不好'--查询语句select a.WARD_NO,a.PRO_NO,b.EVA_LEVEL,isnull(c.[评价人次],0) as '评价人次' from b b left join
(
select distinct [WARD_NO],[PRO_NO] from a
) a
on a.[PRO_NO]=b.[PRO_NO]
left join a c
on b.[PRO_NO]=c.[PRO_NO] and b.EVA_LEVEL=c.EVA_LEVEL
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-13 09:45:50
-- 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',73 union all
select 10206,30,'B',4
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([PRO_NO] int,[PRO_NAME] varchar(23),[EVA_LEVEL] varchar(1),[LEVEL_NAME] varchar(4))
insert [B]
select 30,'您目前身体恢复情况如何?','A','很好' union all
select 30,'您目前身体恢复情况如何?','B','一般' union all
select 30,'您目前身体恢复情况如何?','C','不好'
--------------开始查询--------------------------select ISNULL(A.[WARD_NO],10206)[WARD_NO],ISNULL(A.PRO_NO,B.PRO_NO)PRO_NO,ISNULL(A.EVA_LEVEL,B.EVA_LEVEL)EVA_LEVEL
,ISNULL(A.评价人次,0)评价人数
from [A] a RIGHT JOIN [B] ON A.PRO_NO=B.PRO_NO AND A.EVA_LEVEL=B.EVA_LEVEL
----------------结果----------------------------
/*
WARD_NO PRO_NO EVA_LEVEL 评价人数
----------- ----------- --------- -----------
10206 30 A 73
10206 30 B 4
10206 30 C 0
*/
create table 表1(WARD_NO int,PRO_NO int,EVA_LEVEL varchar(10),评价人次 int)insert into 表1
select 10206 ,30 ,'A', 73 union all
select 10206 ,30 ,'B',4
create table 表2(
PRO_NO int, PRO_NAME varchar(50),EVA_LEVEL varchar(10),LEVEL_NAME varchar(20))insert into 表2
select 30 ,'您目前身体恢复情况如何?' ,'A','很好' union all
select 30 ,'您目前身体恢复情况如何?' ,'B','一般' union all
select 30 ,'您目前身体恢复情况如何?' ,'C','不好'
go
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
/*
WARD_NO PRO_NO EVA_LEVEL 评价人次
10206 30 A 73
10206 30 B 4
10206 30 C 0
*/
if object_id('table1') is not null
drop table table1
go
create table table1(
ward_no int,
pro_no int,
eva_level nchar(10),
[评价人数] int
) go
if object_id('table2') is not null
drop table table2
go
create table table2(
pro_no int,
Pro_name nvarchar(20),
eva_level nchar(10),
level_name nvarchar(20)
)goinsert into table1
select 10206,30,'A',73 UNION ALL
select 10206,30,'B',4GOinsert into table2
select 30,'您目前身体恢复情况如何?','A','很好' union all
select 30,'您目前身体恢复情况如何?','B','一般' union all
select 30,'您目前身体恢复情况如何?','C','不好'GO
select ward_no,table2.pro_no,table2.eva_level,isnull([评价人数],0) as [评价人数]
from table2 left join table1 on table2.pro_no = table1.pro_no and table2.eva_level = table1.eva_level