文章目录
- 1.select后对表进行修改(delete)
- 2.函数GROUP_CONCAT()
- 3.使用正则表达式
- 3.DATE_FORMAT()
- 4.count() 加条件
- 关于GROUP BY 使用可能忽悠而导致查询结果错误的点
1.select后对表进行修改(delete)
报错:You can’t specify target table ‘Person’ for update in FROM clause
原因:mysql不能在同一语句中先select出同一表中的某些值,然后对这个表做修改
错误写法
delete from Person
where id not in(select min(id) idfrom Person group by email
)
解决方法:添加临时表
正确写法
delete from Person
where id not in(select * from (select min(id) idfrom Person group by email ) t1
)
2.函数GROUP_CONCAT()
我们可以使用函数 GROUP_CONCAT() 将多行中的多个值组合成一个字符串。下面显示了 GROUP_CONCAT() 函数的语法:
GROUP_CONCAT(DISTINCT 字段 ----去重ORDER BY 字段 ----ASC/DESCSEPARATOR sep ---分隔符: SEPARATOR ','
);
用例
select sell_date ,count(distinct product) num_sold ,group_concat(distinct product separator ',') products
from Activities
group by sell_date
3.使用正则表达式
思路
一般来说,如果你被要求匹配一个字符串,应该最先想到写一个正则表达式模式进行匹配。
正则表达式提供各种功能,以下是一些相关功能:
正则表达式书写格式 |
---|
^:表示一个字符串或行的开头 |
[a-z]:表示一个字符范围,匹配从 a 到 z 的任何字符。 |
[0-9]:表示一个字符范围,匹配从 0 到 9 的任何字符。 |
[a-zA-Z]:这个变量匹配从 a 到 z 或 A 到 Z 的任何字符。请注意,你可以在方括号内指定的字符范围的数量没有限制,您可以添加想要匹配的其他字符或范围。 |
[^a-z]:这个变量匹配不在 a 到 z 范围内的任何字符。请注意,字符 ^ 用来否定字符范围,它在方括号内的含义与它的方括号外表示开始的含义不同。 |
[a-z]*:表示一个字符范围,匹配从 a 到 z 的任何字符 0 次或多次。 |
[a-z]+:表示一个字符范围,匹配从 a 到 z 的任何字符 1 次或多次。 |
.:匹配任意一个字符。 |
\.:表示句点字符。请注意,反斜杠用于转义句点字符,因为句点字符在正则表达式中具有特殊含义。还要注意,在许多语言中,你需要转义反斜杠本身,因此需要使用\.。 |
$:表示一个字符串或行的结尾。 |
例子
SELECT user_id, name, mail
FROM Users
-- 请注意,我们还转义了`@`字符,因为它在某些正则表达式中具有特殊意义
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';
3.DATE_FORMAT()
将日期值格式化为特定格式,请使用DATE_FORMAT函数。 DATE_FORMAT函数的语法如下:
DATE_FORMAT(date,format); ---date 字段,format 需要格式
DATE_FORMAT(trans_date,'%Y-%m') ---%Y-%m 2023-08
format | date |
---|---|
%D | 英文后缀如:0th, 1st, 2nd等的一个月之中的第几天 |
%d | 如果是1个数字(小于10),那么一个月之中的第几天表示为加前导加0, 如:00, 01,02, …31 |
%H | 24小时格式的小时,前导加0,例如:00,01…23 |
%h | 小时,12小时格式,带前导零,例如:01,02 … 12 |
%Y | 表示年份,四位数,例如2000,2001 |
%y | 表示年份,两位数,例如00,01 |
%m | 具有前导零的月份名称,例如:00,01,02,… 12 |
%M | 月份全名称,例如:January, February,…December |
format | date |
---|---|
%Y-%m-%d | 2017/04/30 |
%Y-%m | 2017/04 |
4.count() 加条件
-
COUNT( state=‘approved’ OR NULL ) >需要在后面 + or null
-
COUNT( IF (state = ‘approved’, 1, NULL ) )
需要注意的是,count只有在字段数据为NULL时才不计入数量,如果IF (state = ‘approved’, 1, NULL ) 改为IF (state = ‘approved’, 1, 0 ) ,那么非 approved的数据也会被计入其中导致错误。
关于GROUP BY 使用可能忽悠而导致查询结果错误的点
select customer_pref_delivery_datefrom Delivery group by customer_idhaving min(order_date) = customer_pref_delivery_date
这个sql会执行结果是错误的,因为部分数据会被忽略掉,比如那些有多条数据的(一个customer_id可以有多条记录) having min(order_date) = customer_pref_delivery_date 拿到最小的order_date后去跟这个组内的customer_pref_delivery_date去比较,但我们不能保证它拿的是不是min(order_date) 对应的那个customer_pref_delivery_date,所以这里的 = ,即比较,是有问题的
| 328 | 41 | 2019-7-10 | 2019-7-10 |
| 557 | 41 | 2019-7-25 | 2019-7-25 |
| 224 | 81 | 2019-8-31 | 2019-8-31 |
| 602 | 81 | 2019-8-29 | 2019-8-29 |
| 268 | 88 | 2019-7-6 | 2019-7-6 |
| 686 | 88 | 2019-8-25 | 2019-8-26 |
以上是符合条件的,但是会被筛选掉,导致错误
r=如果非要这么写,那么需要搞清楚要比较的是什么customer_pref_delivery_date,比如这里要的是最小的customer_pref_delivery_date
select customer_pref_delivery_datefrom Delivery group by customer_idhaving min(order_date) = min(customer_pref_delivery_date)