H5游戏开拓,消灭星星

H5游戏开辟:消灭星星

2018/01/25 · HTML5 ·
游戏

初稿出处: 坑坑洼洼实验室   

「消灭星星」是一款很非凡的「消除类游戏」,它的玩的方法很轻便:化解相连通的同色砖块。

亚洲城欢迎您 1

H5游戏开辟:一笔画

2017/11/07 · HTML5 ·
游戏

原来的文章出处: 坑坑洼洼实验室   

亚洲城欢迎您 2

SQL 练习题答案

Last updated December 5, 2012.

1. 游戏法规

「消灭星星」存在两个本子,可是它们的条条框框除了「关卡分值」有些出入外,其它的法则没什么区别的。作者介绍的版本的游戏法规整理如下:

1. 色砖遍及

  • 10 x 10 的表格
  • 5种颜色 —— 红、绿、蓝,黄,紫
  • 每类色砖个数在钦命区间内随机
  • 5类色砖在 10 x 10 表格中随机分布

2. 免去法规

七个或四个以上同色砖块相连通便是可被拔除的砖块。

3. 分值准绳

  • 裁撤防总队分值 = n * n * 5
  • 表彰总分值 = 两千 – n * n * 20

「n」表示砖块数量。上边是「总」分值的法则,还会有「单」个砖块的分值准则:

  • 解决砖块得分值 = 10 * i + 5
  • 剩余砖块扣分值 = 40 * i + 20

「i」表示砖块的索引值(从 0
开头)。轻松地说,单个砖块「得分值」和「扣分值」是三个等差数列。

4. 关卡分值

关卡分值 = 一千 + (level – 1) * 2000;「level」即最近关卡数。

5. 过关条件

  • 可免除色块海市蜃楼
  • 总结分值 >= 当前关卡分值

上边几个规范化还要创制游戏才得以过得去。

H5游戏开荒:一笔画

by leeenx on 2017-11-02

一笔画是图论[科普](https://zh.wikipedia.org/wiki/%E5%9B%BE%E8%AE%BA)中八个知名的主题素材,它源点于柯罗Surrey奥堡七桥主题素材[科普](https://zh.wikipedia.org/wiki/%E6%9F%AF%E5%B0%BC%E6%96%AF%E5%A0%A1%E4%B8%83%E6%A1%A5%E9%97%AE%E9%A2%98)。化学家欧拉在她1736年见报的舆论《柯得梅因堡的七桥》中不止化解了七桥主题素材,也提议了单笔画定理,顺带消除了一笔画难题。用图论的术语来讲,对于三个加以的连通图[科普](https://zh.wikipedia.org/wiki/%E8%BF%9E%E9%80%9A%E5%9B%BE)存在一条恰好含有全体线段何况未有重新的路径,那条路线就是「一笔画」。

招来连通图那条路线的经过就是「一笔画」的游戏进程,如下:

亚洲城欢迎您 3

 

Try searching this page for keywords like ‘segmentation’ or ‘PLY’.

2. MVC 设计方式

作者此次又是利用了 MVC
格局来写「消灭星星」。星星「砖块」的数据结构与各个状态由 Model
完毕,游戏的大目的在于 Model 中造成;View 映射 Model
的调换并做出相应的一言一动,它的职分至关心保护借使显示动画;顾客与游戏的相互由
Control 达成。

从逻辑规划上看,Model 比较重而View 与 Control
比较轻,但是,从代码量上看,View 相当重而 Model 与 Control 相对十分轻。

玩耍的落实

「单笔画」的贯彻不复杂,笔者把贯彻进度分成两步:

  1. 底图绘制
  2. 互动绘制

「底图绘制」把连通图以「点线」的方式突显在画布上,是游玩最轻巧达成的片段;「交互绘制」是顾客绘制解题路线的历程,那一个历程会入眼是处理点与点动态成线的逻辑。

一、补充作业一、

 

设有三个关系:

               S(SNO, SNAME, AGE, SEX,Sdept)

               SC(SNO, CNO, GRADE)

               C(CNO, CNAME, TEACHER)

试用关系代数表达式表示下列查询:

 

1、查询学号为S3学生所学课程的课程名与任课教师名。

  

2、查询至少选修LIU老师所教课程中一门课的女生姓名。

3、查询WANG同学不学的课程的课程号。

4、查询至少选修两门课程的学生学号。

5、查询选修课程中包含LIU老师所教全部课程的学生学号。

补充作业二、

 

三个关系同上,试用SQL语言表示下列查询:

 

1、  查询门门课程都及格的学生的学号

方法1:

提示:根据学号分组,就得到每个学生所有的课程成绩,在某个学生这一组成绩里,如果他所有的课程成绩都大于60分则输出该组学生的学号

Select sno frome sc group by sno having(min(grade)>=60)

 

2、查询既有课程大于90分又有课程不及格的学生的学号

自身连接:

Select sno from sc where grade >90 and sno in (select sno from sc where grade<60)

 

3、查询平均分不及格的课程号和平均成绩

Select cno , avg(GRADE) from sc group by cno having avg(grade)<60

查询平均分及格的课程号和课程名

Select C.cno , Cname from SC,C where C.cno=SC.cno group by C.cno having avg(grade)>=60

 

4、找出至少选修了2号学生选修过的全部课程的学生号

提示:不存在这样的课程y,学生2选修了y,而学生x没有选。

SELECT DISTINCT Sno

   FROM SC as SCX

   WHERE NOT EXISTS

      (SELECT *

       FROM SC as SCY

       WHERE SCY.Sno =‘2’AND NOT EXISTS

                               (SELECT *

                                  FROM SC SCZ

                          WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))



5、求各门课程去掉一个最高分和最低分后的平均分

第一步,求所有成绩的平均分(去掉一个最高分和最低分)

select   avg(GRADE)   from   SC       where   GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE)     and     GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE   desc)  

第二步,将所有成绩按各门课程的课程号CNO分组

SELECT CNO avg(GRADE)   from   SC       where   GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE)     and     GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE   desc) group by CNO

If you would like to contribute links, please e-mail them
torms@dgp.toronto.edu.

3. Model

10 x 10 的表格用长度为 100 的数组可周全映射游戏的一定量「砖块」。

[ R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G,
G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y,
Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R,
R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B,
B, Y, Y, P, P ]

1
2
3
4
5
6
7
8
9
10
11
12
[
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P
]

Highlander – 红棕,G – 翠绿,B – 碧绿,Y – 深紫,P – 玉石白。Model
的主导任务是以下多个:

  • 变动砖墙
  • 排除砖块 (生成砖块分值)
  • 坚实砖墙
  • 撤废残砖 (生成表彰分值)

底图绘制

「一笔画」是多关卡的嬉戏格局,笔者决定把关卡(连通图)的定制以七个布局接口的款型对外揭露。对外暴露关卡接口必要有一套描述连通图形状的正统,而在小编前面有七个采取:

  • 点记法
  • 线记法

举个连通图 —— 五角星为例来讲一下那七个选项。

亚洲城欢迎您 4

点记法如下:

JavaScript

levels: [ // 当前关卡 { name: “五角星”, coords: [ {x: Ax, y: Ay}, {x:
Bx, y: By}, {x: Cx, y: Cy}, {x: Dx, y: Dy}, {x: Ex, y: Ey}, {x: Ax, y:
Ay} ] } … ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
levels: [
// 当前关卡
{
name: "五角星",
coords: [
{x: Ax, y: Ay},
{x: Bx, y: By},
{x: Cx, y: Cy},
{x: Dx, y: Dy},
{x: Ex, y: Ey},
{x: Ax, y: Ay}
]
}
]

线记法如下:

JavaScript

levels: [ // 当前关卡 { name: “五角星”, lines: [ {x1: Ax, y1: Ay, x2:
Bx, y2: By}, {x1: Bx, y1: By, x2: Cx, y2: Cy}, {x1: Cx, y1: Cy, x2: Dx,
y2: Dy}, {x1: Dx, y1: Dy, x2: Ex, y2: Ey}, {x1: Ex, y1: Ey, x2: Ax, y2:
Ay} ] } ]

1
2
3
4
5
6
7
8
9
10
11
12
13
levels: [
// 当前关卡
{
name: "五角星",
lines: [
{x1: Ax, y1: Ay, x2: Bx, y2: By},
{x1: Bx, y1: By, x2: Cx, y2: Cy},
{x1: Cx, y1: Cy, x2: Dx, y2: Dy},
{x1: Dx, y1: Dy, x2: Ex, y2: Ey},
{x1: Ex, y1: Ey, x2: Ax, y2: Ay}
]
}
]

「点记法」记录关卡通过海关的贰个答案,即端点要按自然的顺序存放到数组
coords中,它是有序性的记录。「线记法」通过两点描述连通图的线条,它是冬季的笔录。「点记法」最大的优势是表现更简洁,但它必需记录二个通过海关答案,作者只是关卡的搬运工不是关卡创设者,所以作者最终选项了「线记法」。:)

 

Papers & Archives

3.1 生成砖墙

砖墙分两步生成:

  • 色砖数量分配
  • 打垮色砖

评论上,能够将 100 个格子能够均分到 5
类颜色,但是小编玩过的「消灭星星」都不采取均分政策。通过深入分析五款「消灭星星」,其实能够窥见贰个原理
—— 「色砖之间的数量差在二个一定的间隔内」。

倘诺把守旧意义上的均分称作「完全均分」,那么「消灭星星」的分红是一种在均分线上下波动的「不完全均分」。

亚洲城欢迎您 5

作者把上面包车型地铁「不完全均分」称作「波动均分」,算法的有血有肉落实能够敬仰「不定均分算法」。

「打散色砖」其实正是将数组乱序的进度,作者推荐应用「
费雪耶兹乱序算法」。

以下是伪代码的兑现:

JavaScript

// 波动均分色砖 waveaverage(5, 4, 4).forEach( // tiles 即色墙数组
(count, clr) => tiles.concat(generateTiles(count, clr)); ); //
打垮色砖 shuffle(tiles);

1
2
3
4
5
6
7
// 波动均分色砖
waveaverage(5, 4, 4).forEach(
// tiles 即色墙数组
(count, clr) => tiles.concat(generateTiles(count, clr));
);
// 打散色砖
shuffle(tiles);

相互绘制

在画布上绘制路线,从视觉上正是「选用或一而再连通图端点」的进度,这些历程须求缓慢解决2个难点:

  • 手指下是还是不是有端点
  • 入选点到待选中式茶食时期是或不是成线

访问连通图端点的坐标,再监听手指滑过的坐标能够领悟「手指下是或不是有一点」。以下伪代码是采撷端点坐标:

JavaScript

// 端点坐标新闻 let coords = []; lines.forEach(({x1, y1, x2, y2})
=> { // (x1, y1) 在 coords 数组空头支票 if(!isExist(x1, y1))
coords.push([x1, y1]); // (x2, y2) 在 coords 数组不设有
if(!isExist(x2, y2)) coords.push([x2, y2]); });

1
2
3
4
5
6
7
8
// 端点坐标信息
let coords = [];
lines.forEach(({x1, y1, x2, y2}) => {
// (x1, y1) 在 coords 数组不存在
if(!isExist(x1, y1)) coords.push([x1, y1]);
// (x2, y2) 在 coords 数组不存在
if(!isExist(x2, y2)) coords.push([x2, y2]);
});

以下伪代码是监听手指滑动:

H5游戏开拓,消灭星星。JavaScript

