本文已参与「新人创作礼」活动，一起开启掘金创作之路。
哎,最近小铭和我闹别扭了,小铭你不要离开我啊5555555555看😫😥
我对她说的几句话印象比较深:
1.我怕我会爱上你 (第一次浴缸里)
2.当初我就不应该来 (上一次浴缸里)
3.我怕我会原谅你(泰国火锅吃饭的时候)
5555小铭我想你
前几天你妹去上学,我感觉好像是我去上大学一样,不知道你在家里是不是很孤独,小可怜.我最近干的事情我错啦,我以后再也不会了!!!
41.一个表,有时候是左列有时候是右列,挑选你需要的值的两种方法
select distinct
case when user1_id = 1 then user2_id
when user2_id = 1 then user1_id
end as user1friend
from Friendship
select
user2_id as friend
from
friendship where user1_id='1'
union
select
user1_id as friend
from
friendship where user2_id='1'
42.如果,需要让原本的所有数值都出现,如果没有也需要出现,可以考虑两种方法:
1.先提取出所有出现过的,然后再连接
right join
(
select distinct sub_id from submissions where parent_id is null
) t2
2.直接做自连接
SELECT s1.sub_id AS post_id,IFNULL(COUNT(DISTINCT s2.sub_id),0) AS number_of_comments
FROM Submissions s1 LEFT JOIN Submissions s2
ON s1.sub_id = s2.parent_id
where s1.parent_id is null #如果用AND会出错GROUP BY s1.sub_id
ORDER BY s1.sub_id
43.having中的错误写法
select
*
from orders
group by seller_id
having order_date <> min(order_date)
44.找寻第二大:直接用dense_rank()!!!别的表in来in去都是些**做法
select
u.user_id as seller_id,
if(t2.item_brand=u.favorite_brand,'yes','no') as 2nd_item_fav_brand
from
users u left join
(
select
*
from
(
select
seller_id,
item_id,
dense_rank() over(partition by seller_id order by order_date asc) as rk
from orders
)t1 join items using(item_id)
where rk=2
)t2
#这里是所有的第二件商品
on u.user_id=t2.seller_id
45.avg妙用:
round(sum(if(rating<3,1,0))/count(*)*100,2) poor_query_percentage #与下面的那句话有相同的语义
ROUND(avg(rating < 3) * 100,2) poor_query_percentage #其中统计的是rating<3的答案0或者是1的个数
