SQL语句学习汇总（Mysql）

一、如下user表，查询表中每人重复出现的次数

id	name	mobile
1	老大	13812340010
2	老二	13812340020
3	老二	13812340020
4	老三	13812340030
5	老四	13812340040
6	老五	13812340050
7	老五	13812340050

SELECT mobile,COUNT(*) 
FROM user 
GROUP BY mobile HAVING COUNT(*) > 1;

 查询结果：

mobile	COUNT(*)
13812340010	1
13812340020	2
13812340030	1
13812340040	1
13812340050	2

循环嵌套使用：

SELECT * 
FROM user 
WHERE mobile IN (SELECT mobile FROM user GROUP BY mobile HAVING COUNT(*) > 1)
ORDER BY mobile DESC;

二、两张表的内联操作

SELECT v.create_time,o.*
FROM ins_order o 
INNER JOIN ins_order_visit v ON o.order_no = v.order_no AND v.create_time > '2020-01-10';

三、三张表的内联操作（io表为主表，内联iov表，且内联iovi表，因此只有同时满足两对内联条件的记录才会显示）

SELECT io.*
FROM ins_order io 
INNER JOIN ins_order_visit iov ON io.order_no = iov.order_no AND iov.del_flag = '0'
INNER JOIN ins_order_visit_idea iovi ON io.cont_no = iovi.cont_no AND iovi.del_flag = '0';

四、根据出生日期范围查询内部用户列表

    <!-- 根据出生日期范围查询内部用户列表 -->
    <select id="selectUserListByDateRange" resultMap="BaseResultMap">
        SELECT
            bn.name,bn.sex,bn.department
        FROM
            bn_inner_user bn
        WHERE
            substring(bn.card_no,7,8) &gt;= #{minDate} and substring(bn.card_no,7,8) &lt; #{maxDate}
    </select>

 五、删除表中多余的记录

DELETE FROM ins_order_visit_idea
WHERE cont_no IN ( SELECT cont_no FROM ( SELECT cont_no FROM ins_order_visit_idea GROUP BY cont_no HAVING COUNT( cont_no ) > 1 ) e )
  AND Id NOT IN ( SELECT Id FROM ( SELECT MIN( Id ) AS Id FROM ins_order_visit_idea GROUP BY cont_no HAVING COUNT( cont_no ) > 1 ) t );