使用Min Max编写SQL查询

I have a query that provides a list of transactions by item, date, time, Qty.

我有一个查询，按项目，日期，时间，数量提供交易列表。

SELECT item_no, trx_dt, trx_tm, quantity 
FROM dbo.Item_Trx_Table 
WHERE item='Item_1' AND [doc_type]<>'w' AND transDate='2016-08-22 00:00:00.000'

I need help figuring out how to get the Qty on Min(trx_tm) and Max(trx_tm) for each Item, and Date.

我需要帮助弄清楚如何获得每个项目和日期的最小数量（trx_tm）和最大值（trx_tm）。

Any guidance would be appreciated.

任何指导将不胜感激。

1 个解决方案

#1

It's a bit unclear what you desire, but I think you want to for example get the MAX(trx_tm) for each Item, and find the Qty? If so, use GROUP BY Item and select MAX or MIN of Item or Date. Then to also get the Qty do a inner join. I would make four separate queries (MAX Item, Min Item, MAX Date, MIN Date).

有点不清楚你想要什么，但我想你想要为每个项目得到MAX（trx_tm），并找到数量？如果是这样，请使用GROUP BY Item并选择Item或Date的MAX或MIN。然后还要让Qty做一个内连接。我将进行四个单独的查询（MAX项，最小项，最大日期，最小日期）。

Here is the first one: to get the MAX(trx_tm) for each Item, and find the Qty:

这是第一个：获取每个Item的MAX（trx_tm），并找到Qty：

SELECT grp.Item, grp.max_trx_tm, tbl.Qty
(
    SELECT Item, MAX(trx_tm) AS max_trx_tm
    FROM dbo.Item_Trx_Table
    GROUP BY Item 
) AS grp
INNER JOIN dbo.Item_Trx_Table tbl ON (tbl.Item = grp.Item AND tbl.trx_tm = grp.max_trx_tm)
WHERE tbl.Item='Item_1' AND tbl.doc_type <> 'w' AND tbl.transDate='2016-08-22 00:00:00.000'