|
aku1 发表于 2008-5-9 10:56:33 |
We can figure out the difference in three expressions: (Sum(),Compute ,Cube) by
two select statement.
CASE1
USE pubs
SELECT SUBSTRING(title, 1, 65) AS title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id
GROUP BY title
WITH CUBE
ORDER BY title
下面是结果集： title qty
----------------------------------------------------------------- ------
NULL 493
But Is It User Friendly? 30
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 20
Cooking with Computers: Surreptitious Balance Sheets 25
...
The Busy Executive's Database Guide 15
The Gourmet Microwave 40
You Can Combat Computer Stress! 35
(17 row(s) affected)
USE pubs
SELECT SUBSTRING(title, 1, 65) AS title, qty AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id
GROUP BY title,qty
ORDER BY title
COMPUTE sum(qty)
CASE2
Can you understand the 'with cube' expression from this case ?
USE pubs
CREATE TABLE cube_examples
(product_name varchar(30) NULL,
customer_name varchar(30) NULL,
number_of_orders int NULL
)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50)
首先，发出一个带 GROUP BY 子句和结果集的典型查询。 USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
ORDER BY product_name
GROUP BY 使结果集在组内形成组。下面是结果集： product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
(7 row(s) affected)
然后，使用 CUBE 运算符发出一个带 GROUP BY 子句的查询。结果集应包括相同的信息以及各 GROUP BY 列的超聚合信息。 USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
WITH CUBE
CUBE 运算符的结果集包含上述简单 GROUP BY 结果集的值，并为 GROUP BY 子句中的各行添加超聚合信息。NULL 代表结果集中所有计算出的聚合值。下面是结果集： product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
NULL Eastern Connection 50
NULL Romero y tomillo 100
NULL Wilman Kala 110
CASE3
I will find out the difference between two select statement with Rollup a
nd Cube ,please note the select statement with rollup will only diplay the top Row about 'NULL'
USE pubs
SELECT pub_name, title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE
下面是结果集： pub_name title qty
-------------------- ---------------------------------------- ------
Algodata Infosystems But Is It User Friendly? 30
Algodata Infosystems Cooking with Computers: Surreptitious Ba 25
Algodata Infosystems Secrets of Silicon Valley 50
Algodata Infosystems Straight Talk About Computers 15
Algodata Infosystems The Busy Executive's Database Guide 15
Algodata Infosystems NULL 135
Binnet & Hardley Computer Phobic AND Non-Phobic Individu 20
Binnet & Hardley Fifty Years in Buckingham Palace Kitche 20
... ...
NULL Sushi, Anyone? 20
NULL The Busy Executive's Database Guide 15
NULL The Gourmet Microwave 40
NULL You Can Combat Computer Stress! 35
(36 row(s) affected)
Hints :The GROUPING function can be used only with CUBE or ROLLUP. The GROUPING function returns 1 when an expression evaluates to NULL, because the column value is NULL and represents the set of all values. The GROUPING function returns 0 when the corresponding column (whether it is NULL or not) did not come from either the CUBE or ROLLUP options as a syntax value. The returned value has a tinyint data type.
|
