SQL：“First”的替代方案功能？
我正在尝试编写一个我不想使用笛卡尔积的查询。我打算使用第一个函数，因为某些 Type_Codes 有多个描述，而且我不想增加我的美元。
Select
Sum(A.Dollar) as Dollars,
A.Type_Code,
First(B.Type_Description) as FstTypeDescr
From
Totals A,
TypDesc B
Where
A.Type_Code = B.Type_Code
Group by A.Type_Code
我只想获取给定代码的任何描述（我并不关心是哪一个）。尝试使用 FIRST 时出现以下错误：
[IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "FIRST" of type "FUNCTION"
还有其他方法可以做到这一点吗？
I'm trying to write a query I don't want to have Cartesian products on. I was going to use the First function, because some Type_Codes have multiple descriptions, and I don't want to multiply my dollars.
Select
Sum(A.Dollar) as Dollars,
A.Type_Code,
First(B.Type_Description) as FstTypeDescr
From
Totals A,
TypDesc B
Where
A.Type_Code = B.Type_Code
Group by A.Type_Code
I just want to grab ANY of the descriptions for a given code (I don't really care which one). I get the following error when trying to use FIRST:
[IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "FIRST" of type "FUNCTION"
Is there another way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用
MIN()而不是First()。Instead of
First(), useMIN().first() 不是 SQL 标准。我忘记它适用于什么数据库产品，但它不适用于大多数 SQL 引擎。正如 Recursive 所指出的， min() 在这里为您的目的完成了同样的事情，区别在于，根据索引和查询的其他组件，它可能需要搜索许多记录才能找到最小值，在您的情况下 - - 以及我自己的许多 - 你真正想要的是任何比赛。我不知道任何标准的 SQL 方式来问这个问题。 SQL 似乎是由寻求集合论严格应用的数学家设计的，而不是由寻求尽可能快速有效地解决现实世界问题的实际计算机极客设计的。
first() is not SQL standard. I forget what database product it works in, but it's not in most SQL engines. As Recursive points out, min() accomplishes the same thing for your purposes here, the difference being that depending on indexes and other components of the query, it may require a search of many records to find the minimum value, when in your case -- and many of my own -- all you really want is ANY match. I don't know any standard SQL way to ask that question. SQL appears to have been designed by mathematicians seeking a rigorous application of set theory, rather than practical computer geeks seeking to solve real-world problems as quickly and efficiently as possible.
我忘记了此功能的实际名称，但您可以创建它，以便实际加入子查询。在该子查询中，您可以像 oxbow_lakes 建议的那样使用“top 1”，
例如：
I forget the actual name of this feature, but you can make it so you actually join to a subquery. in that subquery, you can do like oxbow_lakes suggests and use "top 1"
something like: