MySql查询(大脑挑战)
MySql查询(大脑挑战)
提问于 2016-05-30 05:28:01
列出所有在过去三个月(90天)内完成三次或三次以上维修工作的飞机。同时，列出所有飞机注册号、plane_type和制造商。
试点表：
reg_nbr, plane_type, manufacturer, resident, date_service
'101', 'SE Prop', 'Lear', '1', '1998-12-01'
'202', 'DE Prop', 'Cessna', '1', '1999-11-22'
'303', 'SE Jet', 'Bombardier', '1', '2000-07-06'
'404', 'ME Jet', 'Lear', '1', '2001-02-24'
'505', 'SE Prop', 'Cessna', '0', '2003-12-01'
'606', 'DE Prop', 'Bombardier', '1', '2004-11-22'
'707', 'SE Jet', 'Lear', '1', '2005-07-06'
'808', 'ME Jet', 'Cessna', '0', '2005-02-24'
'909', 'SE Prop', 'Bombardier', '1', '2002-02-12' 服务表：
workorder_nbr, file_nbr, reg_nbr, date_start, date_end, work_description, hours_worked, person_nbr
'2061', '3101', '101', '2014-03-16', '2014-03-16', 'Maint', '2', '901'
'2062', '3101', '101', '2014-07-20', '2014-07-21', 'Maint', '3', '901'
'2063', '3102', '202', '2015-12-22', '2015-12-22', 'Replace', '5', '901'
'2064', '3102', '202', '2015-07-12', '2015-07-13', 'Clean', '2', '901'
'2065', '3103', '303', '2015-05-01', '2015-05-01', 'Maint', '2', '901'
'2066', '3104', '404', '2015-07-09', '2015-07-09', 'Clean', '5', '901'
'2067', '3108', '505', '2015-07-09', '2015-07-09', 'Maint', '2', '901'
'2068', '3106', '606', '2015-08-21', '2015-08-21', 'Check', '1', '901'
'2069', '3101', '101', '2016-01-15', '2016-01-16', 'Maint', '3', '901'
'2070', '3101', '101', '2016-01-31', '2016-01-31', 'Repair', '15', '901'
'2071', '3101', '101', '2016-02-08', '2016-02-10', 'Repair', '8', '901'
'2072', '3108', '505', '2016-01-08', '2016-01-09', 'Upgrade', '20', '901'
'2073', '3108', '505', '2016-02-02', '2016-02-02', 'Repair', '1', '901'
'2074', '3108', '505', '2014-02-11', '2014-02-11', 'Maint', '1', '901' 我的问题：(无论如何，这是错误的)
SELECT service.reg_nbr, plane_type, manufacturer
FROM service, aircraft
WHERE date_start BETWEEN (date(NOW() AND 90DAY)
GROUP BY service.reg_nbr
HAVING COUNT(service.reg_nbr) >=3我在哪里挣扎，如何使它显示当前日期到90天前，按问题的要求)
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-05-30 05:32:24
一种选择是使用INTERVAL
WHERE date_start BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()以下是完整的查询：
SELECT s.reg_nbr
FROM aircraft a
INNER JOIN service s
ON a.reg_nbr = s.reg_nbr
WHERE date_start BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()
GROUP BY s.reg_nbr
HAVING COUNT(*) >= 3Stack Overflow用户
发布于 2016-05-30 05:31:22
您应该使用DATE_ADD函数：
SELECT service.reg_nbr, plane_type, manufacturer
FROM service, aircraft
WHERE date_start BETWEEN date(NOW()) AND DATE_ADD(NOW(), INTERVAL -90 DAY) 参考资料：date-add
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37518158
复制相关文章
相似问题
腾讯云开发者
