在项目中混合使用CASE 、JOIN等sql语句

一、基础信息

---

开发背景

该项目为一套多俱乐部台球管理系统，在基本需求中需要查询台球俱乐部的球台的各种数据。

---

实现技术

• mysql5.6
• php5.4
• 框架thinkphp3.2

---

实现目标

实现一个查询该俱乐部所有球台的相关数据的数据接口

---

接口要求

1. 查询球台信息表的部分信息(除去球台每小时价格)
2. 在接口中获取与所有球台关联的购物提示的提醒数量,并将返回字段定义为orders
3. 判断球台开始打扫时间与当前时间是否相差5分钟，并返回一个是否打扫中字段cleaning
   1.相差5分钟，代表打扫完毕：cleaning返回0；
   2.不够5分钟，代表球台仍在打扫中：cleaning返回1；

---

逻辑说明

当球台为打扫中状态时，球台不能进行其它操作，如：开台（开灯）、添加用户等。当球台为开台状态时，也不能进行打扫操作。

---

数据表

1.球台信息表：table_info

Column	About
table_id	主键（AUTO_INCREMENT）
name	球台名称（例如：A3－斯诺克）
club_id	对应俱乐部id
hour_price	每小时价格
start_time	开台时间
clean_time	打扫时间
…	其余字段省略

---

2.购物提示表：shopping_tips

Column	About
tips_id	主键（AUTO_INCREMENT）
table_id	球台信息表主键
club_id	对应俱乐部id
status	状态（是否被确认）
…	其余字段省略

---

二、实现流程

---

目标数据集

---

php代码

if (isset($_REQUEST['club_id'])) {
    $map['club_id']=I('param.club_id');
    $json['data']=M('table_info')->where($map)
    ->join('shopping_tips ON table_info.table_id=shopping_tips.table_id AND shopping_tips.status = 0', 'LEFT OUTER')
    //实现判断时间是否大于五分钟
    //和统计shopping_tips表对应台号的订单数量
    ->field('case TIMESTAMPDIFF(MINUTE,table_info.clean_time,now())<=5 WHEN 1 THEN 1 ELSE 0 END AS cleaning,
    table_info.table_id,table_info.name,table_info.hour_price,
    table_info.start_time,Count(shopping_tips.tips_id) AS ORDERS')
    //使用统计函数需使用group()方法分组
    ->group('table_info.table_id')
    ->order('table_info.table_id')->select();
    $json['status_code']='0001';
    $this->ajaxReturn($json);
}else{
    $json['status_code']='0002';
    $json['error']='缺少参数';
    $this->ajaxReturn($json);
}

---

输出的sql

在thinkphp可使用fetchSql(true)方法将sql语句输出

SELECT case TIMESTAMPDIFF(MINUTE,table_info.clean_time,now())<=5 WHEN 1 THEN 1 ELSE 0 END AS cleaning,table_info.table_id,table_info.name,table_info.hour_price,table_info.start_time,
Count(shopping_tips.tips_id) AS ORDERS FROM `table_info` LEFT OUTER JOIN shopping_tips ON table_info.table_id=shopping_tips.table_id AND shopping_tips.status = 0 WHERE 
table_info.club_id = '1' GROUP BY table_info.table_id ORDER BY table_info.table_id

---

JSON results

---

The end

以上就是接口的实现方法。

在实现功能的时候尽量少用多个数组来达到目的