SQL Max函数可在其他表中使用相关字段检索一个记录

Question

在我们的查询工具（使用ODBC）4.1我正在尝试获取最大Inv_shipment_date和相应的Inv_bill_Weight，product_id和division_cd。我有4个表必须从中获取数据（必须使用1个来加入另一个）。我很难编写SQL将结果降低到一条线，显示最大发货日期。

表格和所需的字段：

fos：package_keyinv_shipment_dateINV_BILLED_WAEIGHTdistion_key

fif：package_keyproduct_key

PD：产品密钥product_id

dd：distrive_keydistion_cd

最终希望在表中获得的结果max（inv_shipment_date）INV_BILLED_WAEIGHTproduct_iddistion_cd

我写的是空白的数据（我知道最大发货日期实际上是20170112，计费重量为20，difiss is dol，而产品ID为03-01984-001）

我在做什么错？感谢您的帮助微笑。gif

SELECT 
max(fos.inv_shipment_date_key), 
dd.division_cd, 
pd.product_id,
max(fos.inv_billed_weight)

FROM DCDM.FREIGHT_OUT_SUMMARY fos,
    (select max(inv_shipment_date_key) as Maxinvdate, inv_billed_weight
    from dcdm.freight_out_summary
    group by inv_billed_weight) maxdate,
dcdm.division_dim dd,
dcdm.product_dim pd,
dcdm.freight_invoice_fact fif
where fos.inv_billed_weight = maxdate.inv_billed_weight
and fos.inv_shipment_date_key = maxdate.maxinvdate
and fos.package_key = fif.package_key
and fif.product_key = pd.product_key
and fos.division_key = dd.division_key
and pd.product_id = '03-01984-001'
group by
fos.inv_shipment_date_key, 
dd.division_cd, 
pd.product_id,
fos.inv_billed_weight

Answer 1

如果我正确理解您的问题，您将需要这样的人。如果您在最后一行删除AND条件，则将获得所有product id的结果。我建议您删除最后的AND条件，然后运行它以查看您是否获得了期望的结果。但是没有示例数据，真的很难猜测。

SELECT *
FROM
  (SELECT Package_key,
          Inv_Billed_Weight,
          Division_Key,
          row_Number() over (partition BY Division_Key
                             ORDER BY Inv_Shipment_Date DESC) AS rn
   FROM DCDM.FREIGHT_OUT_SUMMARY) fos
INNER JOIN dcdm.division_dim dd 
    ON fos.division_key = dd.division_key
INNER JOIN dcdm.freight_invoice_fact fif 
    ON fos.package_key = fif.package_key
INNER JOIN dcdm.product_dim pd 
    ON fif.product_key = pd.product_key
WHERE fos.rn=1
    AND pd.product_id = '03-01984-001'

Answer 2

我假设您想要按产品运输日期，这是正确的吗？如果是这样，我建议最初在这里使用子问题，然后加入其他表（或者您可以先加入，但这似乎不必要）。当然，这取决于您使用的标识符 - 如果您有一个理想的"装运ID"。例如：

select
Max(table1.Inv_shipment_date) over (partition by table2.product_ID) as max_shipment_date,
table1.SHIPMENT_ID,
table2.product_Id,
table3.division_Cd,
table4.inv_billed_weight
from
(select
SHIPMENT_ID,
inv_shipment_date
from table 1) as table1
left join
(select
SHIPMENT_ID,
product_ID
from table 2) as table2
on table 1.SHIPMENT_ID = table2.SHIPMENT_ID 
left join
(select
SHIPMENT_ID,
division_Cd
from table 3) as table3
on table 1.SHIPMENT_ID = table3.SHIPMENT_ID
left join
(select
SHIPMENT_ID,
inv_billed_weight
from table 4) as table4
on table 1.SHIPMENT_ID = table4.SHIPMENT_ID