初學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)