题一：现有strings表如下：
id指序列号；
string列中存放的是字符串，且字符串中仅包含数字、字母和逗号类型的字符。
id | string |
1 2 3 | 10,A,B,C,D A,B,C,D,E,F A,11,B,C,D,E,G |
请你统计每个字符串中逗号出现的次数cnt。
以上例子的输出结果如下：
id | cnt |
1 2 3 | 4 5 6 |
方法一：
用到的函数：
length(s)函数： s是字符串， 返回的是所求的字符串s的长度。
replace(a,b,c)： 在字符串a中，将a中出现的b，替换成c。再把这个替换之后的串的结果返回。
select id,
length(string)-length(replace(string,',','')) as cnt
from strings
方法二：
SELECT
id,
length(
regexp_replace(STRING, '[A-Z 0-9]', '')
)
FROM
strings;
使用正则表达是把字母数字删除。取长度即可
题2：现有employees表如下：
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
10004 | 1954-05-01 | Christian | Koblick | M | 1986-12-01 |
10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
请你将employees中的first_name，并按照first_name最后两个字母升序进行输出。
以上示例数据的输出如下：
first_name |
Christian |
Tzvetan |
Bezalel |
Duangkaew |
Georgi |
Kyoichi |
Anneke |
Sumant |
Mary |
Parto |
Saniya |
思路：
substr(string,start,length)
string - 指定的要截取的字符串。
start - 必需，规定在字符串的何处开始。正数 - 在字符串的指定位置开始，负数 - 在从字符串结尾的指定位置开始，0 - 在字符串中的第一个字符处开始。
length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。
例如：select substr('abcdefg',3,4) from dual; 结果是cdef
select substr('abcdefg',-3,4) from dual; 结果efg
注意：字符串中的第一个位置始终为1。以下两个sql查询的结果相同：
例如：select substr('abcdefg',0,3) from dual; 结果是abc
select substr('abcdefg',1,3) from dual; 结果是abc
right(str, num) 函数。从右边开始截取str字符串num长度
方法一：
select first_name
from employees
order by substring(first_name,-2)
或者
select first_name
from employees
order by substring(first_name,-2,2)
方法二：
#select first_name from (
#select first_name,right(first_name,2) as sort
#from employees ) t
#order by t.sort
或者
select first_name
from employees
order by right(first_name,2)
题3：
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
输出格式:
dept_no | employees |
d001 | 10001,10002 |
d002 | 10006 |
d003 | 10005 |
d004 | 10003,10004 |
d005 | 10007,10008,10010 |
d006 | 10009,10010 |
方法：
group_concat()函数返回X的非null值的连接后的字符串。如果给出了参数Y，将会在每个X之间用Y作为分隔符。如果省略了Y，“，”将作为默认的分隔符。每个元素连接的顺序是随机的。
注意：要求连接符就是逗号时，省略Y，不然会出现两个逗号。
SELECT
dept_no,
group_concat(emp_no) employees
FROM
dept_emp
GROUP BY
dept_no
补充：
一、concat()函数
1.功能：将多个字符串连接成一个字符串
语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null
数据库表结构：
SELECT CONCAT(id,NAME,price) FROM demo;
SELECT CONCAT(id,NAME) FROM demo WHERE price ='17';
给拼接的字符串添加分隔符：
SELECT CONCAT(NAME,',',price) FROM demo WHERE id='1';
二、group_concat()函数
以id分组，把price字段的值在一行打印出来，分号分隔。 其中GROUP_CONCAT()函数中SEPARATOR后面接的字符表示分隔符
SELECT id,GROUP_CONCAT(price SEPARATOR ';') FROM demo GROUP BY id;
示例效果：
题4：查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
如：
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
输出格式:
avg_salary |
73292 |
#方法一
select avg(salary) as avg_salary
from salaries
where salary > (select min(salary) from salaries where to_date = '9999-01-01')
and salary < (select max(salary) from salaries where to_date = '9999-01-01')
and to_date = '9999-01-01'
#方法二
#select avg(salary) as avg_salary from salaries
#where to_date = '9999-01-01'
#and salary not in (select max(salary) from salaries where to_date = '9999-01-01')
#and salary not in (select min(salary) from salaries where to_date = '9999-01-01')
#方法三
#SELECT AVG(salary)
#FROM (
# SELECT *,
# RANK() OVER(ORDER BY salary) r1,
# RANK() OVER(ORDER BY salary DESC) r2
# FROM salaries
# WHERE to_date = '9999-01-01'
#) a
#WHERE r1!=1 AND r2!=1
