----ifnull isnull函数

SELECT * FROM t3;
id name
1
2
3 NULL

SELECT IFNULL(NAME,'kkkk') FROM t3;

if函数

点击(此处)折叠或打开

mysql> select * from t3;
+------+------+
| id | cj |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 3 |
| 4 | 5 |
| 5 | 5 |
| 6 | 7 |
+------+------+
6 rows in set (0.00 sec)

mysql> select *,if(id=cj,'eq','neq') from t3;
+------+------+----------------------+
| id | cj | if(id=cj,'eq','neq') |
+------+------+----------------------+
| 1 | 2 | neq |
| 2 | 3 | neq |
| 3 | 3 | eq |
| 4 | 5 | neq |
| 5 | 5 | eq |
| 6 | 7 | neq |
+------+------+----------------------+

MySQL> select * from t1;
+--------+---------------------+
| userid | atime |
+--------+---------------------+
| 1 | 2013-08-12 11:05:25 |
| 2 | 2013-08-12 11:05:29 |
| 3 | 2013-08-12 11:05:32 |
| 5 | 2013-08-12 11:05:34 |
| 1 | 2013-08-12 11:05:40 |
| 2 | 2013-08-12 11:05:43 |
| 3 | 2013-08-12 11:05:48 |
| 5 | 2013-08-12 11:06:03 |
+--------+---------------------+
8 rows in set (0.00 sec)
其中userid不唯一，要求取表中每个userid对应的时间离现在最近的一条记录．初看到一个这条件一般都会想到借用临时表及添加主建借助于join操作之类的．
给一个简方法：
MySQL> select userid,substring_index(group_concat(atime order by atime desc),",",1) as atime from t1 group by userid;
+--------+---------------------+
| userid | atime |
+--------+---------------------+
| 1 | 2013-08-12 11:05:40 |
| 2 | 2013-08-12 11:05:43 |
| 3 | 2013-08-12 11:05:48 |
| 5 | 2013-08-12 11:06:03 |
+--------+---------------------+
4 rows in set (0.03 sec)
substring_index(col_name,"分隔符",n) ----n表示前n部分