mysql小技巧

假设我们有关系marks(student_id,score),我们希望基于如下标准为学生评定分数等级：score<40 得F；40<=score<60 得C； 60<=score<80 得B;80<=score 得A 。写出下列查询操作： a.基于marks关系显示每个学生的分数等级 b.找出各分数等级的学生数

select *,elt(Interval(score,0,40,60,80),’F’,’C’,’B’,’A’) From marks

select elt(Interval(score,0,40,60,80),’F’,’C’,’B’,’A’) ,count(*) from marks group by Elt(interval(score,0,40,60,80),’F’,’C’,’B’,’A’)

INTERVAL(_<code>N,N1,N2,N3_,…)</code>

Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).

mysql> **`SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);`**
        -> 3
mysql> **`SELECT INTERVAL(10, 1, 10, 100, 1000);`**
        -> 2
mysql> **`SELECT INTERVAL(22, 23, 30, 44, 200);`**
        -> 0

>[`ELT(_N`_,_`str1`_,_`str2`_,_`str3`_,...)](http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_elt) Returns _`str1`_ if _`N`_ = `1`, _`str2`_ if _`N`_ = `2`, and so on. Returns `NULL` if _`N`_ is less than `1` or greater than the number of arguments. [`ELT()`](http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_elt) is the complement of [`FIELD()`](http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field).

mysql> **`SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');`**
        -> 'ej'
mysql> **`SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');`**
        -> 'foo'

例如 set t="11,4,19,32" 如何能得到t=4的结果啊，或者得到","这个字符在t里出现的次数也行。

SELECT LENGTH(T)-LENGTH(REPLACE(T,',',''))+1