如何在sql server 2008的别名列中找到最大值？ [英] How to find max value in a alias column in sql server 2008?

问题描述

您好，

如何在sql server 2008的别名列中找到最大值？



我的查询是： -

Hello,
How to find max value in a alias column in sql server 2008?

My query is:-

SELECT
LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO FROM CurrentConfiguration1 where [Product Name] like '%Customized%'

我的结果是： -

My Result is:-

ProductName                        CreationSQNO
Customized Kreation Wardrobe       1
Customized Kreation Wardrobe       2
Customized Kreation Wardrobe       6
Customized Kreation Wardrobe       4
Customized Kreation Wardrobe       n

现在我想在CreationSQNO栏中找到最高或最高价值。

那么，它怎么可能？



请帮帮我。



先谢谢。



Ankit Agarwal

软件工程师

Now i want to find highest or max value in CreationSQNO column.
So,How it can be possible?

Please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

推荐答案

让我展示小技巧。



1.您编写的每个SQL Select查询都可以转换为子查询。您需要做的就是将其括在括号中，并可选择指定别名。然后你可以从这个子查询中选择：

Let me show small tricks.

1. Every SQL Select query you write can be turned into a sub-query. All you need to do is to enclose it into brackets and optionally assign an alias. Then you can just select from this sub-query:

SELECT ProductName, Max(CreationSQNO) FROM
(
    LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
    RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO 
    FROM CurrentConfiguration1 
    where [Product Name] like '%Customized%'
) AS a
GROUP BY ProductName;

2.你写的每个查询都可以变成CTE - 公用表表达式：

2. Every query you write can be turned into a CTE - Common Table Expression:

;WITH a AS
(
    LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
    RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO 
    FROM CurrentConfiguration1 
    where [Product Name] like '%Customized%'
) 
SELECT ProductName, Max(CreationSQNO) 
FROM a
GROUP BY ProductName;

3.你可以直接在查询中使用MAX（）：

3. You can use MAX() directly in your query:

SELECT
LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
MAX(RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1)) AS MaxCreationSQNO FROM CurrentConfiguration1 where [Product Name] like '%Customized%'
GROUP BY LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name])))

注意：如果你需要所有产品的最大值然后从选择列表中删除GROUP BY子句和ProductName列，如下所示：

Note: if you need the max value of all Products then remove GROUP BY clause and the column ProductName from the select list, like this:

;WITH a AS
(
    LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
    RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO 
    FROM CurrentConfiguration1 
    where [Product Name] like '%Customized%'
) 
SELECT Max(CreationSQNO) 
FROM a;

这篇关于如何在sql server 2008的别名列中找到最大值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！