Set EngineBehavior 70
Close Databases
*!* 构造测试数据:
Create Cursor t2 (学号 c(4),姓名 c(8),课目 c(10),成绩 N(3))
Insert Into t2 Values ("1001","张三","数学",102)
Insert Into t2 Values ("1001","张三","语文",120)
Insert Into t2 Values ("1002","李四","数学",119)
Insert Into t2 Values ("1002","李四","语文",108)
*!* 方法一:定制型 SQL 语句:
Select 学号, 姓名, Sum(Iif(课目 = "数学", 成绩, 0)) As "数学", Sum(Iif(课目 = "语文", 成绩, 0)) As "语文" ;
Order By 学号 Group By 学号, 姓名 From t2
*!* 方法二:通用性 SQL 语句:(可以是更多的、不确定的课目)
Select Distinct 课目 From t2 Into Array laClasses
lcSQL = "Select 学号, 姓名 "
For Each lcClass In laClasses
lcSQL = lcSQL + ", " + "SUM( IIF(课目 = [" + lcClass + "], 成绩, 0) ) As " + lcClass
Endfor
m.lcSQL = m.lcSQL + " Order By 学号 Group By 学号, 姓名 From t2"
&lcSQL
*!* 方法三:交叉表 SQL 语句:
Do (_Genxtab) With 'xTab', .F., .F., .F. ,2, 3, 4 &&, .F., 0, .F.
Browse Last
今天累了,先做三个简单的,对付一下。
明天再做三个 Join 和子 Select 连接的 SQL 示例。
怎样?
Close Databases
*!* 构造测试数据:
Create Cursor t2 (学号 c(4),姓名 c(8),课目 c(10),成绩 N(3))
Insert Into t2 Values ("1001","张三","数学",102)
Insert Into t2 Values ("1001","张三","语文",120)
Insert Into t2 Values ("1002","李四","数学",119)
Insert Into t2 Values ("1002","李四","语文",108)
*!* 方法一:定制型 SQL 语句:
Select 学号, 姓名, Sum(Iif(课目 = "数学", 成绩, 0)) As "数学", Sum(Iif(课目 = "语文", 成绩, 0)) As "语文" ;
Order By 学号 Group By 学号, 姓名 From t2
*!* 方法二:通用性 SQL 语句:(可以是更多的、不确定的课目)
Select Distinct 课目 From t2 Into Array laClasses
lcSQL = "Select 学号, 姓名 "
For Each lcClass In laClasses
lcSQL = lcSQL + ", " + "SUM( IIF(课目 = [" + lcClass + "], 成绩, 0) ) As " + lcClass
Endfor
m.lcSQL = m.lcSQL + " Order By 学号 Group By 学号, 姓名 From t2"
&lcSQL
*!* 方法三:交叉表 SQL 语句:
Do (_Genxtab) With 'xTab', .F., .F., .F. ,2, 3, 4 &&, .F., 0, .F.
Browse Last
今天累了,先做三个简单的,对付一下。
明天再做三个 Join 和子 Select 连接的 SQL 示例。
怎样?
民工子弟学校22班团小组长阳光模特队长冲锋篮球队前锋小苹果合唱队领唱蓝天舞蹈队编舞