olap.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 718 行 · 第 1/2 页

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 (a varchar(22) not null , b int);
insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
left(a,10)	a	sum(b)
2006-07-01	2006-07-01 21:30	1
2006-07-01	2006-07-01 23:30	10
2006-07-01	NULL	11
NULL	NULL	11
select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
x	a	sum(b)
2006-07-01	2006-07-01 21:30	1
2006-07-01	2006-07-01 23:30	10
2006-07-01	NULL	11
NULL	NULL	11
drop table t1;
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 
VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
a	SUM(b)
1	30
2	90
3	30
NULL	150
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
a	SUM(b)
1	30
2	90
3	30
NULL	150
SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
a	b	COUNT(*)
1	10	1
1	20	1
1	NULL	2
2	10	2
2	30	1
2	40	1
2	NULL	4
3	30	1
3	NULL	1
NULL	NULL	7
SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
a	b	COUNT(*)
1	10	1
1	20	1
1	NULL	2
2	10	2
2	30	1
2	40	1
2	NULL	4
3	30	1
3	NULL	1
NULL	NULL	7
SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
x	a	SUM(b)
x	1	30
x	2	90
x	3	30
x	NULL	150
NULL	NULL	150
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
x	a	SUM(b)
x	1	30
x	2	90
x	3	30
x	NULL	150
NULL	NULL	150
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
x	a	SUM(b)
x	1	30
x	2	90
x	3	30
x	NULL	150
NULL	NULL	150
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	SIMPLE	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;
CREATE TABLE t1 (a int, KEY (a));
INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
a	SUM(a)
1	4
3	6
4	4
NULL	14
DROP TABLE t1;