sql笔记

sql排序 id本身为字符串

select (ID+0) AS orderfield from tablename t  ORDER BY orderfield

select max(ID+0) from tablename

日期加减

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

向 "OrderDate" 添加 2 天

left' join 多表查询 

SELECT
	字段1，
	字段2
FROM
	tablename1 t1
LEFT JOIN tablename2 t2 ON t1.column = t2.column
LEFT JOIN tablename3 t3 ON t1.column = t3.column
AND t3. column = '0'
WHERE 1 = 1
AND t2.column = '7'
AND t3.column IS NULL

-- 查询字段
SELECT	
	DATE_FORMAT(now(), '%y-%m-%d') as nowDate,
	DATE_ADD( begindate,INTERVAL (days + 7) DAY) as addDaysDate,
	str_to_date( DATE_ADD( begindate,INTERVAL (days + 7) DAY),'%Y-%m-%d') as addDaysDateStr 
-- where 过滤条件(两个String字符串类型的日期比较大小)
AND DATE_FORMAT(now(), '%y-%m-%d') < (
	str_to_date(
		DATE_ADD(
			t2.begindate,
			INTERVAL (t1.crdtagt + 7) DAY
		),
		'%Y-%m-%d'
	)
)

-- 字符串类型的日期可以直接比较大小（注：mysql数据库，例如where过滤条件）
-- MySQL字符串转换为数字 -- varchar(50) days，days+0
-- 方法一：SELECT CAST('123' AS SIGNED);
-- 方法二：SELECT CONVERT('123',SIGNED);
-- 方法三：SELECT '123'+0;

mysql拼接字符串

update parm_ctrl set PARMONE=CONCAT(PARMONE, ',704') where parmid ='9086';

1、jdbc查询记录数

2、sql语句判断两个时间段是否有交集

//按时间分组统计订单量
SELECT LEFT(create_time,15),COUNT(1) FROM  order 
WHERE create_time > '2017-11-01 00:00:00'  AND create_time<'2017-11-01 09:40:00'
GROUP BY LEFT(create_time,15) DESC

//统计一天的订单量交易量
SELECT count(1) FROM order WHERE create_time BETWEEN '2017-08-07 00:00:00' AND '2017-08-08 00:00:00';

// 去重复统计
SELECT COUNT(DISTINCT trans_batch_id) FROM  batchpay_order 
WHERE create_time > '2017-11-02 00:00:00'  AND create_time<'2017-11-02 21:40:00'
AND member_id='1194851';