方法 2 :
*!*
区别应届生,往届生,一句 SQL - Select 语句
Set EngineBehavior 70
*!*
1. 把录取分先导入到一个表中:
Create Cursor 录取分 (Id I, 科类 C(6), 批次 C(4), 总分高 I, 总分低 I, 专业分高 I, 专业分低 I)
Insert Into 录取分 Values (1, "文史", "一本", 999, 548, 0, 0)
Insert Into 录取分 Values (2, "文史", "二本", 547, 404, 0, 0)
Insert Into 录取分 Values (3, "文史", "专科", 403, 200, 0, 0)
Insert Into 录取分 Values (1, "理工", "一本", 999, 544, 0, 0)
Insert Into 录取分 Values (2, "理工", "二本", 543, 335, 0, 0)
Insert Into 录取分 Values (3, "理工", "专科", 334, 200, 0, 0)
Insert Into 录取分 Values (1, "声乐文", "一本", 999, 308, 999, 127)
Insert Into 录取分 Values (2, "声乐文", "二本", 307, 300, 126, 115)
Insert Into 录取分 Values (3, "声乐文", "专科", 299, 160, 126, 115)
Insert Into 录取分 Values (1, "声乐理", "一本", 999, 308, 999, 127)
Insert Into 录取分 Values (2, "声乐理", "二本", 307, 300, 126, 115)
Insert Into 录取分 Values (3, "声乐理", "专科", 299, 160, 126, 115)
Insert Into 录取分 Values (1, "器乐文", "一本", 999, 308, 999, 110)
Insert Into 录取分 Values (2, "器乐文", "二本", 307, 285, 999, 110)
Insert Into 录取分 Values (3, "器乐文", "专科", 284, 160, 999, 110)
Insert Into 录取分 Values (1, "器乐理", "一本", 999, 308, 999, 110)
Insert Into 录取分 Values (2, "器乐理", "二本", 307, 285, 999, 110)
Insert Into 录取分 Values (3, "器乐理", "专科", 284, 160, 999, 110)
Insert Into 录取分 Values (1, "美术文", "一本", 999, 308, 999, 210)
Insert Into 录取分 Values (2, "美术文", "二本", 999, 308, 209, 180)
Insert Into 录取分 Values (3, "美术文", "专科", 307, 160, 179, 160)
Insert Into 录取分 Values (1, "美术理", "一本", 999, 308, 999, 210)
Insert Into 录取分 Values (2, "美术理", "二本", 999, 308, 209, 180)
Insert Into 录取分 Values (3, "美术理", "专科", 307, 160, 179, 160)
*!*
2. 然后用一句 SQL 语句,把两个表 As 连接, :
Select T1.学校, T2.批次, T1.考生类别 应届, ;
Sum(Iif(T1.科类 = '理工'
, 1, 0)) As 理工
, ;
Sum(Iif(T1.科类 = '文史'
, 1, 0)) As 文史
, ;
Sum(Iif(T1.科类 = '声乐文', 1, 0)) As 声乐文, ;
Sum(Iif(T1.科类 = '器乐文', 1, 0)) As 器乐文, ;
Sum(Iif(T1.科类 = '美术文', 1, 0)) As 美术文, ;
Sum(Iif(T1.科类 = '声乐理', 1, 0)) As 声乐理, ;
Sum(Iif(T1.科类 = '器乐理', 1, 0)) As 器乐理, ;
Sum(Iif(T1.科类 = '美术理', 1, 0)) As 美术理, ;
Cnt(*) As 文理合计 ;
Where T1.科类 = T2.科类 And Between(T1.总分, T2.总分低, T2.总分高) And ;
Between(T1.专业分, T2.专业分低, T2.专业分高) ;
Group By T1.学校, T1.考生类别, Id Order By T1.学校, T1.考生类别, Id ;
From 分数统计表 T1, 录取分 T2