welcome to 's blog...


公告

我的分类(专题)

日志更新

最新评论

留言板

链接

搜索


Blog信息




SQL Select notes
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.


 


阅读全文 | 回复(0) | 引用通告 | 编辑


发表评论:

    昵称:
    密码: (游客无须输入密码)
    主页:
    标题:



Powered by Oblog.