在写的php 简单的orm实现 -- 用以解决昨天面试时的sql测试题-CSDN博客

-- ----------------------------
CREATE TABLE `stu` (
  `snum` int(11) NOT NULL,
  `name` varchar(40) NOT NULL,
  `lesson` varchar(40) NOT NULL,
  `score` int(8) NOT NULL,
  PRIMARY KEY  (`snum`,`lesson`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

查出总科目的平均分大于 45的学生记录

xser::load_library('orm','orm');
		orm::init('mysql/localhost:3306/sesecn_blog:utf8/root:root',xser::log_provider());


		orm::getDBO()->insert('stu',array(
			'snum'=>11,'name'=>'衰男', 'lesson'=>'英语','score'=>55
		));
		orm::getDBO()->insert('stu',array(
			'snum'=>11,'name'=>'衰男', 'lesson'=>'语文','score'=>88
		));
		orm::getDBO()->insert('stu',array(
			'snum'=>11,'name'=>'衰男', 'lesson'=>'数学','score'=>44
		));
		
		orm::getDBO()->insert('stu',array(
			'snum'=>12,'name'=>'兽女', 'lesson'=>'英语','score'=>36
		));
		orm::getDBO()->insert('stu',array(
			'snum'=>12,'name'=>'兽女', 'lesson'=>'语文','score'=>48
		));
		orm::getDBO()->insert('stu',array(
			'snum'=>12,'name'=>'兽女', 'lesson'=>'数学','score'=>41
		));
		
		$records = orm::getDBO()->select(
			'stu',
			"snum as '学号' ,name as '名字',sum(score) as '总分',avg(score) as '平均分'",
			null ,
			'snum',
			array('avg(score)'=>array(42,'>'))
		);
		
		xser::dump($records,orm::getDBO()->get_last_sql());

结果:

SELECT snum as '学号' ,name as '名字',sum(score) as '总分',avg(score) as '平均分' FROM `test`.`stu`   GROUP BY snum   HAVING  avg(score) > 42   :
Array
(
    [0] => Array
        (
            [学号] => 11
            [名字] => 衰男
            [总分] => 187
            [平均分] => 62.3333
        )

)