我想我需要自我加入？

Question

I have a table with the following structure:

id, Metal, AmPm, GBP, USD, EUR, Updated, TimeStamp

Sample values include:

INSERT INTO `LondonFixes` (`id`, `Metal`, `AmPm`, `GBP`, `USD`, `EUR`, `Updated`, `TimeStamp`) VALUES
(228, 'Gold', 'AM', '1779.22000', '2748.00000', '2253.20000', '2012-07-13', '2012-07-13 15:19:35'),
(224, 'Gold', 'PM', '1022.33700', '1579.00000', '1294.05000', '2012-07-13', '2012-07-13 13:16:59'),

I have queried the data and am able to get the last 10 rows and order them how I want as follows:

    $r = mysql_query("SELECT * FROM(SELECT id, GBP, AmPm, Updated FROM LondonFixes WHERE Metal = 'Gold' AND UPDATED < now() ORDER BY id DESC LIMIT 10) AS src ORDER BY id ASC");

This returns a table of the form:

id, GBP, AmPm, Updated

The problem is that I need a table of the form:

id, GBP_AM, GBP_PM, Updated

Basically, AmPm values can either contain the values "AM" or "PM". I want to show the GBP value for both AM and PM on one line where updated is the same on both lines.

e.g. Required output based on above sample data:

1, 1779.22000, 1022.33700, 2012-07-13

I think that this is a self Join, but I just don't understand self joins and I am not even sure that Self Joins is what I need.

Answer 1

You don't need to incur the overhead of a self-join. Instead, simply do it with conditional checking:

SELECT a.*
FROM
(
      SELECT MAX(CASE AmPm WHEN 'AM' THEN GBP END) AS GBP_AM,
             MAX(CASE AmPm WHEN 'PM' THEN GBP END) AS GBP_PM,
             Updated
        FROM LondonFixes
       WHERE Updated < CURDATE() AND Metal = 'Gold'
    GROUP BY Updated
    ORDER BY Updated DESC
       LIMIT 10
) a
ORDER BY a.Updated

This is assuming the id field is not important to you. I'm excluding it in my solution.