ITEEDU

10.3.8. 在SQL语句中使用COLLATE

• 使用COLLATE子句，能够为一个比较覆盖任何默认校对规则。COLLATE可以用于多种SQL语句中。下面是一些例子：

  ·         使用ORDER BY：

  ·                SELECT k
  ·                FROM t1
  ·                ORDER BY k COLLATE latin1_german2_ci;

  ·         使用AS：

  ·                SELECT k COLLATE latin1_german2_ci AS k1
  ·                FROM t1
  ·                ORDER BY k1;

  ·         使用GROUP BY：

  ·                SELECT k
  ·                FROM t1
  ·                GROUP BY k COLLATE latin1_german2_ci;

  ·         使用聚合函数：

  ·                SELECT MAX(k COLLATE latin1_german2_ci)
  ·                FROM t1;

  ·         使用DISTINCT：

  ·                SELECT DISTINCT k COLLATE latin1_german2_ci
  ·                FROM t1;

  ·         使用WHERE：

  ·                     SELECT *
  ·                     FROM t1
  ·                     WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;
  ·                     SELECT *
  ·                     FROM t1
  ·                     WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

  ·         使用HAVING：

  ·                SELECT k
  ·                FROM t1
  ·                GROUP BY k
  ·                HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;