1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
|
drop table if exists t1,t2,t3;
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;
create table t1 (col1 int, col2 varchar(5), col_t1 int);
create table t2 (col1 int, col2 varchar(5), col_t2 int);
create table t3 (col1 int, col2 varchar(5), col_t3 int);
insert into t1 values(10,'hello',10);
insert into t1 values(20,'hello',20);
insert into t1 values(30,'hello',30);
insert into t1 values(10,'bye',10);
insert into t1 values(10,'sam',10);
insert into t1 values(10,'bob',10);
insert into t2 select * from t1;
insert into t3 select * from t1;
select count(*) from t1 group by col1 having col1 = 10;
count(*)
4
select count(*) as count_col1 from t1 group by col1 having col1 = 10;
count_col1
4
select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
count_col1
4
select count(*) from t1 group by col2 having col2 = 'hello';
count(*)
3
select count(*) from t1 group by col2 having col1 = 10;
ERROR 42S22: Unknown column 'col1' in 'having clause'
select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
count_col1
10
select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10;
count_col1
10
select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10;
count_col1
10
select col1 as count_col1 from t1 as tmp1 group by count_col1 having count_col1 = 10;
count_col1
10
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10;
count_col1 col2
10 bob
10 bye
10 hello
10 sam
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10;
count_col1 col2
10 bob
10 bye
10 hello
10 sam
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello';
count_col1 col2
10 hello
20 hello
30 hello
select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello';
count_col1 group_col2
10 hello
20 hello
30 hello
select sum(col1) as co12 from t1 group by col2 having col2 10;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10' at line 1
select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10;
co2 cc
40 4
select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10;
ERROR 42S22: Unknown column 't1.col1' in 'having clause'
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2 having t2.col1 <= 10);
col1
10
20
30
10
10
10
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2
having t2.col1 <=
(select min(t3.col1) from t3));
col1
10
20
30
10
10
10
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2 having t1.col1 <= 10);
col1
10
10
10
10
select t1.col1 as tmp_col from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2 having tmp_col <= 10);
tmp_col
10
10
10
10
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2 having col_t1 <= 10);
col1
10
10
10
10
select sum(col1) from t1
group by col_t1
having (select col_t1 from t2 where col_t1 = col_t2 order by col_t2 limit 1);
sum(col1)
40
20
30
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2 having col_t1 <= 10)
having col_t1 <= 20;
ERROR 42S22: Unknown column 'col_t1' in 'having clause'
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
group by t2.col1, t2.col2 having col_t1 <= 10)
group by col_t1
having col_t1 <= 20;
col1
10
select col_t1, sum(col1) from t1
group by col_t1
having col_t1 > 10 and
exists (select sum(t2.col1) from t2
group by t2.col2 having t2.col2 > 'b');
col_t1 sum(col1)
20 20
30 30
select sum(col1) from t1
group by col_t1
having col_t1 in (select sum(t2.col1) from t2
group by t2.col2, t2.col1 having t2.col1 = t1.col1);
ERROR 42S22: Unknown column 't1.col1' in 'having clause'
select sum(col1) from t1
group by col_t1
having col_t1 in (select sum(t2.col1) from t2
group by t2.col2, t2.col1 having t2.col1 = col_t1);
sum(col1)
40
20
30
select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1
group by t1.col1, t2.col1 having col1 = 2;
ERROR 23000: Column 'col1' in having clause is ambiguous
select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1
group by t1.col1, t2.col1 having col1 = 2;
ERROR 23000: Column 'col1' in having clause is ambiguous
drop table t1, t2, t3;
create table t1 (s1 int);
insert into t1 values (1),(2),(3);
select count(*) from t1 group by s1 having s1 is null;
count(*)
select s1*0 as s1 from t1 group by s1 having s1 <> 0;
s1
0
0
0
Warnings:
Warning 1052 Column 's1' in having clause is ambiguous
select s1*0 from t1 group by s1 having s1 = 0;
s1*0
select s1 from t1 group by 1 having 1 = 0;
s1
select count(s1) from t1 group by s1 having count(1+1)=2;
count(s1)
select count(s1) from t1 group by s1 having s1*0=0;
count(s1)
1
1
1
select * from t1 a, t1 b group by a.s1 having s1 is null;
ERROR 23000: Column 's1' in having clause is ambiguous
drop table t1;
create table t1 (s1 char character set latin1 collate latin1_german1_ci);
insert into t1 values ('ΓΌ'),('y');
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
select s1,count(s1) from t1
group by s1 collate latin1_swedish_ci having s1 = 'y';
s1 count(s1)
y 1
drop table t1;
|
