<?xml version="1.0" encoding="utf-8" ?><rss version="2.0"><channel><title><![CDATA[Hopes_J的博客]]></title><description><![CDATA[]]></description><link>https://blog.youkuaiyun.com/Hopes_J</link><language>zh-cn</language><generator>https://blog.youkuaiyun.com/</generator><copyright><![CDATA[Copyright &copy; Hopes_J]]></copyright><item><title><![CDATA[查询fruits表中每个供应商所供应水果价格最高的记录]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105925656</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105925656</guid><author>Hopes_J</author><pubDate>Mon, 04 May 2020 23:30:18 +0800</pubDate><description><![CDATA[分组排序：


]]></description><category></category></item><item><title><![CDATA[2个时间相减获得正确的秒数]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105916449</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105916449</guid><author>Hopes_J</author><pubDate>Mon, 04 May 2020 13:20:25 +0800</pubDate><description><![CDATA[]]></description><category></category></item><item><title><![CDATA[mysql日期 获取本月第一天 获取下个月的第一天]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105862229</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105862229</guid><author>Hopes_J</author><pubDate>Thu, 30 Apr 2020 15:08:42 +0800</pubDate><description><![CDATA[select curdate();                       --获取当前日期
select last_day(curdate());                    --获取当月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day);   --获取本月第一天
select date_add(curda...]]></description><category></category></item><item><title><![CDATA[MySQL DATEDIFF() 函数]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105777012</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105777012</guid><author>Hopes_J</author><pubDate>Sun, 26 Apr 2020 21:02:47 +0800</pubDate><description><![CDATA[DATEDIFF() 函数返回两个日期之间的天数
DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。
注释：只有值的日期部分参与计算。


]]></description><category></category></item><item><title><![CDATA[mysql中round()函数]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105771185</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105771185</guid><author>Hopes_J</author><pubDate>Sun, 26 Apr 2020 16:13:24 +0800</pubDate><description><![CDATA[在mysql中，round函数用于数据的四舍五入，它有两种形式：
1、round(x,d)  ，x指要处理的数，d是指保留几位小数
这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；
2、round(x)  ,其实就是round(x,0),也就是默认d为0；
下面是几个实例
1、查询: select round(1123.26723,2);
 结果：1...]]></description><category></category></item><item><title><![CDATA[删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105740379</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105740379</guid><author>Hopes_J</author><pubDate>Fri, 24 Apr 2020 21:43:05 +0800</pubDate><description><![CDATA[SQL不允许子查询直接引用外层查询的同一张表，需要select as 生成一张临时表
方法一：

方法二：

]]></description><category></category></item><item><title><![CDATA[更改被作为外健的字段]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105734740</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105734740</guid><author>Hopes_J</author><pubDate>Fri, 24 Apr 2020 16:45:52 +0800</pubDate><description><![CDATA[将employee1表中dept_no（外健）改为d_no,数据类型不变
第一步：查外健名
show create table employee1
第二步：删外健
alter table employee1
drop foreign key employee1_ibfk_1
第三步：删索引
创建外键时会自动创建索引，也要删除
alter table employee1
drop index  E_...]]></description><category></category></item><item><title><![CDATA[怎样查询fruits表中s_id和f_name信息重复的记录]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105720857</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105720857</guid><author>Hopes_J</author><pubDate>Thu, 23 Apr 2020 23:51:49 +0800</pubDate><description><![CDATA[SELECT s_id,f_name,COUNT(f_name) as 重复次数
from fruit
GROUP BY s_id,f_name
HAVING COUNT(f_name)&gt;=2
ORDER BY s_id,f_name
对s_id和f_name先后进行分组后，对第二次分组的f_name计数&gt;=2就是重复次数

...]]></description><category></category></item><item><title><![CDATA[语法书写顺序sfwghol]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105716224</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105716224</guid><author>Hopes_J</author><pubDate>Thu, 23 Apr 2020 20:17:42 +0800</pubDate><description><![CDATA[select 字段名
from 表名
where 条件
group by  字段名
having 条件
order by 字段名
limit m,n;

]]></description><category></category></item><item><title><![CDATA[每个部门获得前三高工资的所有员工]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105692849</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105692849</guid><author>Hopes_J</author><pubDate>Wed, 22 Apr 2020 21:06:35 +0800</pubDate><description><![CDATA[编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
mysql中：
select d.Name as Department,e.Name as Employee,e.Salary as Salar...]]></description><category></category></item><item><title><![CDATA[HIVE中窗口函数、分组排序]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105670901</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105670901</guid><author>Hopes_J</author><pubDate>Tue, 21 Apr 2020 23:26:56 +0800</pubDate><description><![CDATA[Hive
row_number() over()直接排序，10个第1名排名为1到10
select user_id,pay_time,amont,Row_Number() OVER (partition by user_id ORDER BY amont desc ) rank from tmp
rank() over()是跳跃排序，有两个第二名时接下来就是第四名
select workdept,...]]></description><category></category></item><item><title><![CDATA[对所有员工薪水按照1～N排序]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105670839</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105670839</guid><author>Hopes_J</author><pubDate>Tue, 21 Apr 2020 23:23:55 +0800</pubDate><description><![CDATA[]]></description><category></category></item><item><title><![CDATA[双十一第3笔订单]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105670722</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105670722</guid><author>Hopes_J</author><pubDate>Tue, 21 Apr 2020 23:16:50 +0800</pubDate><description><![CDATA[HIVE中
select * from
（SELECT user_id ,
pay_time amont,
Row_Number() OVER (partition by user_id ORDER BY pay_time ) rank FROM employee
）t  rank=3

]]></description><category></category></item><item><title><![CDATA[某银行想拉出 近两笔交易金额相差大于1000元的所有客户信息]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105668880</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105668880</guid><author>Hopes_J</author><pubDate>Tue, 21 Apr 2020 21:52:23 +0800</pubDate><description><![CDATA[某银行想拉出 近两笔交易金额相差大于1000元的所有客户信息
select o2.*
FROM
(SELECT 姓名,总价 as 总价1 ,订货日期,if(@x=姓名,@r:=@r+1,@r:=1) as r1,@x:=姓名
from orders
order by 姓名,订货日期 desc) as o1 INNER JOIN (SELECT 姓名,总价 as 总价2,订货日期,if(@x=姓名,...]]></description><category></category></item><item><title><![CDATA[添加、删除主键，唯一健，外健]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105615343</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105615343</guid><author>Hopes_J</author><pubDate>Sun, 19 Apr 2020 14:46:35 +0800</pubDate><description><![CDATA[1、删除主键：tb_emp4
alter table tb_emp4
drop primary key
添加主键：tb_emp4
alter table tb_emp4
add primary key(name)
2、删除唯一键：tb_deptment2
alter table tb_dept2
drop index name
添加唯一键：
alter table tb_dept2
add uni...]]></description><category></category></item><item><title><![CDATA[复制表]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105613710</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105613710</guid><author>Hopes_J</author><pubDate>Sun, 19 Apr 2020 13:04:17 +0800</pubDate><description><![CDATA[create table person1 as
SELECT * from person;

]]></description><category></category></item><item><title><![CDATA[批量删除sql语句]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105613625</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105613625</guid><author>Hopes_J</author><pubDate>Sun, 19 Apr 2020 12:58:17 +0800</pubDate><description><![CDATA[1:方法一：
DELETE from person
WHERE ID between 11 and 12;
2:方法二：
DELETE from person
WHERE name in (‘l’,‘m’);

]]></description><category></category></item><item><title><![CDATA[添加与删除自增ID]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105602573</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105602573</guid><author>Hopes_J</author><pubDate>Sat, 18 Apr 2020 18:06:40 +0800</pubDate><description><![CDATA[添加自增ID

ALTER TABLE a
ADD IDD INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (IDD);


删除自增ID

alter table a
change IDD IDD int
]]></description><category></category></item><item><title><![CDATA[创建临时表]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105475987</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105475987</guid><author>Hopes_J</author><pubDate>Sun, 12 Apr 2020 20:56:12 +0800</pubDate><description><![CDATA[]]></description><category></category></item><item><title><![CDATA[substring_index(str,delim,count)]]></title><link>https://blog.youkuaiyun.com/Hopes_J/article/details/105443275</link><guid>https://blog.youkuaiyun.com/Hopes_J/article/details/105443275</guid><author>Hopes_J</author><pubDate>Fri, 10 Apr 2020 22:04:26 +0800</pubDate><description><![CDATA[]]></description><category></category></item></channel></rss>