Tips：当你看到这个提示的时候，说明当前的文章是由原emlog博客系统搬迁至此的，文章发布时间已过于久远，编排和内容不一定完整，还请谅解`
一些常见功能的查询sql
日期：2019-4-10 阿珏 折腾代码 浏览：1462次 评论：1条
1、场景：查询一个选手的排名及距离上一名差多少票
1、场景：查询一个选手的排名及距离上一名差多少票
方法二（去重排序查询比自己少的数量）
$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'];
}
方法一（定义变量累加排序）
$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);
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
$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%'
本博客所有文章 如无特别注明 均为原创。 作者： 阿珏 ， 复制或转载请 以超链接形式 注明转自 阿珏博客 。
原文地址《 一些常见功能的查询sql 》
网友评论：
标签：常见,value,查询,sql,vote,aid,array,id From： https://www.cnblogs.com/Ajue/p/18202443
头条新闻 2年前 (2019-04-11)
文章不错非常喜欢