1. select a* 用于系统统计功能
2. SELECT a.*, b.*
3. FROM
4. (SELECT SUM(DOMESTIC_TRAIN) + SUM(OVERSEA_TRAIN_TOTAL) AS zj,
5. SUM(DEGREE_PHD) AS qzgdbsx,
6. SUM(DOMESTIC_TRAIN) AS jnjxrcs,
7. SUM(OVERSEA_TRAIN_TOTAL) AS jwjxrcs
8. FROM TRAIN_INTERFLOW
9. where YEAR_START=to_char(sysdate,'yyyy')-2
10. ) a,
11. (SELECT SUM(PARTICIPANT_NUMBER) AS cyjglxkrcs
12. FROM EDU_REVOLUTION
13. where YEAR_START=to_char(sysdate,'yyyy')-2
14. ) b;
每个字段看系统统计的需求。
2.添加新的排序序号
SELECT
@rownum :=@rownum + 1 AS num,
id,
amount
FROM
t_service_orders,
(SELECT @rownum := 0) b
ORDER BY
amount DESC
LIMIT 10
3.日期类型判断
判断日:date_format(birthday,'%Y-%m-%d')='2016-08-23'
判断月:date_format(birthday,'%Y-%m')='2016-08'
判断年:date_format(birthday,'%Y')='2016'
语句:SELECT
*
FROM
t_service_orders
WHERE
DATE_FORMAT(start_time, '%Y-%m-%d') = '2017-03-06'
4.计算年龄
year(getdate()) - year(birthday)
SELECT
(YEAR(NOW()) - YEAR(birthday)) AS age
FROM
demo
GROUP BY
age
5.计算两个时间差
MINUTE 分钟 可以换成小时、天、秒
SELECT * from t_car_orders where TIMESTAMPDIFF(MINUTE,create_time,NOW()) > 15 and status=0
6.随机数及保留两位小数点
保留两位小数
SELECT FORMAT(121.5347528458,2)
随机1到100的小数
select 1+RAND()*(100)
随机1到100的整数
select CEIL(1+RAND()*(100))
综合例子
update t_bikes set location_x = (SELECT FORMAT((SELECT 121.5347528+(select RAND()/8)),7)), location_y = (SELECT FORMAT((SELECT 31.2121407+(select RAND()/8)),7))
7.like的用法
问题:like '%%'并不能把null的值查出来
解决方式:xx like '%%' and xx is null
8.一张表插入到另一张表
a.表结构一样
insert into table select * from table
b.表结构不一样
insert into table(,,,) select , , , from table
8.1 一张表插入到另一张表(带自增的)
实例
set @orderNumber := 0;
INSERT INTO system_order (
order_number,
table_id,
created_dtm,
created_by,
table_key
) SELECT
@orderNumber := @orderNumber+1,
id,
created_dtm,
1,
'eshop_product_list'
FROM
eshop_product_list