巴拉巴拉
小魔仙

SQL随手记

1.MySQL实现类型Oracle的伪列

SELECT 
    @rownum:=@rownum+1 AS rownum,
    ys_sys_userlist.* 
FROM 
    (SELECT @rownum:=0) r, 
    ys_sys_userlist;

2.使用exists来替代类似where id in(select id from xxx)的功能

select * from cs_tb_salebill s 
where [not] exists (
    select 1 from cs_tb_feedback f 
    where f.wordenum = s.wordenum
)

3.计算两个坐标的点距离,或者找附近的点

-- 其中31.780698是指定的lat坐标,117.1431是指定的lng坐标,而1000则是距离,单位是m(米)

SELECT longitude,latitude, 
     ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(SIN((31.780698 * PI() / 180 - latitude * PI() / 180) / 2) , 2) 
                +
                COS(31.780698 * PI() / 180)
                *
                COS(latitude * PI() / 180)
                *
                POW(SIN((117.1431 * PI() / 180 - longitude * PI() / 180) / 2) , 2)
            )
        ) * 1000
    )as distance
FROM location
HAVING distance < 1000
ORDER BY distance ASC

4.IF的使用

SELECT 
    *
FROM ys_sys_userlist 
WHERE 
    IF(isdelete=1,rolecode='006',rolecode='003');

SELECT 
    IF(isdelete=0,username,userid) as name,
    userId
FROM ys_sys_userlist
    WHERE rolecode='003'

其中的IF中有三个参数,在where中,可以用来做三次判断,先判断第一个参数,为true则判断第二个,为false则判断第三个参数。

如果放在select中,则可以先判断第一个参数,然后在选择输出那个字段,同样,为true则输出第二个参数的字段,为false则输出第三个参数的字段。

5.解决关键字冲突可以试试`

6.MyBatis->获取自增长生成列

<insert id="add" parameterType="User" keyProperty="id" useGeneratedKeys="true">
    INSERT INTO tb_user(<include refid="all"/> )
    VALUES (0,#{userName},#{pwd})
</insert>

7.递归获取数据登记和数据路径

先查看简单的数据源

select name,id,pid from table_area
name    id      pid 
中国      1       0
安徽      2       1
合肥      3       2
包河      4       3
四川      5       1
成都      6       5
肥西      7       3

修改后:

SELECT name,id,pid,levels,paths FROM (
   SELECT id,pid,name,
   @le:= IF (pid = 0 ,0, 
     IF( LOCATE( CONCAT('|',pid,':'),@pathlevel)  > 0 ,   
         SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1
    ,@le+1) ) levels
    , @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel
    , @pathnodes:= IF( pid =0,'/中国', 
      CONCAT_WS('/',
      IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 , 
        SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1)
            ,@pathnodes ) ,name ) )paths
    ,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall 
    FROM table_area, (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
    ORDER BY pid,id
) src
ORDER BY paths
name    id      pid     levels  paths
中国      1       0       0       /中国
四川      5       1       1       /中国/四川
成都      6       5       2       /中国/四川/成都
安徽      2       1       1       /中国/安徽
合肥      3       2       2       /中国/安徽/合肥
包河      4       3       3       /中国/安徽/合肥/包河区
肥西      7       3       3       /中国/安徽/合肥/肥西县

8.处理类似map权限分配的表增删处理的思路
@param custId 客户的Id
@param arrAppId 给该客户分配的appId的数组
我们要给custId的客户分配appId账号的使用权限
首先先删除该客户不存在的App账号
后删除该表中没有的数据,如果这条数据已经存在则不插入。
!当前剩余的问题:插入不能批量插入,等后续更新

delete from tablename where custId = ? and appId not in (?,?,?)
INSERT INTO table(column1,column2,column3 ...columnN)  
SELECT value1,value2,value3 ...valueN  
FROM dual  
WHERE NOT EXISTS(  
      SELECT 1 
      FROM table  
      WHERE value = ?  
);

9.拼接查询出来的结果为一条数据
SELECT GROUP_CONCAT(id separator “,”) FROM tablename

10.时间查询

几个小时内的数据
DATE_SUB(NOW(), INTERVAL 5 HOUR)

今天
select * from 表名 where to_days(时间字段名) = to_days(now());

昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

连续三个月,不区分年的数据
-- sm是起始月份
-- size是月份跨度大小
set @sm=6;
set @size = 3;
select * from ys_log_message
where 
if(@sm > 12 - @size,
	month(createtime) >= @sm or month(createtime) < @sm + @size - 12,
	month(createtime) >= @sm and  month(createtime) < @sm + @size)