sql工作问题总结

1. sql排序：
1、 order by ……
2、 row_number() over(partition by …… order by ……)
　　使用说明：此函数适合做分组、排序，而不能在使用它分组的同时使用聚合函数
3、嵌套查询，保持内层查询的排列顺序，可以使用rownum记录内层记录的行号，外层查询按rownum进行排序即可。（与oracle的分页查询类似）

2. 处理除数为0的情况：
1、使用decode()函数。例：略
2、使用nullif()函数。（推荐使用）例：min(nvl(p.vm_total_price_member / nullif(p.vm_total_price_origin, 0), 0)) as vmMinTotalPriceDiscount
说明：nullif(a,b)函数是在a==b时将a转为了null。当一个数去除以null时，得到的结果就是null。然后再用nvl()函数将null值转为0。

3. group by
在使用group by时，select能够查询出来的信息量只能是group by的字段和聚合函数中的内容。
所以有个使用小技巧：如果要查多个字段信息，但这些字段没有必要使用聚合函数来算它的值，则我们可以变相的使用 max(字段名) 来取。
注意：此种方式仅适用于一对多的情况中，取“一”方字段时，因为此时分组内“一”方的分组内的所有记录的字段都是一样的，故通过这种方法取出来的也就是我们要的数据。

4. oracle 中根据一张表快速创建另外一张表：

create table account_file_import_2 as select * from account_file_import where 1<> 1;

5. 将查询结果批量插入一张表：

insert into account_file_import_2 select * from account_file_import

6. 用一条SQL去定点批量更新多条记录

例：将coupon_order中的数据统计好后，更新到coupon表，更新的记录为coupon_order中所有的id_coupon

update coupon c
   set (c.give_num, c.average_price) = (select count(1),
                                               sum(co.average_price)
                                          from coupon_order co
                                         where co.status in
                                               ('未兑换', '兑换中', '已兑换')
                                           and c.id_coupon = co.id_coupon
                                         group by co.id_coupon)

 where c.id_coupon in (select id_coupon from coupon_order);

7. oracle & mysql

http://lib.csdn.net/base/oracle/structure
http://lib.csdn.net/base/mysql/structure

8.MySQL查询指定时间的数据

user_event ：用户事件表
create_time ：表中存储时间的字段

#获取当月数据
SELECT * FROM user_event WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')

#获取3月份数据
SELECT * FROM user_event WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT('2016-03-01','%Y-%m')

#获取三月份数据
SELECT * FROM user_event WHERE YEAR(create_time)='2016' AND MONTH(create_time)='3'

#获取本周数据
SELECT * FROM user_event WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW()); 

#查询上周的数据 
SELECT * FROM user_event WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1; 

#查询距离当前现在6个月的数据 
SELECT * FROM user_event WHERE create_time BETWEEN DATE_SUB(NOW(),interval 6 month) and NOW();

#查询上个月的数据 
SELECT * FROM user_event WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')

#查询今天的信息记录：
SELECT * FROM user_event WHERE TO_DAYS(`create_time`) = TO_DAYS(NOW());

#查询昨天的信息记录：
SELECT * FROM user_event WHERE TO_DAYS(now()) - TO_DAYS(create_time) <= 1;

#查询近7天的信息记录：
SELECT * FROM user_event WHERE DATE_SUB(curdate(), INTERVAL 7 DAY) <= DATE(create_time);

#查询近30天的信息记录：
SELECT * FROM user_event WHERE DATE_SUB(curdate(), INTERVAL 30 DAY) <= DATE(create_time);

#查询上一月的信息记录：
SELECT * FROM user_event WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(create_time, '%Y%m')) =1;

9. mysql每隔指定数目取记录

SELECT * from (Select car_log.*,(@rowNum:=@rowNum+1) as rowNo From car_log, (Select (@rowNum :=0) ) b order by id asc) as a where mod(a.rowNo, 20) = 1

10. mysql将多条数据合并成一行

select
  c.id,
  c.name,
  GROUP_CONCAT(bl.lock_id SEPARATOR ',') as lockIdList
from car c, box b, box_lock bl
where c.id = b.car_id
      and b.id = bl.box_id
GROUP BY c.id

11. mysql中指定数据排序

SELECT * FROM user ORDER BY user_id<>7,score DESC;
主要是“user_id<>7”，就会把用户id为7的排在前面。

如果是多条数据行：
SELECT * FROM user ORDER BY user_id NOT IN(7,8,9),score DESC;

12. update where not exist 不能是同一张表

场景：表里只能存在一条 status = ‘DEFAULT’ 的记录，带条件更新

UPDATE zk_image
SET `status` = 'DEFAULT'
WHERE
    NOT EXISTS (
        SELECT
            count(0)
        FROM
            zk_image t
        WHERE
            t.`status` = 'DEFAULT'
    )
AND id = 1;

上面的 SQL 会报错：
[Err] 1093 - You can't specify target table 'zk_image' for update in FROM clause

使用下面的就可以：

UPDATE zk_image
SET `status` = 'DEFAULT'
WHERE
    NOT EXISTS (
        SELECT * FROM
            (
                SELECT
                    count(0) AS cnt
                FROM
                    zk_image
                WHERE
                    `status` = 'DEFAULT'
            ) AS t
        WHERE
            t.cnt >= 1
    )
AND id = 1;

参考：https://blog.csdn.net/fly910905/article/details/88633902

13. 如果记录的状态值 status 变更，则相应的更新 status_change_time

刚开始写出了如下的错误 sql:

UPDATE t_account
SET balance = 123,
status = 1,
status_change_time =
CASE
    WHEN STATUS != 1 THEN
        NOW()
    ELSE
        status_start_time
    END
WHERE
    id = 135

这种写法 status_change_time 的值永远不会改变。

错误原因是：status 已经被 set 成 1 了，那么 status != 1 就永远不会成立，所以 status_change_time 的值就不会改变。

找到原因后，又写出了下面的 sql ：

UPDATE t_account 
SET balance = 123,
status = 1,
status_change_time = ( 
    SELECT CASE WHEN status != 1 THEN NOW() ELSE status_start_time END
    FROM t_account 
    WHERE id = 135 
) 
WHERE
    id = 135

结果 sql 异常，报如下错：

MySQL Error 1093 - Can't specify target table for update in FROM clause

最终改成了下面的这种写法就可以了：

UPDATE t_account 
INNER JOIN (
    SELECT CASE WHEN status != 1 THEN NOW() ELSE status_start_time END as change_time
    FROM t_account 
    WHERE id = 135 
) t
SET balance = 123,
status = 1,
status_change_time = t.change_time
WHERE
    id = 135

参考：https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

posted on 2014-02-13 10:24 快鸟阅读(320) 评论(0) 编辑收藏举报