加入聚合功能

发布于 2025-02-08 05:20:09 字数 2332 浏览 1 评论 0原文

我有一个表显示生产订单（Orderid）的生产步骤（POSID），以及将运行哪台机器（Machid）；我正在尝试减少表格，以显示每个顺序的一个记录 - 仍然打开的最低位置（字段“ posID”）（字段“打开” = y）;即该订单的下一个生产步骤。

数据

orderid	posid	Machid	Open
1	1	A	N
1	2	B	y
1	3	C	y
2	4	C	y
2	5	d	y
2	6	e	y

结果

我	：	：
想要	我	y
2	示例	示例

有尝试了两种方法，但我似乎无法工作：

我不想将“ Machid”放在小组中，因为这给了我所有开放的记录，但我也不是认为“ Machid”字段具有适当的汇总功能来使这项工作。
```
 选择“ OrderId”，Min（“ PosID”），“ Machid”
 从表T0
 其中“打开” ='y'
 组成的“有序”
 
```

使用这种方法，我不断收到T1的错误消息。我还尝试了t1.min（“ posid”）和min（t1。“ posid”）。

 选择T0。“ OrderId”，T0。“ PosID”，T0。“ MACHID”
 从表T0
 加入
     （选择“ OrderId”，Min（“ POSID”）
     从桌子
     其中“打开” ='y'
     小组由“ Orderid”）T1
 在T0上。“ orderid” = T1。“ OrderId”
 和t0。“ posid” = t1。“ posid”

原文

I have a table showing production steps (PosID) for a production order (OrderID) and which machine (MachID) they will be run on; I’m trying to reduce the table to show one record for each order – the lowest position (field “PosID”) that is still open (field “Open” = Y); i.e. the next production step for the order.

Example data I have:

OrderID	PosID	MachID	Open
1	1	A	N
1	2	B	Y
1	3	C	Y
2	4	C	Y
2	5	D	Y
2	6	E	Y

Example result I want:

OrderID	PosID	MachID
1	2	B
2	4	C

I’ve tried two approaches, but I can’t seem to get either to work:

I don’t want to put “MachID” in the GROUP BY because that gives me all the records that are open, but I also don’t think there is an appropriate aggregate function for the “MachID” field to make this work.
```
 SELECT “OrderID”, MIN(“PosID”), “MachID”
 FROM Table T0
 WHERE “Open” = ‘Y’
 GROUP BY “OrderID”
```

With this approach, I keep getting error messages that T1.”PosID” (in the JOIN clause) is an invalid column. I’ve also tried T1.MIN(“PosID”) and MIN(T1.”PosID”).

 SELECT T0.“OrderID”, T0.“PosID”, T0.“MachID”
 FROM Table T0
 JOIN
     (SELECT “OrderID”, MIN(“PosID”)
     FROM Table
     WHERE “Open” = ‘Y’
     GROUP BY “OrderID”) T1
 ON T0.”OrderID” = T1.”OrderID”
 AND T0.”PosID” = T1.”PosID”

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟花易冷人易散 2025-02-15 05:20:09

尝试以下操作：

SELECT “OrderID”,“PosID”,“MachID” FROM (
SELECT 
T0.“OrderID”, 
T0.“PosID”, 
T0.“MachID”,
ROW_NUMBER() OVER (PARTITION BY “OrderID” ORDER BY “PosID”) RNK
FROM Table T0
WHERE “Open” = ‘Y’
) AS A 
WHERE RNK = 1

在选择列在上面的问题中所写的列时，我包含了括号，但通常不需要。

它的作用是首先过滤打开顺序，然后将秩序从1到x将posid OrderId

posid	Machid	Open	RNK	1
2	B	y	1	1
1	3	C	y	2
2	4	C y 1 2 4 C	y	1
2	5	D	y 2	y 2 y 2 y 2 y 2
y 2	6	e	y	3

过滤在“ RNK”列上，指示每个订单最低的POSID。 Select子句中的Row_number（）称为窗口函数，还有更多非常有用的。

PS上述解决方案应适用于MSSQL

Try this:

SELECT “OrderID”,“PosID”,“MachID” FROM (
SELECT 
T0.“OrderID”, 
T0.“PosID”, 
T0.“MachID”,
ROW_NUMBER() OVER (PARTITION BY “OrderID” ORDER BY “PosID”) RNK
FROM Table T0
WHERE “Open” = ‘Y’
) AS A 
WHERE RNK = 1

I've included the brackets when selecting columns as you've written it in the question above but in general it's not needed.

What it does is it first filters open OrderIDs and then numbers the OrderIDs from 1 to X which are ordered by PosID

OrderID	PosID	MachID	Open	RNK
1	2	B	Y	1
1	3	C	Y	2
2	4	C	Y	1
2	5	D	Y	2
2	6	E	Y	3

After it filters on the "rnk" column indicating the lowest PosID per OrderID. ROW_NUMBER() in the select clause is called a window function and there are many more which are quite useful.

P.S. Above solution should work for MSSQL

回复收藏 0 原文

~没有更多了~