olap.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

(2,2), (2,2),
(4,1), (4,1), (4,1), (4,1),
(2,1), (2,1);
SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)
4
6
4
14
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)
4
6
14
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)
4	1
6	2
4	1
14	3
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)
4	1
6	2
14	3
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(*)
4	1
6	4
4	4
14	9
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(*)
4	1
6	4
4	4
14	9
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)	COUNT(*)
4	1	1
6	2	4
4	1	4
14	3	9
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)	COUNT(*)
4	1	1
6	2	4
4	1	4
14	3	9
SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
a	sum(b)
1	4
1	4
2	2
2	4
2	6
4	4
4	4
NULL	14
SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
a	sum(b)
1	4
2	2
2	4
2	6
4	4
NULL	14
SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
b	a	sum(b)
4	1	4
NULL	1	4
1	2	2
2	2	4
NULL	2	6
1	4	4
NULL	4	4
NULL	NULL	14
SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
b	a	sum(b)
4	1	4
NULL	1	4
1	2	2
2	2	4
NULL	2	6
1	4	4
NULL	4	4
NULL	NULL	14
ALTER TABLE t1 ADD COLUMN c INT;
SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
a	b	sum(c)
1	4	NULL
1	4	NULL
1	NULL	NULL
2	1	NULL
2	1	NULL
2	2	NULL
2	2	NULL
2	NULL	NULL
4	1	NULL
4	1	NULL
4	NULL	NULL
NULL	NULL	NULL
SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
a	b	sum(c)
1	4	NULL
1	NULL	NULL
2	1	NULL
2	2	NULL
2	NULL	NULL
4	1	NULL
4	NULL	NULL
NULL	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES
(1,4),
(2,2), (2,2),
(4,1), (4,1), (4,1), (4,1),
(2,1), (2,1);
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
a	SUM(b)
1	4
SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
a	SUM(b)
1	4
DROP TABLE t1;
CREATE TABLE t1 (a int(11) NOT NULL);
INSERT INTO t1 VALUES (1),(2);
SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
a	m
1	1
2	2
NULL	3
SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
a	m
1	1
2	2
NULL	3
DROP TABLE t1;
set div_precision_increment= @sav_dpi;
CREATE TABLE t1 (a int(11));
INSERT INTO t1 VALUES (1),(2);
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
GROUP BY a;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
GROUP BY a WITH ROLLUP;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
NULL	3	4
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
GROUP BY a;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
GROUP BY a WITH ROLLUP;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
NULL	3	4
SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
GROUP BY a WITH ROLLUP;
a	SUM(a)	SUM(a)+1	CONCAT(SUM(a),'x')	SUM(a)+SUM(a)	SUM(a)
1	1	2	1x	2	1
2	2	3	2x	4	2
5	5	6	5x	10	5
NULL	8	9	8x	16	8
DROP TABLE t1;
CREATE TABLE t1 (a int(11));
INSERT INTO t1 VALUES (1),(2);
SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
a	a+1	SUM(a)
1	2	1
2	3	2
NULL	NULL	3
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
a+1
2
3
NULL
SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
a+SUM(a)
2
4
NULL
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
a	b
2	3
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
a	b
NULL	NULL
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
a	b
NULL	NULL
SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
IFNULL(a, 'TEST')
1
2
TEST
CREATE TABLE t2 (a int, b int);
INSERT INTO t2 VALUES
(1,4),
(2,2), (2,2),
(4,1), (4,1), (4,1), (4,1),
(2,1), (2,1);
SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP;
a	b	SUM(b)
1	4	4
1	NULL	4
2	1	2
2	2	4
2	NULL	6
4	1	4
4	NULL	4
NULL	NULL	14
SELECT a,b,SUM(b), a+b as c FROM t2
GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
a	b	SUM(b)	c
1	NULL	4	NULL
2	NULL	6	NULL
4	NULL	4	NULL
NULL	NULL	14	NULL
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
GROUP BY a, b WITH ROLLUP;
IFNULL(a, 'TEST')	COALESCE(b, 'TEST')
1	4
1	TEST
2	1
2	2
2	TEST
4	1
4	TEST
TEST	TEST
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (1, 2);
SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
a	b	c	count
1	1	1	1
1	1	NULL	1
1	2	1	1
1	2	NULL	1
1	NULL	NULL	2
NULL	NULL	NULL	2
DROP TABLE t1;
CREATE TABLE t1 (a int(11) NOT NULL);
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
a	a + 1	COUNT(*)
1	2	1
2	3	1
NULL	NULL	2
SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
a	LENGTH(a)	COUNT(*)
1	1	1
2	1	1
NULL	NULL	2
DROP TABLE t1;
create table t1 ( a varchar(9), b int );
insert into t1 values('a',1),(null,2);
select a, max(b) from t1 group by a with rollup;
a	max(b)
NULL	2
a	1
NULL	2
select distinct a, max(b) from t1 group by a with rollup;
a	max(b)
NULL	2
a	1
drop table t1;
CREATE TABLE t1(id int, type char(1));
INSERT INTO t1 VALUES
(1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
(6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT type FROM t1 GROUP BY type WITH ROLLUP;
type
A
B
C
NULL
SELECT type FROM v1 GROUP BY type WITH ROLLUP;
type
A
B
C
NULL
EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using filesort
DROP VIEW v1;
DROP TABLE t1;
CREATE TABLE t1 (a int(11) NOT NULL);
INSERT INTO t1 VALUES (1),(2);
CREATE VIEW v1 AS
SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
DESC v1;
Field	Type	Null	Key	Default	Extra
a	bigint(11)	YES		NULL	
LENGTH(a)	bigint(10)	YES		NULL	
COUNT(*)	bigint(21)	NO		0	
SELECT * FROM v1;
a	LENGTH(a)	COUNT(*)
1	1	1
2	1	1
NULL	NULL	2
DROP VIEW v1;
DROP TABLE t1;