初学SQL-交叉表

**********************************************************************************************

自己研究的3种静态实现交叉表方法
**********************************************************************************************

**********************************************************************************************

方案一
**********************************************************************************************
 

USE stuDBTest
GO
SET NOCOUNT ON

select stuNo=jHS.stuNo,java成绩=jHS.java,HTML成绩=jHS.HTML,SQL成绩=jHS.SQL,C#成绩=C#.Score
from
(select stuNo=jH.stuNo,java=jH.java,HTML=jH.HTML,SQL=SQL.Score
from
(select stuNo=java.stuNo,java=java.Score,HTML=HTML.Score
from
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='Java')) java,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='HTML')) HTML
where java.stuNo=HTML.stuNo) jH,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='SQL')) SQL
where jH.stuNo=SQL.stuNo)    jHS,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='C#'))  C#
where jHS.stuNo=C#.stuNo

**********************************************************************************************

方案二
**********************************************************************************************
 

select stuNo=jHSC.stuNo,
java成绩=isnull(jHSC.java,0),
HTML成绩=isnull(jHSC.HTML,0),
SQL成绩=isnull(jHSC.SQL,0),
C#成绩=isnull(jHSC.C#,0),
SQLAdvance成绩=isnull(SQLAdvance.score,0)
from
(select stuNo=jHS.stuNo,java=jHS.java,HTML=jHS.HTML,SQL=jHS.SQL,C#=C#.score
from
(select stuNo=jH.stuNo,java=jH.java,HTML=jH.HTML,SQL=SQL.score
from
(select stuNo=java.stuNo,java=java.Score,HTML=HTML.Score
from
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='Java')) java   --看做表 JAVA

left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='HTML')) HTML   --看做表 HTML
                                                              on java.stuNo=HTML.stuNo)  jH  --连接JAVA和HTML后看做表jH

left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='SQL'))  SQL    --看做表 SQL
                                                                        on jH.stuNo=SQL.stuNo)            jHS  ----连接SQL和jH后看做表jHS
left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='C#'))  C#      --看做表 C#
                                                                                  on jHS.stuNo=C#.stuNo)       jHSC        --连接C#和jHS后看做表jHSC
                  
left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName='SQLAdvance'))  SQLAdvance on jHSC.stuNo=SQLAdvance.stuNo     --连接SQLAdvance和jHSC

GO

**********************************************************************************************

方案三
**********************************************************************************************
 

--得到科目号
DECLARE @javaID int,@HTMLID int,@SQLID int,@C#ID int,@SQLAdvanceID int
select @javaID=SubjectID from stuSubject where SubjectName='Java'
select @HTMLID=SubjectID from stuSubject where SubjectName='HTML'
select @SQLID=SubjectID from stuSubject where SubjectName='SQL'
select @C#ID=SubjectID from stuSubject where SubjectName='C#'
select @SQLAdvanceID=SubjectID from stuSubject where SubjectName='SQLAdvance' 
--实现交叉表
select stuNo,
Java成绩=sum(case subjectID WHEN @javaID then Score else 0 end),
HTML成绩=sum(case subjectID WHEN @HTMLID then Score else 0 end),
SQL成绩=sum(case subjectID WHEN @SQLID then Score else 0 end),
C#成绩=sum(case subjectID WHEN @C#ID then Score else 0 end),
SQLAdvance成绩=sum(case subjectID WHEN @SQLAdvanceID then Score else 0 end)
from stuTests T
group by stuNO

GO

 

*********************************************************************************************
动态仍在学习。。。

*********************************************************************************************
declare @SQL nvarchar(2000)
set @SQL = ''
declare @CaseSQL nvarchar(1000)
set @CaseSQL = ''

declare @TmpSQL nvarchar(1000)
set @TmpSQL = ''

--select @CaseSQL = @CaseSQL +',' + SubjectName
--From stuSubject
--print @CaseSQL

select @CaseSQL=@CaseSQL +
  ', Sum(Case SubjectID
  When '''+Convert(nvarchar(10),SubjectID)+''' Then Score
  Else 0
 End) As '''+SubjectName+''''
from stuSubject

--print @CaseSQL

Set @SQL = '
Select stuNo' + @CaseSQL +
'
From stuTests
Group by stuNo'

print @SQL
 
Exec sp_executesql @SQL

****************************************************************************************************************************

