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