一些常見功能的查詢sql

阿珏酱發表於2024-05-20
SQL

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

一些常見功能的查詢sql

日期：2019-4-10 阿珏 折騰程式碼 瀏覽：1462次 評論：1條

一些常見功能的查詢sql 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 》

網友評論：

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

相關文章