sql server - Hogyan használhatom a T-t?

original title: "sql server - How do I use T-SQL Group By"


Translate

I know I need to have (although I don't know why) a GROUP BY clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc:

SELECT count(userID), userName
FROM users
GROUP BY userName

When else would GROUP BY be useful, and what are the performance ramifications?



Tudom, hogy kell lennie (bár nem tudom miért) egy GROUP BY záradékot az SQL lekérdezés végén, amely olyan összesített funkciókat használ, mint a gróf, az összeg, az átlag stb.: SELECT count (userID), userName FROM felhasználó. ..

Ez az összefoglalás a fordítás után. Ha meg szeretné tekinteni a teljes fordítást, kattintson a "fordítás" ikonra


Minden válasz
  • Translate

    To retrieve the number of widgets from each widget category that has more than 5 widgets, you could do this:

    SELECT WidgetCategory, count(*)
    FROM Widgets
    GROUP BY WidgetCategory
    HAVING count(*) > 5
    

    The "having" clause is something people often forget about, instead opting to retrieve all their data to the client and iterating through it there.


  • Translate

    GROUP BY is similar to DISTINCT in that it groups multiple records into one.

    This example, borrowed from http://www.devguru.com/technologies/t-sql/7080.asp, lists distinct products in the Products table.

    SELECT Product FROM Products GROUP BY Product
    
    Product
    -------------
    Desktop
    Laptop
    Mouse
    Network Card
    Hard Drive
    Software
    Book
    Accessory
    

    The advantage of GROUP BY over DISTINCT, is that it can give you granular control when used with a HAVING clause.

    SELECT Product, count(Product) as ProdCnt
    FROM Products
    GROUP BY Product
    HAVING count(Product) > 2
    
    Product      ProdCnt
    --------------------
    Desktop          10
    Laptop            5
    Mouse             3
    Network Card      9
    Software          6
    

  • Translate

    Group By forces the entire set to be populated before records are returned (since it is an implicit sort).

    For that reason (and many others), never use a Group By in a subquery.


  • Translate

    Counting the number of times tags are used might be a google example:

    SELECT TagName, Count(*)
    AS TimesUsed
    FROM Tags
    GROUP BY TagName ORDER TimesUsed
    

    If you simply want a distinct value of tags, I would prefer to use the DISTINCT statement.

    SELECT DISTINCT TagName
    FROM Tags
    ORDER BY TagName ASC
    

  • Translate

    GROUP BY also helps when you want to generate a report that will average or sum a bunch of data. You can GROUP By the Department ID and the SUM all the sales revenue or AVG the count of sales for each month.