时间戳

首页 > 解决方案 > 在多对一临时表查询中选择名称与值相似且名称与其他值相似的值

sql - 在多对一临时表查询中选择名称与值相似且名称与其他值相似的值

问题描述

我正在尝试为资产跟踪工具构建一个新的搜索参数，并且正在使用两个临时表连接和查询，因为资产可能有多个我需要的软件，并且有条件的只会找到那些具有...值的软件它们附加到任何名称，但过滤掉那些不再匹配的潜在匹配：在这种情况下是软件。我知道我必须做错事，但我看不到它......下面是使用的代码生成稍后在搜索中使用的临时表...

CREATE TEMPORARY TABLE lookup_tbl_2 SELECT am_software.id,
            am_software.asset_name,
            am_software.sw_name,
            am_software.sw_key,
            am_software.sw_osver
        FROM am_software
        UNION ALL
        SELECT am_software_archive.id,
            am_software_archive.asset_name,
            am_software_archive.sw_name,
            am_software_archive.sw_key,
            am_software_archive.sw_osver
        FROM am_software_archive;

CREATE TEMPORARY TABLE lookup_tbl_1 SELECT am_assets.id,
            am_assets.asset_name,
            am_assets.asset_family,
            am_assets.asset_type,
            am_assets.asset_location,
            am_assets.asset_manufacturer,
            am_assets.asset_model,
            am_assets.asset_serial,
            am_assets.asset_status,
            am_assets.asset_retired_on,
            am_networks.connection_type,
            CASE WHEN am_networks.ipa_pointer = 1 THEN 'Dynamic' ELSE CONCAT_WS('.', am_ip_addresses.ip_address, am_networks.ip_address) END AS 'display_address'
        FROM am_assets
        JOIN am_networks ON am_assets.asset_name = am_networks.asset_name
        JOIN am_locations ON am_assets.asset_location = am_locations.id
        JOIN am_asset_family ON am_assets.asset_family = am_asset_family.id
        JOIN am_asset_type ON am_assets.asset_type = am_asset_type.id
        JOIN am_ip_addresses ON am_networks.ipa_pointer = am_ip_addresses.id
        JOIN am_connection_types ON am_networks.connection_type = am_connection_types.id
        UNION ALL
        SELECT am_asset_archive.id,
            am_asset_archive.asset_name,
            am_asset_archive.asset_family,
            am_asset_archive.asset_type,
            am_asset_archive.asset_location,
            am_asset_archive.asset_manufacturer,
            am_asset_archive.asset_model,
            am_asset_archive.asset_serial,
            am_asset_archive.asset_status,
            am_asset_archive.asset_retired_on,
            am_network_archive.connection_type,
            CASE WHEN am_network_archive.ipa_pointer = 1 THEN 'Dynamic' ELSE CONCAT_WS('.', am_ip_addresses.ip_address, am_network_archive.ip_address) END AS 'display_address'
        FROM am_asset_archive
        JOIN am_network_archive ON am_asset_archive.asset_name = am_network_archive.asset_name
        JOIN am_locations ON am_asset_archive.asset_location = am_locations.id
        JOIN am_asset_family ON am_asset_archive.asset_family = am_asset_family.id
        JOIN am_asset_type ON am_asset_archive.asset_type = am_asset_type.id
        JOIN am_ip_addresses ON am_network_archive.ipa_pointer = am_ip_addresses.id
        JOIN am_connection_types ON am_network_archive.connection_type = am_connection_types.id;

同样，目标是搜索临时表并将值返回给 ui；所以这就是我遇到麻烦的地方：

    SELECT lookup_tbl_1.asset_name as 'asset_name' 
    FROM lookup_tbl_1 
    JOIN lookup_tbl_2 
    ON lookup_tbl_1.asset_name = lookup_tbl_2.asset_name 
    WHERE lookup_tbl_2.sw_name LIKE 'Office 2010' AND lookup_tbl_2.sw_name LIKE 'Atom'
    AND lookup_tbl_1.asset_location = 5;

软件名称是文本，不必具有类似名称，但即使我使用：

    SELECT lookup_tbl_1.asset_name as 'asset_name' 
    FROM lookup_tbl_1 
    JOIN lookup_tbl_2 
    ON lookup_tbl_1.asset_name = lookup_tbl_2.asset_name 
    WHERE lookup_tbl_2.sw_name = 'Office 2010' 
    AND lookup_tbl_2.sw_name = 'Atom'
    AND lookup_tbl_1.asset_location = 5;

已知这些值与我试图显示资产名称的特定资产相关联，如果我删除其中一个软件名称条件（sw_name），它会起作用，但当有多个时不起作用......

我一直在寻找可行的解决方案，并尝试过类似的方法：

WHERE lookup_tbl_2.sw_name LIKE 'Office 2010' AND 'Atom'

WHERE lookup_tbl_2.sw_name LIKE 'Office 2010' 'Atom'

WHERE lookup_tbl_2.sw_name = 'Office 2010' AND 'Atom'

WHERE (find_in_set('Office 2010', lookup_tbl_2.sw_name)>0 AND find_in_set('Atom', lookup_tbl_2.sw_name)>0)

都返回相同的空结果，但删除第二个 sw_name 条件工作正常......

预期：应返回资产名称列表，例如“JWW90120”（应包含在预期列表中的实际资产名称值，因为它与所有条件匹配）。

实际：空结果。

标签： sqlmariadb

解决方案

我从一位同事那里得到了一些帮助，他在解决了一些错误后帮助我工作，我们终于让它工作了......

工作代码：

    SELECT lookup_tbl_1.asset_name FROM lookup_tbl_1 WHERE lookup_tbl_1.asset_location = 5 
        AND EXISTS (
            SELECT lookup_tbl_2.asset_name FROM lookup_tbl_2 WHERE
                lookup_tbl_1.asset_name = lookup_tbl_2.asset_name AND
                lookup_tbl_2.sw_name in ('Office 2010', 'Atom')
            );

这个问题已经解决了......希望它可以帮助其他可能在未来遇到这种情况的人。

推荐阅读