英文:
SQL Server Multiply Without Asterisk (*)
问题
我正在使用一个资产管理系统，允许我创建图表。我试图让一个SQL查询在其中工作，但显然他们不希望执行全选查询，所以他们设置了一个条件，阻止了select语句中的星号。然而，我想要将订单数量乘以订单金额，以提供一个显示每个预算花费多少钱的图表。
如何绕过他们的限制，将价格乘以数量的值添加到我的查询中？
select budget, price * qty as 'amount spent'
from orderline
英文:
I'm working in an asset management system that allows me to create charts. I'm attempting to get a SQL query to work in it, and apparently they don't want a select all query performed, so they have a condition that prevents the asterisk in the select statement. However, I want to multiply order quantity by the order amount to provide a chart showing how much money is spent per budget.
How can I bypass their stupidity to get that price times quantity value into my query?
select budget, price * qty as 'amount spent'
from orderline
答案1
得分: -1
我同意这个限制很荒谬。但如果您控制模式，您可以创建一个用户定义的标量函数来执行它。
CREATE FUNCTION MULTIPLY
(
@param1 AS FLOAT,
@param2 AS FLOAT
)
RETURNS FLOAT
WITH SCHEMABINDING
AS
BEGIN
RETURN @param1 * @param2
END
然后：
SELECT budget, dbo.MULTIPLY(price,qty) as 'amount spent' FROM orderline
英文:
I agree this limitation is absurd. If you have control of the schema though, you could create a user-defined scalar function to do it.
CREATE FUNCTION MULTIPLY
(
@param1 AS FLOAT,
@param2 AS FLOAT
)
RETURNS FLOAT
WITH SCHEMABINDING
AS
BEGIN
RETURN @param1 * @param2
END
then:
SELECT budget, dbo.MULTIPLY(price,qty) as 'amount spent' FROM orderline
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论