一、
SELECT tmp2.name,tmp2.browseNum FROM (SELECT tmp.`name`, COUNT(tmp.id) AS browseNum FROM(SELECT b.name as name,b.id as id FROM product_category a
LEFT JOIN product_category b ON substring(a.id,1,5)=b.id
WHERE a.attribute = ‘1‘ ORDER BY id) tmp
GROUP BY tmp.id,tmp.`name` limit 6) tmp2 ORDER BY tmp2.browseNum DESC
二、
select * from (
select name,account,wechat,lastLoginTime,contacts,phone,duties,IFNULL(status,10) status from
(select name,regNo from com_info) a
left join
(
select account,wechat,companyKey,lastLoginTime from u_user
left JOIN u_user_role on u_user.userId = u_user_role.userId where u_user_role.roleId=1 group by companyId
)b on a.regNo = companyKey
left join
(select comInfoId,contacts,phone,duties from operator_info group by comInfoId)c on
c.comInfoId = a.regNo
left join
(select companyId,min(status) status from com_perinfo group by companyId) d on
d.companyId = a.regNo
)a order by status
三、字符串拼接
select concat("update product_category set `subsidyLimit`=‘", `subsidyLimit` ,"‘ where id=‘", id, "‘;") from product_category WHERE id LIKE ‘%B%‘ AND `level` = 2
select concat("update u_permission set sequence=‘", sequence ,"‘,icon=‘",icon,"‘ where permissionId=‘", permissionId, "‘;") from u_permission WHERE sequence BETWEEN ‘41‘ AND ‘46‘
select concat("INSERT INTO u_role_permission ( roleId,permissionId)VALUES(‘", roleId, "‘,‘", permissionId, "‘);") from u_role_permission WHERE permissionId=‘51e4a46bac1f4b3db853a27344f181ca‘
select concat("update main_log set outResult=‘", a.comContractUrl,"‘ where functionName = ‘企业上传合作协议‘ AND functionKey=‘", a.orderId, "‘;")
from (SELECT b.functionName,b.comContractUrl,b.orderId FROM
(SELECT t.comContractUrl,t.orderId,m.functionName FROM transaction_info t LEFT JOIN main_log m ON t.orderId = m.functionKey )b)a
concat(IFnULL(b.handOverFileUrl,""),",",IFNULL(b.bankUrl,""),",",IFNULL(b.receiptUrl,""))
四、添加字段
ALTER TABLE people ADD COLUMN name VARCHAR(100) DEFAULT NULL COMMENT ‘姓名‘
修改表 people 增加字段 name 长度100 默认为null 备注：姓名
五、有条件的修改字段
<update id="updateSecondRentalInThird" parameterType="com.rental.entity.SecondRental">
UPDATE secondrental
<set>
<if test="fid !=null">fid = #{ fid },</if>
<if test="sname !=null">sname = #{ sname },</if>
</set>
WHERE
sid=#{sid}
</update>
六、有条件的插入数据
insert into tablename( 属性1，属性2) values (‘值1‘，‘值2’);
insert into tablename(属性1，属性2) select ‘值1‘,‘值2‘ from dual where exists (select 值1 from tablename where 子句);
例子：
<insert id="insertdemo" parameterType="com.略.entity.table_name">
insert into table_name (id, name
)
select #{id}, #{name}
from dual where not exists
(select 1 from table_name where id=#{id} and name=#{name})
</insert>
<insert id="insertOrgInfo" parameterType="com.xql.ctv.entity.ctv.entity.organization_type">
insert into organization_type( id, typeName, orglv, parentType, instruction, updateTime)
select #{id,jdbcType=CHAR}, #{typeName,jdbcType=VARCHAR}, #{orglv,jdbcType=VARCHAR},
#{parentType,jdbcType=BIT}, #{instruction,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}
FROM dual
WHERE not EXISTS (SELECT typeName FROM organization_type WHERE typeName = #{typeName})
</insert>
七、拆出时间中的月份
SELECT count(1)as mount, DATE_FORMAT(payTime,‘%Y‘)AS years,DATE_FORMAT(payTime,‘%m‘)AS months FROM pay_info
GROUP BY years,months
八、sum()带条件统计
SUM(if(a.comType=1,a.registeredCapital,0))AS foreignRegistered, -- 求所有 类型为1 所有registeredCapital 的 总和
IFNULL(SUM(1111>status AND status>=50),0) -- 求 status<100的status 的个数
IFNULL(SUM(b.employedNum>=50),0)AS totalPerson2, -- 求 数量在50-100之间的 个数
九、字符串去除某字符包含了某字符串
SELECT COUNT(1)amount,replace(replace(industry,"[\"",""),"\"]","")AS industryNew FROM com_basic_info
WHERE replace(replace(industry,"[\"",""),"\"]","") LIKE ‘%互联网%‘ OR replace(replace(industry,"[\"",""),"\"]","") LIKE ‘%专用%‘ GROUP BY industry
十、分组后再分组
SELECT SUM( c.total) total,
CASE WHEN c.type IN(‘1‘,‘2‘,‘3‘) THEN ‘1‘
WHEN c.type IN (‘4‘) THEN ‘4‘
WHEN c.type IN (‘5‘) THEN ‘5‘
WHEN c.type IN (‘6‘) THEN ‘6‘
WHEN c.type IN (‘7‘) THEN ‘7‘
ELSE c.type END as types, IFNULL(SUM(c.mount),0) AS mount
FROM
(
SELECT (SELECT COUNT(1) FROM com_basic_info)total,COUNT(1) AS mount,
SUBSTRING(a.comType,1,1) AS type
FROM com_basic_info a LEFT JOIN com_year_report b ON a.creditCode = b.creditCode
WHERE b.reportYear = (DATE_FORMAT(NOW(),‘%Y‘)-1) GROUP BY type
)c
GROUP BY types
十一、时间取当今年 的往前3个月的 数据
SELECT COUNT(1) FROM com_basic_info WHERE DATE_FORMAT(foundingTime,‘%Y-%m‘) = DATE_FORMAT(NOW(),‘%Y-%m‘)
OR DATE_FORMAT(foundingTime,‘%Y-%m‘) = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),‘%Y-%m‘)
OR DATE_FORMAT(foundingTime,‘%Y-%m‘) = date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),‘%Y-%m‘)
十二、截取字符串
SUBSTRING(b.payedDate,5,6)
十三、关联表更新
UPDATE tdb_goods AS g JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name JOIN tdb_goods_cates AS c ON g.goods_cate = c.cate_name SET g.brand_name = b.brand_id, g.goods_cate = c.cate_id;
十四、left join on 条件
SELECT count(1),a.level,a.minScore,a.maxScore,a.topLimit FROM(
(SELECT level,minScore,maxScore,topLimit
FROM bracket_info b LEFT JOIN credit_limit_by_bracket_item c ON b.id = c.bracketId LEFT JOIN credit_limit_by_bracket_record cr
ON c.recordId = cr.recordId
WHERE cr.recordId = ‘58a01f9d96174c25b70e7fa546cfa949‘ ORDER BY LEVEL asc)a
LEFT JOIN (SELECT score FROM voucher_apply_info WHERE batchId = ‘a1854d50ee484c04b744952ca90605e6‘ AND STATUS = 4 ORDER BY score)b ON (b.score BETWEEN a.minScore AND maxScore)
)GROUP BY level
十五、分组中时间最新的数据
SELECT a.regno,a.score FROM voucher_apply_info a
LEFT JOIN (SELECT MAX(updateTime)AS maxTime FROM voucher_apply_info GROUP BY regno )b ON a.regNo = b.regno
WHERE a.updateTime = b.maxTime ORDER BY a.score DESC LIMIT 50
十六、表几种连接方式
1） 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2）左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3） 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4） 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
十七、修改sql中字段注释
alter table test1 modify column field_name int comment ‘修改后的字段注释‘;
alter table goods_info modify column status int comment ‘修改后的字段注释‘;
十八、一个企业多条数据多个标签，用|分隔 组装
-- 效果：国家高新技术企业 | 科技小巨人培育企业
select IFNULL(GROUP_CONCAT(b.tagName SEPARATOR ‘ | ‘),‘‘)as tagNameNew,a.comId,a.tagId FROM com_tag_relevance a
LEFT JOIN com_tag b ON a.tagId=b.tagId
group by a.comId;
十九、随机排序
SELECT * FROM voucher_apply_info order by rand();
固定一条数据排在前面，其它随机排序
1、SELECT * FROM com_info order by field(regNo,340191000113172) DESC;
2、SELECT * FROM com_info order by IF(regNo=‘340191000113172‘,1,rand()) DESC;
注：
0<rand()<1
MySQL的笔记
原文：https://www.cnblogs.com/qiuqiu001/p/10354587.html
