SQL JOIN表，其中有多个相同id的实例

Question

I have two tables within the same database; tableA has the date I need while tableB has the cost. These tables are like an archive of changing dates and costs so a single item is repeated multiple times but I only want the most recent date and lowest cost.

Data example:

tableA

  LocalSKU: aaa-aaa1 date: 12/1/1
  LocalSKU: aaa-aaa1 date: 11/1/3
  LocalSKU: aaa-aaa1 date: 10/2/1

tableB

  SKU: aaa-aaa1 cost: 0.15
  SKU: aaa-aaa1 cost: 5
  SKU: aaa-aaa1 cost: 0

Desired result:

SKU: aaa-aaa1 date: 12/1/1 cost: 0

I've tried a simple query to pull back a single result with this query:

SELECT MAX(date) 
FROM tableA 
WHERE LocalSKU = (SELECT MIN(cost) 
                  FROM tableB 
                  WHERE SKU = tableB.LocalSKU GROUP BY SKU);

which yielded 0 results.

I'm new to subquerying and joining, is there a single query that can be made to get the desired result? I'm able to get the information I want in separate queries, but I got stuck trying to merge the arrays to combine the desired information.

Help is much appreciated!

Answer 1

Join the tables and use the aggregation function on the result.

SELECT a.localsku, MIN(cost), MAX(date)
FROM tableA
JOIN tableB ON a.localsku = b.sku
GROUP BY a.localsku

Actually, it will probably be more performant to do the grouping first and then join the subqueries, since joining large tables is expensive.

SELECT a.localsku, a.date, b.cost
FROM (
    SELECT localsku, MAX(date) AS date
    FROM tableA
    GROUP BY localsku) AS a
JOIN (
    SELECT sku, MIN(cost) as cost
    FROM tableB
    GROUP BY sku) AS b
ON a.localsku = b.sku