MySql 将类似名称设置为 NULL

Question

我不太确定如何问这个问题，但这里是。

如果我有这样的查询:

SELECT `locations`.`location_name` AS 'Location', `inventory`.`equip_model` AS 'Model', count( `inventory`.`equip_model` ) AS 'Count'
FROM mod_inventory_data AS `inventory`
LEFT JOIN mod_locations_data AS `locations` ON `locations`.location_id = `inventory`.location_id
WHERE inventory.equip_model LIKE '%iPad%'
GROUP BY Location, Model

它返回如下结果:

Location        Model                    Count
------------------------------------------------
Bannach         iPad                     7
Bannach         iPad 2                   1
Ben Franklin    iPad                     3
Ben Franklin    iPad 2                   1
Ben Franklin    iPad 64 gb               3
Ben Franklin    iPad2 64GB               1
Jefferson       iPad                     4
Jefferson       Ipad 216 gb wi-fi        6
Jefferson       iPad2 64GB               2
Kennedy         iPad                     2
Kennedy         iPad 2 WIFI 16GB Black   1
Madison         iPad                     3
Madison         iPad 2                   6
McKinley        iPad                     4
McKinley        iPad 2                   2
McKinley        iPad 2 MC769LL/A         6
McKinley        iPad 64 gb               1

有没有办法替换重复的位置名称，使其看起来像这样:

Location        Model                    Count
------------------------------------------------
Bannach         iPad                     7
NULL            iPad 2                   1
Ben Franklin    iPad                     3
NULL            iPad 2                   1
NULL            iPad 64 gb               3
NULL            iPad2 64GB               1
Jefferson       iPad                     4
NULL            Ipad 216 gb wi-fi        6
NULL            iPad2 64GB               2
Kennedy         iPad                     2
NULL            iPad 2 WIFI 16GB Black   1
Madison         iPad                     3
NULL            iPad 2                   6
McKinley        iPad                     4
NULL            iPad 2                   2
NULL            iPad 2 MC769LL/A         6
NULL            iPad 64 gb               1

基本上，我希望位置名称仅显示在找到的第一个实例上，这意味着此后的任何 null 值都适用于该位置。

希望我问的是对的......

Answer 1

SELECT Location, Model, Count
FROM (
    SELECT IF(Location = @PrevLocation, NULL, Location) AS Location,
           Model, Count, @PrevLocation := Location
    FROM (<your query>) AS subq
    CROSS JOIN (SELECT @PrevLocation := NULL) AS init
    ) AS subq2