如何从表中获取最大行数

发布于 2024-08-29 12:12:56 字数 1856 浏览 3 评论 0原文

您好，

我有以下代码和一个大问题：

WITH CALC1 AS (
SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM @[email protected] WHERE OTIT01 <> ''

UNION ALL

SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM @[email protected] WHERE OTIT02 <> ''

UNION ALL

SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM @[email protected] WHERE OTIT03 <> ''

UNION ALL

SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM @[email protected] WHERE OTIT04 <> ''

UNION ALL

SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM @[email protected] WHERE OTIT05 <> ''

ORDER BY OTQUOT ASC
)

SELECT OTQUOT, ITEMS, MAX(COST)
FROM CALC1
WHERE OTQUOT = '04886471'
GROUP BY OTQUOT, ITEMS

结果：

 04886471 FEPO5050WCGA24 13.21
 04886471 GFRK1650SGL 36.21
 04886471 FRA7500GA 12.6
 04886471 CGIFESHAZ 11.02
 04886471 CGIFESHPDPR 11.79
 04886471 GFRK1350DBL 68.23
 04886471 RET1.63825GP 32.55
 04886471 FRSA 0.12
 04886471 GFRK1350SGL 55.94
 04886471 GFRK1650DBL 71.89
 04886471 FEPO6565WCGA24 16.6
 04886471 PCAP5050GA 0.28
 04886471 FEPO6565NCPAG24 0.000000

如何获得具有最高值的 Itemcode 的行的结果？在这种情况下我需要结果： 04886471 GFRK1650DBL 71.89 但我不知道如何更改我的代码来实现这一点 - 有人可以帮助我吗？

原文

I have the following code and a massive problem:

WITH CALC1 AS (
SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM @[email protected] WHERE OTIT01 <> ''

UNION ALL

SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM @[email protected] WHERE OTIT02 <> ''

UNION ALL

SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM @[email protected] WHERE OTIT03 <> ''

UNION ALL

SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM @[email protected] WHERE OTIT04 <> ''

UNION ALL

SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM @[email protected] WHERE OTIT05 <> ''

ORDER BY OTQUOT ASC
)

SELECT OTQUOT, ITEMS, MAX(COST)
FROM CALC1
WHERE OTQUOT = '04886471'
GROUP BY OTQUOT, ITEMS

result:

 04886471 FEPO5050WCGA24 13.21
 04886471 GFRK1650SGL 36.21
 04886471 FRA7500GA 12.6
 04886471 CGIFESHAZ 11.02
 04886471 CGIFESHPDPR 11.79
 04886471 GFRK1350DBL 68.23
 04886471 RET1.63825GP 32.55
 04886471 FRSA 0.12
 04886471 GFRK1350SGL 55.94
 04886471 GFRK1650DBL 71.89
 04886471 FEPO6565WCGA24 16.6
 04886471 PCAP5050GA 0.28
 04886471 FEPO6565NCPAG24 0.000000

How can I get the result of the row with the Itemcode that has the highest value?
In this case I need the result:
04886471 GFRK1650DBL 71.89
but i dont know how to change my code to get that - can anybody please help me?

分享到QQ

分享到微博

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

发布评论

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

一梦等七年七年为一梦 2024-09-05 12:12:56

使用ROW_NUMBER() 窗口函数提供选择顶行的方法。请注意，您需要将其放入派生子查询中，因为 WHERE 子句无法引用查询自己的行号。您必须使用外部查询来做到这一点：

WITH ( ...your UNION query... )
SELECT *
FROM (
  SELECT OTQUOT, ITEMS, COST, ROW_NUMBER() OVER (ORDER BY COST DESC) AS RN
  FROM CALC1
  WHERE OTQUOT = '04886471'
) T
WHERE T.RN = 1;

此外，我不会为 UNION 查询中的 ORDER BY 烦恼。

Use the ROW_NUMBER() windowing function to provide a means to select the top row. Note that you need to put it into a derived subquery because a WHERE clause cannot reference the query's own row number. You have to do that with an outside query:

WITH ( ...your UNION query... )
SELECT *
FROM (
  SELECT OTQUOT, ITEMS, COST, ROW_NUMBER() OVER (ORDER BY COST DESC) AS RN
  FROM CALC1
  WHERE OTQUOT = '04886471'
) T
WHERE T.RN = 1;

Also I wouldn't bother with the ORDER BY inside your UNION query.

回复收藏 0 原文