求教这个SQL应该怎么写？

pengxuan 2015-10-31 07:56:23



--班级表

IF EXISTS(SELECT 1 FROM sysobjects WHERE name='ZKClass' and type='u')

   DROP TABLE ZKClass

GO

CREATE TABLE ZKClass

(

 ZKClassID		int						NOT NULL,	--班级编号

 TermYear		int						NOT NULL,	--年份

 TermName		varchar(10)				NOT NULL,	--学期

 Grade			varchar(10)				NOT NULL,	--年级

 Course			varchar(10)				NOT NULL,	--科目

 ClassType		varchar(10)				NOT NULL,	--班型(普通班,重点班,实验班)

 Teacher		varchar(10)				NOT NULL,	--教师

 SchoolTime		varchar(20)				NOT NULL,	--上课时间

 SourceZKClassID	varchar(10)				NULL,	--上个季度班级的ID

 YContDate			datetime				NULL,	--第一个日期

 KContDate			datetime				NULL	--第一个日期

)

GO

--秋季班

INSERT INTO ZKClass

SELECT 1,2015,'秋季','一年级','数学','普通班','张三','周六下午2:00-5:00',NULL,NULL,NULL,'2015-9-1' UNION ALL

SELECT 2,2015,'秋季','一年级','数学','普通班','张三','周五上午8:30-11:30',NULL,NULL,NULL,'2015-9-1' UNION ALL

SELECT 3,2015,'秋季','一年级','数学','普通班','李四','周日晚上6:00-9:00',NULL,NULL,NULL,'2015-9-1' UNION ALL

SELECT 4,2015,'秋季','一年级','数学','普通班','王五','周日下午2:00-5:00',NULL,NULL,NULL,'2015-9-1' UNION ALL

SELECT 5,2015,'秋季','一年级','数学','重点班','王五','周日上午8:00-11:00',NULL,NULL,NULL,'2015-9-1' UNION ALL

SELECT 6,2015,'秋季','一年级','数学','普通班','赵六','周六上午8:00-11:00',NULL,NULL,NULL,'2015-9-1'

--寒假班

--秋季张三的2个班到寒假合成了1个班

--秋季李四的1个班到寒假分成了2个班

--秋季赵六的1个普通班到寒假、春季升成了重点班

INSERT INTO ZKClass

SELECT 7,2016,'寒假','一年级','数学','普通班','张三','下午2:00-5:00','1,2','2015-11-1','2015-11-7','2015-11-15' UNION ALL

SELECT 8,2016,'寒假','一年级','数学','普通班','李四','晚上6:00-9:00','3','2015-11-1','2015-11-7','2015-11-15' UNION ALL

SELECT 9,2016,'寒假','一年级','数学','普通班','李四','下午2:00-5:00','3','2015-11-1','2015-11-7','2015-11-15' UNION ALL

SELECT 10,2016,'寒假','一年级','数学','普通班','王五','下午2:00-5:00','4','2015-11-6','2015-11-13','2015-11-20' UNION ALL

SELECT 11,2016,'寒假','一年级','数学','重点班','王五','上午8:00-11:00','5','2015-11-6','2015-11-13','2015-11-20' UNION ALL

SELECT 12,2016,'寒假','一年级','数学','重点班','赵六','上午8:00-11:00','6','2015-11-6','2015-11-13','2015-11-20'

--春季班

INSERT INTO ZKClass

SELECT 13,2016,'春季','一年级','数学','普通班','张三','周六下午2:00-5:00','7','2015-11-1','2015-11-7','2015-11-15' UNION ALL

SELECT 14,2016,'春季','一年级','数学','普通班','张三','周五上午8:30-11:30','7','2015-11-1','2015-11-7','2015-11-15' UNION ALL

SELECT 15,2016,'春季','一年级','数学','普通班','李四','周日晚上6:00-9:00','8,9','2015-11-1','2015-11-7','2015-11-15' UNION ALL

SELECT 16,2016,'春季','一年级','数学','普通班','王五','周日下午2:00-5:00','10','2015-11-6','2015-11-13','2015-11-20' UNION ALL

SELECT 17,2016,'春季','一年级','数学','重点班','王五','周日上午8:00-11:00','11','2015-11-6','2015-11-13','2015-11-20' UNION ALL

SELECT 18,2016,'春季','一年级','数学','重点班','赵六','周六上午8:00-11:00','12','2015-11-6','2015-11-13','2015-11-20'

--班级是顺着延下来的，根据SourceZKClassID（上个季度班级的ID）可以找到关联关系。

--希望根据秋季班的班级编号和当前日期可以获得当前可以报名的班级。

--如果当前日期大于第一个日期，则希望查询到这个班自己关联的寒假和春季班

--如果当前日期大于第二个日期，则希望查询到这个班关联的寒假和春季同年级，同科目，同班型的所有班。

--下面是我写的语句，但效率比较低，不知道有没有更好的设计或查询方法。



--原班续报、跨班续报查询实例

DECLARE @ZKClassID int

SET @ZKClassID=1

DECLARE @NowDate datetime

SET @NowDate='2015-11-7'  --假如这是当前日期

;WITH cteA AS

(

 SELECT * FROM ZKClass WHERE ZKClassID=@ZKClassID

 UNION ALL

 SELECT a.* FROM ZKClass a INNER JOIN cteA b ON CHARINDEX(','+CAST(b.ZKClassID as varchar)+',',','+a.SourceZKClassID+',')>0

), cteB AS

(

 SELECT DISTINCT * FROM cteA  WHERE ZKClassID<>@ZKClassID

), cteC AS

(

 SELECT a.* FROM ZKClass a INNER JOIN (SELECT TermYear,TermName,Grade,Course,ClassType FROM cteB GROUP BY TermYear,TermName,Grade,Course,ClassType) b ON a.TermYear=b.TermYear AND a.TermName=b.TermName AND a.Grade=b.Grade AND a.Course=b.Course AND a.ClassType=b.ClassType AND a.ZKClassID not in(select ZKClassID from cteB)

)

SELECT * FROM cteB WHERE @NowDate>=YContDate

UNION ALL

SELECT * FROM cteC WHERE @NowDate>=KContDate