sql常见问题和特别操作

关于时间的sql

datediff(date,’ 具体时间’) > ’ xxxxx’
date > ’ 具体时间’;

SQL_ERROR_INFO: “You can’t specify target table ‘titles_test’ for update in FROM clause”

不能直接从表中删除筛选出的表。
delete from titles_test
where id not in
(select min_id from
(select min(id)as min_id
from titles_test
group by emp_no) as t1
);

concat()函数

select concat(last_name," ",first_name) as name
from employees;

limit函数

limit value1,value2
value1是起始行的上一行，value2表示步长。

（分类）累加求和
select grade,sum(number) over ( partition col_name ORDER BY grade) AS t_rank
from class_grade;
分类统计：
count(product_name) over (partition by user_id) as cnt
if函数用法：
if (client_id <> 0, name, null) client_name
case when …end:
case when t.is_group_buy = ‘Yes’ then ‘GroupBuy’ else c.name end source

group_concat(x,y)用法：x是列名，y是符号

select dept_no,group_concat(emp_no) as employees

right(col_name,number):

right(first_name,2)

rank,dense_rank,row_number 使用上的区别：

https://zhuanlan.zhihu.com/p/323854708

mysql里查找某一天的后一天的用法是:

DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)

累计求和/平均：

sum(salary) over (order by emp_no) as running_total
avg(salary) over (order by emp_no) as running_total

**ifnull()的用法：

MYSQL IFNULL(ex1,ex2)
如果ex1不是NULL，IFNULL()返回ex1，否则它返回ex2。**

replace()函数：

select REPLACE(‘abcdefghabc’,‘abc’,‘xxx’)–输入的字符串为：abcdefghabc

结果为：xxxdefghxxx

计算时间差：

date_add(t.login_date,Interval -t.rnk day)
datediff(date1,date2)