having.result
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:4k
- drop table if exists t1,t2;
- create table t1 (a int);
- select count(a) as b from t1 where a=0 having b > 0;
- b
- insert into t1 values (null);
- select count(a) as b from t1 where a=0 having b > 0;
- b
- select count(a) as b from t1 where a=0 having b >=0;
- b
- 0
- explain extended select count(a) as b from t1 where a=0 having b >=0;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
- Warnings:
- Note 1003 select count(test.t1.a) AS `b` from test.t1 where (test.t1.a = 0) having (count(test.t1.a) >= 0)
- drop table t1;
- CREATE TABLE t1 (
- raw_id int(10) NOT NULL default '0',
- chr_start int(10) NOT NULL default '0',
- chr_end int(10) NOT NULL default '0',
- raw_start int(10) NOT NULL default '0',
- raw_end int(10) NOT NULL default '0',
- raw_ori int(2) NOT NULL default '0'
- );
- INSERT INTO t1 VALUES (469713,1,164123,1,164123,1),(317330,164124,317193,101,153170,1),(469434,317194,375620,101,58527,1),(591816,375621,484273,1,108653,1),(591807,484274,534671,91,50488,1),(318885,534672,649362,101,114791,1),(318728,649363,775520,102,126259,1),(336829,775521,813997,101,38577,1),(317740,813998,953227,101,139330,1),(1,813998,953227,101,139330,1);
- CREATE TABLE t2 (
- id int(10) unsigned NOT NULL default '0',
- contig_id int(10) unsigned NOT NULL default '0',
- seq_start int(10) NOT NULL default '0',
- seq_end int(10) NOT NULL default '0',
- strand tinyint(2) NOT NULL default '0',
- KEY id (id)
- );
- INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2);
- SELECT e.id,
- MIN( IF(sgp.raw_ori=1,
- (e.seq_start+sgp.chr_start-sgp.raw_start),
- (sgp.chr_start+sgp.raw_end-e.seq_end))) as start,
- MAX( IF(sgp.raw_ori=1,
- (e.seq_end+sgp.chr_start-sgp.raw_start),
- (sgp.chr_start+sgp.raw_end-e.seq_start))) as end,
- AVG(IF (sgp.raw_ori=1,e.strand,(-e.strand))) as chr_strand
- FROM t1 sgp,
- t2 e
- WHERE sgp.raw_id=e.contig_id
- GROUP BY e.id
- HAVING chr_strand= -1 and end >= 0
- AND start <= 999660;
- id start end chr_strand
- 133197 813898 813898 -1.0000
- drop table t1,t2;
- CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
- INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
- select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;
- Fld1 q
- 1 20
- 3 50
- select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
- Fld1 max(Fld2)
- 1 20
- 3 50
- select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
- Fld1 max(Fld2)
- 1 20
- 3 50
- select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
- Fld1 max(Fld2)
- 1 20
- 3 50
- select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
- Fld1 max(Fld2)
- 1 20
- 3 50
- drop table t1;
- create table t1 (id int not null, qty int not null);
- insert into t1 values (1,2),(1,3),(2,4),(2,5);
- select id, sum(qty) as sqty from t1 group by id having sqty>2;
- id sqty
- 1 5
- 2 9
- select sum(qty) as sqty from t1 group by id having count(id) > 0;
- sqty
- 5
- 9
- select sum(qty) as sqty from t1 group by id having count(distinct id) > 0;
- sqty
- 5
- 9
- drop table t1;
- CREATE TABLE t1 (
- `id` bigint(20) NOT NULL default '0',
- `description` text
- ) ENGINE=MyISAM;
- CREATE TABLE t2 (
- `id` bigint(20) NOT NULL default '0',
- `description` varchar(20)
- ) ENGINE=MyISAM;
- INSERT INTO t1 VALUES (1, 'test');
- INSERT INTO t2 VALUES (1, 'test');
- CREATE TABLE t3 (
- `id` bigint(20) NOT NULL default '0',
- `order_id` bigint(20) NOT NULL default '0'
- ) ENGINE=MyISAM;
- select
- a.id, a.description,
- count(b.id) as c
- from t1 a left join t3 b on a.id=b.order_id
- group by a.id, a.description
- having (a.description is not null) and (c=0);
- id description c
- 1 test 0
- select
- a.*,
- count(b.id) as c
- from t2 a left join t3 b on a.id=b.order_id
- group by a.id, a.description
- having (a.description is not null) and (c=0);
- id description c
- 1 test 0
- INSERT INTO t1 VALUES (2, 'test2');
- select
- a.id, a.description,
- count(b.id) as c
- from t1 a left join t3 b on a.id=b.order_id
- group by a.id, a.description
- having (a.description is not null) and (c=0);
- id description c
- 1 test 0
- 2 test2 0
- drop table t1,t2,t3;