当前位置： 文江博客话题详情

如何将 Group By 应用于此 SQL 查询

发布于 2024-08-04 18:52:34 字数 3314 浏览 3 评论 0原文

我正在尝试列出要盘点的库存清单，前提是该商品在过去 2 个月内有过销售： 我正在使用 Pervasive SQL，它是一个 BusinessVision 表。 此查询有效，但我不知道如何聚合以显示一项：

SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND",
"SALES_HISTORY_HEADER"."IN_DATE"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
    where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'

好的。此查询将为我提供具有不同日期的零件编号的重复结果。我只想让每个零件号出现一次，前提是它是在过去 2 个月内售出的。 例如：

A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090810 
A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090811 
A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090812

我更喜欢简单地这样设置：

A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000

在下一行显示下一个产品。 我怎样才能做到这一点？

一些答案后编辑的部分： 感谢您关于 DISTINCT 的建议。 我不知道为什么它会重复结果。查看查询结果（部分）（不包括“SALES_HISTORY_HEADER”。“IN_DATE”：

CODE           INV_DESCRIPTION                 BVSTKUOM   INV_COMMITTED   ONHAND    
--------------------------------------------------------- --------------- ----------
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         180.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000

当使用 GROUP BY 或 DISTINCT 时，我得到这些结果：

CODE           INV_DESCRIPTION                 BVSTKUOM   INV_COMMITTED   ONHAND    
--------------------------------------------------------- --------------- ----------
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         50.00000  
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         180.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         0.00000   
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000

我期望只有 2 行，即 180 和 915 的行。

原文

I'm trying to make a list of inventory to be counted, provided the item has had a sale in the last 2 months:
I'm using Pervasive SQL and it's a BusinessVision table.
This query works, but I don't know how to aggregate to have one item displayed:

SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND",
"SALES_HISTORY_HEADER"."IN_DATE"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
    where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'

Ok. This query would give me repeated results of a part number, with different dates. I just want to have one occurrence for each part number, provided it as sold in the last 2 months.
For example:

A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090810 
A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090811 
A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090812

I would prefer simply have it like this:

A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000

and on the next row the next product.
How can I do that?

EDITED PART AFTER SOME ANSWERS:
Thanks for the suggestion about DISTINCT.
I don't know why it repeats results yet. See (part of) the result of the query (not including "SALES_HISTORY_HEADER"."IN_DATE":

CODE           INV_DESCRIPTION                 BVSTKUOM   INV_COMMITTED   ONHAND    
--------------------------------------------------------- --------------- ----------
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         180.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000

And when using the GROUP BY, or DISTINCT I get these results:

CODE           INV_DESCRIPTION                 BVSTKUOM   INV_COMMITTED   ONHAND    
--------------------------------------------------------- --------------- ----------
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         50.00000  
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         180.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         0.00000   
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000

I would expect just 2 rows, the ones with 180 and 915.

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

扫码二维码加入Web技术交流群

发布评论

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

评论（4）

掩饰不了的爱 2024-08-11 18:52:34

我不了解 Pervasive SQL，但通常您必须按要分组的所有列进行分组，在本例中是所有列（“SALES_HISTORY_HEADER”除外。“IN_DATE”，它不在您想要的输出中）

SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'
group by "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"

I don't know about Pervasive SQL, but normally you have to group by all the columns you want grouped, in this case that's all the columns (except "SALES_HISTORY_HEADER"."IN_DATE", which isn't in your desired output)

SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'
group by "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
回复 原文
一场春暖 2024-08-11 18:52:34

所以普适控制中心（8.70.014.000）没有解释计划或任何看起来像分析的东西，所以我不能超越个人的看法。但对我来说，GROUP BY 感觉更快一点：

    SELECT t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date
      FROM "INVENTORY" t
INNER JOIN "SALES_HISTORY_DETAIL" shd ON shd.code = t.code
INNER JOIN "SALES_HISTORY_HEADER" shh ON shh.number = shd.number
  GROUP BY t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date

请注意，Pervasive 要求您包含所有未执行聚合函数的列。

使用 DISTINCT 的版本：

    SELECT DISTINCT t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date
      FROM "INVENTORY" t
INNER JOIN "SALES_HISTORY_DETAIL" shd ON shd.code = t.code
INNER JOIN "SALES_HISTORY_HEADER" shh ON shh.number = shd.number

您正在处理什么版本的 BV/Pervasive？如果需要的话我可以针对 v6/2000i 进行测试。

So the Pervasive Control Center (8.70.014.000) doesn't have an explain plan or anything that looks like profiling, so I can't speak beyond personal perception. But to me, the GROUP BY feels a tad faster:

    SELECT t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date
      FROM "INVENTORY" t
INNER JOIN "SALES_HISTORY_DETAIL" shd ON shd.code = t.code
INNER JOIN "SALES_HISTORY_HEADER" shh ON shh.number = shd.number
  GROUP BY t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date

Mind that Pervasive requires you to include all columns that don't have aggregate functions performed.

Version using DISTINCT:

    SELECT DISTINCT t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date
      FROM "INVENTORY" t
INNER JOIN "SALES_HISTORY_DETAIL" shd ON shd.code = t.code
INNER JOIN "SALES_HISTORY_HEADER" shh ON shh.number = shd.number

What version of BV/Pervasive are you dealing with? I can test against v6/2000i if need be.

回复 原文
握住我的手 2024-08-11 18:52:34

您确定您不是在寻找 SELECT DISTINCT（如果存在重复项，它会返回单行）吗？如果您从 SELECT 中排除 IN_DATE，我认为这就是您想要的。

SELECT DISTINCT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
    where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'

Are you sure you're not looking for SELECT DISTINCT, which returns a single row if there are duplicates? If you exclude the IN_DATE from your SELECT, I think this is what you want.

SELECT DISTINCT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
    where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'
回复 原文
慕巷 2024-08-11 18:52:34

好吧，原来重复的问题是因为我忘记过滤掉仓库了。我应该在 where 子句中添加以下内容：

WHERE "INVENTORY"."WHSE" = '00'

这解决了问题

Well, it turns out that the problem with duplicates was because I forgot to filter out warehouses. I should have added in the where clause this:

WHERE "INVENTORY"."WHSE" = '00'

This took care of the problem

回复 原文
~没有更多了~

关于作者

疏忽

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

烙印

文章 0 评论 0

singlesman

文章 0 评论 0

给自己一个微笑

文章 0 评论 0

独孤求败

文章 0 评论 0

晨钟暮鼓

文章 0 评论 0

我是自愿种绣球花的

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文