--防止null
DECLARE @SQL NVARCHAR(1000)
SET @SQL=''
DECLARE @CASESQL NVARCHAR(1000)
SET @CASESQL=''
--拼语句
SELECT @CASESQL=@CASESQL+',SUM(CASE SubjectID WHEN '''+CONVERT(NVARCHAR(10),SubjectID)+'''
THEN Score ELSE 0 END) AS '''+SubjectName+''''
from stuSubject
print @casesql
SELECT @SQL='SELECT stuNo'+@CASESQL+'FROM stuTests GROUP BY stuNo'
print @sql
EXEC (@SQL)

**********************************************************************************************

初始化数据
**********************************************************************************************
use master
go
xp_cmdshell 'mkdir d:\project', NO_OUTPUT  --创建文件夹project,xp_cmdshell为系统存储过程
--检验数据库是否存在，如果为真，删除此数据库--
IF EXISTS(SELECT NAME FROM master.dbo.SYSDATABASES WHERE NAME=N'stuDBTest')
DROP DATABASE stuDBTest

GO

--创建数据库--
CREATE DATABASE stuDBTest
ON
(NAME=N'stuDB',
 FILENAME='d:\project\stuDBTest.mdf',
 SIZE=5mb,
 MAXSIZE=10mb,
 FILEGROWTH=15%)
LOG ON
 (NAME=N'stuDB_log',
  FILENAME='d:\project\stuDBTest_log.ldf',
  SIZE=2mb,
  MAXSIZE=4mb,
  FILEGROWTH=15%)

GO

USE stuDBTest
GO
SET NOCOUNT ON

--创建学员表stuInfo
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME=N'stuInfo')
 DROP TABLE stuInfo

GO

CREATE TABLE stuInfo
(stuName    NVARCHAR(20)    NOT NULL,
 stuNo      NCHAR(6)        NOT NULL,
 stuSex     NCHAR(4)        NOT NULL,
 stuAge     SMALLINT        NOT NULL,
 stuSeat    SMALLINT        IDENTITY(1,1),
 stuAddress NTEXT)     

GO

ALTER TABLE stuInfo
  ADD CONSTRAINT PK_stuNo      PRIMARY KEY(stuNo),
      CONSTRAINT CK_stuNo      CHECK(stuNo LIKE 'S253[0-9][0-9]'),
      CONSTRAINT CK_stuSex     CHECK(stuSex='男' OR stuSex='女'),
      CONSTRAINT CK_stuAge     CHECK(stuAge BETWEEN 15 AND 40),
      CONSTRAINT CK_stuSeat    CHECK(stuSeat<=30),
      CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress

GO

--创建科目表
IF EXISTS(SELECT * FROM sysobjects WHERE name='stuSubject')
   DROP TABLE stuSubject
Go

CREATE TABLE stuSubject  -- 科目表
(
 SubjectID  INT  IDENTITY (1,1),   --ID，自动编号
 SubjectName varchar(50)  NOT NULL,   --科目名称

)
GO

Insert stuSubject(SubjectName) values('Java')
Insert stuSubject(SubjectName) values('HTML')
Insert stuSubject(SubjectName) values('SQL')
Insert stuSubject(SubjectName) values('C#')

Insert stuSubject(SubjectName) values('SQLAdvance')
GO

--创建内测表
IF EXISTS(SELECT * FROM sysobjects WHERE name='stuTests')
   DROP TABLE stuTests
Go

CREATE TABLE stuTests  -- 内测表
(
 TestID  INT  IDENTITY (1,1),   --ID，自动编号
 stuNo  CHAR(6)  NOT NULL,   --学号
 SubjectID  INT   NOT NULL,   --科目ID
 Score  INT  NOT NULL  --内测成绩

)
GO

INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES('张秋丽','s25301','男',18,'北京海淀')
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛阳')
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('欧阳俊雄','s25304','男',28,'威武哈')

Insert into stuTests(SubjectID, stuNo, Score)
values(1,'s25301',50)
Insert into stuTests(SubjectID, stuNo, Score)
values(2,'s25301',60)

Insert into stuTests(SubjectID, stuNo, Score)
values(3,'s25301',70)
Insert into stuTests(SubjectID, stuNo, Score)
values(4,'s25301',80)

Insert into stuTests(SubjectID, stuNo, Score)
values(1,'s25302',100)
Insert into stuTests(SubjectID, stuNo, Score)
values(2,'s25302',99)

Insert into stuTests(SubjectID, stuNo, Score)
values(3,'s25302',98)
Insert into stuTests(SubjectID, stuNo, Score)
values(4,'s25302',97)