select a.logid,a.staffid,a.title,a.edittime,b.staffname,b.orgid,c.orgcode from fitdoa.tb_oa_log a left join fitdoa.tb_oa_staff b ON a.staffid=b.staffid left join fitdoa.tb_oa_organization c ON b.orgid=c.orgid where c.parentid='D001' and select DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) and select DATE_FORMAT(a.edittime,'Y% m% d%')<=CURDATE(); 帮我看看这个语句有什么问题行不行? You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1' at line 1 这是报的错 是不是因为a.edittime 不是常量的原因 如果是这样的话 这条语句该怎么写?
select a.logid,a.staffid,a.title,a.edittime,b.staffname,b.orgid,c.orgcode from fitdoa.tb_oa_log a left join fitdoa.tb_oa_staff b ON a.staffid=b.staffid left join fitdoa.tb_oa_organization c ON b.orgid=c.orgid where c.parentid='D001' and DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) and DATE_FORMAT(a.edittime,'Y% m% d%') <=CURDATE();你前面加了一些 select 导致语法错误。
不是,去掉SELECT SELECT a.logid,a.staffid,a.title,a.edittime,b.staffname,b.orgid,c.orgcode FROM fitdoa.tb_oa_log a LEFT JOIN fitdoa.tb_oa_staff b ON a.staffid=b.staffid LEFT JOIN fitdoa.tb_oa_organization c ON b.orgid=c.orgid WHERE c.parentid='D001' AND DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND DATE_FORMAT(a.edittime,'Y% m% d%') <=CURDATE();
还是多了一个 select 直接改成如下,不需要用date_format , 这样可以直接利用索引。效率会高。select a.logid,a.staffid,a.title,a.edittime,b.staffname,b.orgid,c.orgcode from fitdoa.tb_oa_log a left join fitdoa.tb_oa_staff b ON a.staffid=b.staffid left join fitdoa.tb_oa_organization c ON b.orgid=c.orgid where c.parentid='D001' and a.edittime>=DATE_SUB(CURDATE(), INTERVAL 1 DAY) and a.edittime<CURDATE()
OR DATE_FORMAT(datetime,'%Y-%m')
SELECT '2009-6-1 12:00:00'<'2009-6-3'
结果为1,
是可以直接写的,不过最好转换成类型一致
+------------+---------------------+-----------------+
| curdate() | now() | curdate()<now() |
+------------+---------------------+-----------------+
| 2009-12-08 | 2009-12-08 16:24:38 | 1 |
+------------+---------------------+-----------------+
1 row in set (0.00 sec)mysql>
帮我看看这个语句有什么问题行不行?
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1' at line 1
这是报的错 是不是因为a.edittime 不是常量的原因 如果是这样的话 这条语句该怎么写?
from fitdoa.tb_oa_log a left join fitdoa.tb_oa_staff b ON a.staffid=b.staffid
left join fitdoa.tb_oa_organization c ON b.orgid=c.orgid
where c.parentid='D001'
and DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and DATE_FORMAT(a.edittime,'Y% m% d%') <=CURDATE();你前面加了一些 select 导致语法错误。
SELECT a.logid,a.staffid,a.title,a.edittime,b.staffname,b.orgid,c.orgcode FROM fitdoa.tb_oa_log a
LEFT JOIN fitdoa.tb_oa_staff b ON a.staffid=b.staffid
LEFT JOIN fitdoa.tb_oa_organization c ON b.orgid=c.orgid
WHERE c.parentid='D001' AND DATE_FORMAT(a.edittime,'Y% m% d%')>=SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND DATE_FORMAT(a.edittime,'Y% m% d%') <=CURDATE();
from fitdoa.tb_oa_log a left join fitdoa.tb_oa_staff b ON a.staffid=b.staffid
left join fitdoa.tb_oa_organization c ON b.orgid=c.orgid
where c.parentid='D001'
and a.edittime>=DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and a.edittime<CURDATE()