如何将四个表分别统计后，利用左连接形成一个视图？

[红豆加牛奶冰]

数据库BYSJ
表如下

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[Paper_info]    脚本日期: 03/22/2014 12:43:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Paper_info](
	[PKID] [uniqueidentifier] NOT NULL,
	[PaperName] [varchar](50) NOT NULL,
	[PaperAuthor] [varchar](50) NOT NULL,
	[SchoolID] [int] NOT NULL,
	[PaperClassID] [int] NOT NULL,
	[PaperTime] [datetime] NOT NULL,
	[BelongYear] [int] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_Paper_info] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[PaperClass]    脚本日期: 03/22/2014 12:44:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaperClass](
	[PKID] [uniqueidentifier] NOT NULL,
	[PaperClassID] [int] NOT NULL,
	[PaperClassName] [varchar](50) NOT NULL,
	[WeighNum] [int] NOT NULL,
 CONSTRAINT [PK_PaperClass_1] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[Patent_info]    脚本日期: 03/22/2014 12:45:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patent_info](
	[PKID] [uniqueidentifier] NOT NULL,
	[PatentID] [varchar](50) NOT NULL,
	[PatentName] [varchar](50) NOT NULL,
	[PatentAuthor] [varchar](50) NOT NULL,
	[PatentClassID] [int] NOT NULL,
	[SchoolID] [int] NOT NULL,
	[PatentTime] [datetime] NOT NULL,
	[BelongYear] [int] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_Patent_info_1] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[PatentClass]    脚本日期: 03/22/2014 12:46:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PatentClass](
	[PKID] [uniqueidentifier] NOT NULL,
	[PatentClassID] [int] NOT NULL,
	[PatentClassName] [varchar](50) NOT NULL,
	[WeightNum] [int] NOT NULL,
 CONSTRAINT [PK_PatentClass_1] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[Subject_info]    脚本日期: 03/22/2014 12:47:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Subject_info](
	[PKID] [uniqueidentifier] NOT NULL,
	[SubjectID] [int] NOT NULL,
	[SubjectName] [varchar](50) NOT NULL,
	[SubjectAuthor] [varchar](50) NOT NULL,
	[SchoolID] [int] NOT NULL,
	[SubjectClassID] [int] NOT NULL,
	[SubjectTime] [datetime] NOT NULL,
	[BelongYear] [int] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_Subject_info_1] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[SubjectClass]    脚本日期: 03/22/2014 12:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubjectClass](
	[PKID] [uniqueidentifier] NOT NULL,
	[SubjectClassID] [int] NOT NULL,
	[SubjectClassName] [varchar](50) NOT NULL,
	[WeightNum] [varchar](50) NOT NULL,
 CONSTRAINT [PK_SubjectClass_1] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[Win_info]    脚本日期: 03/22/2014 12:50:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Win_info](
	[PKID] [uniqueidentifier] NOT NULL,
	[WinID] [int] NOT NULL,
	[WinName] [varchar](50) NOT NULL,
	[WinAuthor] [varchar](50) NOT NULL,
	[SchoolID] [int] NOT NULL,
	[WinClassID] [int] NOT NULL,
	[WinTime] [datetime] NOT NULL,
	[BelongYear] [int] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_Win_info_1] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [BYSJ]
GO
/****** 对象:  Table [dbo].[WinClass]    脚本日期: 03/22/2014 12:51:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WinClass](
	[PKID] [uniqueidentifier] NOT NULL,
	[WinClassID] [int] NOT NULL,
	[WinClassName] [varchar](50) NOT NULL,
	[WeightNum] [int] NOT NULL,
 CONSTRAINT [PK_WinClass] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

将上面的四个info表分别按*classID进行统计，然后进行左连接
使其出现视图如下

请大家帮帮忙！

[红豆加牛奶冰]

引用 1 楼 yupeigu 的回复:

直接用union all合并可以吗

要用左连接

[LongRui888]

引用 2 楼 hdjnnb 的回复:

引用 1 楼 yupeigu 的回复:

直接用union all合并可以吗

要用左连接

哦，那可以用哪个字段关联呢

[LongRui888]

直接用union all合并可以吗

[红豆加牛奶冰]

引用 3 楼 yupeigu 的回复:

引用 2 楼 hdjnnb 的回复:

[quote=引用 1 楼 yupeigu 的回复:]
直接用union all合并可以吗

要用左连接

哦，那可以用哪个字段关联呢[/quote]
我使用如下语句，进行统计

1
2
3
4
5
use BYSJ
select tp.SchoolID 学校,count(tp.ctp) 论文 ,max(case tp.PaperClassID when '1' then tp.ctp else 0 end)CSCD,max(case tp.PaperClassID when '2' then tp.ctp else 0 end)CSSCI,max(case tp.PaperClassID when '3' then tp.ctp else 0 end)as'CSI/EI',tp.BelongYear
from (SELECT SchoolID,COUNT(PaperName) ctp,PaperClassID,BelongYear FROM dbo.Paper_info GROUP BY SchoolID,PaperClassID,BelongYear)tp
group by SchoolID,BelongYear,tp.ctp
go

得到结果如下

我需要以学校和年限两个字段为条件将两个表进行组合

[红豆加牛奶冰]

不是两个表是四个统计结果