MySQL 查询结果带行号
select a,
@i := @i + 1 as b
from (select 'x' as a
union all
select 'y' as a
union all
select 'z' as a) a,
(select @i := 0) b
a | b |
---|---|
x | 1 |
y | 2 |
z | 3 |
行转列(使用笛卡尔积)
select label,
case when
label = '减少此类作品数' then sum(share_count)
when label = '分享数' then sum(negative_count)
end as value
from
(
select sum(share_count) as share_count
, sum(negative_count) as negative_count
from makepolo.creative_report
where date = '2020-12-06'
) a ,
(
select '减少此类作品数' as label
union all
select '分享数' as label
) b
group by 1
为数据增加合计行(使用笛卡尔积)
-- 昨日绑定账户数
select b.label vendor_id
, sum(bind_acct) bind_acct
from (
select vendor_id
, count(distinct id) bind_acct
from makepolo.entity_vendor_account
where create_time >= '2021-03-08 00:00:00'
and create_time <= '2021-03-08 23:59:59'
group by 1
) a, (
select '全部' as label
union all
select 1 as label
union all
select 2 as label
union all
select 4 as label
) b
where label = a.vendor_id or label = '全部' -- 笛卡尔积乘开后,这里对原有数据保留正确行,同时并上合计行
group by 1
随机抽样10行
select * from makepolo.material_report
order by rand(212)
limit 10
删除表
-
当你不再需要该表时, 用
drop table
; -
当你仍要保留该表,但要删除所有记录时, 用
truncate
; -
当你要删除部分记录时(always with a WHERE clause), 用
delete
.
时间相关函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
UNIX_TIMESTAMP(date) | 返回日期date 的UNIX 时间戳 |
FROM_UNIXTIME | 返回UNIX 时间戳的日期值 |
WEEK(date) | 返回日期date 为一年中的第几周 |
YEAR(date) | 返回日期date 的年份 |
HOUR(time) | 返回time 的小时值 |
MINUTE(time) | 返回time 的分钟值 |
MONTHNAME(date) | 返回date 的月份名 |
DATE_FORMAT(date,fmt) | 返回按字符串fmt 格式化日期date 值 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr,expr2) | 返回起始时间expr 和结束时间expr2 之间的天数 |
date_add() -- 向日期添加指定的时间间隔。
date_add(date,INTERVAL expr type)
date_sub() -- 从日期减去指定的时间间隔。
date_sub(date,INTERVAL expr type)
datediff() -- 返回两个日期之间的天数。
datediff(date1, date2)
date_format() -- 用于以不同的格式显示日期/时间数据。
date_format(date,format)
select curdate();
# 2020-12-21
select current_date();
# 2020-12-21
select current_time();
# 10:36:32
select date('2020-12-02 23:25:11')
# 2020-12-02
-- 通过日期计算所在的季度
select floor((date_format(curdate(), '%m') +2)/3)) as quartly
# 或
select floor((month(curdate()) +2)/3)) as quartly
join
-
使用
left join
的时候左表 如果过大,会造成运行缓慢。可以改用join
,看查询结果是否一致。总之左表尽量小。 -
where 子句不要把函数写在运算符左边
-
使用索引提升查询速度
union
union
比union all
的执行效率差一些。union
是将union all
后的结果进行一次distinct
,去除重复记录后的结果。
设置唯一键
alter table rpt_fancy.mkpl_creative add unique key (dat, vendor_id, company_id);
-
当一个属性声明为主键时,它将不接受NULL值。另一方面,当声明为Unique的属性时,它可以接受一个NULL值。
-
表中只能有一个主键,但可以有多个唯一键。
-
定义主键时自动创建聚簇索引。相反,Unique键生成非聚集索引。
通过这种方法根据唯一键插入表:
insert into mkpl_creative (dat, vendor_id, company_id, ad_cnt_api)
select * from mkpl_creative_ad_cnt
on duplicate key update ad_cnt_api = values (ad_cnt_api)
修改字段为not null
alter table rpt_fancy.mkpl_creative modify dat varchar(255) not null comment '日期';
alter table rpt_fancy.mkpl_creative modify vendor_id int not null comment '媒体';
alter table rpt_fancy.mkpl_creative modify company_id int not null comment '客户id';
doris设置为货币格式
- 加入千分符
select split_part(money_format(18796355.668) , '.', 1); -- 不保留小数部分
# 18,796,355
select money_format(18796355.668); -- 按惯例保留2位小数
# 18,796,355.67
Having子句的使用
-
having
子句不仅能用在通过group by
聚合后的结果上,还能够使用在对任何select
后字段计算的数据筛选中。 -
但是
having
子句似乎不能过滤窗口函数的结果(普遍性没有得到验证)
字符串处理
locate()
函数判断一个字符串在另一个字符串中出现的位置,如果没有出现返回0.
select locate('bar', 'foobar');
#> 4
select locate('bar', 'foooobar');
#> 6
select locate('qtt', 'foobar');
#> 0
统计逗号分隔字段元素的个数
写SQL的时候会遇到如下的问题,统计如下表中project_id
字段中id的个数。
company_id | project_id |
---|---|
77 | 94882,214880,94881,154882,94871,94879 |
140 | 2890,2872,3178,4314,4976 |
6 | 2173,5101,274884 |
6 | 4186,4192,4193 |
109 | 214899,94919,94920 |
305 | 5000,4999,5011 |
32 | 4514,5024,5262 |
49 | 1009,1008,1379 |
注意project_id
是varchar(255)
类型的,我们并没有现成的方法统计这个形如list
的字段元素(数据库并没有list
这样的对象),只能通过字符串处理的方式。其实规律很简单,我们只需要统计,
的个数然后+1
就可以了。那么如何求逗号个数呢?我们使用原字符串长度 与 替换了逗号后的字符串长度 相减即可,求字符串长度用char_length()
函数。
select company_id
, project_id
, char_length(project_id) - char_length(replace(project_id,',','')) + 1 as tag_cnt
from makepolo.local_material_tag
order by 3 desc
company_id | project_id | tag_cnt |
---|---|---|
77 | 94882,214880,94881,154882,94871,94879 | 6 |
140 | 2890,2872,3178,4314,4976 | 5 |
6 | 2173,5101,274884 | 3 |
6 | 4186,4192,4193 | 3 |
109 | 214899,94919,94920 | 3 |
305 | 5000,4999,5011 | 3 |
32 | 4514,5024,5262 | 3 |
49 | 1009,1008,1379 | 3 |
可以看到,使用这个方法准确无误地计算出了逗号分隔字段元素的数量。