interview

/ 0评 / 0

笔试题

  1. 查询姓“黄”的老师个数;查询学过“语文”课程并且也学过“数学”课程的同学的编号、姓名;查询所有课程成绩小于60分的同学的编号、姓名,表结构如下:
表名 字段 备注
Student (id,name,age,sex) 学生表
Course (Ida,name,teacher_id) 课程表
Score (student_id,course_id,score) 分数表
Teacher (id,name) 教师表
建表语句:
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `teacher_id` int(10) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;

INSERT INTO `Course` VALUES (1, '语文', 1);
INSERT INTO `Course` VALUES (2, '数学', 2);
INSERT INTO `Course` VALUES (3, '英语', 3);

DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score`  (
  `student_id` int(10) UNSIGNED NOT NULL,
  `course_id` int(10) UNSIGNED NULL DEFAULT NULL,
  `score` int(3) UNSIGNED NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8;

INSERT INTO `Score` VALUES (1, 1, 66);
INSERT INTO `Score` VALUES (1, 2, 80);
INSERT INTO `Score` VALUES (1, 3, 90);
INSERT INTO `Score` VALUES (2, 1, 80);
INSERT INTO `Score` VALUES (2, 2, 80);
INSERT INTO `Score` VALUES (2, 3, 90);
INSERT INTO `Score` VALUES (3, 1, 50);
INSERT INTO `Score` VALUES (3, 3, 59);

DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(3) UNSIGNED NULL DEFAULT NULL,
  `sex` int(1) UNSIGNED NULL DEFAULT NULL COMMENT '1:男 2:女',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;

INSERT INTO `Student` VALUES (1, '小明', 18, 1);
INSERT INTO `Student` VALUES (2, '小hua', 18, 2);
INSERT INTO `Student` VALUES (3, '小xin', 16, 1);

DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;

INSERT INTO `Teacher` VALUES (1, '黄老师');
INSERT INTO `Teacher` VALUES (2, '李老师');
INSERT INTO `Teacher` VALUES (3, '黄达');


查询姓“黄”的老师个数:  
select count(id) from Teacher where name like '黄%';  
查询学过“语文”课程并且也学过“数学”课程的同学的编号、姓名: 
select stu.id, stu.name, co.name from Student stu join Score sc on stu.id = sc.student_id join Course co on sc.course_id = co.id where co.name="语文" and exists (select student_id from Score left join Course on course_id = id where student_id = stu.id and name="数学");
查询所有课程成绩小于60分的同学的编号、姓名:  
select id, name from Student where id not in (select student_id from Score where score >= 60);
  1. 写个function,将数组 array(0,1,2,array(3,4,array(5),6),7,array(8,array(9)),array(10,array(11,array(12)))) ,转换为新数组 array(0,1,2,3,4,5,6,7,8,9,10,11,12)
function qunum($arr)
{
    static $res = array();
    foreach ($arr as $key => $value) {
        if (is_array($value)) {
            qunum($value);
        } else {
            $res[] = $value;
        }
    }
    return $res;
}
  1. 写一个匹配日期格式为“2018-01-0108:00:00AM”的正则表达式
/^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[1-2]\d|30|31)\s*(0\d|1[0-2]):[0-5]\d:[0-5]\d\s*(A|P)M$/
  1. 写个函数对数组 [["name"=>"sam","age"=>22],["name"=>"jim","age"=>20],["name"=>"paul","age"=>26]] 按年龄倒序排序
$keysValue = [];
foreach ($arr as $k => $v) {
    $keysValue[$k] = $v['age'];
}
array_multisort($keysValue, SORT_DESC, $arr);
echo '<pre>';
print_r($arr);exit;
echo '</pre>';
  1. 假设有这样一个二维数组 [["id"=>1,"pid"=>0,"name"=>"a"],["id"=>2,"pid"=>1,"name"=>"b"],["id"=>3,"pid"=>1,"name"=>"c"],["id"=>4,"pid"=>2,"name"=>"d"]] 表示这样的一棵树
graph TB
a-->b
a-->c
b-->d

请写个函数将二维数组转换成这样的json输出

[{
    "id":1,"name":"a","children":[
        {
            "id":2,"name":"b","children":[
                {"id":4,"name":"d"}
            ]
        },
        {"id":3,"name":"c"}
    ]
}]

参考答案:

$rows = [["id"=>1,"pid"=>0,"name"=>"a"],["id"=>2,"pid"=>1,"name"=>"b"],["id"=>3,"pid"=>1,"name"=>"c"],["id"=>4,"pid"=>2,"name"=>"d"]];

function doJson($rows, $pid)
{
    $childs = findChild($rows, $pid);
    if (empty($childs)) {
        return null;
    }

    foreach ($childs as $k => $v) {
        $res = doJson($rows, $v['id']);
        if ($res != null) {
            $childs[$k]['children'] = $res;
        }
    }

    return $childs;
}

function findChild(&$arr, $id)
{
    $childs = array();
    $num = 0;

    foreach ($arr as $k => $v) {
        if ($v['pid'] == $id) {
            $childs[$num]['id'] = $v['id'];
            $childs[$num]['name'] = $v['name'];
            $num++;
        }
    }
    return $childs;
}

echo '<pre>';
print_r(json_encode(doJson($rows, 0)));exit;
echo '</pre>';

评论已关闭。