sql
Marttin2
Be-Happy!
Be-Healthy!
Be-Strong!
展开
-
mysql数据库,表增加字段语句
-- mysql数据库,表增加字段语句ALTER TABLE table_course ADD COLUMN column2 VARCHAR (255) CHARACTERSET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '副标题' AFTER column1;原创 2021-12-20 10:24:28 · 1454 阅读 · 0 评论 -
mybatis 处理 in 语句过长
<select id="testList" resultType="YourBeanVo"> SELECT c.* FROM table_sheet c WHERE c.sheet_id IN <foreach collection="idList" index="index" item="item" open="(" close=")"> <if test="index != 0"> <choose> &l..原创 2021-12-18 13:08:27 · 771 阅读 · 0 评论 -
MySQL_查询连续打卡天数
-- MySQL_查询连续打卡天数SELECT user_id, check_date, checked, -- 签到标识 @pre_check :=IF (checked, @pre_check + 1, 0) AS 'continue_check_in'FROM test_check_in, (SELECT @pre_check := 0) initWHERE 1 = 1 -- AND user_id = '99560637587259414'ORDER BY user_i.原创 2021-12-15 14:23:15 · 1785 阅读 · 0 评论 -
MySQL 查询指定年份12个月份的订单支付金额
-- MySQL 查询指定年份12个月份的订单支付金额SELECT user_id, SUM(case month(a.create_time) when 1 then money else 0 end) AS Jan, SUM(case month(a.create_time) when 2 then money else 0 end) AS Feb, SUM(case month(a.create_time) when 3 then money else 0 end) AS Mar..原创 2021-12-08 15:34:17 · 742 阅读 · 0 评论 -
MySQL 查询连续登陆天数大于7天的用户
-- MySQL 查询连续登陆天数大于7天的用户SELECT username, max(days) continue_days, min(login_date) start_date, max(login_date) end_dateFROM ( SELECT username, @continue_day := ( CASE WHEN ( @last_uid = username AND DATEDIFF(loginDate, @la..原创 2021-12-08 14:54:01 · 965 阅读 · 0 评论 -
MySQL lead() 查询相邻数据的字段
SELECT user_id, order_code, create_time, LEAD (create_time, 1) OVER ( #返回的值是向前一行的orderDate,没有定义后续行,返回NULL。 PARTITION BY user_id ORDER BY CREATE_time DESC ) nextCreateDateFROM pay_orderORDER BY user_id DESC;...原创 2021-12-06 16:12:16 · 745 阅读 · 0 评论 -
MySQL Lead() 查询连续出现3次的数据
SELECT DISTINCT remarks AS ConsecutiveRemarksFROM ( SELECT Id, remarks, Lead (remarks, 1) OVER () AS remarks_1, #选择指定行的向前一行的内容 Lead (remarks, 2) OVER () AS remarks_2 #选择指定行的向前两行的内容 FROM score ) AS cWHERE c.remarks = c.remarks_1.原创 2021-12-06 16:06:06 · 380 阅读 · 0 评论 -
MySQL dense_rank()
SELECT score, remarks, dense_rank () over ( PARTITION BY remarks ORDER BY score DESC ) AS 'Rank'FROM score;注意:dense_rank()是的排序数字是连续的、不间断。当有相同的分数时,它们的排名结果是并列的原创 2021-12-06 16:02:59 · 895 阅读 · 0 评论 -
MySQL rank()
SELECT score, remarks, rank () over ( PARTITION BY remarks ORDER BY score DESC ) AS 'Rank'FROM score;原创 2021-12-06 16:00:28 · 384 阅读 · 0 评论 -
MySQL order by 将 null 数据排在最后
order by ifnull(字段,'zzz')原创 2021-11-22 11:23:18 · 1660 阅读 · 0 评论 -
MySQL 日期查询,将年、月、日分列显示
/*日期查询,年月日分开*/SELECT id, EXTRACT(YEAR FROM create_time) AS YEAR, EXTRACT(MONTH FROM create_time) AS MONTH, EXTRACT(DAY FROM create_time) AS DAYFROM train_commentORDER BY create_time DESC;原创 2021-11-22 11:08:32 · 1043 阅读 · 0 评论 -
MySQL 按天统计查询数量
/*按天统计*/SELECT count(id) countNum, DATE(create_time) createTimeFROM train_commentGROUP BY DATE(create_time)ORDER BY DATE(create_time) DESC;原创 2021-11-22 11:07:13 · 1119 阅读 · 0 评论 -
MySQL 查询日期的前一季度的时间
-- 查询日期的前一季度的时间SELECT create_time, DATE_ADD( create_time, INTERVAL '1-1' DAY_HOUR ) addOneDayOneHour, DATE_ADD( create_time, INTERVAL - 1 QUARTER ) lastQuarterFROM train_comment;原创 2021-11-22 11:04:53 · 492 阅读 · 0 评论 -
mysql 行转列查询
SELECT b.u2_id, a.material_id, MAX( CASE b.target_id WHEN '171220360770501' THEN b.column_value ELSE '' END ) AS 171220360770501_target, MAX( CASE b.target_id WHEN '171220353803921' THEN b.column_value ELSE '' END ) AS 17.原创 2021-11-18 18:10:47 · 426 阅读 · 0 评论 -
Mysql count()函数中多条件去重
SELECT a.id, a.xxx, count( DISTINCT concat(o.o_id, o.create_time) ) as target FROM yyyyy a JOIN zzzz o ON o.oid = a.vidGROUP BY a.id, a.xxx本例子中是两个字段确定一条数据,统计数量过程中对结果(o.o_id, o.create_time)去重...原创 2021-11-18 17:44:57 · 2017 阅读 · 0 评论 -
mysql 将字段属性修改成 text 类型
-- 将表 xxx_sheet 字段 xxx_content 类型改成 textALTER TABLE xxx_sheet CHANGE xxx_content xxx_content TEXT;原创 2021-11-11 10:12:52 · 6642 阅读 · 0 评论 -
mysql 查询指定月份的所有天数
SELECT DATE_FORMAT( date_add( last_day("2021-02-01"), INTERVAL ( cast( help_topic_id AS signed INTEGER ) - DAY (last_day("2021-02-01")) ) + 1 DAY ), '%Y-%m-%d' ) DAYFROM mysql.help_topicWHERE help_topic_id < DAY (last_.原创 2021-10-14 13:19:54 · 1024 阅读 · 0 评论 -
mybatis choose标签使用
<where> <choose> <when test="name != null and name.trim() != ''"> AND a.name = #{name} </when> <otherwise> AND (1=1) </otherwise> .原创 2021-07-23 14:27:21 · 93 阅读 · 0 评论 -
mysql 如何判断字符串是 null 或者 “”
CASE WHEN (ISNULL(c.old_name) = 1) || (LENGTH(trim(c.old_name)) = 0) THEN b. NAME ELSE c.old_name END AS `name`,原创 2021-07-10 10:50:40 · 101 阅读 · 0 评论 -
MySQL 向下递归查询,完全的sql语句不用函数
# 向下递归 select group_concat( ids._id ) FROM ( SELECT @r AS _id, ( SELECT @r := group...原创 2021-03-17 19:50:03 · 1038 阅读 · 0 评论 -
mybatis 嵌套查询list写法
public class BeanName implements Serializable{ private String id; private String propertyName1; private String propertyName2; private String propertyName3; private String propertyName4; private List<PriceInfoDto> priceInfo; //子集1 .原创 2021-02-20 08:45:03 · 1265 阅读 · 1 评论 -
mybatis 递归查询所有子分类写法
Bean:public class Category { private Integer categoryid; //分类id private String name2; private String remarks; private List<Category> categoryList; //其下子集} Dao:List<Category> getAllCategory(Integer pid);mapping: <.原创 2021-02-04 11:02:28 · 475 阅读 · 0 评论 -
MySQL union 的使用你真知道吗?
MySQL union 的使用你真知道吗? (SELECT id, titleFROM A) x UNION(SELECT id, titleFROM B) y union的使用不但会将x和y合并后的数据去重, 还会将x或y各自中的重复数据进行去重...原创 2020-12-10 15:40:34 · 168 阅读 · 0 评论 -
MySQL ROW_NUMBER()函数,分组排序加序号
SELECT ROW_NUMBER ( ) OVER ( PARTITION BY a.inspection_point ORDER BY a.id DESC ) AS rowNum, a.inspection_point, a.id FROM t_product_inspection a WHERE a.STATUS = 0; *******语法格式:ROW_NUMBER ( ) OVER ( PARTITION BY 分组列 ORDER BY 排序列 DESC )...原创 2020-10-21 09:28:14 · 943 阅读 · 2 评论 -
MySQL 询当日07:00和上日0:00间的数据
SELECT DATE_FORMAT( CONCAT( date_sub( DATE_FORMAT( now( ), '%Y-%m-%d' ), INTERVAL 1 DAY ), ' 00:00:00' ), '%Y-%m-%d %H:%i:%s' ), DATE_FORMAT( CONCAT( DATE_FORMAT( now( ), '%Y-%m-%d' ), ' 07:00:00' ), '%Y-%m-%d %H:%i:%s' )原创 2020-10-20 16:02:50 · 237 阅读 · 0 评论 -
MySQL 对 float 和double 类型字段的四舍五入方法结果不准确的问题处理
SELECT ROUND( num + 0.00000001, 2 ) FROM tb;1、对于精确值的处理,是按照四舍五入的原则2、对于近似值,不管小数点后是啥,都取最接近的偶数原创 2020-09-16 17:12:05 · 653 阅读 · 0 评论 -
myBatis 标签使用小计,Set、 Where、 forEach标签的使用
1、set 标签的使用,自动帮助我们把最后一个逗号给去掉了 <update> update table <set> <if test="name != null and name.length()>0">name = #{name},</if> <if test="gender != null and gender.length()>0">gender = #{gender},</..原创 2020-09-07 14:54:18 · 698 阅读 · 0 评论 -
MySQL 根据字段的不同值进行升序和降序排序
-- 根据字段的不同值进行升序和降序排序( SELECT * FROM t_customer_list WHERE customer = 1 ORDER BY CREATE_DATE DESC ) UNION ALL( SELECT * FROM t_customer_list WHERE supplier = 1 ORDER BY CREATE_DATE ASC );SELECT * FROM t_customer_list ORDER BYCASE WHEN supplier =.原创 2020-09-07 14:40:21 · 1514 阅读 · 0 评论 -
myBatis 批量更新的方法
//MySQL 批量更新的方法 public void uptBatch(List<YourBean> beanList) { //批量更新的操作 if (null != beanList && beanList.size() > 0) { // 每个批次条数 100 ,分批条数不能太大,防止sql过长导致更新失败的异常 Integer partSize = Integer.parseInt(pSize)..原创 2020-09-02 15:45:43 · 203 阅读 · 0 评论 -
MySQL 查询多字段重复数据
//多字段重复数据 SELECT PUR_ORDER_ID, MATERIAL_ID, count( * ) FROM b_purchase_order_detail GROUP BY PUR_ORDER_ID, MATERIAL_ID HAVING count( * ) > 1; //查询多字段重复数据 SELECT * FROM b_purchase_ord...原创 2020-08-17 09:53:55 · 2604 阅读 · 1 评论 -
MySQL 查询时间:年月日时分秒
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T') FROM DUAL;原创 2020-08-14 14:14:49 · 1775 阅读 · 0 评论 -
MySQL 按xxx属性分组,取yyy属性最大值所在行的数据
SELECT x.* FROM b_purchase_order_detail x WHERE x.TAX_AMOUNT = ( SELECT max( y.TAX_AMOUNT ) FROM b_purchase_order_detail y WHERE x.MATERIAL_ID = y.MATERIAL_ID ) ORDER BY x.MATERIAL_ID;原创 2020-08-12 22:17:51 · 180 阅读 · 0 评论 -
MySQL 查询当前时间点所属月份的整月数据
where 1=1AND DATE_FORMAT( c.create_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' )原创 2020-06-26 21:33:30 · 824 阅读 · 0 评论 -
MySQL 字段分组,取某个属性最大的那一条数据中,某一个字段值。那个字段是不一样的,不能出现在GROUP BY中
SELECT s.id AS '主表 id', p.id AS '子表 id' FROM (SELECT max( sp.start_date ) startDate, max( sp.update_date ) updateDate, spl.project_id AS customerId, spl.varieties_id AS varietiesId FROM t_sales_price_list spl JOIN t_sales_price sp ON spl.price.原创 2020-06-26 17:31:13 · 579 阅读 · 0 评论 -
sql,查询结果按照数字大小顺序排序,并且null字段排在最后
order by a.sort is null, a.sort+0 ASC原创 2020-06-26 17:04:37 · 1626 阅读 · 1 评论 -
MySQL 查询,拼接html代码
//mysql查询,拼接html代码 SELECT '<table class="htmlStr" id="htmlStr" >' AS htmlStrUNION ALL SELECT '<tr id="thead"><th style="width:10%">ID</th><th style="width:20%">片区</th><th style="width:20%">客户</th>.原创 2020-06-05 20:05:16 · 293 阅读 · 0 评论 -
MySQL拼接html字符串
//MySQL拼接html字符串 "<span style='color:red'>上海市</span>" CASE a.colName WHEN 2 THEN"<span style='color:red'>北京市</span>" WHEN 3 THEN"<span style='color:red'>上海市</span>" ELSE " " END AS.原创 2020-06-05 13:46:15 · 228 阅读 · 0 评论 -
Mybatis 同时查询结果及总数量
1、数据库链接:加设置,&allowMultiQueries=true2、mapper<!--查询结果及总数量 mapper--> <resultMap id="data" type="java.util.LinkedHashMap"/> <resultMap id="data" type="SalesMessagePrice"/> <resultMap type="Long" id="nums" autoMapping="true"/&原创 2020-06-02 09:24:59 · 3200 阅读 · 3 评论 -
MySQL 查询某一个字段重复的所有数据
//查询名称重复的数据SELECT id, material_code, material_nameFROM t_materialWHERE material_code IN ( SELECT material_code FROM t_sys_material GROUP BY material_code HAVING COUNT(mate...原创 2020-04-29 10:45:02 · 1709 阅读 · 1 评论 -
MySQL help_topic_id 查询语句,将用逗号拼接的字段,拆分成多条数据进行显示
SELECT DISTINCT info.`id`, SUBSTRING_INDEX( SUBSTRING_INDEX( info.`material_id`, ',', b.help_topic_id + 1 ), ',', - 1 ) AS material_id FROM ( ...原创 2020-04-26 10:37:32 · 4896 阅读 · 7 评论