问题描述:

I have two different tables to track location of equipment. The "equipment" table tracks the current location and when it was installed there. If the equipment was previously at a different location, that information is kept in the "locationHistory" table. There is one row per equip_id in the equipment table. There can be 0 or more entries for each equip_id in the locationHistory table.

equipment

equip_id

current_location

install_date_at_location

locationHistory

equip_id

location

install_date

pickup_date

I want an SQL query that gets the date of the FIRST install_date for each piece of eqipment...

Example:

equipment

=========

equip_id | current_location | install_date_at_location

123 location1 1/23/2011

locationHistory

===============

equip_id | location | install_date | pickup_date

123 location2 1/1/2011 1/5/2011

123 location3 1/7/2011 1/20/2011

Should return: 123, 1/1/2011

Thoughts?

网友答案:

You will want to union the queries that each look at one field, then use a MIN against it. Or you can use the CASE and MIN for the same effect

select e.equip_id, MIN(CASE WHEN h.install_date < e.install_date_at_location
     THEN h.install_date
     ELSE e.install_date_at_location
 END)  as first_install_date
from equipment e 
left join locationHistory h on h.equip_id = e.equip_id
group by e.equip_id
网友答案:

Well, the critical piece of information is whether the install_at_location_date in equipment can ever be less than what I assume is the historical information in locationHistory. If that's not possible, you can do:

SELECT * FROM locationHistory L INNER JOIN
   (SELECT equip_id, MIN(install_date) AS firstDate FROM locationHistory)
     AS firstInstalls F
    ON L.equip_id = F.equip_id AND L.install_date = F.firstDate

But if you have to worry about both tables, you need to create view that normalizes the tables for you, and then apply the query against the view:

CREATE VIEW normalLocations (equip_id, location, install_date) AS
   SELECT equip_id, location, install_date_at_location FROM equipment
   UNION ALL
   SELECT equip_id, location, install_date FROM equipment;

SELECT * FROM normalLocations L INNER JOIN
   (SELECT equip_id, MIN(install_date) AS firstDate FROM normalLocations)
      AS firstInstalls F
   ON L.equip_id = F.equip_id AND L.install_date = F.firstDate
网友答案:

A simple way to do it is:

SELECT U.Equip_ID, MIN(U.Install_Date)
  FROM (SELECT E.Equip_ID, E.Install_Date_At_Location AS Install_Date
          FROM Equipment AS E
        UNION
        SELECT L.Equip_ID, L.Install_Date
          FROM LocationHistory AS L
       ) AS U
 GROUP BY U.Equip_ID

This could generate a lot of rows from the LocationHistory table, but it isn't clear that it is worth 'optimizing' it by trying to apply a GROUP BY and MIN to the second half of the UNION (because you'd immediately redo the grouping with the result from the information in the equipment table).

相关阅读:
Top