In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
SQL Prompt is a practical SQL grammar prompt tool. SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency.
Writing GROUP BY clauses is a very common but tedious activity for SQL programmers. This article describes that SQL Prompt will populate it for you, and the query returns all non-aggregate columns.
"have you seen this new feature of automatically populating GROUP BY with non-aggregate columns?" My colleague exclaimed shortly after installing a new version of SSMS. I'm not, but I'm curious. I've written thousands of summary queries in my career, so it sounds like it'll save me some time.
I searched SSMS for the virtual machine I used to test the new version of SQL Server, but I couldn't find it. It turns out that my test virtual machine does not have SQL Prompt installed, after all, this is not a new SSMS feature, but one of those "hidden" SQL Prompt enhancements.
Manually fill in the GROUP BY
Suppose I need to write a query on the WideWorldImporters database to get the sum of the items ordered, grouped by zip code and the year in which the customer account was opened.
In a typical way, I first fill in the form and its connection conditions. I type "or" to find the tables related to the order without even knowing that they exist in the Sales schema.
I extended the SELECT list to initially return only the basic lines I needed to use. I apply my preferred prompt format style, and the query looks like this:
SELECT Customers.DeliveryPostalCode AS PostalCode, YEAR (Customers.AccountOpenedDate) AS AccountOpenYear, OrderLines.Quantity FROM Sales.OrderLines JOIN Sales.Orders ON Orders.OrderID = OrderLines.OrderID JOIN Sales.Customers ON Customers.CustomerID = Orders.CustomerID
The query returns hundreds of thousands of rows, and a small number of samples are as follows:
PostalCode AccountOpenYear Quantity-90398 2013 7 90005 2013 7 90313 2013 7 90434 2013 7
I want to check the total order quantity by customer delivery zip code and the year in which the customer opened the account, so the final SELECT list is as follows:
SELECT Customers.DeliveryPostalCode AS PostalCode, YEAR (Customers.AccountOpenedDate) AS AccountOpenYear, SUM (OrderLines.Quantity) AS TotalOrderQuantity
This is just a GROUP BY clause. Usually, I just copy and paste the non-aggregate column in the SELECT clause and replace SELECT with GROUP BY:
GROUP BY Customers.DeliveryPostalCode AS PostalCode, YEAR (Customers.AccountOpenedDate) AS AccountOpenYear
Finally, I just deleted the alias to give this final query:
SELECT Customers.DeliveryPostalCode AS PostalCode, YEAR (Customers.AccountOpenedDate) AS AccountOpenYear, OrderLines.Quantity FROM Sales.OrderLines JOIN Sales.Orders ON Orders.OrderID = OrderLines.OrderID JOIN Sales.Customers ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.DeliveryPostalCode, YEAR (Customers.AccountOpenedDate
All in all, this is a tedious process.
Use the SQL prompt to automatically populate GROUP BY
Like all clauses in the SQL statement, SQL Prompt helps fill in GROUP BY;. I haven't noticed it before! Simply press the Spacebar after typing the clause of GROUP BY, as shown in the figure.
Select all non-aggregate columns, which will populate the GROUP BY clause with the following expression:
GROUP BY YEAR (Customers.AccountOpenedDate), Customers.DeliveryPostalCode
Alternatively, as shown in the above figure, you can select non-aggregate columns separately. If you want to add additional grouping, such as by the month the customer opened the account, simply add the MONTH (Customers.AccountOpenedDate) AS AccountOpenMonth to the select clause, delete the existing GROUP BY clause, and then automatically refill the column expression. The GROUP BY clause now looks like this:
GROUP BY YEAR (Customers.AccountOpenedDate), MONTH (Customers.AccountOpenedDate), Customers.DeliveryPostalCode
In some cases, the value in your SELECT clause may be in the aggregate function, but not in the GROUP BY clause. For example, suppose we change the SELECT query to include two new columns, highlighted in bold text in the following code. One is the text value, and the other is to calculate the GROUP BY for the columns in the existing column:
SELECT Customers.DeliveryPostalCode AS PostalCode, YEAR (Customers.AccountOpenedDate) AS AccountOpenYear, MONTH (Customers.AccountOpenedDate) AS AccountOpenMonth, 1 AS Value, YEAR (Customers.AccountOpenedDate)-1 AS PreviousAccountOpenYear, SUM (OrderLines.Quantity) AS TotalOrderQuantity FROM Sales.OrderLines JOIN Sales.Orders ON Orders.OrderID = OrderLines.OrderID JOIN Sales.Customers ON Customers.CustomerID = Orders.CustomerID GROUP BY YEAR (Customers.AccountOpenedDate) MONTH (Customers.AccountOpenedDate), Customers.DeliveryPostalCode
This query will be executed as is, because we do not need to group literals, nor do we need to group expressions that use only columns or expressions already in GROUP BY.
However, if we delete the existing GROUP BY clause and let SQL Prompt populate it automatically, it will look slightly different:
GROUP BY YEAR (Customers.AccountOpenedDate), MONTH (Customers.AccountOpenedDate), YEAR (Customers.AccountOpenedDate)-1, Customers.DeliveryPostalCode
This is a rare situation that will almost certainly have no impact on query execution, but be aware of this.
This is the query we completed, but if you want to order the output, please try typing ORDER BY and see what happens!
Conclusion
Once SQL Prompt is installed, you will immediately realize how it enhances SSMS's native IntelliSense, but this may prevent you from seeing many other features hidden in various parts of the tool. Usually, I just stumble upon them, or when my colleagues remind me. This is just another essence of a function I find I often use once I am aware of its existence!
Writing GROUP BY is a very common but tedious activity for SQL programmers. I always say, replace that boring thing with software.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 207
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.