Welcome to Sign in | Join | Help
in Search

ROLLUP si CUBE partial in SQL Server 2005

Last post 06-02-2007, 9:58 AM by rsocol. 1 replies.
Sort Posts: Previous Next
  •  06-01-2007, 7:53 PM 2002

    ROLLUP si CUBE partial in SQL Server 2005

    Se poate face o interogare rollup sau cube partial, ca in Oracle?

    "select continent As Continet, countryregioncode As Cod, name as Tara,sum(saleslastyear) As Vanzari
    from SalesTerritory
    group by name  rollup (continent,countryregioncode) "

     

    Daca da, cum? 

  •  06-02-2007, 9:58 AM 2003 in reply to 2002

    Re: ROLLUP si CUBE partial in SQL Server 2005

    Nu sunt familiar cu clauza ROLLUP din Oracle, dar cred că poţi să obţii rezultatul dorit folosind funcţia GROUPING pentru a filtra rândurile cu subtotaluri, de exemplu:

    select continent As Continet, countryregioncode As Cod, name as Tara,sum(saleslastyear) As Vanzari
    from SalesTerritory group by name, continent,countryregioncode with rollup
    HAVING GROUPING(name) = GROUPING(continent)

     

    Folosind un alt exemplu din AdventureWorks, să presupunem că avem următorul view:

    CREATE VIEW Purchasing.PurchasesByVendorsAndStates
    AS
    SELECT V.Name AS VendorName, A.City, s.Name AS StateProvinceName, CountryRegionCode, P.TotalDue
    FROM Purchasing.PurchaseOrderHeader P INNER JOIN Purchasing.Vendor V ON P.VendorID = V.VendorID
    INNER JOIN Purchasing.VendorAddress VA ON V.VendorID = VA.VendorID
    INNER JOIN Person.Address AS A ON VA.AddressID=A.AddressID
    INNER JOIN Person.StateProvince S ON A.StateProvinceID=S.StateProvinceID

    Dacă folosim direct ROLLUP, astfel:

    SELECT VendorName, City, StateProvinceName, CountryRegionCode, SUM(TotalDue) AS TotalDue
    FROM Purchasing.PurchasesByVendorsAndStates
    GROUP BY CountryRegionCode, StateProvinceName, City, VendorName WITH ROLLUP

    Atunci obţinem un rezultat de genul:

    VendorName                           City             StateProvinceName   CountryRegionCode TotalDue
    ------------------------------------ ---------------- ------------------- ----------------- ---------------------
    Greenwood Athletic Company Lemon Grove Arizona US 2472770.0523
    NULL Lemon Grove Arizona US 2472770.0523
    NULL NULL Arizona US 2472770.0523
    Allenson Cycles Altadena California US 498589.5915
    Gardner Touring Cycles Altadena California US 25633.6353
    NULL Altadena California US 524223.2268
    Cruger Bike Company Berkeley California US 31499.8141
    Trikes, Inc. Berkeley California US 1461653.945
    NULL Berkeley California US 1493153.7591
    [...]
    American Bicycles and Wheels West Covina California US 9641.0145
    Bloomington Multisport West Covina California US 8243.9511
    International Bicycles West Covina California US 1589172.9591
    International Trek Center West Covina California US 24070.75
    NULL West Covina California US 1631128.6747
    NULL NULL California US 31564099.5102
    [...]
    Team Athletic Co. Spokane Washington US 436401.90
    Varsity Sport Co. Spokane Washington US 52176.445
    NULL Spokane Washington US 488578.345
    Electronic Bike Repair & Supplies Tacoma Washington US 2154773.3718
    NULL Tacoma Washington US 2154773.3718
    NULL NULL Washington US 18899318.6589
    NULL NULL NULL US 70479332.6383
    NULL NULL NULL NULL 70479332.6383
    (169 row(s) affected)

    Dacă folosim următorul query:

    SELECT VendorName, City, StateProvinceName, CountryRegionCode, SUM(TotalDue) AS TotalDue
    FROM Purchasing.PurchasesByVendorsAndStates
    GROUP BY CountryRegionCode, StateProvinceName, City, VendorName WITH ROLLUP
    HAVING GROUPING(VendorName)=GROUPING(City)

    atunci obţinem un rezultat de tipul următor:

    VendorName                          City             StateProvinceName  CountryRegionCode TotalDue
    ----------------------------------- ---------------- ------------------ ----------------- ---------------------
    Greenwood Athletic Company Lemon Grove Arizona US 2472770.0523
    NULL NULL Arizona US 2472770.0523
    Allenson Cycles Altadena California US 498589.5915
    Gardner Touring Cycles Altadena California US 25633.6353
    Cruger Bike Company Berkeley California US 31499.8141
    Trikes, Inc. Berkeley California US 1461653.945
    [...]
    American Bicycles and Wheels West Covina California US 9641.0145
    Bloomington Multisport West Covina California US 8243.9511
    International Bicycles West Covina California US 1589172.9591
    International Trek Center West Covina California US 24070.75
    NULL NULL California US 31564099.5102
    [...]
    Team Athletic Co. Spokane Washington US 436401.90
    Varsity Sport Co. Spokane Washington US 52176.445
    Electronic Bike Repair & Supplies Tacoma Washington US 2154773.3718
    NULL NULL Washington US 18899318.6589
    NULL NULL NULL US 70479332.6383
    NULL NULL NULL NULL 70479332.6383
    (107 row(s) affected)

    fiind eliminate astfel subtotalurile pe fiecare oraş, păstrând doar subtotalurile după stat, ţară şi totalul general.

    Răzvan 

View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems