GROUP BY的用法:GROUP BY (Transact-SQL)
-- Declare Table variable..
DECLARE @tbA TABLE
(
ID INT NOT NULL IDENTITY(1,1)
, NM CHAR(1) NOT NULL
, CAT CHAR(3) NOT NULL
, VAL MONEY
)
;
-- Insert test data...
INSERT INTO @tbA VALUES
('A','001',1)
, ('B','001',2)
, ('C','001',3)
, ('D','001',4)
, ('E','001',5)
, ('F','001',6)
, ('G','001',7)
, ('H','001',8)
, ('A','002',100)
, ('B','002',200)
, ('C','002',300)
, ('D','002',400)
, ('E','003',500)
, ('F','003',600)
, ('G','003',700)
, ('H','003',800)
;
-- GROUP BY
SELECT
NM
, CAT
, SUM(VAL) VAL
FROM @tbA
GROUP BY
NM
, CAT
ORDER BY
NM
, CAT
;
/* Result:
NM CAT VAL
---- ---- ---------------------
A 001 1.00
A 002 100.00
B 001 2.00
B 002 200.00
C 001 3.00
C 002 300.00
D 001 4.00
D 002 400.00
E 001 5.00
E 003 500.00
F 001 6.00
F 003 600.00
G 001 7.00
G 003 700.00
H 001 8.00
H 003 800.00
*/
-- GROUP BY GROUPING SETS
SELECT
NM
, CAT
, SUM(VAL) VAL
FROM @tbA
GROUP BY
GROUPING SETS(
NM
, CAT
)
ORDER BY
NM
, CAT
;
/* Result
NM CAT VAL
---- ---- ---------------------
NULL 001 36.00
NULL 002 1000.00
NULL 003 2600.00
A NULL 101.00
B NULL 202.00
C NULL 303.00
D NULL 404.00
E NULL 505.00
F NULL 606.00
G NULL 707.00
H NULL 808.00
*/
-- GROUP BY WITH ROLLUP
SELECT
NM
, CAT
, SUM(VAL) VAL
FROM @tbA
GROUP BY
NM
, CAT
WITH ROLLUP
ORDER BY
NM
, CAT
;
/* Result
NM CAT VAL
---- ---- ---------------------
NULL NULL 3636.00
A NULL 101.00
A 001 1.00
A 002 100.00
B NULL 202.00
B 001 2.00
B 002 200.00
C NULL 303.00
C 001 3.00
C 002 300.00
D NULL 404.00
D 001 4.00
D 002 400.00
E NULL 505.00
E 001 5.00
E 003 500.00
F NULL 606.00
F 001 6.00
F 003 600.00
G NULL 707.00
G 001 7.00
G 003 700.00
H NULL 808.00
H 001 8.00
H 003 800.00
*/
-- GROUP BY WITH CUBE
SELECT
NM
, CAT
, SUM(VAL) VAL
FROM @tbA
GROUP BY
NM
, CAT
WITH CUBE
ORDER BY
NM
, CAT
;
/*Result:
M CAT VAL
---- ---- ---------------------
NULL NULL 3636.00
NULL 001 36.00
NULL 002 1000.00
NULL 003 2600.00
A NULL 101.00
A 001 1.00
A 002 100.00
B NULL 202.00
B 001 2.00
B 002 200.00
C NULL 303.00
C 001 3.00
C 002 300.00
D NULL 404.00
D 001 4.00
D 002 400.00
E NULL 505.00
E 001 5.00
E 003 500.00
F NULL 606.00
F 001 6.00
F 003 600.00
G NULL 707.00
G 001 7.00
G 003 700.00
H NULL 808.00
H 001 8.00
H 003 800.00
*/