请教时间段内过生日的会员查询sql问题（有农历和阳历。）

Question

数据库

数据库两个字段

• is_lunar(是否农历) 0 = 阳历 ，1 = 农历

• birthday 具体生日时间 date类型 格式 1985-07-19

目前已实现的sql（写死的只能查当天过生日的数据）

 "SELECT * FROM `ta_vip_member` WHERE  (  is_lunar = 0 and DATE_FORMAT(birthday, '%m-%d') = '08-03' ) OR (  is_lunar = 1 and DATE_FORMAT(birthday, '%m-%d') = '06-12' ) ORDER BY id desc"

需求

查询出某个时间段内的所有过生日的会员 包括农历和阳历
这个sql有点懵逼了。

Answer 1

你就一个参数...怎么查询时间段？

Answer 2

SELECT * FROM `ta_vip_member` WHERE ( is_lunar = 0 and DATE_FORMAT(birthday, '%m-%d') >= '08-03' AND DATE_FORMAT(birthday, '%m-%d') < '08-03' ) OR ( is_lunar = 1 and DATE_FORMAT(birthday, '%m-%d') >= '06-12' AND DATE_FORMAT(birthday, '%m-%d') < '06-12') ORDER BY id desc

既然楼主都这样写SQL了 其实也可以这样

SELECT * FROM `ta_vip_member` 
    WHERE  ( is_lunar = 0 and birthday between     DATE_FORMAT(birthday, '%m-%d') and  '08-03'  DATE_FORMAT(birthday, '%m-%d') and  '08-03')
        OR ( is_lunar = 1 and birthday between  DATE_FORMAT(birthday, '%m-%d') and  '06-12'  DATE_FORMAT(birthday, '%m-%d') and  '08-03')
        ORDER BY id desc

针对楼主问题有几个疑问

1. 查询某个时间段？

2. 为什么要这么存？有什么优化效果？要是在程序里这样写会被打死的

对于会员生日字段为何不这样

1. 管它农历阴历，甩时间戳进去，统统转为农历，其他交给程序来换算

2. 楼主是不明白那句SQL是什么意思么？

Answer 3

select * from risk_controls where (status = 1 and created_at between '2017-7-31' AND '2017-08-05') OR (status = 0 and created_at between '2017-7-31' AND '2017-08-05')

between and 就可以轻松搞定拉~