SQL — проблема с SQL-запросом; группируйтесь и присоединяйтесь

Предположим, у меня есть таблица с именем [ProductPriceHistory], например:

HistoryID..ProductCode..EffectDate.... Price.... IsActive...ProductName
1----------11-----------1 Jan 09-------100-------true-------AAA
2----------11-----------1 Feb 09-------150-------true-------AAA
3----------11-----------1 Mar 09-------200-------false------AAA
4----------22-----------1 Jan 09-------150-------true-------BBB
5----------22-----------1 Feb 09-------200-------true-------BBB
6----------22-----------1 Mr 09--------250-------true-------AAA

Как узнать окончательный статус всех активных продуктов на последнюю дату?

То есть мой запрос найдет строку:

6----------22-----------1 Mr 09--------250-------true-------AAA

sql grouping
person user366312    schedule 09.06.2009    source источник

Ответы (4)


arrow_upward
1
arrow_downward

чтобы получить значение данного кода продукта, используйте:

DECLARE @ProcuctCode  int
SET @ProductCode=11

SELECT
    h.* 
    FROM ProductPriceHistory h
        INNER JOIN (SELECT
                        ProductCode
                            ,MAX(EffectDate) AS MaxEffectDate
                        FROM ProductPriceHistory
                        WHERE ProductCode=@ProductCode
                            AND IsActive='true'
                        GROUP BY ProductCode
                   ) dt ON h.ProductCode=dt.ProductCode AND h.EffectDate=dt.MaxEffectDate
    WHERE h.ProductCode =@ProductCode

чтобы найти все продукты, используйте:

SELECT
    h.* 
    FROM ProductPriceHistory h
        INNER JOIN (SELECT
                        ProductCode
                            ,MAX(EffectDate) AS MaxEffectDate
                        FROM ProductPriceHistory
                        WHERE IsActive='true'
                        GROUP BY ProductCode
                   ) dt ON h.ProductCode=dt.ProductCode AND h.EffectDate=dt.MaxEffectDate
     ORDER BY h.ProductCode
person KM.    schedule 09.06.2009
comment
Выглядит хорошо, но вам нужно где-то добавить h.IsActive='true'. +1 - person wcm; 09.06.2009
comment
@wcm, производная таблица содержит IsActive='true', и затем она будет присоединена к соответствующей строке, поэтому больше ничего делать не нужно... - person KM.; 09.06.2009
comment
@KM, если вы предполагаете, что ProductCode и EffectiveDate уникальны, то вы правы. Это, вероятно, то, что имел в виду JMSA, поэтому я уступаю. - person wcm; 09.06.2009
comment
@wcm, в этом вопросе особо нечего вдаваться, мне просто нужно было сделать предположение. Если это не так, ОП может легко изменить это в соответствии со своими потребностями. - person KM.; 09.06.2009

arrow_upward
1
arrow_downward

вы не совсем указываете полностью - возможно, запрос @tekBlues - это то, что вам нужно, или, может быть:

SELECT * FROM ProductPriceHistory t1
WHERE t1.EffectDate =
  (SELECT MAX(t2.EffectDate)
   FROM ProductPriceHistory t2
   WHERE t2.IsActive=true)
  AND t1.IsActive=true
person Alex Martelli    schedule 09.06.2009

arrow_upward
0
arrow_downward

Предполагая, что ProductCode и EffectDate однозначно идентифицируют строку, вы можете сделать:

SELECT *
  FROM productpricehistory
     , (SELECT productcode
             , MAX(effectdate) effectdate
          FROM productpricehistory
         GROUP BY productcode) maxhistory
 WHERE productpricehistory.productcode = maxhistory.productcode
   AND productpricehistory.effectdate = maxhistory.effectdate
   AND IsActive = TRUE;

Если ProductCode и EffectDate не однозначно идентифицируют строку, вы, вероятно, захотите использовать HistoryId вместо EffectDate, если мы можем предположить, что HistoryId уникален и что увеличение HistoryId означает также увеличение EffectDate.

редактировать: я понимаю, что относился к активному иначе, чем вы, - я предполагал, что IsActive применяется только к конкретному EffectDate, но я вижу, что вы деактивируете весь продукт, установив для его «IsActive» значение false. Я обновил соответственно, предполагая, что впоследствии вы сможете активировать продукт, создав новую строку с IsActive = true.

person Steve Broberg    schedule 09.06.2009

arrow_upward

arrow_downward

person    schedule
comment
Вы забыли «IsActive = true»? - person ; 09.06.2009
comment
@jmsa - не нужно группировать, если все ссылочные столбцы (дата вступления в силу) передаются в агрегатную функцию (которой она и является). Это типичное решение для такого рода проблем, но обратите внимание, что если у вас нет уникального ограничения EffectDate, вы получите повторяющиеся строки. - person ahains; 09.06.2009