如何修改现有SQL查询语句实现仅展示近30天内有活跃记录的设备

你可以直接在原有WHERE子句中新增最近30天的时间过滤条件，和原有搜索规则用AND关联即可，不同数据库的时间函数略有差异，以下是常见数据库的适配写法：

• MySQL适配：新增条件为 last_seen_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
• PostgreSQL适配：新增条件为 last_seen_at >= NOW() - INTERVAL '30 days'
• SQL Server适配：新增条件为 last_seen_at >= DATEADD(day, -30, GETDATE())

以下是调整后的完整SQL（以MySQL为例）：

SELECT name as "Device Name",description as "Description",last_seen_at  as "Last Seen At",collector as "Collector",alias_name as "Alias Name",model as "Model",device_types as "Device Types",ip_function_type as "IP Function Type",system_oid as "System OID",vendor as "Vendor",host_name as "Host Name",primary_ip_address as "Primary IP Address",city as "City",state as "State",collection_system as "Collection System",location as "Location",location_description as "Location Description",std_device_name as "Standard Device Name",collector_alternate_id as "Collector Alternate Id",create_time as "Create Time",device_model_id as "Device Model Id",location_id as "Location Id",display_name as "Display Name",display_description as "Display Description",supported_protocols as "Supported Protocols",polled_item_count as "Polled Iten Count",life_cycle_state as "Life Cycle State",life_cycle_state_last_chg_on as "Life Cycle State Last Change On",contact_status as "Contact Status",calculated_contact_status as "Calculated Contact Status",last_updated_at as "Last Updated At",npm_status as "NPM Status",node_id as "Node Id"
FROM  NPM_DEVICES
WHERE  --device_id > 20000000 and 
last_seen_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND
(Lower(name) LIKE Lower('%$Search%') or Lower(description) LIKE Lower('%$Search%') or Lower(collector) LIKE Lower('%$Search%')
or Lower(alias_name) LIKE Lower('%$Search%') or Lower(model) LIKE Lower('%$Search%') or Lower(device_types) LIKE Lower('%$Search%')
or ip_function_type LIKE '%$Search%' or Lower(display_name) LIKE Lower('%$Search%') or
Lower(display_description) LIKE Lower('%$Search%') or Lower(system_oid) LIKE Lower('%$Search%') or
Lower(location) LIKE Lower('%$Search%') or Lower(location_description) LIKE Lower('%$Search%') or
Lower(vendor) LIKE Lower('%$Search%') or Lower(life_cycle_state) LIKE Lower('%$Search%') or
Lower(contact_status) LIKE Lower('%$Search%') or Lower(state) LIKE Lower('%$Search%') or
Lower(city) LIKE Lower('%$Search%') or Lower(std_device_name) LIKE Lower('%$Search%') or
collector_alternate_id LIKE '%$Search%' or primary_ip_address LIKE '%$Search%'
or  Lower(host_name) LIKE Lower('%$Search%'))
LIMIT $resultlimit;

注：如果last_seen_at字段可能存在NULL值，上述条件会自动过滤掉无最后活跃时间记录的设备，符合你"last_seen_at有记录"的要求。

内容的提问来源于stack exchange，提问作者VishnuVardhan