一些常见功能的查询sql

https://www.52ecy.cn/post-105.html

1、场景：查询一个选手的排名及距离上一名差多少票

方法一（定义变量累加排序）
\$temp = DB::fetch_all("SELECT a.cid,a.total,a.forge_vote,a.jewel_vote,(@rowNum:=@rowNum+1) AS rank FROM pre_vote_competition AS a, (SELECT (@rowNum :=0) ) b WHERE aid={\$aid} ORDER BY (a.total+a.forge_vote+a.jewel_vote) DESC ");
foreach (\$temp as \$key => \$value) {
if (\$value['cid'] == \$cid) {
//当前自己的排名
\$rank = \$value['rank'];
if (\$up) {
\$up = \$up - (\$value['total'] + \$value['forge_vote'] + \$value['jewel_vote']);
}
break;
}
//和上一名选手相差的票数
\$up = \$value['total'] + \$value['forge_vote'] + \$value['jewel_vote'];
}

2、场景：查询一篇文章的上一篇和下一篇，支持断号
\$sql = "
SELECT
*
FROM
" . DB::table(\$this->_table) . "
WHERE
aid IN (
SELECT
CASE
WHEN SIGN(aid - {\$id}) > 0 THEN MIN(aid)
WHEN SIGN(aid - {\$id}) < 0 THEN MAX(aid)
END AS aid
FROM
pre_exe_article
WHERE
aid <> {\$id}
GROUP BY
SIGN(aid - {\$id})
ORDER BY
SIGN(aid - {\$id})
)
ORDER BY
aid ASC";
return DB::fetch_all(\$sql);

方法二（去重排序查询比自己少的数量）
\$temp = DB::fetch_first("SELECT distinct total+jewel_vote+forge_vote ,COUNT(*)+1 AS RANK FROM " . DB::table('vote_competition') ." WHERE total+jewel_vote+forge_vote>" . \$competition['all'] . " and aid={\$aid} ORDER by forge_vote desc");
\$rank = \$temp['RANK'];
//无法直接查询到上一名选手票数
if (\$temp['total+jewel_vote+forge_vote']) {
//这里查询出来的是和第一名选手相差的票数
    \$up = \$temp['total+jewel_vote+forge_vote']-\$competition['all'];
}

3、批量更新MySQL多条记录的多个字段
mysql更新语句很简单，更新一条数据的某个字段，一般这样写：
UPDATE mytable SET myfield = 'value' WHERE id = '1';
如果更新同一字段为同一个值，mysql也很简单，修改下where即可：

UPDATE mytable SET myfield = 'value' WHERE id in (1,2,3);
那如果更新多条数据为不同的值，可能很多人会这样写：
foreach (\$display\_order as \$id => \$ordinal) {
    \$sql = "UPDATE categories SET display\_order = \$ordinal WHERE id = \$id";
    mysql\_query(\$sql);
}
即是循环一条一条的更新记录。
一条记录update一次，这样性能很差，也很容易造成阻塞。

那么能不能一条sql语句实现批量更新呢？
mysql并没有提供直接的方法来实现批量更新，但是可以用点小技巧来实现。
UPDATE mytable SET
    myfield = CASE id
        WHEN 1 THEN '3'
        WHEN 2 THEN '4'
        WHEN 3 THEN '5'
    END
WHERE id IN (1,2,3)
这句sql的意思是，更新display\_order 字段：

   如果id=1 则display\_order 的值为3，

   如果id=2 则 display\_order 的值为4，

   如果id=3 则 display\_order 的值为5。

即是将条件语句写在了一起。

这里的where部分不影响代码的执行，但是会提高sql执行的效率。

确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

更新多值
UPDATE categories SET
    display\_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

封装成PHP函数,传入相应数据，一键生成sql

 /\*\*
 \* 批量更新函数
 \* @param \$data array 待更新的数据，二维数组格式
 \* @param array \$params array 值相同的条件，键值对应的一维数组
 \* @param string \$table array 表
 \* @param string \$field string 值不同的条件，默认为id
 \* @return bool|string
 \*/
function batchUpdate(\$data, \$field, \$table ,\$params = [])
{
   if (!is\_array(\$data) || !\$field || !\$table || !is\_array(\$params)) {
   return false;
   }

 \$updates = parseUpdate(\$data, \$field);
 \$where = parseParams(\$params);

 // 获取所有键名为\$field列的值，值两边加上单引号，保存在\$fields数组中
 // array\_column()函数需要PHP5.5.0+，如果小于这个版本，可以自己实现，
 // 参考地址：[http://php.net/manual/zh/function.array-column.php#118831](http://php.net/manual/zh/function.array-column.php#118831)
 \$fields = array\_column(\$data, \$field);
 \$fields = implode(',', array\_map(function(\$value) {
  return "'".\$value."'";
 }, \$fields));

 \$sql = sprintf("UPDATE \`%s\` SET %s WHERE \`%s\` IN (%s) %s", \$table, \$updates, \$field, \$fields, \$where);

   return \$sql;
}

/\*\*
 \* 将二维数组转换成CASE WHEN THEN的批量更新条件
 \* @param \$data array 二维数组
 \* @param \$field string 列名
 \* @return string sql语句
 \*/
function parseUpdate(\$data, \$field)
{
 \$sql = '';
 \$keys = array\_keys(current(\$data));
 foreach (\$keys as \$column) {

  \$sql .= sprintf("\`%s\` = CASE \`%s\` \\n", \$column, \$field);
  foreach (\$data as \$line) {
   \$sql .= sprintf("WHEN '%s' THEN '%s' \\n", \$line[\$field], \$line[\$column]);
  }
  \$sql .= "END,";
 }

 return rtrim(\$sql, ',');
}

/\*\*
 \* 解析where条件
 \* @param \$params
 \* @return array|string
 \*/
function parseParams(\$params)
{
   \$where = [];
   foreach (\$params as \$key => \$value) {
   \$where[] = sprintf("\`%s\` = '%s'", \$key, \$value);
   }

   return \$where ? ' AND ' . implode(' AND ', \$where) : '';
}

批量替换字符串
UPDATE \`emlog\_blog\` SET \`content\` = replace (\`content\`,'ws2.sinaimg.cn','cdn.sinaimg.cn.52ecy.cn') WHERE \`content\` LIKE '%ws2.sinaimg.cn%'