一些常見功能的查詢sql

提示：當你看到這個提示的時候，說明當前的文章是由原emlog博客系統搬遷至此的，文章發佈時間已過於久遠，編排和內容不一定完整，還請諒解`

一些常見功能的查詢 sql

日期：2019-4-10 阿珏折騰代碼瀏覽：1462 次評論：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}) &lt; 0 THEN MAX(aid)
			END AS aid
		FROM
			pre_exe_article
		WHERE
			aid &lt;> {$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%'

網友評論：

頭條新聞 2 年前 (2019-04-11)
文章不錯非常喜歡