easel.addEventListener(“touchmove”, e => { let x0 =
e.targetTouches[0].pageX, y0 = e.targetTouches[0].pageY; // 端点半径
—— 取连通图端点半径的2倍,升高活动端体验 let r = radius * 2;
for(let [x, y] of coords){ if(Math.sqrt(Math.pow(x – x0, 2) +
Math.pow(y – y0), 2) <= r){ // 手指下有端点,判别是不是连线
if(canConnect(x, y)) { // todo } break; } } })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
easel.addEventListener("touchmove", e => {
let x0 = e.targetTouches[0].pageX, y0 = e.targetTouches[0].pageY;
// 端点半径 —— 取连通图端点半径的2倍,提升移动端体验
let r = radius * 2;
for(let [x, y] of coords){
if(Math.sqrt(Math.pow(x – x0, 2) + Math.pow(y – y0), 2) <= r){
// 手指下有端点,判断能否连线
if(canConnect(x, y)) {
// todo
}
break;
}
}
})

在未绘制任何线段或端点此前,手指滑过的大肆端点都会被当做「一笔画」的发轫点;在绘制了线段(或有选中式茶食)后,手指滑过的端点能还是无法与选中式茶食串连成线段须求依附现存条件实行推断。

亚洲城欢迎您 6

上海教室,点A与点B可连日来成线段,而点A与点C不可能再三再四。作者把「能够与钦定端点连接成线段的端点称作可行连接点」。连通图端点的可行连接点从连通图的线条中提取:

JavaScript

coords.forEach(coord => { // 有效连接点(坐标)挂载在端点坐标下
coord.validCoords = []; lines.forEach(({x1, y1, x2, y2}) => { //
坐标是日前线段的起源 if(coord.x === x1 && coord.y === y1) {
coord.validCoords.push([x2, y2]); } // 坐标是最近线段的极端 else
if(coord.x === x2 && coord.y === y2) { coord.validCoords.push([x1,
y1]); } }) })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
coords.forEach(coord => {
// 有效连接点(坐标)挂载在端点坐标下
coord.validCoords = [];
lines.forEach(({x1, y1, x2, y2}) => {
// 坐标是当前线段的起点
if(coord.x === x1 && coord.y === y1) {
coord.validCoords.push([x2, y2]);
}
// 坐标是当前线段的终点
else if(coord.x === x2 && coord.y === y2) {
coord.validCoords.push([x1, y1]);
}
})
})

But…有效连接点只好决断几个点是还是不是为底图的线条,那只是一个静态的参照,在事实上的「交互绘制」中,会遇上以下情形:

亚洲城欢迎您 7
如上图,AB已串连成线段,当前选中式茶食B的有效性连接点是 A 与 C。AB
已经三回九转成线,借使 BA 也串连成线段,那么线段就再度了,所以那时候 BA
不能够成线,唯有 AC 技能成线。

对选中式茶食来说,它的平价连接点有二种:

  • 与选中式茶食「成线的行之有效连接点」
  • 与选中式点心「未成线的卓有效率连接点」

内部「未成线的管用连接点」手艺参预「交互绘制」,何况它是动态的。

亚洲城欢迎您 8

回头本节内容起头提的多少个难点「手指下是还是不是有端点」 与
「选中式点心到待选中式茶食之间是不是成线」,其实可统一为一个难题:手指下是或不是留存「未成线的有效性连接点」。只须把监听手指滑动遍历的数组由连通图全体的端点坐标
coords 替换为眼下选中式茶食的「未成线的实惠连接点」就能够。

时至今天「一笔画」的重要功能已经落到实处。能够当先体验一下:

亚洲城欢迎您 9

 1、查询7号课程没有考试成绩的学生学号。

Graphics Conference Paper Link
Archive(Ke-Sen
Huang)

3.2 消除砖块

「消除砖块」的平整相当粗略 —— 隔壁相连通一样色即能够解除

亚洲城欢迎您 10
前四个结合符合「相邻相连通一样色即能够防去」,所以它们能够被排除;第多个结合就算「相邻一样色」然而不「相衔接」所以它不可能被化解。

「消除砖块」的同期有一个至关心珍视要的义务:生成砖块对应的分值。在「游戏法则」中,小编曾经提供了相应的数学公式:「化解砖块得分值
= 10 * i + 5」。

「消除砖块」算法完成如下:

JavaScript

function clean(tile) { let count = 1; let sameTiles =
searchSameTiles(tile); if(sameTiles.length > 0) { deleteTile(tile);
while(true) { let nextSameTiles = []; sameTiles.forEach(tile => {
nextSameTiles.push(…searchSameTiles(tile)); makeScore(++count * 10 +
5); // 标识当前分值 deleteTile(tile); // 删除砖块 }); //
清除达成,跳出循环 if(nextSameTiles.length === 0) break; else {
sameTiles = next萨姆eTiles; } } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
function clean(tile) {
let count = 1;
let sameTiles = searchSameTiles(tile);
if(sameTiles.length > 0) {
deleteTile(tile);
while(true) {
let nextSameTiles = [];
sameTiles.forEach(tile => {
nextSameTiles.push(…searchSameTiles(tile));
makeScore(++count * 10 + 5); // 标记当前分值
deleteTile(tile); // 删除砖块
});
// 清除完成,跳出循环
if(nextSameTiles.length === 0) break;
else {
sameTiles = nextSameTiles;
}
}
}
}

免去的算法使用「递归」逻辑上会清晰一些,但是「递归」在浏览器上轻易「栈溢出」,所以小编没有使用「递归」达成。

电动识图

笔者在录加入关贸总协定组织卡配置时,开采贰个7条边以上的连片图很轻巧录错或录重线段。作者在思索是还是不是开垦贰个自动识别图形的插件,毕竟「一笔画」的图样是有准则的几何图形。

亚洲城欢迎您 11

地点的关卡「底图」,一眼就可以识出多个颜色:

  • 白底
  • 端点颜色
  • 线条颜色

与此同不时候那二种颜色在「底图」的面积大小顺序是:白底 > 线段颜色 >
端点颜色。底图的「收罗色值表算法」比比较粗略,如下伪代码:

JavaScript

let imageData = ctx.getImageData(); let data = imageData.data; // 色值表
let clrs = new Map(); for(let i = 0, len = data.length; i < len; i +=
4) { let [r, g, b, a] = [data[i], data[i + 1], data[i + 2],
data[i + 3]]; let key = `rgba(${r}, ${g}, ${b}, ${a})`; let value =
clrs.get(key) || {r, g, b, a, count: 0}; clrs.has(key) ? ++value.count :
clrs.set(rgba, {r, g, b, a, count}); }

1
2
3
4
5
6
7
8
9
10
let imageData = ctx.getImageData();
let data = imageData.data;
// 色值表
let clrs = new Map();
for(let i = 0, len = data.length; i < len; i += 4) {
let [r, g, b, a] = [data[i], data[i + 1], data[i + 2], data[i + 3]];
let key = `rgba(${r}, ${g}, ${b}, ${a})`;
let value = clrs.get(key) || {r, g, b, a, count: 0};
clrs.has(key) ? ++value.count : clrs.set(rgba, {r, g, b, a, count});
}

对于连通图来讲,只要把端点识别出来,连通图的轮廓也就出来了。

    Select sno fromsc where cno=’7′ and
grade is null

Reproducible
Researcharchive
(image processing, vision, machine learning) (Xin Li)

3.3 抓好砖墙

砖墙在破除了一些砖石后,会出现空洞,此时必要对墙体实行抓实:

向下夯实 向左夯实 向左下夯实(先下后左)

一种高效的兑现方案是,每一遍「解决砖块」后直接遍历砖墙数组(10×10数组)再把空洞压实,伪代码表示如下:

JavaScript

for(let row = 0; row < 10; ++row) { for(let col = 0; col < 10;
++col) { if(isEmpty(row, col)) { // 水平方向(向左)加强if(isEmptyCol(col)) { tampRow(col); } // 垂直方向(向下)压实 else {
tampCol(col); } break; } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for(let row = 0; row < 10; ++row) {
for(let col = 0; col < 10; ++col) {
if(isEmpty(row, col)) {
// 水平方向(向左)夯实
if(isEmptyCol(col)) {
tampRow(col);
}
// 垂直方向(向下)夯实
else {
tampCol(col);
}
break;
}
}
}

But…
为了坚实七个华而不实对一张大数组举行全量遍历并不是一种高效的算法。在小编看来影响「墙体坚实」功效的成分有:

  1. 固定空洞
  2. 砖块移动(抓好)

举目四望墙体数组的第一指标是「定位空洞」,但能无法不扫描墙体数组直接「定位空洞」?

墙体的「空洞」是出于「消除砖块」形成的,换种说法 ——
被扫除的砖头留下来的坑位正是墙体的肤浅。在「化解砖块」的同偶然候标识空洞的职务,那样就毫无全量扫描墙体数组,伪代码如下:

JavaScript

function deleteTile(tile) { // 标志空洞 markHollow(tile.index); //
删除砖块逻辑 … }

1
2
3
4
5
6
function deleteTile(tile) {
// 标记空洞
markHollow(tile.index);
// 删除砖块逻辑
}

在上边的压实动图,其实能够阅览它的抓实进度如下:

  1. 空洞上方的砖块向下活动
  2. 空驶列车左侧的砖块向左移动

墙体在「抓实」进度中,它的界限是实时在调换,假使「抓好」不按实际边界进行围观,会时有产生多余的空域扫描:

亚洲城欢迎您 12

什么样记录墙体的边际?
把墙体拆分成三个个单独的列,那么列最最上端的空白格片段正是墙体的「空白」,而别的非最上端的空白格片段即墙体的「空洞」。

亚洲城欢迎您 13

小编使用一组「列集结」来陈述墙体的界限并记下墙体的肤浅,它的模型如下:

JavaScript

/* @ count – 列砖块数 @ start – 顶端行索引 @ end – 尾部行索引 @
pitCount – 坑数 @ topPit – 最顶端的坑 @ bottomPit – 最底部的坑 */ let
wall = [ {count, start, end, pitCount, topPit, bottomPit}, {count,
start, end, pitCount, topPit, bottomPit}, … ];

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
@ count – 列砖块数
@ start – 顶部行索引
@ end – 底部行索引
@ pitCount – 坑数
@ topPit – 最顶部的坑
@ bottomPit – 最底部的坑
*/
let wall = [
{count, start, end, pitCount, topPit, bottomPit},
{count, start, end, pitCount, topPit, bottomPit},
];

其一模型能够描述墙体的八个细节:

  • 空列
  • 列的总是空洞
  • 列的非一而再空洞
JavaScript

// 空列 if(count === 0) { ... } // 连续空洞 else if(bottomPit -
topPit + 1 === pitCount) { ... } // 非连续空洞 else { ... }

<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td><div class="crayon-nums-content" style="font-size: 13px !important; line-height: 15px !important;">
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-1">
1
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-2">
2
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-3">
3
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-4">
4
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-5">
5
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-6">
6
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-7">
7
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-8">
8
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-9">
9
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-10">
10
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-11">
11
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-12">
12
</div>
</div></td>
<td><div class="crayon-pre" style="font-size: 13px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
<div id="crayon-5b8f3d2c2df29914802382-1" class="crayon-line">
// 空列
</div>
<div id="crayon-5b8f3d2c2df29914802382-2" class="crayon-line crayon-striped-line">
if(count === 0) { 
</div>
<div id="crayon-5b8f3d2c2df29914802382-3" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-4" class="crayon-line crayon-striped-line">
}
</div>
<div id="crayon-5b8f3d2c2df29914802382-5" class="crayon-line">
// 连续空洞
</div>
<div id="crayon-5b8f3d2c2df29914802382-6" class="crayon-line crayon-striped-line">
else if(bottomPit - topPit + 1 === pitCount) { 
</div>
<div id="crayon-5b8f3d2c2df29914802382-7" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-8" class="crayon-line crayon-striped-line">
}
</div>
<div id="crayon-5b8f3d2c2df29914802382-9" class="crayon-line">
// 非连续空洞
</div>
<div id="crayon-5b8f3d2c2df29914802382-10" class="crayon-line crayon-striped-line">
else {
</div>
<div id="crayon-5b8f3d2c2df29914802382-11" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-12" class="crayon-line crayon-striped-line">
}
</div>
</div></td>
</tr>
</tbody>
</table>

砖块在化解后,映射到单个列上的空洞会有二种布满形态 —— 再三再四与非一而再。

亚洲城欢迎您 14

「延续空洞」与「非接二连三空洞」的抓牢进度如下:

亚洲城欢迎您 15

事实上「空驶列车」放大于墙体上,也有「空洞」类似的布满形态 ——
一而再与非再三再四。
亚洲城欢迎您 16

它的坚实进度与充饥画饼类似,这里就不赘述了。

端点识别

理论上,通过访问的「色值表」能够一贯把端点的坐标记别出来。作者设计的「端点识别算法」分以下2步:

  1. 按像素扫描底图直到碰着「端点颜色」的像素,步向第二步
  2. 从底图上排除端点并记录它的坐标,再次来到继续第一步

伪代码如下:

JavaScript

for(let i = 0, len = data.length; i < len; i += 4) { let [r, g, b,
a] = [data[i], data[i + 1], data[i + 2], data[i + 3]]; //
当前像素颜色属于端点 if(isBelongVertex(r, g, b, a)) { // 在 data
中清空端点 vertex = clearVertex(i); // 记录端点消息vertexes.push(vertext); } }

1
2
3
4
5
6
7
8
9
10
for(let i = 0, len = data.length; i < len; i += 4) {
let [r, g, b, a] = [data[i], data[i + 1], data[i + 2], data[i + 3]];
// 当前像素颜色属于端点
if(isBelongVertex(r, g, b, a)) {
// 在 data 中清空端点
vertex = clearVertex(i);
// 记录端点信息
vertexes.push(vertext);
}
}

But…
下边包车型地铁算法只好跑无损图。作者在采用了一张手提式有线电话机截屏做测量试验的时候开掘,搜罗到的「色值表」长度为
四千+ !那平素形成端点和线条的色值无法直接获取。

透过深入分析,能够窥见「色值表」里超越八分之四色值都以近乎的,相当于在本来的「搜罗色值表算法」的功底上增多一个类似颜色过滤即能够找寻端点和线条的主色。伪代码完结如下:

JavaScript

let lineColor = vertexColor = {count: 0}; for(let clr of clrs) { //
与底色相近,跳过 if(isBelongBackground(clr)) continue; //
线段是数据第二多的颜料,端点是第三多的颜色 if(clr.count >
lineColor.count) { [vertexColor, lineColor] = [lineColor, clr] } }

1
2
3
4
5
6
7
8
9
let lineColor = vertexColor = {count: 0};
for(let clr of clrs) {
// 与底色相近,跳过
if(isBelongBackground(clr)) continue;
// 线段是数量第二多的颜色,端点是第三多的颜色
if(clr.count > lineColor.count) {
[vertexColor, lineColor] = [lineColor, clr]
}
}

取到端点的主色后,再跑贰遍「端点识别算法」后居识别出 203
个端点!那是为什么吗?

亚洲城欢迎您 17

上海教室是放手5倍后的底图局地,金红端点的周边和中间充斥着大批量噪点(杂色块)。事实上在「端点识别」进程中,由于噪点的留存,把原先的端点被分解成十八个或数11个小端点了,以下是跑过「端点识别算法」后的底图:

亚洲城欢迎您 18

经过上航海用教室,能够直观地搜查捕获四个结论:识别出来的小端点只在目的(大)端点上汇集布满,并且大端点范围内的小端点叠加交错。

假诺把叠合交错的小端点归并成一个多边点,那么那些大端点将不胜好像目的端点。小端点的集结伪代码如下:

JavaScript

for(let i = 0, len = vertexes.length; i < len – 1; ++i) { let vertexA
= vertexes[i]; if(vertextA === undefined) continue; // 注意这里 j = 0
并不是 j = i +1 for(let j = 0; j < len; ++j) { let vertexB =
vertexes[j]; if(vertextB === undefined) continue; //
点A与点B有增大,点B合併到点A并删除点B if(is克罗斯(vertexA, vertexB)) {
vertexA = merge(vertexA, vertexB); delete vertexA; } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
for(let i = 0, len = vertexes.length; i < len – 1; ++i) {
let vertexA = vertexes[i];
if(vertextA === undefined) continue;
// 注意这里 j = 0 而不是 j = i +1
for(let j = 0; j < len; ++j) {
let vertexB = vertexes[j];
if(vertextB === undefined) continue;
// 点A与点B有叠加,点B合并到点A并删除点B
if(isCross(vertexA, vertexB)) {
vertexA = merge(vertexA, vertexB);
delete vertexA;
}
}
}

加了小端点归并算法后,「端点识别」的准确度就上去了。经我本地质衡量试已经得以
百分百 识别有损的连结图了。

 

Mesh Libraries and Tools

3.4 解决残砖

上一小节提到了「描述墙体的分界并记录墙体的悬空」的「列群集」,笔者是一贯运用这些「列群集」来化解残砖的,伪代码如下:

JavaScript

function clearAll() { let count = 0; for(let col = 0, len =
this.wall.length; col < len; ++col) { let colInfo = this.wall[col];
for(let row = colInfo.start; row <= colInfo.end; ++row) { let tile =
this.grid[row * this.col + col]; tile.score = -20 – 40 * count++; //
标识表彰分数 tile.removed = true; } } }

1
2
3
4
5
6
7
8
9
10
11
function clearAll() {
let count = 0;
for(let col = 0, len = this.wall.length;  col < len; ++col) {
let colInfo = this.wall[col];
for(let row = colInfo.start; row <= colInfo.end; ++row) {
let tile = this.grid[row * this.col + col];
tile.score = -20 – 40 * count++; // 标记奖励分数
tile.removed = true;
}
}
}

线条识别

作者分五个步骤达成「线段识别」:

  1. 加以的五个端点连接成线,并征集连线上N个「样本点」;
  2. 遍历样本点像素,如若像素色值不对等线段色值则意味着那七个端点之间不设有线段

怎么着搜集「样式点」是个难点,太密集会潜濡默化属性;太疏松精准度不能够保险。

在小编前面有四个选项:N 是常量;N 是变量。
假设 N === 5。局地提取「样式点」如下:

亚洲城欢迎您 19

上海教室,会识别出三条线条:AB, BC 和 AC。而事实上,AC不可能成线,它只是因为
AB 和 BC 视觉上共一线的结果。当然把 N 值向上提升能够解决这一个难点,不过 N
作为常量的话,那些常量的取量供给靠经验来判别,果然甩掉。

为了防止 AB 与 BC 同处一直线时 AC 被识别成线段,其实很简单 ——
四个「样本点」的区间小于或等于端点直径
假设 N = S / (2 * R),S 代表两点的偏离,翼虎代表端点半径。局地提取「样式点」如下:

亚洲城欢迎您 20

如上航海用体育场所,成功地绕过了 AC。「线段识别算法」的伪代码完毕如下:

JavaScript

for(let i = 0, len = vertexes.length; i < len – 1; ++i) { let {x: x1,
y: y1} = vertexes[i]; for(let j = i + 1; j < len; ++j) { let {x:
x2, y: y2} = vertexes[j]; let S = Math.sqrt(Math.pow(x1 – x2, 2) +
Math.pow(y1 – y2, 2)); let N = S / (R * 2); let stepX = (x1 – x2) / N,
stepY = (y1 – y2) / n; while(–N) { // 样本点不是线段色
if(!isBelongLine(x1 + N * stepX, y1 + N * stepY)) break; } //
样本点都合格 —- 表示两点成线,保存 if(0 === N) lines.push({x1, y1, x2,
y2}) } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for(let i = 0, len = vertexes.length; i < len – 1; ++i) {
let {x: x1, y: y1} = vertexes[i];
for(let j = i + 1; j < len; ++j) {
let {x: x2, y: y2} = vertexes[j];
let S = Math.sqrt(Math.pow(x1 – x2, 2) + Math.pow(y1 – y2, 2));
let N = S / (R * 2);
let stepX = (x1 – x2) / N, stepY = (y1 – y2) / n;
while(–N) {
// 样本点不是线段色
if(!isBelongLine(x1 + N * stepX, y1 + N * stepY)) break;
}
// 样本点都合格 —- 表示两点成线,保存
if(0 === N) lines.push({x1, y1, x2, y2})
}
}

 2、查询7号课程成绩在90分以上或60分以下的上学的小孩子学号。

Surface_Mesh(D.
Sieger, M. Botsch)

4. View

View 首要的职能有八个:

  • UI 管理
  • 映射 Model 的变化(动画)

UI
管理重视是指「分界面绘制」与「财富加载管理」,这两项功效相比较遍布本文就径直略过了。View
的重视是「映射 Model
的变迁」并达成对应的动画片。动画是错综复杂的,而映射的规律是归纳的,如下伪代码:

JavaScript

update({originIndex, index, clr, removed, score}) { // 还从未
originIndex 或未有色值,直接不管理 if(originIndex === undefined || clr
=== undefined) return ; let tile = this.tiles[originIndex]; // tile
存在,剖断颜色是还是不是一致 if(tile.clr !== clr) { this.updateTileClr(tile,
clr); } // 当前目录变化 —– 表示地点也许有变化 if(tile.index !== index)
{ this.updateTileIndex(tile, index); } // 设置分数 if(tile.score !==
score) { tile.score = score; } if(tile.removed !== removed) { //
移除或加多当前节点 true === removed ? this.bomb(tile) :
this.area.addChild(tile.sprite); tile.removed = removed; } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
update({originIndex, index, clr, removed, score}) {
// 还没有 originIndex 或没有色值,直接不处理
if(originIndex === undefined || clr === undefined) return ;
let tile = this.tiles[originIndex];
// tile 存在,判断颜色是否一样
if(tile.clr !== clr) {
this.updateTileClr(tile, clr);
}
// 当前索引变化 —– 表示位置也有变化
if(tile.index !== index) {
this.updateTileIndex(tile, index);
}
// 设置分数
if(tile.score !== score) {
tile.score = score;
}
if(tile.removed !== removed) {
// 移除或添加当前节点
true === removed ? this.bomb(tile) : this.area.addChild(tile.sprite);
tile.removed = removed;
}
}

Model 的砖块每趟数据的更改都会文告到 View 的砖块,View
会遵照对应的变型做相应的动作(动画)。

属性优化

由于「自动识图」供给对图像的的像素点实行扫描,那么质量确实是个供给关切的标题。小编设计的「自动识图算法」,在辨认图像的长河中要求对图像的像素做一回扫描:「采撷色值表」
与 「搜集端点」。在扫描次数上实际很难下跌了,可是对于一张 750 * 1334
的底图来讲,「自动识图算法」须求遍历四遍长度为
750 * 1334 * 4 = 4,002,000
的数组,压力依然会有个别。笔者是从压缩被围观数组的尺码来提高质量的。

被扫描数组的尺寸怎么削减?
小编直接通过裁减画布的尺寸来完结降低被扫描数组尺寸的。伪代码如下:

JavaScript

// 要减弱的倍数 let resolution = 4; let [width, height] = [img.width
/ resolution >> 0, img.height / resolution >> 0];
ctx.drawImage(img, 0, 0, width, height); let imageData =
ctx.getImageData(), data = imageData;

1
2
3
4
5
// 要压缩的倍数
let resolution = 4;
let [width, height] = [img.width / resolution >> 0, img.height / resolution >> 0];
ctx.drawImage(img, 0, 0, width, height);
let imageData = ctx.getImageData(), data = imageData;

把源图片减弱4倍后,获得的图纸像素数组唯有原本的
4^2 = 16倍。那在性质上是非常大的晋级换代。

Select sno from sc where cno=’7′ and grade
not between 60and 90

GTS(2D
dynamic/constrained Delaunay triangulation, robust geometric predicates,
mesh boolean set operations, refinement/coarsening, view-independent
continuous LOD, view-dependent LOD, AABB-trees, Kd-trees, graph
partitioning, isosurfacing, area, volume, mean/gaussian/principal
curvature, stripification)

5. Control

Control 要管理的作业相当多,如下:

  • 绑定 Model & View
  • 浮动通关分值
  • 判别通关条件
  • 对外交事务件
  • 客商交互

开始化时,Control 把 Model 的砖块单向绑定到 View 的砖块了。如下:

Object.defineProperties(model.tile, { originIndex: { get() {…}, set(){
… view.update({originIndex}) } }, index: { get() {…}, set() { …
view.update({index}) } }, clr: { get() {…}, set() { …
view.update({clr}) } }, removed: { get() {…}, set() { …
view.update({removed}) } }, score: { get() {…}, set() { …
view.update({score}) } } })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Object.defineProperties(model.tile, {
    originIndex: {
        get() {…},
        set(){
            …
            view.update({originIndex})
        }
    },  
    index: {
        get() {…},
        set() {
            …
            view.update({index})
        }
    },
    clr: {
        get() {…},
        set() {
            …
            view.update({clr})
        }
    },
    removed: {
        get() {…},
        set() {
            …
            view.update({removed})
        }
    },  
    score: {
        get() {…},
        set() {
            …
            view.update({score})
        }
    }
})
 

「通过海关分值」与「判别通关条件」那对逻辑在本文的「游戏准绳」中有相关介绍,这里不再赘述。

对外交事务件规划如下:

name detail
pass 通关
pause 暂停
resume 恢复
gameover 游戏结束

客户交互 APIs 规划如下:

name type deltail
init method 初始化游戏
next method 进入下一关
enter method 进入指定关卡
pause method 暂停
resume method 恢复
destroy method 销毁游戏

选用「自动识图」的建议

纵然小编在地头测验的时候能够把具备的「底图」识别出来,然则并无法保险别的开辟者上传的图样是不是被很好的识别出来。我建议,能够把「自动识图」做为二个独门的工具使用。

小编写了三个「自动识图」的单身工具页面:
能够在这一个页面生成对应的卡子配置。

 

trimesh2 mesh
library(read
PLY/OFF/3DS/OBJ, write PLY/OFF/OBJ, subdivision, smoothing, curvature
estimation, triangle stripping, ICP, cleanup, decimation, basic shapes)
(S. Rusinkiewicz)

6. 问题

在果壳网有二个有关「消灭星星」的话题:popstar关卡是何等规划的?

以此话题在最后提议了一个主题材料 ——
「不能够排除和最大得分不满足过关条件的矩阵」

亚洲城欢迎您 21

「不也许化解的矩阵」其实正是最大得分为0的矩阵,本质上是「最大得分不满足过关条件的矩阵」。

最大得分不满意过关条件的矩阵
求「矩阵」的最大得分是三个「马鞍包难题」,求解的算法不难:对现阶段矩阵用「递归」的方式把持有的消灭分支都施行贰遍,并取最高分值。但是javascript 的「递归」极易「栈溢出」导致算法不能实践。

实则在腾讯网的话题中涉嫌三个技术方案:

网络查到鱼贯而入提出做个工具随便生成关卡,自动总结,把符合得分条件的关卡筛选出来

以此实施方案代价是昂贵的!作者提供有源码并未减轻这几个标题,而是用三个比较取巧的法子:进去游玩前检查是事为「无法排除矩阵」,倘诺是再度生成关卡矩阵

小心:小编利用的取巧方案并未有消除难题。

结语

上边是本文介绍的「一笔画」的线上
DEMO 的二维码:

亚洲城欢迎您 22

游玩的源码托管在:
里头玩耍达成的主心骨代码在:
自行识图的代码在:

感谢耐心阅读完本小说的读者。本文仅表示小编的个人观点,如有不妥之处请不吝赐教。

谢谢您的读书,本文由 坑坑洼洼实验室
版权全部。借使转发,请证明出处:凹凸实验室()

1 赞 1 收藏
评论

亚洲城欢迎您 23

 3、查询课程名以“数据”七个字开端的全部科目标课程号和课程名。

OpenMesh(PLY/OBJ,
halfedge mesh, decimation, Loop/Sqrt3 subdivision, view-dependent
progressive meshes, stripification) (J. Möbius, M. Habbecke)

7. 结语

上面是本文介绍的「消灭星星」的线上 DEMO 的二维码:

亚洲城欢迎您 24

娱乐的源码托管在:

谢谢耐心阅读完本小说的读者。本文仅表示作者的个人观点,如有不妥之处请不吝赐教。
假定对「H5游戏开荒」感兴趣,招待关切大家的专栏。

Select cno,cname from c where cname like
‘数据%’

CGAL(2D/3D halfedge
mesh, tet meshes) [full packages
list]
(Authors)

参谋资料

  • Knapsack problem
  • NP-completeness
  • popstar关卡是何许统一希图的?
  • 费雪耶兹乱序算法
  • 不定均分算法

    1 赞 收藏
    评论

亚洲城欢迎您 25

 

MeshLab(import
PLY/STL/OFF/OBJ/3DS/COLLADA/PTX/V3D/PTS/APTS/XYZ/GTS/TRI/ASC/X3D/X3DV/VRML/ALN,
export PLY/STL/OFF/OBJ/3DS/COLLADA/VRML/DXF/GTS/U3D/IDTF/X3D,
selection/smoothing painting, linear measurements, export planar slices,
mesh decimation/repair/optimization, mesh alignment) [All
Filters]
(Authors)

 4、查询各样学员具有课程的平分成绩,输出学生学号和平均成绩。

OpenFlipper(import/export
OFF/OBJ/PLY/STL/STLA/STLB/OM, selection tools w/ surface/volume lasso
and sphere brush, decimation, smoothing, edge/face editing) (J. Möbius)

    Select sno,avg(grade)from sc group by
sno

mview(read/view
PMesh/GTS/OFF/COFF/PLY/VRML/Shallo/VTK ASCII POLYDATA/OBJ) (H. Cantzler,
T. Breckon)

 5、查询每门课程的选修人数,输出课程号和选修人数。

PLY
Tools(read/write
PLY)

    Selectcno,count(*) from sc group by
cno

ply2vri(convert
PLY mesh to signed-distance volumetric grid, VRI/PPM formats) (B.Allen)

 6、查询选修7号课程的学员的学号、姓名、性别。

JMeshLib(read/write
OFF/PLY/STL/VRML1/VRML2/OBJ/IV 2.1) (M. Attene)

    Selects.sno,sname,ssex from s,sc where
s.sno=sc.sno and cno=’7′

ReMESH(automatic
manifold repair, isolated component removal, hole filling, handle
removal, degenerate triangle removal, sharp feature recovery, defect
detection and hilighting, manual repair tools) (M. Attenne)

    或: Select sno,sname,ssex from s
where sno in

GPUmesh – Easy Cross-Plateform Cross-API Mesh Management for
GPUs(S.
Lefebvre)

              ( Select sno from sc where
cno=’7′ )

A48:
A Dynamic Adaptive Mesh Library based on Stellar Operators (L. Velho)

 7、查询选修7号课程的学生的平均年龄。

Volumetric
Mesh:
tetrahedral and cube volumetric 3D meshes (J. Barbic)

    Selectavg(sage) from s,sc where
s.sno=sc.sno and cno=’7′

3d-workspace(quadric
mesh simplification, re-meshing,
recursive/monte-carlo/sphere-packing/voxel sampling, skeleton
extraction, Laplacian/scale-dependent/mean-curvature-flow smoothing,
Sqrt3/Loop/modified-butterfly/longest-edge subdivision, minimum bounding
box, mean value coordinates, Green coordinates, curvature (polynomial
fitting, two other implementations), FFD, voxel deformation, skinning
with dual quaternions, mesh voxelization, octree, kd-tree, colormap,
mesh slicing)
(Authors)

    或: Select avg(sage) from s where sno
in

SimplexMesh:
general non-manifold, non-regular simplicial mesh for mixed dimensions
\leq 3 (C. Batty)

              (Select sno from sc where
cno=’7′ )

Shape Modeling

 8、查询有30名上述学生选修的课程号。

CGAL Subdivision
Demo亚洲城欢迎您,(Sqrt3,
Quad-Triangle, Catmull-Clark, Doo-Sabin) (L.-J. Shiue, P. Alliez, R.
Ursu, L. Kettner)

    Select cno fromsc group by cno having
count(*)>30

interactive mesh
deformation(S.
Yoshizawa)

 9、查询于今并未有考试比不上格的学童学号。

manifold harmonics shape
deformation(B.
Vallet, B. Lévy)

    Select distinctsno from sc where sno
not in

Graphite(Catmull-Clark
subdivision)
(Authors)

         ( Select sno from sc where
grade<60 )

MeshLab(loop/butterfly/midpoint
subdivision)
(Authors)

    或: Select sno from sc group by sno
havingmin(grade)>=60

3D Model Synthesis from
examples(P.
Merrell)

10、查询全部考试成绩的平均分同样的学员学号分组

ShapeShop: sketch-based hierarchical implicit surface (“BlobTree”)
modeling (and
SurfaceTree)(R.
Schmidt, B. Wyvill, K. Singh)

二、

Real-time implicit surface
polygonizer(R.
Schmidt)

 1、寻找选修课程号为C2的学员学号与成就。

As-rigid-as-possible 2D shape
manipulation(R.
Schmidt)

Select sno,grade from sc where
cno=’C2′

Similarity-Based Surface
Modelling(S.
Zelinka)

 

Mesh Modelling Using Curve
Analogies(S.
Zelinka)

 2、寻找选修课程号为C4的学生学号与姓名。

2D laplacian curve
editing(O.
Sorkine)

    Selects.sno,sname from s,sc where
s.sno=sc.sno and cno=’C4′

Convolution Surfaces for Line Skeletons with Polynomial Weight
Distributions(X.
Jin, J. Feng, Q. Peng, C-L. Tai)

    注意本题也能够用嵌套做

CARVErobust
boolean operations between arbitrary polygonal models (T. Sargeant)

思维本题改为“寻觅选修课程号为C4的上学的小孩子学号、姓名与成就”后仍能用嵌套做啊?

GeoBrush: Interactive Mesh Geometry
Cloning(discrete
expmap, 3D Green coordinates deformation, GPU Poisson solver) (K.
Takayama, R. Schmidt, K. Singh, T. Igarashi, T. Boubeker, O. Sorkine)

 

MeshFlow: Interactive Visualization of Mesh Construction
Sequences(J.
Denning, W. Kerr, F. Pellacini)

 3、找寻选修课程名称叫 Maths
的上学的小孩子学号与姓名。

3D Modeling with
Silhouettes(A.
Rivers, F. Durand, T. Igarashi)

    Selects.sno,sname from s,sc,c

Apparent Layer Operations for the Manipulation of Deformable
Objects(T.
Igarashi, J. Mitani)

    where  s.sno=sc.sno and c.cno=sc.cno
andcname=’Maths’

Mesh Snapping: Robust Interactive Mesh Cutting Using Fast Geodesic
Curvature
Flow(J.
Zhang, C. Wu, J. Cai, J. Zheng, X-C. Tai)

瞩目本题也足以用嵌套做

Mixed Finite Elements for Variational Surface
Modeling(A.
Jacobson, E. Tosun, O. Sorkine, D. Zorin)

 

Bounded Biharmonc Weights for Real-Time
Deformation(A.
Jacobson, I. Baran, J. Popović, O. Sorkine)

 4、寻找选修课程号为C2或C4的学生学号。

DT-Grid: data structure for high-resolution level
sets(open
and closed surfaces, dilation, CSG, texture coordinates, conversion from
OBJ/PLY, to PLY) (M. Nielsen, K. Museth)

    Select distinctsno from sc where cno
in (‘C2′,’C4’)

Shape Space Exploration of Constrained
Meshes(Y-L.
Yang, Y.-J. Yang, H. Pottmann, N. Mitra)

或: Select distinct sno from sc where
cno=’C2′ or cno=’C4′

Slices: A Shape-proxy Based on Planar
Sections(J.
McCrae, K. Singh, N. Mitra)

 

EXPLORATION OF CONTINUOUS VARIABILITY IN COLLECTIONS OF 3D
SHAPES(M.
Ovsjanikov, W. Li, L. Guibas, N. Mitra)

H5游戏开拓,消灭星星。 5、找寻选修课程号为C2和C4的学习者学号。

GlobFit: Consistently Fitting Primitives by Discovering Global
Relations(data)
(Y. Li, X. Wu, Y. Chrysanthou, A. Sharf, D. Cohen-Or, N. Mitra)

    Select sno fromsc where cno=’C2′ and
sno in

Detail-Replicating Shape
Stretching(Implicit
mesh fairing using curvature flow, patch-based texture synthesis, octree
with ray tracing, bézier splines) (I. Alhashim)

         ( Select sno from sc where
cno=’C4′ )

Volumetric Modeling with Diffusion
Surfaces(K.
Takayama, O. Sorkine, A. Nealen, T. Igarashi)

    注意本题也得以用三翻五次做

Metropolis Procedural
Modeling(J.
Talton, Y. Lou, S. Lesser, J. Duke, R. Mech, V. Koltun)

思考:Select distinct sno from sc where
cno=’C2′ andcno=’C4’正确吗?

siteplan: rapid architectural prototyping using procedural
extrusions(T.
Kelly, P. Wonka)

 

mesh-talent
(gauss-newton solver, (unknown) graph-based mesh deformation)
(Authors)

 6、搜索不学C2科指标学生姓名和年龄。

Smooth Shape-Aware Functions with Controlled
Extrema(A.
Jacobson, T. Weinkauf, O. Sorkine)

    Selectsname,sage from s where sno not
in

SESAME: Sketch, Extrude, Sculpt and Manipulate
Easily(J.
Y. Oh)

         ( Selectsno from sc where
cno=’C2′ )

Virtual
LEGO(J.
Y. Oh)

    或: Select sname,sage from s where
not exists

Easy Mesh
Cutting(Z.
Ji, L. Liu, Z. Chen, G. Wang)

              (Select * from sc where
sno=s.sno and cno=’C2′ )

sculpt: simple level set sculpting
program(multi-resolution
point splatting) (R. Bridson)

 

Segmentation

 7、寻觅选修了数据库学科的有着学员姓名。(同3)

efpisoft: hierarchical mesh segmentation based on fitting
primitives(M.
Attene)

    Select snamefrom s,sc,c

mesh segmentation benchmark database and
viewer(X.
Chen, A. Golovinskiy, T. Funkhouser)

where  s.sno=sc.snoand c.cno=sc.cno and
cname=’数据库’

Graphite(variational
shape
approximation,image
vectorization)
[documentation
wiki]
(Authors)

 

SegMatch: Shape Segmentation and Shape Matching from Point
Cloud(T.
Dey, S. Goswami)

 8、寻找数据库课程不比格的女孩子姓名。

ShapeAnnotatorsegmentation
tool (fitting primitives, barycentric/height/integral geodesic Morse,
Plumber, Lloyd
clustering)(Authors)

    连接:Select sname from s,sc,c

Shape Diameter Function (SDF) segmentation
tool(L.
Shapira)

         where  s.sno=sc.sno
andc.cno=sc.cno and cname=’数据库’

Parameterization

                and grade<60 and
ssex=’女’

fast stretch-minimizing
parameterization(includes
shape-preserving/Tutte/harmonic parameterization, and natural conformal
parameterization ) (S. Yoshizawa)

    嵌套:Select sname from s where
ssex=’女’ and  sno in

Graphite(ABF,
ABF++, DPBF, LSCM, HLSCM, Barycentric, mean-value coordinates, L2
stretch,spectral
conformal,Periodic
Global
Parameterization,Constrained
texture
mapping,texture
atlas
generation)
[documentation
wiki]
(Authors)

               (Select sno from sc where
grade<60 and cno in

CGAL(LSCM, discrete
conformal/authalic, Floater mean-value, Tutte barycentric) [full
packages
list]
(Authors)

                     ( Select cno from c
where cname=’数据库’ )

linear discrete conformal
parameterization(K.
Crane)

               )

Discrete Exponential Map
Demo(R.
Schmidt)

 

Local/Global Approach to Mesh
Parameterization(ARAP,
ASAP) (L. Liu, L. Zhang, Y. Xu, G. Gotsman, S. Gortler)

 9、找寻各门课程的平分成绩,输出课程名和平均战表。

Mesh Processing

    Selectcname,avg(grade) from
sc,c

mesh smoothing/denoising by averaging with similarity-based
weights(S.
Yoshizawa)

    wherec.cno=sc.cno  group by
c.cno,cname

estimating curvature tensors on triangle meshes with
CGAL(P.
Alliez)

记挂本题也足以用嵌套做吧?

Graphite(mesh
curvature,uniform
remeshing,mesh
repair,volume
meshing,manifold
harmonics,appearance-preserving
simplification,normal
mapping)
[documentation
wiki]
(Authors)

 

CGAL(mesh
simplification, mesh ridges/umbilics, mesh curvature)[full packages
list]
(Authors)

10、找寻各类学生的平分战表,输出学生姓名和平均战绩。

MeshLab(discrete
curvature, monte-carlo/stratified/poisson-disk mesh sampling, Hausdorff
distance between meshes, uniform remeshing, voronoi vertex clustering,
laplacian/Taubin smoothing, mesh unsharp mask, geodesic distances)
[All
Filters]
(Authors)

    Selectsname,avg(grade) from
s,sc

Simplification
Envelopesgenerate
mesh level-of-detail hierarchies (J. Cohen, A. Varshney, G. Turk)

    wheres.sno=sc.sno group by
s.sno,sname

Volfillfill
holes in mesh using volumetric diffusion (S. Marschner, K. Berglund)

沉凝本题也能够用嵌套做吗?

trimeshinfo: compute mesh
properties(manifold,
genus, orientation, volume, self-intersection, boundaries, connected
components, …)
(Authors)

 

Discrete Laplace Operator on Meshed
Surfaces(J.
Sun, M. Belkin, Y. Wang)

11、搜索足足有二十四个学生选修的教程名。

QualMesh: Delaunay meshing of surfaces and
volumes(T.
Dey, T. Ray)

    Select cnamefrom c where cno in

DelIso: delaunay meshing of
isosurfaces(T.
Dey, J. Levine)

         ( Selectcno from sc group by cno
having count(*)>=30 )

SurfRemesh: Delaunay Remeshing of Polygonal
Surfaces(T.
Dey, T. Ray)

留心本题也足以用再而三做

DelPSC: delaunay mesh generation for surfaces, volumes and
complexes(T.
Dey, J. Levine)

 

TriMesh2MT: convert polygon mesh to
Multi-Triangulation(M.
Attenne, E. Danovaro, P. Magillo)

12、搜索选修了十分的多于3门课程的学生姓名。

approximating gradients on meshes and point coulds via diffusion
metric(C.
Luo, I. Safa, Y. Wang)

    Select snamefrom s where sno in

geodesic: multiple source/target exact geodesic algorithm for
triangular
mesh(unknown)

         ( Selectsno from sc group by sno
having count(*)>=3 )

IsoEx: Feature Sensitive Mesh
Extraction(L.
Kobbelt, M. Botsch, Schwanecke, H.P. Seidel)

潜心本题也足以用延续做

discrete connections and smooth vector fields on triangle
meshes(K.
Crane, M. Desbrun, P. Schroder)

 

Scapeterrain
simplification (M. Garland)

13、找出各门课程的大成均不低于90分的上学的小孩子姓名。

Terraterrain
simplification (M. Garland)

   Select snamefrom s,sc where
s.sno=sc.sno

QSlimmesh
simplification software (M. Garland)

         group bys.sno,sname having
min(grade)>=90

Permission
Gridmesh
simplification (S. Zelinka)

方法二:

Topology-based Smoothing of 2D Scalar Fields with
C1-Continuity[Page]
(T. Weinkauf, Y. Gingold, O. Sorkine)

Select sname from s where sno not
in

HanTun: computing handle and tunnel loops in 3D
models(T.
Dey, K. Li)

         ( Selectsno from sc where
grade<90 )

Non-Iterative, Feature-Preserving Mesh
Smoothing(also
[Page])
(T. Jones)

设若有一门极大于90分就能输出该学生学号

Efficient Implementation of Marching Cubes’ Cases with Topological
Guarantees(T.
Lewiner, H. Lopes, A. Viera, G. Tavares)

 

Direct Spherical Harmonic Transform of a Triangulated
Mesh(M.
Mousa, R. Chaine, S. Akkouche)

14、搜索数据库课程战表比十分大于该门课程平均分的学员姓名。

Normalizing for Anisotropy in Triangle
Models(M.
Kazhdan)

    Select snamefrom s,sc,c

Symmetry Descriptors and 3D Shape
Matching(M.
Kazhdan)

    where  s.sno=sc.sno and sc.cno=c.cno
and cname=’数据库’ and grade>

Unconstrained Isosurface Extraction on Arbitrary
Octrees(M.
Kazhdan)

         ( Selectavg(grade) from
sc,c

Fast Mean-Curvature Flow via Finite-Elements
Tracking(M.
Kazhdan)

           where sc.cno=c.cnoand
cname=’数据库’

Interactive and Anisotropic Geometry Processing Using the Screened
Poisson
Equation(M.
Kazhdan)

         )

Blended Intrinsic
Maps(V.
Kim, Y. Lipman, T. Funkhouser)

15、寻找种种系科男女学生的平均年龄和人口。

Spin Transforms of Discrete
Surfaces(K.
Crane, U. Pinkall, P. Schröder)

    Selectsdept,ssex,avg(sage),count(*)
from s group by sdept,ssex

shapeDNA:
compute accurate eigenvalues and eigenfunctions of the Laplace Beltrami
operator using higher order FEM with Dirichlet or Neumann boundary
conditions (cubic FEM, global mesh refinement, tangential smoothing,
PLY, SMF, OBJ, OFF, VTK, ASC3D, STL) (M. Reuter)

16、找寻Computer系(JSJ)课程平均分最高的学生学号和姓名。

Manifold Mesh
Processing(non-manifold
repair, smoothing, normals from point clouds, curvature calculation,
local shape descriptors, Polymender manifold mesh from triangle soup,
mesh simplification) (C. Grimm)

    Selects.sno,sname from s,sc where
s.sno=sc.sno and sdept=’JSJ’

GMSH: a three-dimensional finite element mesh generator with built-in
pre- and post-processing
facilities(procedural
parameterized geometry, 1/2/3D simplicial finite element meshing,
element size control, scalar/vector/tensor datasets) (C. Geuzaine, J.-F.
Remacle)

    group bys.sno,sname

Skeleton Extraction by Mesh
Contraction(O.
Au, C.-L. Tai, H.-K. Chu, D. Cohen-Or, T.-Y. Lee)

    havingavg(grade) >=ALL

Polymender mesh
repairer(water-tightness,
closed surface repair, sharp features, signed volume generation) (T. Ju)

         ( Selectavg(grade) from
s,sc

Algorithms to Automatically Quantify the Geometric Similarity of
Anatomical
Surfaces(D.
Boyer, Y. Lipman, E. St Clair, J. Puente, B. Patel, T. Funkhouser, J.
Jernvall, I. Daubechies)

           wheres.sno=sc.sno and
sdept=’JSJ’

Discrete Laplacians on General Polygonal
Meshes(M.
Alexa, M. Wardetzky)

           group bys.sno

meshfix(dilation,
intersection test, ensure minimal distance, resolve overlaps, cut using
shell, cleanup, (M. Attene)

         )

progressive
meshesimplementation
(H. Hao)

17、(补充)查询每门科指标及格率。

HKS: Computing Heat Kernel
Signature(J.
Sun, M. Ovsjanikov, L. Guibas)

    本题能够分三步做:

SDFGen: generate grid-based signed distance field (level set) from
triangle
meshes(C.
Batty, R. Bridson)

   

mesh_query: robustly checking inside/outside and segment intersection
with a
mesh(R.
Bridson)

    第1步:获得每门课的选修人数

Point Set Processing

     createview  v_all(cno,cnt)

QPoly: meshing scattered 3D
points(Y.
Ohtake)

         as selectcno, count(*) from sc
group by cno

SLIM: sparse low-degree
implicits(fitting,
rendering, reflection lines, analytic curvature measures, crest lines,
suggestive contours) (Y. Ohtake, A. Belyaev, M. Alexa)

    第2步:得到每门课及格人数

Multi-scale Compactly Supported Radial Basis
Functions(Y.
Ohtake)

     createview 
v_pass(cno,cnt_pass)

MeshLab(ICP
range-map alignment, ball pivoting, point-set normals, Robust Implicit
MLS (RIMLS), Algebraic point-set surface (APSS), Poisson surface
reconstruction) [All
Filters]
(Authors)

         as selectcno, count(*) from sc
where grade>=60 group by cno

Vrip(convert
aligned range images to volumetric format, extract mesh surface) (B.
Curless)

   
第3步:每门课的通过海关人数/每门课选修人数

Zipper(combine
range images into polygonal mesh) (G. Turk, H. Ge, B. Curless)

     selectv_all.cno, cnt_pass*100/cnt 
from  v_all, v_pass

Scanalyze(manual/ICP
alignment and merging of range image data, fill holes, decimation,
editing, PLY/SD formats)
(Authors)

     where v_all.cno = v_pass.cno

QSplat(real-time
multiresolution point-set rendering) (S. Rusinkiewicz, G. King)

 

Constructing Laplace Operator from Point Clouds in
R^d(J.
Sun, M. Belkin, Y. Wang)

18、查询平均分不如格的学生的学号,姓名,平均分。

NormFet: Approximating Normals and Feature Sizes from Noisy Point
Clouds(T.
Dey, J. Sun, L. Molnar)

    Selectsc.sno,sname,avg(grade) from
student,sc

AMLS for Smoothing Noisy Point
Clouds(adaptive
moving least squares) (T. Dey, J. Sun)

    wherestudent.sno=sc.sno

Cocone: Delaunay meshing of point sets with
boundaries(T.
Dey, J. Giesen)

    group bysc.sno,sname

Tight Cocone: water-tight Delaunay meshing of point sets using
approximate medial
axis(T.
Dey, S. Goswami, W. Zhao)

    havingavg(grade)<60

SuperCocone: efficient Delaunay meshing of large point
sets(T.
Dey, J. Hudson)

寻思本题也足以用嵌套做啊?

RobustCocone: Delaunay meshing of noisy point
sets(T.
Dey, S. Goswami)

 

Peel: isotropic reconstruction of surfaces from point sets with or
without
boundaries(T.
Dey, K. Li)

19、查询平均分不比格的学员人数。

4-points Congruent Sets for Robust Surface
Registration(D.
Aiger, N. Mitra, D. Cohen-Or)

    Select count(*)from student

CGAL(Poisson
reconstruction, spacing estimation, simplification, outlier removal,
smoothing, normal estimation, orientation estimation)[full packages
list]
(Authors)

    where sno in

integral estimation on a k-manifold embedded in
R-d(C.
Luo, J. Sun, Y. Wang)

         ( selectsno from sc group by sno
having avg(grade)<60 )

discrete Laplace-Beltrami operator on point
clouds[Page]
(C. Luo, I. Safa, Y. Wang)

    上面是多个卓绝的荒谬

FReg: detect approximate symmetries of point sets and B-Rep
Models(Authors)

Select count(*) from sc group by sno
havingavg(grade)<60

Normal Improvement for Point
Rendering(bilateral
normal filtering)
[Page]
(T. Jones)

那是各种学生有几门不如格的数目

Reconstruction of Solid Models from Oriented Points
Sets(M.l
Kazhdan)

 

Poisson Surface
Reconstruction(M.
Kazhdan)

三、

Multilevel Streaming for Out-of-Core Surface
Reconstruction(M.
Bolitho, M. Kazhdan, R. Burns, H. Hoppe)

 1、查询薪水在一千到贰仟元之间的男子业务员的真名和办公编号。

CUDA-Based implementations of SoftAssign and
EM-ICP(T.
Tamaki, M. Abe, B. Raytchev, K. Kaneda, M. Slomp)

    SelectYname,Ono from YWY

Coherent Point
Drift(rigid,
affine, nonrigid N-D alignment and correspondence) (A. Myronenko)

    where Salarybetween 1000 and 3000 and
Ysex=’男’

PowerCrust(watertight
polygonal meshing of point set, medial axis transform, simplified medial
axis) (N. Amenta) [updated
port]

 2、查询各样办公室的业务员人数,输出办公室编号和对应的人口。

Curves and Surfaces

    SelectOno,count(*) from YWY group by
Ono

A Bidirectional Generating Algorithm for Rational Parametric
Curves(Z.
Li, L. Ma)

 3、查询每一种客户在二〇〇〇年二月选购的总金额,输出客商号和呼应的总金额。

Fast Detection of the Geometric Form of Two-Dimensional Cubic Bézier
Curves(S.
Vincent)

    SelectKno,sum(Fmoney) from FP

Exact Evaluation of Subdivision
Surfaces(eigenstructures
for Catmull-Clark and Loop schemes) (J. Stam)

    where Fdatebetween ‘2002.5.1’ and
‘2002.5.31’

Exact Evaluation of Catmull-Clark Subdivision Surfaces near B-Spline
Boundaries(D.
Lacewell, B. Burley)

    group by Kno

Smooth Two-Dimensional Interpolations: A Recipe for All
Polygons(E.
Malsch, J. Lin, G. Dasgupta)

 4、查询二〇〇二年11月买卖次数超越5次的装有顾客号,且按顾客号升序排序。 

Normal Patches /
PN-Triangles(R.
Stimpson)

    Select Kno fromFP

Marching
Cubes(.vol
files) (R. Stimpson)

    where Fdatebetween ‘2002.5.1’ and
‘2002.5.31’

Coons
Patches(R.
Stimpson)

    group by Kno

Exact Catmull-Clark Subdivision
evaluation(and
mean-curvature minimization) (F. Hecht)

    havingcount(*)>5

Laplacian Surface
Editing(2D
curve deformation) (O. Sorkine, D. Cohen-Or, Y. Lipman, M. Alexa, C.
Roessl, H.-P. Seidel)

    order by KnoASC

Elasticurves: Exploiting Stroke Dynamics and Inertia for the Real-time
Neatening of Sketched 2D
Curves(Y.
Thiel, K. Singh, R. Balakrishnan)

 5、查询各办公室男性和女子业务员的平均薪酬。

Computational Geometry

    SelectOno,Ysex,avg(Salary) from YWY
group by Ono,Ysex

2D voronoi diagrams with
CGAL(P.
Alliez)

 6、查询二〇〇二年五月早就在王海亮业务员手中购得过商品的客户号、

TRIANGLE: 2D high-quality exact/constrained/conforming Delaunay
triangulations(J.
Shewchuk)

            客商姓名和联系电话。

Stellar: a tetrahedral mesh improvement
program(B.
Klingner, J. Shewchuk)

    SelectKno,Kname,Phone from KH where
Kno in

TetGen: A
Quality Tetrahedral Mesh Generator and a 3D Delaunay Triangulator
(constrained/conforming Delaunay, Voronoi, boundary-conforming,
quality/size control, adaptive mesh refinement, intersection testing)
(H. Si)

         ( SelectKno from FP

manifold approximation of 3D medial
axis(S.
Yoshizawa)

           whereFdate between ‘2002.5.1’
and ‘2002.5.31’ and Yno in

CGAL(n-D geometry,
2D/3D spherical geometry, n-D convex hull, 2D/3D/spherical booleans, 2D
minkowski sums, 2D polygon partition/offset/skeleton, 2D curve
intersection, 2D/3D envelopes, 2D/3D triangulation, 2D/3D alpha shapes,
2D delaunay/voronoi/apollonius/conforming delaunay/conforming gabriel,
3D delaunay isosurfacing, 3D skin surface, n-D spatial data structures,
bounding volumes, optimal distances, least-squares geometry fitting,
2D/surface function interpolation, kinetic data structures, AABB Tree,
3D periodic triangulations, tetrahedral meshing w/
implicit/volume/polyhedral boundaries) [full packages
list]
(Authors)

                      ( Select Yno from
YWY where Yname=’王海亮’ )

METRO: measure differences between triangular
meshes(Authors)

         )

BOOLEconvert
CSG to Brep (curved solid primitives, polygonal solids) (T. Culver)

    注意本题也足以用一连做

MAPC:
exact manipulation of algebraic points and curves in the plane (D.
Manocha, J. Keyser, T. Culver, M. Foskey, S. Krishnan)

 7、查询全数薪水比1538号业务员高的业务员的编号、姓名和薪俸。

Fast Polygon Triangulation based on Seidel’s
Algorithm(A.
Narkhede, D. Manocha)

    SelectYno,Yname,Salary from YWY where
Salary >

UNC GAMMA Collision Detection/Proximity Query
Packages(penetration
depth, intersection detection, tolerance verification, exact and
approximate distance computation, separation distance, contact
determination, Minkowski sums, Lin-Canny closest features, uniform grid
spatial decomposition, OBB/Swept-Sphere/convex-hull bounding-volume
hierarchies, polygon soups, rigid motion)

         ( SelectSalary from YWY where
Yno=’1538′ )

DeformCD: collision detection for deformable
models(M.
Tang, D. Manocha)

 8、查询全数与1538号业务员在同八个办公的其余业务员的编号和人名。

CurveSkel: 1D curve skeleton of 3D
shape(T.
Dey, J. Sun)

    SelectYno,Yname from YWY where
Yno!=’1538′ and Ono in

Shortest paths on a polyhedral
surface(B.
Kaneva, J. O’Rourke)

         ( SelectOno from YWY where
Yno=’1538′ )

Antiprism polyhedra
library(convex
hull, polar reciprocals (dual meshes), geodesic spheres, uniform
polyhedra, uniform tilings on surfaces, lattices and grids, ring/spiral
of points on sphere, equilibrium of points repelling on sphere,
near-uniform sphere tessellation, many other polyhedra types, OFF
conversion utilities) [A. Rossiter]

 9、查询出卖总金额最高的业务员的数码。

Nuages3D
delaunay mesh reconstruction from parallel cross-sections (B. Geiger)

    Select Yno fromFP group by Yno having
sum(Fmoney) >=ALL

ShortLoop: computing loops in a shortest homology
basis(O.
Busaryev, T. Dey, J. Sun, Y. Wang)

         ( Selectsum(Fmoney) from FP group
by Yno )

Coordinate Free Geometric
Programming(S.
Mann, N. Litke, T. DeRose)

10、查询全数业务员的数码、姓名、工资以及薪水比她高的别样业务员的平均薪金。

Calculation of Mappings between one and n-dimensional values using
hilbert space-filling
curve(J.
K. Lawder)

    应用自连接

A Fast and Robust GJK Implementation for Collision Detection of Convex
Objects(G.
van den Bergen)

   
SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)

Fast Distance Field and Generalized Voronoi Diagram Computation using
Graphics
Hardware(Authors)

    from   YWY Y1, YWY Y2

Efficient Collision Detection of Complex Deformable Models using AABB
Trees(G.
van den Bergen)

    where  Y1.Salary < Y2.Salary

M.E.S.H. : Measuring Error between Surfaces using the Hausdorff
distance(N. Aspert,
D. Santa-Cruz, T. Ebrahimi)

    group by  Y1.Yno  

HandleTunnel: computing handle and tunnel loops on
surfaces(T.
Dey, K. Li, J. Sun)

 

tunicate: robust computational geometry predicates in floating
point(R.
Bridson)

四、

Intersections and Distances

 1、寻觅每一个班级的班级代码、学生人数、平均成绩。

Fast 3D Line Segment—Triangle Intersection
Test(N.
Chirkov)

    SelectBJDM,count(*),avg(CJ) from SC
group by BJDM

An Efficient Ray-Quadrilateral Intersection
Test(A.
Lagae, P Dutre)

 2、找出各个学员的班级代码、学生姓名、考试科目数、总成绩。

On Faster Sphere-Box Overlap
Testing(T.
Larsson, T. Akenine-Moller, E. Lengyel)

    SelectBJDM,XSXM,count(*),sum(CJ) from
SC

Fast Tetrahedron-Tetrahedron Overlap
Algorithm(F.
Ganovelli, F. Ponchio, C. Rocchini)

    group byBJDM,BNXH,XSXM

Ray Bilinear Patch
Intersections(S.
Ramsey, C. Hansen, K. Potter)

 3、输出一张表格,每位学生对应一条记下,包罗字段:

GPU-Based Tiled Ray Casting using Depth
Peeling(F.
Bernadon, C. Pagot, J. Comba, C. Silva)

         
班级代码、学生姓名、语文战绩、数学成绩、外语战表。

An Efficient and Robust ray-box intersection
algorithm(A.
Williams, S. Barrus, R. Morley, P. Shirley)

   
SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJ

A Fast Triangle-Triangle Intersection
Test(T.
Moller)

    from  SC SC1, SC SC2, SC SC3

Fast and Robust Triangle-Triangle Overlap Test using Orientation
Predicates(P.
Guigue, O. Devillers)

    whereSC1.BJDM=SC2.BJDM and
SC1.BNXH=SC2.BNXH and

A Fast Triangle-Triangle Overlap Test Using Signed
Distances(H.
Shen, Z. Tang)

         SC2.BJDM=SC3.BJDM and
SC2.BNXH=SC3.BNXH and

Fast Ray-Axis Aligned Bounding Box Overlap Tests with Plücker
Coordinates(J.
Mahovsky, B. Wyvill)

          SC1.KM=’语文’ and SC2.KM=’数学’
and SC3.KM=’外语’

Fast Ray/Axis-Aligned Bounding Box Overlap Tests using Ray
Slopes(M.
Eisemann, M. Magnor, T. Grotsch, S. Muller)

 4、输出一张表格,有成绩低于60分的每位学生对应一条记下,富含字段:

Fast, Minimum Storage Ray-Triangle
Intersection(T.
Moller, B. Trumbore)

         
班级代码、学生姓名、最低成绩。

Lightweight Bounding Volumes for Ray
Tracing(D.
Cline, K. Steele, P. Egbert)

    SelectBJDM,XSXM,min(CJ) from SC

Fast and Accurate Circle-Circle and Circle-Line 3D Distance
Computation(D.
Vranek)

    where  CJ<60 group by
BJDM,BNXH,XSXM

A Shaft Culling
Tool(E.
Haines)

    或:  SelectBJDM,XSXM,min(CJ) from
SC

Fast 3D Triangle-Box Overlap
Testing(T.
Akenine-Moller)

          group byBJDM,BNXH,XSXM

Intersection Test for Collision Detection in Particle
Systems(E.-A.
Karabassi, G. Papaioannou, T. Theoharis, A. Boehm)

          havingmin(CJ)<60

Techniques for Interactive Ray Tracing of Bézier
Surfaces(C.
Benthin, I. Wald, P. Slusallek)

 5、输出一张表格,有实际业绩低于60分的每位学生对应一条记下,包罗字段:

Photorealistic Rendering

         
班级代码、学生姓名、最高成绩、平均成绩。

TAGL: software
rasterizer(B.
Levy)

    SelectBJDM,XSXM,max(CJ) from SC

ShadeVis: compute per-vertex ambient occlusion
term(Authors)

    group byBJDM,BNXH,XSXM

MeshLab(vertex/face
ambient occlusion)
(Authors)

    havingmin(CJ)<60

LumosQuad(2D
lighting simulation and rendering, quadtree conjugate gradient Poisson
solver) (T. Kim, M. Lin)

    请思量下列做法是还是不是准确:

OpenEXR(read/write/view
OpenEXR HDR images)

          SelectBJDM,XSXM,max(CJ),avg(CJ)
from SC

bv(BRDF
browser, many analytic and empirical BRDF included) (S. Rusinkiewicz)

         where  CJ<60 group
byBJDM,BNXH,XSXM

LightPack(light
field authoring and rendering)

 6、输出一张表格,全体成绩都不低于60分的每人学生对应一条记下,包罗字段:

aperture(light-field
viewer)

         
班级代码、学生姓名、平均战绩。

renderman shader to dump out
grids(M.
Pharr)

    SelectBJDM,XSXM,avg(CJ) from SC

skin
shader(M.
Pharr)

    group by BJDM,BNXH,XSXM

raytracing quaternion julia sets on the
GPU(K.
Crane)

    havingmin(CJ)>=60

radiosity
renderers(progressive
and wavelet radiosity, face cluster radiosity) (A. Willmott, P.
Heckbert)

 7、输出一张表格,每种人学生对应一条记下,包含字段:

Radiator radiosity
system(matrix,
progressive, wavelet, clustering) (A. Willmott)

         
班级代码、学生姓名、去掉一个低于分后的平分战表。

SunSky sun/sky model
code(A.
Willmott)

   
SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1) from SC

brdfview BRDF
viewer(P.
Heckbert)

    group byBJDM,BNXH,XSXM

SkyWorksreal-time
cloud rendering (M. Harris)

 8、输出一张表格,每门科目对应一条记下,满含字段:

SHLIGHT:
spherical harmonic lighting program (T. Huang, I. Lisle)

         
科目、去掉二个最低分后的平均成绩。

glSoftShadowOpenGL
soft shadows implementation (U. Borgenstam, J. Svensson)

    Select
KM,(sum(CJ)-min(CJ))/(count(*)-1)from SC

Triangle Mesh Tangent Space
Calculation(M.
Mittring, Crytek)

    group by KM

MiniLight minimal global illumination
renderer(monte-carlo
path tracing, progressive refinement, Ward linear tone mapping, octree
spatial index) (H. Ainsworth)

 

P3
ToneMapper(openEXR,RGBE
in, PNG, PPM out, Ward tone mapping) (H. Ainsworth)

 

Perceptuumglobal
illumination renderer (monte carlo ray tracing, motion blur, DOF, glossy
scattering, generalized BRDFS, progressive refinement, photon mapping)
(H. Ainsworth)

 

raytracing, path tracing, and distance-field rendering with
CUDA(mxadd)

        实验教导中“八 SQL查询语句”
的答案

Volumetric Lighting and Shadowing NV32
Shader(S.
Lefebvre, S. Guy)

 

Computing a View Frustum to Maximize an Object’s Image
Area(K-L.
Low, A. Ilie)

 1、查询年龄在19至贰十三岁之间的女孩子的学号,姓名,年龄,按年龄从大到小排列。

BTFShop: Interactive Editing and Modeling of Bidirectional Texture
Functions(J.
Kautz, S. Boulos, F. Durand)

    Selectsno,sname,sage from
student

Efficient Splatting Using Modern Graphics
Hardware(D.
Xue, R. Crawfis)

    where sagebetween 19 and 21 and
ssex=’女’

Parameter Estimation for Photographic Tone
Reproduction(E.
Reinhard)

    order by sagedesc

Textured Shadow
Volumes(J.
Hasselgren, T. Akenine-Moller)

 2、查询姓名中有“明”字的上学的小孩子人数。

Hardware-Accelerated Point Generation and Rendering of Point-Based
Impostors(J.
Baerentzen)

    Select count(*)from student

Reducing Noise in Image-Space Caustics with Variable-Sized
Splatting(C.
Wyman, C. Dachsbacher)

    where snamelike “%明%”

Robust Soft Shadow Mapping with Backprojection and Depth
Peeling(L.
Bavoil, S. Callahan, C. Silva)

 3、查询1001课程未有实际业绩的上学的小孩子的学号。

Visibility Cuts shadows for Precomputed Radiance
Transfer(also
Ashikhmin-Shirley shader) (F. Hecht)

    Select sno fromsc where cno=’1001′ and
grade is null

Frequency Analysis and Sheared Reconstruction for Rendering Motion
Blur(K.
Egan, Y.-T. Tseng, N. Holzschuch, F. Durand, R. Ramamoorthi)

 4、查询JSJ、SX、WL系的学员学号,姓名,结果按系及学号排列。

PCA-Based Relighting
Framework(S.
Enrique, R. Ramamoorthi)

    Selectsno,sname,sdept from
student

Layered 3D: Tomographic Image Synthesis for Attenuation-based Light
Field and High Dynamic Range
Displays(G.
Wetzstein, D. Lanman, W. Heidrich, R. Raskar)

    where sdept in( ‘JSJ’, ‘SX’, ‘WL’
)

Spark: Modular, Composable Shaders for Graphics
Hardware(T.
Foley, P. Hanrahan)

    order bysdept,sno

BRDF Displacement Interpolation Using Lagrangian Mass
Transport(function/distribution
interpolation, non-euclidean manifolds,
BRDF/color/value/envmap/stippling, non-negative least squares) (N.
Bonneel, M. van de Panne, S. Paris, W. Heidrich)

 5、总括每一门课的总分、平均分,最高分、最低分。

NonPhotorealistic Rendering

   
Selectcno,sum(grade),avg(grade),max(grade),min(grade)

rtsc: suggestive contours/highlights, principal hilights, apparent
ridges,
etc(D.
DeCarlo, A. Finkelstein, S. Rusinkiewicz, A. Santella, M. Burns, J.
Klawe)

    from sc

fast and robust detection of crest lines on
meshes(mesh
denoising, mesh curvature) (S. Yoshizawa)

    group by cno

xshade: exaggerated shading
rendering(S.
Rusinkiewicz, M. Burns, D. DeCarlo)

 6、查询平均分胜出90分的男学生学号及平均分。

jot: NPR
library

    连接:

Weighted Voronoi
Stippling(A.
Secord)

    selectsc.sno,avg(grade) from
student,sc

dpix: interactive rendering of 3D models using stylized
lines(sketchup,
self-similar line artmaps, coherent self-similar line styalization,
high-quality line visibility, stylized focus)

    wherestudent.sno=sc.sno and
ssex=’男’

Visualization

    group by sc.sno

Visualizing LIDAR in Google
Earth(M.
Isenburg, J. Shewchuck)

    havingavg(grade)>90

farthest-point seeding for placement of streamlines in 2D vector fields
with
CGAL(A.
Mebarki)

    嵌套:

VolPack(fast
volume rendering with shear/warp factorization) (P. Lacroute)

    selectsno,avg(grade) from sc

Simulation / Animation

    where sno in (select sno from student
where ssex=’男’)

Inverse Kinematics for Serial
Manipulators(D.
Manocha, Y. Zhu)

    group by sno

El
Topo(robust
surface tracking with triangle meshes) (T. Brochu, R. Bridson)

    havingavg(grade)>90

CMU .ASF/.AMC/.V
tools(J.
McCann, C. Twigg, J. Barbic, …?)

 7、查询选修课程超越2门的学习者姓名。

Skinning Mesh Animations
Demo(C.
Twigg)

    select snamefrom student,sc

DANCE(physics-based
character animation and simulation) (A. Shapiro, V. Ng-Thow-Hing, P.
Faloutsos)

    where student.sno=sc.sno

Physically Based Simulation and Animation of Gaseous Phenomena in a
Periodic
Domain[Page]
(A. Nealen)

    group bysc.sno,sname

2D Boiling
Demo(CML
coupled map atlas on GPU) (M. Harris, G. Coombe, T. Sheuermann, A.
Lastra)

    havingcount(*)>2

Flo real-time 2D fluid simulation
demo(Cg
navier-stokes incompressible flow fixed no-slip boundaries) (G. James,
M. Harris)

    本题也能够用嵌套做

Disease reaction diffuse
demo(Cg,
on surface) (G. James, M. Harris)

 8、查询 JSJ 系的学习者选修的课程号。

GPU Ocean Surface
Simulation[Page]
(S. Lefebvre)

    Select distinctcno from
student,sc

Practical Parameterization of Rotations Using the Exponential
Map(F.
Grassia)

    where  student.sno=sc.sno and
sdept=’JSJ’

A Simple Fluid Solver Based on the
FFT(J.
Stam)

    本题也足以用嵌套做

Computing the Moment of Inertia of a Solid Defined by a Triangle
Mesh(M.
Kallay)

 9、查询选修1002科目标学生的学员姓名(用一连和嵌套2种办法)

2D Polygon Moment of
Intertia(C.
Batty)

    连接:Select sname from student,sc

Fast and Accurate Computation of Polyhedral Mass
Properties(B.
Mirtich)

          wherestudent.sno=sc.sno and
cno=’1002′

Fast and Easy Reach-Cone Joint
Limits(J.
Wilhelms, A. Van Gelder)

    嵌套:Select sname from student where
sno in

Asynchronous Contact
Mechanics(D.
Harmon, E. Vouga, B. Smith, R. Tamstorf, E. Grinspun)

              (select sno from sc where
cno=’1002′ )

Generalized Biped Walking
Control(S.
Coros, P. Beaudoin, M. van de Panne)

10、查询学生姓名以及她选修课程的科目号及成绩。

Locomotion Skills for Simulated
Quadrupeds(S.
Coros, A. Karpathy, B. Jones, L. Reveret, M. van de Panne)

    Selectsname,cno,grade from
student,sc

StVK: FEM Saint Venant-Kirchhoff deformable object
library(J.
Barbic)

    wherestudent.sno=sc.sno

Mass Spring System: A general 3D mass-spring
system(J.
Barbic)

    考虑本题也足以用嵌套做吗?

Implicit Newmark and Central Differences
Integratorsfor
large deformation nonlinear FEM elasticity (J. Barbic)

11、查询选修“数据库原理”课且成绩 80
以上的学生姓名(用延续和嵌套2种方法)

Large Modal Deformation
Factory:
Model reduction of StVK FEM deformable models (linear modes, arbitrary
free boundary conditions, modal derivatives, mass and stiffness
matrices, triangle mesh to cube volume mesh conversion) (J. Barbic)

    连接:Select sname from
student,sc,course

harmonic
oscillator:
exact timestepping of 1D ODE (J. Barbic)

          wherestudent.sno=sc.sno and
sc.cno=course.cno and

LQR: linear-quadratic regulator
controller(solves
Riccati differential equation) (J. Barbic)

               cname=’数据库原理’ and
grade>80

Dynamics of a single rigid
body(explicit
Euler, symplectic Euler, time-varying external forces and torques,
arbitrary inertia tensors) (J. Barbic)

    嵌套:Select sname from student where
sno in 

A Level-set Method for Skinning Animated Particle
Data(thin-plate
energy, grid mean curvature/laplacian/biharmonic, kdtree, marching
tetrahedra) (H. Bhattacharya, Y. Gao, A. Bargteil)

               (select sno from sc where
grade>80 and cno in 

Sensitive Couture for Interactive Garment Editing and
Modeling[2]
(N. Umetani, D. Kaufman, T. Igarashi, E. Grinspun)

                    ( select cno from
course where cname=’数据库原理’ )

DelFEM(FEM,
Poisson, Diffusion, Advection-Diffusion, Linear Solid,
St.Venant-Kirchhoff Material, incompressive hyperelastic material,
Stokes Fluid, Navier-Stokes Fluid, Helmholtz, 3D rigid body, 6 types of
constraint model, coupling analysis of rigid and elastic material, ILU
(0) preconditioned CG, ILU (0) preconditioned BiCGSTAB) (N. Umetani)

               )

DT-Grid: data structure for high-resolution level
sets(volume-conserving
mean curvature flow, shape metamorphosis, advection, one-sided,
second-order central, WENO finite difference) (M. Nielsen, K. Museth)

 

Exact
CCD:
Geometrically exact continuous collision detection for triangle meshes.
(T. Brochu, E. Edwards, R. Bridson)

14、查询未有选修1002科目标学员的学员姓名。

Fluid3D:
barebones 3D inviscid free-surface fluid simulator in irregular domains
(C. Batty)

    Select snamefrom student

VariationalViscosity2D:
2D viscous free surface simulation (C. Batty)

    where sno notin ( select sno from sc
where cno=’1002′)

free-surface 2D liquid solver with irregular static
obstacles(C.
Batty, F. Bertails, R. Bridson)

    或: select sname from student

Quadratic extrapolation of data in the normal
direction(eg
to extrapolate velocities from fluid to solid region) (C. Batty)

         where notexists

Texturing

              (select * from sc where
cno=’1002′ and sno=student.sno)

FlexiStickers: Photogrammetric Texture Mapping using Casual
Images(Y.
Tzur, A. Tal)

   
思虑本题也得以用一般的总是做呢?

Texture
filtering(bilinear,
mipmap, elliptic-weighted average) [link to .h/.cpp at bottom of
page
] (M. Pharr)

15、查询平均分最高的学习者学号及平均分。

3D Cellular Noise
function(M.
Pharr)

    Selectsno,avg(grade)

Jump Map Texture
Synthesis(S.
Zelinka)

    from sc

Hybrid Texture
Synthesis[Page]
(A. Nealen, M. Alexa)

    group by sno

Octree Textures on the
GPU(S.
Lefebvre)

    havingavg(grade) >=ALL ( Select
avg(grade)

Parallel Controllable Texture Synthesis
Tutorial(S.
Lefebvre)

                              from
sc

Image
Quilting(T.
Jones)

                              group by
sno

Floating
Textures(M.
Eisemann)

                            )

Advanced Reaction-Diffusion Models for Texture
Synthesis(A.
Sanderson, R. Kirby, C. Johnson, L. Yang)

16、查询每门科目成绩都高于该门课程平均分的学习者学号。

HWB – A More Intuitive Hue-Based Color
Model(A.
R. Smith, E. R. Lyons)

    能够先总结每门课程平均分

Efficient GPU-Based Texture Interpolation using Uniform
B-Splines(D.
Ruijters, B. ter Haar Romney, P. Suetens)

    create
viewc_avg(cno,avg_grade)

Texture Stitching by Estimating the Laplace-Beltrami Operator by
Restricting 3D
Functions(M.
Kazhdan)

         as selectcno,avg(grade) from sc
group by cno

Filtering Solid Gabor
Noise(A.
Lagae, G. Drettakis)

    再查询

Repetition Maximization based Texture
Rectification(D.
Aiger, D. Cohen-Or, N. Mitra)

    Select distinctsno from sc

2D

    where sno notin ( Select sno from
sc,c_avg

real-time gradient-domain image
painting(multigrid
GPU poisson solver) (J. McCann, N. Pollard)

                      where
sc.cno=c_avg.cno and grade<avg_grade

mean-value coordinate image
cloning(Z.
Farbman, G. Hoffer, Y. Lipman, D. Cohen-Or, D. Lischinski)

                    )

GraphDraw:
draw 2D graphs and export as postscript (K. Crane)

   ===========================================

Interactive Segmentation
Tool-Box(lazy
snapping, GrabCut interactive graph cut)

    SELECT DISTINCT Sno

3D Photo
Pop-Up(D.
Hoeim)

    FROM SC SC1

Anti-Grain Geometry
Project(high-quality
and efficient 2D software rasterizer: subpixel antialiasing, arbitrary
polygons, gradients and gouraud shading, image filtering, bilinear,
bicubic, spline16, spline32, sinc, blackman, strokes with joins and
caps, dashed lines, head/tail markers, arrows, polygon clipping,
alpha-masking, arbitrary images as line patterns, perspective and
bilinear transformations, boolean polygon operations) (M. Shemanarev)

    WHERE SC1.SnoNOT IN

High Quality Hardware Line
Antialiasing(R.
Nelson)

          ( SELECT SC2.Sno

Color Compatibility from Large
Datasets(P.
O’Donovan, A. Agarwala, A. Hertzmann)

            FROM SC SC2

2.5D Cartoon
Models(A.
Rivers, T. Igarashi, F. Durand)

            WHERE SC2.Grade <=

GradientShop: A Gradient-Domain Optimization Framework for Image and
Video
Filtering(P.
Bhat, L. Zitnick)

                 ( SELECT
AVG(SC3.Grade)

Image and Video Processing

                   FROM SC SC3

Adaptive Thresholding Using the Integral
Image[Also](https://link.jianshu.com?t=http://jgt.akpeters.com/papers/BradleyRoth07/)\

                  
WHERE SC3.Cno=SC2.Cno

A One-Pass Version of Two-Pass Image
Resampling(N.
Max)

                 )

PDE-Based Image
Filtering(Nonlinear
diffusion, Total-Variation denoising, Shock Filters) (G. Gilboa)

          )

ImageStack: a command line calculator for
images(HDR
image generation, bilateral/median/circular filter, dimensionality
reduction, FFT, Poission, wavelet transforms, inpainting, andmany
more)
(A. Adams)

    或:

Fast High-Dimensional Filtering Using the Permutohedral
Lattice(A.
Adams, J. Baek, A. Davis)

    SELECT DISTINCTSno

Local Laplacian Filters: Edge-aware Image Processing with a Laplacian
Pyramid(S.
Paris, S. Hasinoff, J. Kautz)

    FROM SC SC1

Domain Transform for Edge-Aware Image and Video
Processing(E.
S. L. Gastal, M. M. Oliveira)

    WHERE NOTEXISTS

Real-time Edge Aware Image Processing with the Bilateral
Grid(J.
Chen, S. Paris, F. Durand)

          (SELECT *

Bilateral
Filter(J.
Chen)

            FROM SC SC2

Local Histogram Equalization using Bilateral
Grid(J.
Chen)

            WHERE SC2.Sno=SC1.Sno AND
SC2.Grade <=

Streaming Multigrid for Gradient-Domain Operations on Large
Images(M.
Kazhdan)

                       (SELECT
AVG(SC3.Grade)

Distributed Gradient-Domain Processing of Planar and Spherical
Images(M.
Kazhdan)

                         FROM SC
SC3

Metric Aware Processing of Spherical
Imagery(M.
Kazhdan)

                        
WHERE SC3.Cno=SC2.Cno

An Image Inpainting Technique Based on the Fast Marching
Method(A.
Telea)

                       )

MIRT: Medical Image Registration
Toolbox(similarity,
mutual information MI, residual complexity RC, sum square differences
SSD, sum absolute differences SAD, correlation coefficient CC, CD2, MS,
transformation models, parametric free form deformation FFD,
non-parametric variational, implicit euler, curvature-based
regularization, multiresolution) (A. Myronenko)

          )

FreeSurfer:
reconstruction of the brain’s cortical surface from structural MRI data
and overlay of functional MRI data
(wiki)
(contributors)

   

DotCount:
count the number of dots in an image (M. Reuter)

 

FDim:
compute fractal dimension of grayscale image (Capacity Dimension,
Information Dimension, Correlation Dimension, Probability Dimension) (M.
Reuter)

(3)检索至少选修LIU老师所授课程中一门学科的女学员姓名。
    SELECT SNAME
    FROM S
    WHERE SEX=‘F’ AND S# IN
     (SELECT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=‘LIU’)

Robust Registration and Template Estimation of 3D MRI
Images(unbiased
inverse consistent registration, modality rigid registration, outlier
detection robust statistics, symmetry, global intensity scaling,
automatic sensitivity parameter estyimation, unbiased half-way space)
(M. Reuter)

NOTICE:有三种写法,例如联接查询写法:
    SELECT SNAME
    FROM S,SC,C
    WHERE SEX=‘F’ AND SC.S#=S.S#
    AND SC.C#=C.C#
    AND TEACHER=’LIU’
但上一种写法更加好有的。

PatchMatch: A Randomized Correspondence Algorithm for Structural Image
Editing(C.
Barnes, E. Schectman, A. Finkelstein, B. Goldman)

(4)检索WANG同学不学的教程的课程号。
    SELECT C#
    FROM C
    WHERE C# NOT IN
     (SELECT C#
     FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’))

The Generalized PatchMatch Correspondence
Algorithm(C.
Barnes, E. Schectman, A. Finkelstein, B. Goldman)

(5)检索至少选修两门学科的学童学号。
    SELECT DISTINCT X.SNO
    FROM SC X,SC Y
    WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO
Notice:对表SC举行自连接,X,Y是SC的五个小名。

Emerging Images
Demo(N.
Mitra, H.-K. Chu, T.-Y. Lee, L. Wolf, H. Yeshurun, D. Cohen-Or)

(6)探求全部学生都选修的学科的学科号与课程名。
    SELECT C#,CNAME
    FROM C
    WHERE NOT EXISTS
     (SELECT *
     FROM S
     WHERE S# NOT IN
      (SELECT *
      FROM SC
      WHERE  SC.C#=C.C#))

GPU_KLT: A GPU-based Implementation of the Kanade-Lucas-Tomasi Feature
Tracker(S.
Sinha)

要从语义上分解:(1)采纳课程的学科号与课程名,不设有不选这门课的同窗。
   在这之中,“不选那门课的同桌”能够表示为:

Gain-Adaptive KLT Tracking and TV-L1 optical flow on the
GPU(C.
Zach)

SELECT *

 FROM  S

 WHERE  S# NOT IN

  (SELECT  *

      FROM SC  

      WHERE  SC.C#=C.C#)

 

或者

SELECT *

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  *

      FROM SC  

      WHERE S.S#=C.S# AND 

SC.C#=C.C# )

 

SSBA: Simple Sparse Bundle
Adjustment(C.
Zach)

      

Bundler: Structure from Motion (SfM) for Unordered Image
Collections(N.
Snavely)

(7)寻找选修课程包括LIU先生所教授的学生学号。    
     SELECT DISTINCT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=’LIU’))   

sba : A Generic Sparse Bundle Adjustment C/C++ Package Based on the
Levenberg-Marquardt
Algorithm(M.
Lourakis)

3.3 设有多少个着力表宝马7系(A,B,C)和S(D,E,F),试用SQL查询语句表述下列关系代数表达式:
  (1)πA(R)(2)σB=’17’(R)(3)R×S(4))πA,FC=D(R×S))
(1)SELECT A FROM R
(2)SELECT * FROM R WHERE B=’17’
(3)SELECT A,B,C,D,E,F FROM R,S
(4)SELECT A,F FROM R,S WHERE R.C=S.D

homest: A C/C++ Library for Robust, Non-linear Homography
Estimation(M.
Lourakis)

3.43.4 设有五个基本表君越(A,B,C)和S(A,B,C)试用SQL查询语句表述下列关系代数表达式:
  (1)R∪S  (2)R∩S  (3)R-S  (4)πA,B(R)πB,C(S)

CMVS: Clustering Views for Multi-view
Stereo(Y.
Furukawa)

    (1)SELECT A,B,C
      FROM R
      UNION
      SELECT A,B,C
      FROM S

PMVS: Patch-based Multi-view Stereo
Software(Y.
Furukawa, J. Ponce)

    (2)SELECT A,B,C
      FROM R
      INTERSECT
      SELECT A,B,C
      FROM S

VisualSFM: A Visual Structure from Motion
System(C.
Wu)

    (3)SELECT A,B,C
      FROM R
      WHERE NOT EXISTS
       (SELECT A,B,C
       FROM S
       WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

SiftGPU: A GPU Implementation of Scale Invariant Feature Transform
(SIFT)(C.
Wu)

    (4)SELECT R.A,R.B,S.C
      FROM R,S
      WHERE R.B=S.B

Global Contrast based Salient Region
Detection(M-M.
Cheng, G-X. Zhang, N. Mitra, X. Huang, S-M. Hu)

3.5 试陈述SQL语言的关系代数特点和元组演算特点。
(P61-62)

Robust Image Retargeting via Axis-Aligned
Deformation(D.
Panozzo, O. Weber, O. Sorkine)

3.6 试用SQL查询语句表述下列对教学数据库中三个核心表S、SC、C的查询:

imageblending(Poisson
Image Editing)
(Authors)

(1)总括有学生选修的科目门数。
    SELECT COUNT(DISTINCT C#) FROM SC

Image-Based Localization using Structure-from-Motion
Models(T.
Sattler, B. Leibe, L. Kobbelt)

(2)求选修C4学科的学生的平均年龄。
    SELECT AVG(AGE)
    FROM S
    WHERE S# IN
     (SELECT S#
     FROM SC
     WHERE C#=’C4′)
或者,
    SELECT AVG(AGE)
    FROM S,SC
    WHERE S.S#=SC.S# AND C#=’004′

Sampling

(3)求LIU老师所授课程的每门学科的学毕生均战绩。
   SELECT CNAME,AVG(GRADE)
   FROM SC ,C
   WHERE SC.C#=C.C# ANDTEACHER=’LIU’
   GROUP BY C#   

Efficient Generation of Poisson-Disk Sampling
Patterns(T.
Jones)

(4)总结每门课程的学生选修人数(超越10人的科目才总计)。供给输出课程号和选修人数,查询结果按人头降序排列,若人数相同,按学科号升序排列。
    SELECT DISTINCT C#,COUNT(S#)
    FROM SC
    GROUP BY C#
    HAVING COUNT(S#)>10
    ORDER BY 2 DESC, C# ASC

Antimony(poisson-disk
sample generation) (D. Dunbar, G. Humphreys)

(5)检索学号比WANG同学大,而年纪比他小的学习者姓名。
    SELECT X.SNAME
    FROM S AS X, S AS Y
    WHERE Y.SNAME=’WANG’ AND X.S#>Y.S# AND X.AGE<Y.AGE

2D Poisson disk sampling with dart
throwing(M.
Pharr)

(6)检索姓名以WANG打头的持有学员的人名和年龄。
    SELECT SNAME,AGE
    FROM S
    WHERE SNAME LIKE ‘WANG%’

Random or Uniform points on a
sphere[Page]
(J. O’Rourke)

(7)在SC中寻觅战表为空值的学童学号和课程号。
    SELECT S#,C#
    FROM SC
    WHERE GRADE IS NULL

Sampling with Hammersley and Halton
Points(T.-T.
Wong, P.-A. Heng, W.-S. Luk)

(8)求年龄大于女子学校友平均年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE
Y.SEX=’女’)

A Low Distortion Map Between Disk and
Square(P.
Shirley, K. Chiu)

(9)求年龄当先全数女子高校友年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE
Y.SEX=’女’)
除法运算

2D Fast Object
Distribution(Halton
sequence computation)
[Page]
(A. Willmott)

3.7 试用SQL更新语句表明对教学数据库中多少个着力表S、SC、C的逐一更新操作:
(1)往基本表S中插入一个学生元组(‘S9’,‘WU’,18)。
    INSERT INTO S(S#,SNAME,AGE) VALUES(’59’,’WU’,18)
(2)在主题表S中搜索每一门课程成绩都高出等于80分的学员学号、姓名和性别,并把检索到的值送往另二个已存在的基本表STUDENT(S#,SANME,SEX)。
    INSERT INTO STUDENT(S#,SNAME,SEX)
     SELECT S#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      (SELECT * FROM SC WHERE
       GRADE<80 AND S.S#=SC.S#)

Generalized Stratified Sampling Using the Hilbert
Curve(M.
Steigleder, M. McCool)

(3)在着力表SC中删去尚无战绩的选课元组。
    DELETE FROM SC
     WHERE GRADE IS NULL

A Stratified Sampling Technique for an
Ellipse(C.-M.
Wang, N.-C. Hwang)

(4)把WANG同学的上学选课和战表整体删减。
    DELETE FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’)

2D Hilbert Curve
Generator(also
precomputed C++ arrays for orders 1-10) (J. Chen)

(5)把选修MATHS课不比格的成绩全改为空值。
    UPDATE SC
    SET GRADE=NULL
    WHERE GRADE<60 AND C# IN
      (SELECT C#
      FROM C
      WHERE CNAME=’MATHS’)

Accurate Multi-dimensional Poisson-disc
Sampling(M.
Gamito, S. Maddock)

(6)把低于总平均成绩的女子高校友战表升高5%。
    UPDATE SC
    SET GRADE=GRADE*1.05
    WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S#
FROM SWHERE SEX=’F’)

interpolate3d: A Natural Neighbour Interpolation program for 3D
data(R.
Hemsley)

(7)在基本表SC中期维修改C4学科的成绩,若战表小于等于75分时升高5%,若成绩超过75分时进步4%(用四个UPDATE语句达成)。
    UPDATE SC
     SET GRADE=GRADE*1.05
     WHERE C#=’C4′ AND GRADE<=75
    UPDATE SC
     SET GRADE=GRADE*1.04
     WHERE C#=’C4′ AND GRADE>75

Blue Noise through Optimal
Transport(lloyd
iteration, voronoi tessellation, arbitrary density functions) (F. de
Goes, K. Breeden, V. Ostromoukhov, M. Desbrun)

3.8 在第1章例1.4中涉及“旅馆管理”关系模型有七个关系情势:
  零件 PART(P#,PNAME,COLOR,WEIGHT)
  项目 PROJECT(J#,JNAME,DATE)
  供应商 SUPPLIER(S#,SNAME,SADDR)
  供应 P_P(J#,P#,TOTOAL)
  采购 P_S(P#,S#,QUANTITY)

Capacity-Constrained Delaunay Triangulation for Point
Distributions(Y.
Xu, L. Liu, C. Gotsman, S. Gortler)

(1)试用SQLDDL语句定义上述多少个基本表,并证实主键和外键。
    CREATE TABLE PART
    (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR(10),WEIGHT REAL,
    PRIMARY KEY(P#))
    
    CREATE TABLE PROJECT
    (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE,
    PRIMARY KEY(J#))
    
    CREATE TABLE SUPLIER
    (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY(S#))
    
    CREATE TABLE P_P
    (J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY(J#,P#),
    FOREIGN KEY(J#) REFERENCE PROJECT(J#),
    FOREIGN KEY(P#) REFERENCE PART(P#))
    
    CREATE TABLE P_S
    (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY(P#,S#),
    FOREIGN KEY(P#) REFERENCE PART(P#),
    FOREIGN KEY(S#) REFERENCE SUPLIER(S#))
    

Data Structures and Compression

(2)试将PROGECT、P_P、PART五个基本表的本来联接定义为二个视图VIEW1,PART、P_S、SUPPLIE酷路泽四个基本表的自然联接定义为二个视图VIEW2。
    CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT
PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT,PART,P_P
          WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW
VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT
PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART,P_S,SUPPLIER
          WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

Efficient Normal Compression in CryENGINE 3
[PPTX]
[PDF]
(texture and code in slides)
[Page]
(A. Kaplanyan)

(3)试在上述五个视图的基本功上拓宽数据查询:

Triangle Strip
Compression(M.
Isenburg)

    1)检索新加坡的中间商所供应的组件的数码和名字。
    SELECT P#,PNAME FROM VIEW2 WHERE SADDR=’SHANGHAI’

Out-of-Core Compression for Gigantic Polygon
Meshes(M.
Isenburg, S. Gumhold)

    2)检索项目J4所用零件的代理商编号和名字。
    SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1
WHERE J#=’J4′)

Streaming Compression of Triangle
Meshes(M.
Isenburg, P. Lindstrom, J. Snoeyink)

3.9 对于教学数据库中基本表SC,已创设下列视图:
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT(C#),AVG(GRADE)
FROMSC
GROUPBYS#
试推断下列查询和更新是或不是同意施行。若允许,写出转变来宗旨表SC上的呼应操作。
  (1)
SELECT*
FROMS_GRADE
      允许
   SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

Streaming Compression of Tetrahedral Volume
Meshes(M.
Isenburg, P. Lindstrom, S. Gumhold, J. Shewchuck)

  (2)
SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE>80
    允许
   SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

Streaming Compression of Delaunay
Triangulations(M.
Isenburg, Y. Liu, J. Shewchuck, J. Snoeyink)

  (3)
SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM>(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’)
    允许
   SELECT S#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE
Y.S#=’S4′)
   GROUP BY S#

LAStools: converting, viewing, and compressing LIDAR data in LAS
format(M.
Isenburg, J. Shewchuck)

  (4)
UPDATES_GRADE
SETC_NUM=C_NUM+1
WHERES#=‘S4’
    不允许

Streaming
Meshes(M.
Isenburg, P. Lindstrom)

  (5)
DELETEFROMS_GRADE
WHEREC_NUM>4
    不允许

OpenRACM: Random-Accessible Compressed
Meshes(S.E.
Yoon, P. Lindstrom)

3.10 预管理形式对于嵌入式SQL的贯彻有何样首要意义?
   
预管理方式是先用预管理程序对源程序开展扫描,识别出SQL语句,并拍卖成宿主语言的函数调用方式;
然后再用宿主语言的编写翻译程序把源程序编写翻译成指标程序。那样,不用增加宿主语言的编写翻译程序,
就会管理SQL语句。

An Improved Adjacency Data Structure for Fast Triangle
Stripping(P.
Reuter, J. Behr, M. Alexa)

3.11 在宿主语言的主次中选取SQL语句有何规定?
在宿主语言的程序中动用SLQ语句有以下规定:
(1)在前后相继中要分别SQL语句与宿主语言说话
(2)允许嵌入的SQL语句引用宿主语言的前后相继变量(称为分享变量),但有两条规定:
   1)援引时,这几个变量前必得加“:”作为前缀标志,以示与数据库中变量有分别。
   2)那个变量由宿主语言的程序定义,并用SQL的DECLARE语句说明。
(3)SQL的成团管理方式与宿主语言单记录管理格局之间要协和。
要求采纳游标机制,把会集操作转产生单记录管理格局。

Simple and Efficient Traversal Methods for Quadtrees and
Octrees(S.
Frisken, R. Perry)

3.12SQL的集聚管理格局与宿主语言单记录处理形式之间什么协和?
    由于SQL语句处理的是记录会集,而宿主语言语句一次只可以管理一个记录,
因而要求用游标(cousor)机制,把集合操作调换来单记录管理格局。

Octree
implementation(H.
Ainsworth)

2.13 嵌入式SQL语句曾几何时不必涉及到游标?哪天必得涉及到游标?
    (1)INSERT、DELETE、UPDATE语句,查询结果一定是单元组时的SELECT语句,
都可一直嵌入在主程序中应用,不必涉及到游标。
    (2)当SELECT语句询问结果是两个元组时,此时宿主语言程序无法运用,
必要求用游标机制把三个元组一遍三个地传递给宿主语言管理。

Fast Generation of Pointerless Octree
Duals(T.
Lewiner, V. Mello, A. Peixoto, S. Pesco, H. Lopes)

 

Numerics and Optimization

 

CNC: CUDA sparse symmetric matrix
solver(L.
Buatois, G. Caumon, B. Levy)

 

OpenNL: Open Numerical
Library(direct/iterative
sparse solvers, preconditioners, Least-Squares Conformal Maps (LSCM)
implementation)
(Authors)

(电商)数据库原理及运用_宪章考核武器试验题及参谋答案

CGAL(LP linear
programming, QP quadratic programming)
(Authors)

 

TAUCS[Package
Mirror]
(sparse preconditioned conjugate-gradient/minres, sparse Cholesky
(sym-pos-semi-def), sparse LU, sparse LDLT) (S. Toledo, D. Chen, V.
Rotkin)

    一、单选 (每空1分,共10分)

UMFPACK(unsymmetric
sparse LU) (T. Davis)

    1.在Access数据库中,数据保存在(   
)对象中。

newmat(C++
dense matrix library
[documentation])
(R. Davies)

       A.窗体        B.查询         
C.报表          D.表

GPUFFTW(GPU
power-of-two FFT library) (N. Govindaraju, D. Manocha)

   
2.纵然某一字段数据型为文本型,字段大小为8,该字段中最多可输入(   
)个汉字。

LUGPU(GPU
dense-matrix LU decomposition) (N. Galoppo, N. Govindaraju, M. Henson,
D. Manocha)

       A.8          B.4              C.16           D.32

CoMISo: Constrained Mixed Integer
Solver(H.
Zimmer, D. Bommes, L. Kobbelt)

    3.文本型字段最多能够存放(   
)个字符。

chag:ppCUDA
parallel primitives library (reduction, prefix operations (scan),
compaction, radix sort)

       A.250        B.252        
C.254           D.255

Blossoming
softwarefor
analyzing polynomial functions (S. Mann, W. Liu)

    4.Access顾客操作分界面由(   
)部分组成。

Automatic Differentiation, C++ Templates, and
Photogrammetry(D.
Piponi)

       A.4           B.5          C.3           D.6

CMA-ES: Covariance Matrix Adaptation – Evolutionary
Strategy(non-linear
non-convex optimization in complex domains) (N. Hansen)

    5.下列(   
)Logo是Access中表对象的标识。

ExpoKit:
matrix exponential for small dense and large sparse matrices (R. B.
Sidje)

       A.         B.         C.        D.

Modal
Matrix:
A class for modal matrix operations (assembly u=Uq, projection q = U^T
u) (J. Barbic)

   
6.在设计Access数据表时,“索引”属性有(    )取值。

Quaternions,MiniVector,Dense
Matrix,Sparse
Matrix(Conjugate
Gradient, Jacobi Preconditioner, Gauss-Seidel ) (J. Barbic)

       A.1              B.2            
 C.3
               D.4

Polar decomposition of a 3×3 matrix and derivatives of polar
decomposition
matrices(J.
Barbic)

    7.Access中包涵有(   
)种数据类型。

levmar : Levenberg-Marquardt nonlinear least squares algorithms in
C/C++(M.
Lourakis)

       A.9              B.10             C.7                D.8

sparseLM : Sparse Levenberg-Marquardt nonlinear least squares in
C/C++(M.
Lourakis)

   
8.在多少个宏中要张开二个报表,应该使用的操作是(    )。

HLBFGS(Preconditioned
L-BFGS, Preconditioned Conjugate Gradient, Gradient Descent, Newton) (Y.
Liu)

       A.OpenForm       B.OpenReport      C.OpenTable      
D.OpenQuery

Conjugate Gradient with Modified Incomplete Cholesky (level zero)
preconditioning(R.
Bridson)

   
9.方可由此Internet实行数量揭橥的目的是(    )。

KKTDirect: a direct solver package for saddle-point ( KKT )
matrices(R.
Bridson)

       A.窗体         B.报表          
C.查询          D.数据访谈页

MPCG: Multi-Preconditioned Conjugate
Gradients(R.
Bridson, C. Greif)

   10.模块窗口由(   
)个部分构成。

Quern: A Sparse QR
Library(R.
Bridson)

       A.2            B.3             C.4              D.5

Data Sets

 

Digital 210
King(building
scans, Revit models, photographs, Green Building XML (gbXML) models, of
210 King St East (Autodesk Toronto building))

    二、填空 (每空1分,共20分)

Unwrapped (planar-parameterized) ABF++/LinABF
Meshes(.OBJ
format, includes normal maps and decimated meshes)

   
1.在人工管理和文书处理阶段,程序设计__依赖于 ___数据表示。

AIM@SHAPE 3D model
repository

   
2.在文件系统中,存取数据的基本单位为___记录____,在数据库系统中,存取数据的为主单位为___数据项_____。

DemoGraphite(bunny,
horse, tiger-face, octaflower, rocker arm, engine volume, skull volume)

   
3.若实体A和B是多对多的联系,实体B和C是1对1的关联,则实体A和C是___多_____对___多_____的联系。

Georgia Tech Large Geometric Models
Archive(Bunny,Turbine
Blade,skeleton hand,dragon,buddha,horse,visible man,grand canyon,puget
sound,angel)

   
4.在三个关乎中不容许出现重复的____元组____,也不允许出现具有同样名字的___属性_____。

Stanford 3D Scanning
Repository(bunny,
drill bit, buddha, dragon, armadillo, lucy, asian dragon, manuscript,
thai statue)

   
5.数据库系统中的四类顾客分别为____数据库助理馆员、数据库设计师、应用程序猿、终端顾客_____。

Stanford Volume Data
Archive(CThead,
MRBrain, CTbunny)

   
6.在存取数据库的数目标进度中,使用了八个数据缓冲区,分别为___系统_____缓冲区和____用户
____缓冲区。

Stanford Light Field
Archive

   
7.学生关系中的班级号属性与班级关系中的班级号主码属性相对应,则____班级号____为学习者关系中的___外码___。

UNC Dynamic Mesh
Datasets(per-frame
meshes, exploding dragon and bunny, cloth simulation, N-body simulation,
falling letters)

   
8.设叁个提到A具备a1个性格和a2个元组,关系B具备b1个属性和b2个元组,则涉及A´B具备___a1+b1____个本性和____
a2´b2 ____个元组。

Image-Based 3D
Models(VRML/TRI
formats, multiple resolutions, textured [mesh
viewer])
(C. Esteban, F. Schmitt)

   
9.设贰个学员关系为S(学生号,姓名),课程关系为C(课程号,课程名),选课关系为X(学生号,课程号,战绩),求出全部选课的学员音讯的演算表明式为_____Õ学生号(X)______与____S
____的当然连接。

Multi-frame 3D scans from video
depthmaps(triangulated
point clouds, OBJ format,100-2200 frames) (T. Weise, B. Leibe, L. Van
Gool)

   10.在八个涉及Escort中,若存在X→Y和X→Z,则设有_____
X→(Y,Z)_______,称此为函数重视的合併性准则。

ETH Face Pose Range Image Data
Set(10,000
640×480 range image point sets of 20 moving faces, read/write code) (M.
Breitenstein, D. Kuettel, T. Weise, L. van Gool, H. Pfister)

 

Quadrangulations(fandisk,beetle,fertility,rockerarm)
(H. Zimmer, D. Bommes, L. Kobbelt)

    三、填空 (每空1分,共20分)

Geometry
Videos(H.
Bricenno)

   
1.若一个事关的其余非主属性都不有的重视重视于别的候选码,则称该关系到达____第二____范式。

PSU Near-Regular Texture
Database(S.
Lee, Y. Liu)

   
2.在SQL中,列级完整性约束分为__6__种情形,表级完整性约束分为__4__种情况。

CMU Motion Capture
Database

   3.
在SQL中,各个视图中的列能够来自差别的___表___,它是在原有表的底子上____建立____的逻辑意义上的新涉嫌。

Skinning Mesh Animations
data(horse,
camel, elephant, chicken, snake ) (D. James, C. Twigg)

   4. 在SQL的查询语句中,group
by选项达成____分组总结______功效,order
by选项达成对结果表的____排序_____功能。

Deformation Transfer for Triangle
Meshes(horse,
camel, cat, lion, faces, flamingo, elephant) (R. Sumner, J. Popovic)

   
5.对于较复杂的系统,概念设计阶段的严重性职分是:首先依照系统的顺序部分应用画出各自对应的____局部ER图______,然后再开展汇总和一体化规划,画出_____整体ER图_____。

Mesh Ensemble Motion
Graphs(D.
James, C. Twigg, A. Cove, R. Wang)

   
6.机器完结阶段的靶子是在Computer种类中获取二个满足______设计需求、成效完善、操作便利___的数据库应用类别。

Cornell Light Measurement
Data(light
source spectra, filter and lens transmission spectra, camera response
spectra, reflectance data)

    7.Access的客商操作分界面由    标题栏、菜单栏、工作区  、工具栏、状态栏等三个部分构成。

The Cornell
Box

   
8.Access“表”结构划设想计窗口中上半片段的“表设计器”是由      字段名称、数据类型、表明        等三列组成。

RenderPark(HDR,
galerkin radiosity, stochastic jacobi radiosity, hierarchical,
clustering, view-importance, higher-order approximations, random walk
radiosity, photon mapping, stochastic ray tracing, bi-directional path
tracing) (P. Bekaert,F. de Laet, P. Peers, V. Masselus, J. Prikryl, P.
Dutre, O. Ceulemans)

    9.Access中的窗体由      页眉、主体      和页脚等多个部分组成。

RaVi(real-time
raytracer) (M. Granz)

 

WinOSi(iterative
two pass optical simulation raytracing) (M. Granz)

    四、填空 (每空1分,共20分)

Scopia.es 3D
models(home
furnishing objects, buildings, plants, characters, .3ds, .blend, .sh3d,
.obj) (C. Folch, Scopia.es)

   1.
设多少个提到为悍马H2(A,B,C,D,E),它的微小函数信赖集为FD={A→B,A→C,(C,D)→E},则该关系的候选码为_____(A,D)___,候选码函数决定E是___伪传递___性。

Crytek Sponza Model
v2(increased
complexity, 3ds, OBJ formats, textures) (F. Meinl, Crytek)

   
2.设贰个事关为ENCORE(A,B,C,D,E),它的小小函数依赖集为FD={A→B,A→C,(A,D)→E},该关系只满意___第一_____范式,若要标准化为高超级的范式,则将获得____2____个关系。

TrueMarble earth image
dataset(multiple
resolutions from 32km to 250m, PNG and geo-referenced GeoTIFF formats)
(Unearthed Outdoors LLC)

   
3.在事实上的数据库管理连串中,对数据库的操作方法有_____一声令下交互、程序推行、窗口界面______等三种。

Volume-Meshed
Models(Heart,
Eiffel Tower, Basket, Bridge, Dragon, Horse, Dinosaur, 3D Spring, Beam)
(J. Barbic)

    4.在SQL中,主码约束的尤为重要字为____
primary key________,外码约束的重要字为______foreignkey
______。

Volume-Meshed
Models(Cube,
TFire, Tire, Dragon, Cow, Sculpt, LetterP, House, StayPuft) (B. Klinger,
J. Shewchuck)

   
5.基本表属于全局情势中的表,它是____实表____,而视图则属于有个别情势中的表,它是____虚表
____。

McGuire Graphics
Data(San
Miguel, Crytek Sponza, Dabrovic Sponza, Sibenik Cathedral, Conference
Room, Chinese Dragon, Teapot, Cube, Happy Buddha, Hairball, Cornell Box,
Rungholt, Lost Empire, Infinite Realities Head, textured OBJs)

   6.
在SQL新版的查询语句中,select选项达成投影运算,from选项实现____连接____运算,where选项达成____选择___运算。

Caltech Mesh
Compendium(Ball
Joint,Dinosaur Sculpture,Golf Club,Igea Artifact,Isis,Knee,Rocker
Arm,Santa,Screwdriver,Teeth Casting,Horse,Skeleton Hand,Turbine
Blade,Cactus,Cat,Cessna,Club,Cow,Distributor
Cap,Engine,Fandisk,Gargoyle,Hypersheet,Knot,Mechanical Part,Oil
Pump,Teapot,Tyrannosaurus Rex,Dragon,Feline Model,Femme,Happy
Buddha,Headus Skull,Max Planck,Molecule,Rabbit Sculpture,Stanford
Bunny,Zander,Zoë,Duck,Mummy,Bust of Nefertiti,Top,Totem,Vase #1,Vase
#2,Chair,Dental Cast,Foot,Hand,female Mannequin Head,Pregnant
Woman,kissing-children
Sculpture,Shoe,Toilet,Cube,Hexagon,Icosahedron,Octahedron,Pentagon,Quadrilateral,Right
Tetrahedron,Right Triangle,Armadillo Man,Dragon,Happy Buddha,Stanford
Bunny,Cat Head,Face,Mannequin Head,Spocks Head,Stamped Part,
Stegosaurus, Torus, Triceratops, Venus Torso)

   
7.数据字典是对系统工作流程中____数据____和____处理____的描述。

Aachen photo
dataset(photos,
SIFT descriptors, 3D reconstruction) (T. Sattler)

   
8.关全面据库系统中的全局格局由若干个基本表所结合,表与表之间的交换是经过定义的____主码____和____外码____实现的。

MIT CSAIL Textured Models
Database()

   
9.在饭店管理中,涉及到的基本表有三个,它们各自为客房表、留宿表、_____行人登记表、成本卡表____。

Vision

   10.在装置或裁撤数据库密码的进度中,密码对于字母     大小写     是乖巧的。

Stanford Calibration Grid
Detector(V.
Vaish)

 

Color CRT Calibration for perception
research(E.
Olds, W. Cowan, P. Jolicoeur)

    五、填空 (每空1分,共10分)

ASIFT: A New Framework for Fully Affine Invariant
Comparison(J.-M.
Morel, G. Yu)

    1.Access的顾客操作分界面由   标题栏、菜单栏、职业区   、工具栏、状态栏等八个部分构成。

SIFT++(A.
Vedaldi)

   
2.Access“表”结构划设想计窗口中上半部分的“表设计器”是由     字段名称、数据类型   和验证等三列组成。

SIFT for
Matlab(A.
Vedaldi)

    3.Access中的窗体由     页眉、主体、页脚      等四个部分构成。

VLFeat(SIFT, MSER,
k-means, hiearchical k-means, agglomerative information bottleneck
(aib), quick shift) (A. Vedaldi, B. Fulkerson)

    4.在Access中模块分为   类模块      
和       标准模块     两连串型。

LFDisplay: a real-time system for light field
microscopy

 

FCam:
FrankenCameracamera
control
library(Contributors)

   
六、依照主教材第四章所给的商品库和教学库,或然依照下列所给的每条SQL查询语句写出相应的效果与利益,或然根据下列所给的每一个作用写出相应的SQL查询语句。(每小题4分,共20分)

Utilities for Structured Light
Scanning(Contributors)

   
在名为货色库的数据库中包涵有商品表1和商品表2,它们的定义分别为:

face-off: Automatic Facial Feature Detection &
Replacement(Authors)

       商品表1(商品代号 char(8),分类名 char(8),单价 float,数量
int)

Google Pose Optimizer: reconstruct pose of sensor based on sensor data
(GPS position, doppler velocity, accelerometers, rate gyroscopes, wheel
encoders, constraint models) (Authors)

       商品表2(商品代号 char(8),产地 char(6),品牌char(6),)

Machine Learning

   
在称呼为教学库的数据库中包罗有上学的小孩子、课程和选课多少个表,它们的概念分别为:

Infer.Net-
Bayesian Inference in Graphical Models (T. Minka, J. Winn, J. Guiver, A.
Kannan)

       学生(学生号 char(7),姓名 char(6),性别
char(2),出生日期 datetime,

ANN- C++
approximate neareset-neighbours in dimensions ~(8,20) (D. Mount, S.
Arya)

            专业 char(10),年级 int)

FLANN-
Fast Library for Approximate Nearest Neighbours (M. Muja, D. Lowe)

       课程(课程号 char(4),课程名 char(10),课程学分
int

Basic Support Vector Machine (SVM)
demo(linear
and RBF kernel) (A. Vedaldi)

       选课(学生号 char(7),课程号 char(4),成绩 int)

Bits and Pieces

    1.select distinct 产地

HXA graphics
library(halton
sequence, sobol sequence, color space conversions, radiance RGBE IO,
random number generators, 2D radial filters) (H. Ainsworth)

        from 商品表2

原来的小说链接:http://www.dgp.toronto.edu/~rms/links.html

       成效:从事商业品库中查询出装有商品的两样产地。

 

    2.select *

        from 学生

        where 学生号 in (select
学生号

          from 选课

          group by 学生号 having
count(*)=1

        )

   
成效:从事教育工作学库中查询出只选修了一门学科的满贯学生。

 

    3.select *

         from 学生

         where 学生号 in (select
学生号

           from 选课

           group by 学生号 having
count(*)<=2   

         ) or not exists (select *

              from 选课

              where
学生.学生号=选课.学生号

         )

    成效:
从事教育工作学库中询问出最多选修了2门科目(含未选任何学科)的全体学童。

 

   
4.从事商业品库中询问出每类(即分类名相同)商品的万丈单价。

 select 分类名,max(单价) as 最高单价

        from 商品表1

        group by 分类名

 

   
5.从事教育工作学库中查询出至少选修了人名称为@m1学员所选课程中一门课的任何上学的小孩子。

select distinct 学生.*

        from 学生,选课

        where 学生.学生号=选课.学生号 and
课程号=any(select 课程号

          from 学生,选课

          where 学生.学生号=选课.学生号
and 姓名=@m1

        )

 

 

 

 

 

1.  
Student(S#,Sname,Sage,Ssex) 学生表 

2.  
Course(C#,Cname,T#) 课程表 

3.  
SC(S#,C#,score) 成绩表 

4.  
Teacher(T#,Tname) 教师表 

5.  
 

6.  
问题: 

7.  
1、查询“001”课程比“002”课程战绩高的具有学生的学号; 

8.  
  select a.S# from (select s#,score from SC where C#=’001′) a,(select s#,score 

9.  
  from SC where C#=’002′) b 

10. 
  where a.score>b.score and a.s#=b.s#; 

11. 
2、查询平均成绩超过60分的同室的学号和平均战表; 

12. 
    select S#,avg(score) 

13. 
    from sc 

14. 
    group by S# having avg(score) >60; 

15. 
3、查询全数同学的学号、姓名、选课数、总战绩; 

16. 
  select Student.S#,Student.Sname,count(SC.C#),sum(score) 

17. 
  from Student left Outer join SC on Student.S#=SC.S# 

18. 
  group by Student.S#,Sname 

19. 
4、查询姓“李”的导师的个数; 

20. 
  select count(distinct(Tname)) 

21. 
  from Teacher 

22. 
  where Tname like ‘李%’; 

23. 
5、查询没学过“叶平”老师课的校友的学号、姓名; 

24. 
    select Student.S#,Student.Sname 

25. 
    from Student  

26. 
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’); 

27. 
6、查询学过“001”并且也学过数码“002”课程的同学的学号、姓名; 

28. 
  select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=’001’and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′); 

29. 
7、查询学过“叶平”老师所教的全数课的同桌的学号、姓名; 

30. 
  select S#,Sname 

31. 
  from Student 

32. 
  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname=’叶平’)); 

33. 
8、查询课程编号“002”的实际业绩比课程编号“001”课程低的具有同学的学号、姓名; 

34. 
  Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002′) score2 

35. 
  from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where score2 <score; 

36. 
9、查询全部课程战表小于60分的同室的学号、姓名; 

37. 
  select S#,Sname 

38. 
  from Student 

39. 
  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 

40. 
10、查询没有学全全部课的同班的学号、姓名; 

41. 
    select Student.S#,Student.Sname 

42. 
    from Student,SC 

43. 
    where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 

44. 
11、查询至少有一门课与学号为“1001”的同室所学一样的同学的学号和姓名; 

45. 
    select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#=’1001′; 

46. 
12、查询至少学过学号为“001”同学全部一门课的别的同学学号和姓名; 

47. 
    select distinct SC.S#,Sname 

48. 
    from Student,SC 

49. 
    where Student.S#=SC.S# and C# in (select C# from SC where S#=’001′); 

50. 
13、把“SC”表中“叶平”老师教的课的成就都转移为此课程的平均战绩; 

51. 
    update SC set score=(select avg(SC_2.score) 

52. 
    from SC SC_2 

53. 
    where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname=’叶平’); 

54. 
14、查询和“1002”号的同室学习的科目一模一样的别的同学学号和姓名; 

55. 
    select S# from SC where C# in (select C# from SC where S#=’1002′) 

56. 
    group by S# having count(*)=(select count(*) from SC where S#=’1002′); 

57. 
15、删除学习“叶平”老师课的SC表记录; 

58. 
    Delect SC 

59. 
    from course ,Teacher  

60. 
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’; 

61. 
16、向SC表中插入一些笔录,那几个记录要求符合以下条件:未有上过编号“003”课程的同班学号、2、 

62. 
    号课的平分战表; 

63. 
    Insert SC select S#,’002′,(Select avg(score) 

64. 
    from SC where C#=’002′) from Student where S# not in (Select S# from SC where C#=’002′); 

65. 
17、按平均成绩从高到低展现全部学员的“数据库”、“企管”、“立陶宛(Lithuania)语”三门的学科成绩,按如下形式浮现: 学生ID,,数据库,集团管理,法文,有效课程数,有效平均分 

66. 
    SELECT S# as 学生ID 

67. 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’004′) AS 数据库 

68. 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’001′) AS 集团管理 

69. 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’006′) AS 英语 

70. 
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 

71. 
    FROM SC AS t 

72. 
    GROUP BY S# 

73. 
    ORDER BY avg(t.score)  

74. 
18、查询各科成绩最高和压低的分:以如下情势彰显:课程ID,最高分,最低分 

75. 
    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 

76. 
    FROM SC L ,SC AS R 

77. 
    WHERE L.C# = R.C# and 

78. 
        L.score = (SELECT MAX(IL.score) 

79. 
                      FROM SC AS IL,Student AS IM 

80. 
                      WHERE L.C# = IL.C# and IM.S#=IL.S# 

81. 
                      GROUP BY IL.C#) 

82. 
        AND 

83. 
        R.Score = (SELECT MIN(IR.score) 

84. 
                      FROM SC AS IR 

85. 
                      WHERE R.C# = IR.C# 

86. 
                  GROUP BY IR.C# 

87. 
                    ); 

88. 
19、按各科平均战绩从低到高和及格率的百分比从高到低顺序 

89. 
    SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均战表 

90. 
        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 

91. 
    FROM SC T,Course 

92. 
    where t.C#=course.C# 

93. 
    GROUP BY t.C# 

94. 
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 

95. 
20、查询如下课程平均战表和及格率的比例(用”1行”展现): 集团管理(001),马克思(002),OO&UML (003),数据库(004) 

96. 
    SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘001’ THEN 1 ELSE 0 END) AS 公司管理平均分 

97. 
        ,100 * SUM(CASE WHEN C# = ‘001’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘001’ THEN 1 ELSE 0 END) AS 企业管理及格百分数 

98. 
        ,SUM(CASE WHEN C# = ‘002’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘002’ THEN 1 ELSE 0 END) AS 马克思平均分 

99. 
        ,100 * SUM(CASE WHEN C# = ‘002’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘002’ THEN 1 ELSE 0 END) AS 马克思及格百分数 

100.                ,SUM(CASE WHEN C# = ‘003’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘003’ THEN 1 ELSE 0 END) AS UML平均分 

101.                ,100 * SUM(CASE WHEN C# = ‘003’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘003’ THEN 1 ELSE 0 END) AS UML及格百分数 

102.                ,SUM(CASE WHEN C# = ‘004’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘004’ THEN 1 ELSE 0 END) AS 数据库平均分 

103.                ,100 * SUM(CASE WHEN C# = ‘004’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘004’ THEN 1 ELSE 0 END) AS 数据库及格百分数 

104.          FROM SC 

105.        21、查询分化老师所教差异科目平均分从高到低呈现 

106.          SELECT max(Z.T#) AS 教授ID,MAX(Z.Tname) AS 教授姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 

107.            FROM SC AS T,Course AS C ,Teacher AS Z 

108.            where T.C#=C.C# and C.T#=Z.T# 

109.          GROUP BY C.C# 

110.          ORDER BY AVG(Score) DESC 

111.        22、查询如下课程战绩第 3 名到第 6 名的学员战绩单:集团管理(001),马克思(002),UML (003),数据库(004) 

112.            [学生ID],[学员姓名],企业管理,马克思,UML,数据库,平均战绩 

113.            SELECT  DISTINCT top 3 

114.              SC.S# As 学生学号, 

115.                Student.Sname AS 学生姓名 , 

116.              T1.score AS 集团管理, 

117.              T2.score AS 马克思, 

118.              T3.score AS UML, 

119.              T4.score AS 数据库, 

120.              ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 

121.              FROM Student,SC  LEFT JOIN SC AS T1 

122.                              ON SC.S# = T1.S# AND T1.C# = ‘001’ 

123.                    LEFT JOIN SC AS T2 

124.                              ON SC.S# = T2.S# AND T2.C# = ‘002’ 

125.                    LEFT JOIN SC AS T3 

126.                              ON SC.S# = T3.S# AND T3.C# = ‘003’ 

127.                    LEFT JOIN SC AS T4 

128.                              ON SC.S# = T4.S# AND T4.C# = ‘004’ 

129.              WHERE student.S#=SC.S# and 

130.              ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 

131.              NOT IN 

132.              (SELECT 

133.                    DISTINCT 

134.                    TOP 15 WITH TIES 

135.                    ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 

136.              FROM sc 

137.                    LEFT JOIN sc AS T1 

138.                              ON sc.S# = T1.S# AND T1.C# = ‘k1’ 

139.                    LEFT JOIN sc AS T2 

140.                              ON sc.S# = T2.S# AND T2.C# = ‘k2’ 

141.                    LEFT JOIN sc AS T3 

142.                              ON sc.S# = T3.S# AND T3.C# = ‘k3’ 

143.                    LEFT JOIN sc AS T4 

144.                              ON sc.S# = T4.S# AND T4.C# = ‘k4’ 

145.              ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 

146.         

147.        23、总结列印各科战表,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

148.            SELECT SC.C# as 课程ID, Cname as 课程名称 

149.                ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 – 85] 

150.                ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 – 70] 

151.                ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 – 60] 

152.                ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 

153.            FROM SC,Course 

154.            where SC.C#=Course.C# 

155.            GROUP BY SC.C#,Cname; 

156.         

157.        24、查询学一生均成绩及其排名 

158.              SELECT 1+(SELECT COUNT( distinct 平均战绩) 

159.                      FROM (SELECT S#,AVG(score) AS 平均成绩 

160.                              FROM SC 

161.                          GROUP BY S# 

162.                          ) AS T1 

163.                    WHERE 平均战表 > T2.等分成绩) as 名次, 

164.              S# as 学生学号,平均成绩 

165.            FROM (SELECT S#,AVG(score) 平均成绩 

166.                    FROM SC 

167.                GROUP BY S# 

168.                ) AS T2 

169.            O大切诺基DEMurano BY 平均战绩 desc; 

170.          

171.        25、查询各科成绩前三名的记录:(不牵记成绩并列意况) 

172.              SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 

173.              FROM SC t1 

174.              WHERE score IN (SELECT TOP 3 score 

175.                      FROM SC 

176.                      WHERE t1.C#= C# 

177.                    ORDER BY score DESC 

178.                      ) 

179.              ORDER BY t1.C#; 

180.        26、查询每门科目被选修的学员数 

181.          select c#,count(S#) from sc group by C#; 

182.        27、查询出只选修了一门课程的整个上学的小孩子的学号和姓名 

183.          select SC.S#,Student.Sname,count(C#) AS 选课数 

184.          from SC ,Student 

185.          where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1; 

186.        28、查询男生、女子人数 

187.            Select count(Ssex) as 男人人数 from Student group by Ssex having Ssex=’男’; 

188.            Select count(Ssex) as 女孩子人数 from Student group by Ssex having Ssex=’女’; 

189.        29、查询姓“张”的学生名单 

190.            SELECT Sname FROM Student WHERE Sname like ‘张%’; 

191.        30、询问同名同性学生名单,并总计同有名气的人数 

192.          select Sname,count(*) from Student group by Sname having  count(*)>1;; 

193.        31、1981年出生的学生名单(注:Student表中Sage列的档次是datetime) 

194.            select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age 

195.            from student 

196.            where  CONVERT(char(11),DATEPART(year,Sage))=’1981′; 

197.        32、查询每门学科的平分成绩,结果按平均战绩升序排列,平均成绩同样时,按学科号降序排列 

198.            Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 

199.        33、查询平均成绩超过85的具有学生的学号、姓名和平均成绩 

200.            select Sname,SC.S# ,avg(score) 

201.            from Student,SC 

202.            where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85; 

203.        34、查询课程名称叫“数据库”,且分数低于60的学员姓名和分数 

204.            Select Sname,isnull(score,0) 

205.            from Student,SC,Course 

206.            where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname=’数据库’and score <60; 

207.        35、查询全体学生的选课情形; 

208.            SELECT SC.S#,SC.C#,Sname,Cname 

209.            FROM SC,Student,Course 

210.            where SC.S#=Student.S# and SC.C#=Course.C# ; 

211.        36、查询任何一门学科成绩在70分以上的全名、课程名称和分数; 

212.            SELECT  distinct student.S#,student.Sname,SC.C#,SC.score 

213.            FROM student,Sc 

214.            WHERE SC.score>=70 AND SC.S#=student.S#; 

215.        37、查询不如格的学科,并按学科号从大到小排列 

216.            select c# from sc where scor e <60 order by C# ; 

217.        38、查询课程编号为003且课程战绩在80分以上的上学的小孩子的学号和姓名; 

218.            select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#=’003′; 

219.        39、求选了课程的学员人数 

220.            select count(*) from sc; 

221.        40、查询选修“叶平”老师所授课程的学员中,战绩最高的上学的小孩子姓名及其成绩 

222.            select Student.Sname,score 

223.            from Student,SC,Course C,Teacher 

224.            where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname=’叶平’ and SC.score=(select max(score)from SC where C#=C.C# ); 

225.        41、查询各种科目及相应的选修人数 

226.            select count(*) from sc group by C#; 

227.        42、查询不一致科目成绩同样的学员的学号、课程号、学生战表 

228.          select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ; 

229.     43、查询每门功课战表最棒的前两名 

230.            SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 

231.              FROM SC t1 

232.              WHERE score IN (SELECT TOP 2 score 

233.                      FROM SC 

234.                      WHERE t1.C#= C# 

235.                    ORDER BY score DESC 

236.                      ) 

237.              ORDER BY t1.C#; 

238.        44、总结每门课程的上学的孩童选修人数(超越10人的教程才总结)。须求输出课程号和选修人数,查询结果按人头降序排列,查询结果按人口降序排列,若人数同样,按学科号升序排列  

239.            select  C# as 课程号,count(*) as 人数 

240.            from  sc  

241.            group  by  C# 

242.            order  by  count(*) desc,c#  

243.        45、检索至少选修两门学科的上学的小孩子学号 

244.            select  S#  

245.            from  sc  

246.            group  by  s# 

247.            having  count(*)  >  =  2 

248.        46、查询任何学生都选修的科目标课程号和科目名 

249.            select  C#,Cname  

250.            from  Course  

251.            where  C#  in  (select  c#  from  sc group  by  c#)  

252.        47、查询没学过“叶平”老师授课的任一门科指标上学的小孩子姓名 

253.            select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname=’叶平’); 

254.        48、查询两门以上比不上格课程的校友的学号及其平均成绩 

255.            select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#; 

256.        49、检索“004”课程分数小于60,按分数降序排列的同窗学号 

257.            select S# from SC where C#=’004’and score <60 order by score desc; 

258.        50、删除“002”同学的“001”课程的成绩 

259.        delete from Sc where S#=’001’and C#=’001′; 

260.         

 

 

 

 

 

 

 

 

 

 

宪章考核武器试验题参谋答案

 

    一、单选 (每空1分,共10分)

   1. D  2. B  3. D 4. B  5. C  6. C 7. B

  1. B 9. D  10. B

二、填空 (每空1分,共20分)

 

   1. 依赖于                 2.
记录、数据项

   3. 多、多                 4.
元组、属性

   5.
数据库管理员、数据库设计师、应用工程师、终端客商(次序无前后相继)

   6. 系统、用户             7.
班级号、外码

   8. a1+b1、a2´b2           9.
Õ学生号(X)、S  (次序无前后相继)

    10. X→(Y,Z)

三、填空 (每空1分,共20分)

 

   1. 第二                           
2.6、4

   3. 表、建构                        4.
分组总结、排序

   5. 局地EEnclave图、全体ECR-V图            6.
布署必要、功用完善、操作便利

   7.
标题栏、菜单栏、专门的职业区(次序无前后相继)

   8.
字段名称、数据类型、表明(次序无先后)

   9. 页眉、主体(次序无前后相继)

四、填空 (每空1分,共20分)

 

   1. (A,D)、伪传递                     

  1. 第一、2

   3.
命令交互、程序实行、窗口分界面(次序无前后相继)

   4. primary key、foreign key

   5. 实表、虚表                        

  1. 连接、选择

   7. 数据、处理(次序无前后相继)          

  1. 主码、外码(次序无前后相继)

   9. 旅客登记表、花费卡表(次序无前后相继)

  1. 大小写

五、填空 (每空1分,共10分)

 

   1. 标题栏、菜单栏、职业区  
(次序无前后相继)

   2. 字段名称、数据类型      
(次序无前后相继)

   3. 页眉、主体、页脚        
(次序无前后相继)

  1. 类模块、标准模块        
    (次序无前后相继)

 

   
六、依照主教材第四章所给的商品库和教学库,大概根据下列所给的每条SQL查询语句写出相应的法力,或然遵照下列所给的种种意义写出相应的SQL查询语句。(每小题4分,共20分)

   1.
从事商业品库中询问出装有商品的例外产地。

   2.
从事教育工作学库中询问出只选修了一门学科的一体学员。

   3.
从事教育工作学库中询问出最多选修了2门课程(含未选任何学科)的任何学生。

   4. select 分类名,max(单价) as 最高单价

        from 商品表1

        group by 分类名

   5. select distinct 学生.*

        from 学生,选课

        where 学生.学生号=选课.学生号 and
课程号=any(select 课程号

          from 学生,选课

          where 学生.学生号=选课.学生号
and 姓名=@m1

        )

 

3.1 名词解释

(1)SQL模式:SQL方式是表和授权的静态定义。贰个SQL情势定义为基本表的聚众。
三个由情势名和形式具备者的客商名或账号来分明,并含有格局中每二个成分(基本表、视图、索引等)的概念。
(2)SQL数据库:SQL(Structured Query
Language),即‘结构式查询语言’,采取葡萄牙共和国(República Portuguesa)语单词表示和结构式的语法则则。
多个SQL数据库是表的集中,它用一个或三个SQL格局定义。
(3)基本表:在SQL中,把守旧的涉及模型中的关系格局称为基本表(Base
Table)。 基本表是实际存款和储蓄在数据库中的表,对应三个事关。
(4)储存文件:在SQL中,把古板的涉及模型中的存款和储蓄形式称为存款和储蓄文件(Stored
File)。 每种存款和储蓄文件与外存上一个物理文件对应。
(5)视图:在SQL中,把守旧的关系模型中的子形式称为视图(View),视图是从若干为主表和(或)别的视图构造出来的表。
(6):在SQL中,把古板的涉嫌模型中的元组称为行(row)。
(7)列:在SQL中,把古板的涉嫌模型中的属性称为列(coloumn)。
(8)实表:基本表被叫作“实表”,它是事实上贮存在数据库中的表。
(9)虚表:视图被称呼“虚表”,创设二个视图时,只把视图的概念存款和储蓄在数额词典中,而不存款和储蓄视图所对应的数目。
(10)相关子查询:在嵌套查询中出现的契合以下特点的子查询:子查询中询问条件注重于外层查询中的有些值,
所以子查询的管理不只二次,要频仍求值,以供外层查询利用。
(11)连接查询:查询时先对表张开笛Carl积操作,然后再做等值联接、选用、投影等操作。
联接查询的效用比嵌套查询低。
(12)交互式SQL:在顶峰交互格局下使用的SQL语言称为交互式SQL。
(13)嵌入式SQL:嵌入在高端语言的顺序中运用的SQL语言称为嵌入式SQL。
(14)分享变量:SQL和宿主语言的接口。分享变量有宿主语言程序定义,再用SQL的DECLARE语句表达,
SQL语句就可引用那一个变量传递数据库音信。
(15)游标:游标是与某一查询结果相交换的标志名,用于把集结操作调换来单记录管理格局。
(16)卷游标:为了制伏游标在带动时无法重临的难堪,SQL2提供了卷游标技巧。
卷游标在推进时不只能沿查询结果七月组顺序原原本本一行行推动,也能一行行再次回到。

3.2 对于教学数据库的四个基本表
  学生S(S#,SNAME,AGE,SEX)
  学习 SC(S#,C#,GRADE)
  课程C(C#,CNAME,TEACHER)
 试用SQL的查询语句表述下列查询:

(1)检索LIU先生所授课程的课程号和课程名。
    SELECT C#,CNAME
    FROM C
    WHERE TEACHER=‘LIU’

(2)检索年龄超越二十四岁的男学生的学号和人名。
    SELECT S#,SNAME
    FROM S
    WHERE (AGE>23) AND (SEX=‘M’)

(3)检索至少选修LIU老师所授课程中一门学科的女上学的儿童姓名。
    SELECT SNAME
    FROM S
    WHERE SEX=‘F’ AND S# IN
     (SELECT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=‘LIU’)

NOTICE:有两种写法,比方联接查询写法:
    SELECT SNAME
    FROM S,SC,C
    WHERE SEX=‘F’ AND SC.S#=S.S#
    AND SC.C#=C.C#
    AND TEACHER=’LIU’
但上一种写法越来越好一些。

(4)检索WANG同学不学的教程的课程号。
    SELECT C#
    FROM C
    WHERE C# NOT IN
     (SELECT C#
     FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’))

(5)检索至少选修两门课程的上学的小孩子学号。
    SELECT DISTINCT X.SNO
    FROM SC X,SC Y
    WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO
Notice:对表SC进行自连接,X,Y是SC的七个外号。

(6)检索全体学生都选修的科指标科目号与课程名。
    SELECT C#,CNAME
    FROM C
    WHERE NOT EXISTS
     (SELECT *
     FROM S
     WHERE S# NOT IN
      (SELECT *
      FROM SC
      WHERE  SC.C#=C.C#))

要从语义上分解:(1)采取课程的科目号与学科名,一纸空文不选这门课的同室。
   个中,“不选那门课的同班”能够表示为:

SELECT *

 FROM  S

 WHERE  S# NOT IN

  (SELECT  *

      FROM SC  

      WHERE  SC.C#=C.C#)

 

或者

SELECT *

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  *

      FROM SC  

      WHERE S.S#=C.S# AND 

SC.C#=C.C# )

 

      

(7)检索选修课程蕴涵LIU先生所教学的学员学号。    
     SELECT DISTINCT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=’LIU’))   

3.3 设有七个主导表途锐(A,B,C)和S(D,E,F),试用SQL查询语句表述下列关系代数表明式:
  (1)πA(R)(2)σB=’17’(R)(3)R×S(4))πA,FC=D(R×S))
(1)SELECT A FROM R
(2)SELECT * FROM R WHERE B=’17’
(3)SELECT A,B,C,D,E,F FROM R,S
(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4 设有七个主题表Rubicon(A,B,C)和S(A,B,C)试用SQL查询语句表述下列关系代数表明式:
  (1)R∪S  (2)R∩S  (3)R-S  (4)πA,B(R)πB,C(S)

    (1)SELECT A,B,C
      FROM R
      UNION
      SELECT A,B,C
      FROM S

    (2)SELECT A,B,C
      FROM R
      INTERSECT
      SELECT A,B,C
      FROM S

    (3)SELECT A,B,C
      FROM R
      WHERE NOT EXISTS
       (SELECT A,B,C
       FROM S
       WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

    (4)SELECT R.A,R.B,S.C
      FROM R,S
      WHERE R.B=S.B

3.5 试陈说SQL语言的关系代数特点和元组演算特点。
(P61-62)

3.6 试用SQL查询语句表述下列对教学数据库中四个基本表S、SC、C的询问:

(1)总结有学生选修的课程门数。
    SELECT COUNT(DISTINCT C#) FROM SC

(2)求选修C4学科的学员的平均年龄。
    SELECT AVG(AGE)
    FROM S
    WHERE S# IN
     (SELECT S#
     FROM SC
     WHERE C#=’C4′)
或者,
    SELECT AVG(AGE)
    FROM S,SC
    WHERE S.S#=SC.S# AND C#=’004′

(3)求LIU老师所授课程的每门学科的学平生均成绩。
   SELECT CNAME,AVG(GRADE)
   FROM SC ,C
   WHERE SC.C#=C.C# ANDTEACHER=’LIU’
   GROUP BY C#   

(4)总括每门课程的学生选修人数(超越10人的科目才总结)。要求输出课程号和选修人数,查询结果按人口降序排列,若人数同样,按学科号升序排列。
    SELECT DISTINCT C#,COUNT(S#)
    FROM SC
    GROUP BY C#
    HAVING COUNT(S#)>10
    ORDER BY 2 DESC, C# ASC

(5)检索学号比WANG同学大,而年纪比他小的学生姓名。
    SELECT X.SNAME
    FROM S AS X, S AS Y
    WHERE Y.SNAME=’WANG’ AND X.S#>Y.S# AND X.AGE<Y.AGE

(6)检索姓名以WANG打头的具备学员的人名和年龄。
    SELECT SNAME,AGE
    FROM S
    WHERE SNAME LIKE ‘WANG%’

(7)在SC中寻找战表为空值的学习者学号和课程号。
    SELECT S#,C#
    FROM SC
    WHERE GRADE IS NULL

(8)求年龄当先女校友平均年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE
Y.SEX=’女’)

(9)求年龄大于全体女子高校友年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE
Y.SEX=’女’)

3.7 试用SQL更新语句表明对教学数据库中五个中央表S、SC、C的各样更新操作:
(1)往基本表S中插入贰个学员元组(‘S9’,‘WU’,18)。
    INSERT INTO S(S#,SNAME,AGE) VALUES(’59’,’WU’,18)
(2)在基本表S中探寻每一门学科成绩都超越等于80分的学生学号、姓名和性别,并把检索到的值送往另贰个已存在的基本表STUDENT(S#,SANME,SEX)。
    INSERT INTO STUDENT(S#,SNAME,SEX)
     SELECT S#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      (SELECT * FROM SC WHERE
       GRADE<80 AND S.S#=SC.S#)

(3)在着力表SC中删除尚无战绩的选课元组。
    DELETE FROM SC
     WHERE GRADE IS NULL

(4)把WANG同学的就学选课和成绩全部刨除。
    DELETE FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’)

(5)把选修MATHS课不比格的成就全改为空值。
    UPDATE SC
    SET GRADE=NULL
    WHERE GRADE<60 AND C# IN
      (SELECT C#
      FROM C
      WHERE CNAME=’MATHS’)

(6)把低于总平均战表的女子高校友成绩进步5%。
    UPDATE SC
    SET GRADE=GRADE*1.05
    WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S#
FROM SWHERE SEX=’F’)

(7)在着力表SC中期维修改C4学科的成就,若成绩小于等于75分时升高5%,若战表超过75分时进步4%(用五个UPDATE语句达成)。
    UPDATE SC
     SET GRADE=GRADE*1.05
     WHERE C#=’C4′ AND GRADE<=75
    UPDATE SC
     SET GRADE=GRADE*1.04
     WHERE C#=’C4′ AND GRADE>75

3.8 在第1章例1.4中涉嫌“旅舍管理”关系模型有四个关系情势:
  零件 PART(P#,PNAME,COLOR,WEIGHT)
  项目 PROJECT(J#,JNAME,DATE)
  供应商 SUPPLIER(S#,SNAME,SADDR)
  供应 P_P(J#,P#,TOTOAL)
  采购 P_S(P#,S#,QUANTITY)

(1)试用SQLDDL语句定义上述四个基本表,并证实主键和外键。
    CREATE TABLE PART
    (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR(10),WEIGHT REAL,
    PRIMARY KEY(P#))
    
    CREATE TABLE PROJECT
    (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE,
    PRIMARY KEY(J#))
    
    CREATE TABLE SUPLIER
    (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY(S#))
    
    CREATE TABLE P_P
    (J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY(J#,P#),
    FOREIGN KEY(J#) REFERENCE PROJECT(J#),
    FOREIGN KEY(P#) REFERENCE PART(P#))
    
    CREATE TABLE P_S
    (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY(P#,S#),
    FOREIGN KEY(P#) REFERENCE PART(P#),
    FOREIGN KEY(S#) REFERENCE SUPLIER(S#))
    

(2)试将PROGECT、P_P、PART四个基本表的本来联接定义为一个视图VIEW1,PART、P_S、SUPPLIE索罗德八个基本表的自然联接定义为二个视图VIEW2。
    CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT
PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT,PART,P_P
          WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW
VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT
PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART,P_S,SUPPLIER
          WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

(3)试在上述三个视图的根基上扩充数据查询:

    1)检索香港(Hong Kong)的代理商所供应的机件的号码和名字。
    SELECT P#,PNAME FROM VIEW2 WHERE SADDR=’SHANGHAI’

    2)检索项目J4所用零件的代理商编号和名字。
    SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1
WHERE J#=’J4′)

3.9 对于教学数据库中基本表SC,已创建下列视图:
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT(C#),AVG(GRADE)
FROMSC
GROUPBYS#
试决断下列查询和立异是还是不是允许奉行。若允许,写出调换成基本表SC上的呼应操作。
  (1)
SELECT*
FROMS_GRADE
      允许
   SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

  (2)
SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE>80
    允许
   SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

  (3)
SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM>(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’)
    允许
   SELECT S#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE
Y.S#=’S4′)
   GROUP BY S#

  (4)
UPDATES_GRADE
SETC_NUM=C_NUM+1
WHERES#=‘S4’
    不允许

  (5)
DELETEFROMS_GRADE
WHEREC_NUM>4
    不允许

3.10 预管理格局对于嵌入式SQL的落实有哪些主要意义?
   
预管理方式是先用预管理程序对源程序进行围观,识别出SQL语句,并处理成宿主语言的函数调用方式;
然后再用宿主语言的编写翻译程序把源程序编写翻译成目的程序。那样,不用扩张宿主语言的编写翻译程序,
就能够管理SQL语句。

3.11 在宿主语言的顺序中选用SQL语句有如何规定?
在宿主语言的次序中选拔SLQ语句有以下规定:
(1)在程序中要分别SQL语句与宿主语言说话
(2)允许嵌入的SQL语句引用宿主语言的程序变量(称为分享变量),但有两条规定:
   1)援引时,那么些变量前必需加“:”作为前缀标志,以示与数据库中变量有分别。
   2)那些变量由宿主语言的程序定义,并用SQL的DECLARE语句表明。
(3)SQL的聚众处理方式与宿主语言单记录管理格局之间要和煦。
需求动用游标机制,把会集操作调换到单记录管理格局。

3.12SQL的会合管理方式与宿主语言单记录管理格局之间怎么和煦?
    由于SQL语句管理的是记录集结,而宿主语言语句贰回只可以管理一个记下,
由此必要用游标(cousor)机制,把会集操作转变到单记录管理情势。

2.13 嵌入式SQL语句何时不必涉及到游标?何时必须涉及到游标?
    (1)INSERT、DELETE、UPDATE语句,查询结果必然是单元组时的SELECT语句,
都可径直嵌入在主程序中央银行使,不必涉及到游标。
    (2)当SELECT语句询问结果是七个元组时,此时宿主语言程序无法采取,
应当要用游标机制把多少个元组叁遍八个地传递给宿主语言管理。

 

Post Author: admin

发表评论

电子邮件地址不会被公开。 必填项已用*标注