让我展示小技巧。



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;