1
drop table if exists t1,t2,t3,t4;
3
Period int DEFAULT '0000' NOT NULL,
4
Varor_period int DEFAULT '0' NOT NULL
6
INSERT INTO t1 VALUES (9410,9412);
17
auto int not null auto_increment,
18
fld1 int DEFAULT '000000' NOT NULL,
19
companynr int DEFAULT '00' NOT NULL,
20
fld3 char(30) DEFAULT '' NOT NULL,
21
fld4 char(35) DEFAULT '' NOT NULL,
22
fld5 char(35) DEFAULT '' NOT NULL,
23
fld6 char(4) DEFAULT '' NOT NULL,
28
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
31
select fld3 from t2 where fld3 like "%cultivation" ;
34
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
59
select fld3,companynr from t2 where companynr = 58 order by fld3;
84
select fld3 from t2 order by fld3 desc limit 10;
96
select fld3 from t2 order by fld3 desc limit 5;
103
select fld3 from t2 order by fld3 desc limit 5,5;
110
select t2.fld3 from t2 where fld3 = 'honeysuckle';
113
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
116
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
119
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
122
select t2.fld3 from t2 where fld3 LIKE 'h%le';
125
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
127
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
129
explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
130
id select_type table type possible_keys key key_len ref rows Extra
131
1 SIMPLE t2 ref fld3 fld3 122 const # Using where; Using index
132
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
133
id select_type table type possible_keys key key_len ref rows Extra
134
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
135
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
136
id select_type table type possible_keys key key_len ref rows Extra
137
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
138
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
139
id select_type table type possible_keys key key_len ref rows Extra
140
1 SIMPLE t2 ref fld3 fld3 122 const # Using where; Using index
141
explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
142
id select_type table type possible_keys key key_len ref rows Extra
143
1 SIMPLE t2 ref fld3 fld3 122 const # Using where; Using index
144
explain select fld3 from t2 ignore index (fld3,not_used);
145
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
146
explain select fld3 from t2 use index (not_used);
147
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
148
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
152
explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
153
id select_type table type possible_keys key key_len ref rows Extra
154
1 SIMPLE t2 range fld3 fld3 122 NULL # Using where; Using index
155
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
160
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
168
select fld1 from t2 where fld1=250501 or fld1="250502";
172
explain select fld1 from t2 where fld1=250501 or fld1="250502";
173
id select_type table type possible_keys key key_len ref rows Extra
174
1 SIMPLE t2 range fld1 fld1 4 NULL # Using where; Using index
175
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
181
explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
182
id select_type table type possible_keys key key_len ref rows Extra
183
1 SIMPLE t2 range fld1 fld1 4 NULL # Using where; Using index
184
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
217
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
219
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
222
select fld1,fld3 from t2 where fld1 like "25050%";
229
select fld1,fld3 from t2 where fld1 like "25050_";
236
select distinct companynr from t2;
250
select distinct companynr from t2 order by companynr;
264
select distinct companynr from t2 order by companynr desc;
278
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%" ORDER BY t2.fld3,period;
286
select distinct fld3 from t2 where companynr = 34 order by fld3;
358
select distinct fld3 from t2 ORDER BY fld3 limit 10;
370
select distinct fld3 from t2 having fld3 like "A%" limit 10;
382
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
446
select distinct lower(substring(fld3,1,3)) as a from t2 having a like "A%" order by a limit 10;
458
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
472
name char(32) not null,
473
companynr int not null,
479
create temporary table tmp engine = myisam select * from t3;
480
insert into t3 select * from tmp;
481
insert into tmp select * from t3;
482
insert into t3 select * from tmp;
483
insert into tmp select * from t3;
484
insert into t3 select * from tmp;
485
insert into tmp select * from t3;
486
insert into t3 select * from tmp;
487
insert into tmp select * from t3;
488
insert into t3 select * from tmp;
489
insert into tmp select * from t3;
490
insert into t3 select * from tmp;
491
insert into tmp select * from t3;
492
insert into t3 select * from tmp;
493
insert into tmp select * from t3;
494
insert into t3 select * from tmp;
495
insert into tmp select * from t3;
496
insert into t3 select * from tmp;
497
alter table t3 add t2nr int not null auto_increment primary key first;
499
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
502
abrogating abrogating
503
admonishing admonishing
508
analyzable analyzable
511
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
512
concat(fld3," ",fld3)
514
abrogating abrogating
515
admonishing admonishing
520
analyzable analyzable
523
select distinct fld5 from t2 limit 10;
535
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
547
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
559
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
560
fld3 repeat("a",length(fld3)) count(*)
564
congresswoman aaaaaaaaaaaaa 1
565
contrition aaaaaaaaaa 1
567
cultivation aaaaaaaaaaa 1
568
definiteness aaaaaaaaaaaa 1
569
demultiplex aaaaaaaaaaa 1
570
disappointing aaaaaaaaaaaaa 1
571
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
572
companynr rtrim(space(512+companynr))
580
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
582
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
583
id select_type table type possible_keys key key_len ref rows Extra
584
1 SIMPLE t2 ALL fld1 NULL NULL NULL # Using where; Using temporary; Using filesort
585
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 # Using index
586
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
587
id select_type table type possible_keys key key_len ref rows Extra
588
1 SIMPLE t1 ALL period NULL NULL NULL # Using temporary; Using filesort
589
1 SIMPLE t3 ref period period 4 test.t1.period #
590
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
591
id select_type table type possible_keys key key_len ref rows Extra
592
1 SIMPLE t3 index period period 4 NULL #
593
1 SIMPLE t1 ref period period 4 test.t3.period #
594
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
595
id select_type table type possible_keys key key_len ref rows Extra
596
1 SIMPLE t1 index period period 4 NULL #
597
1 SIMPLE t3 ref period period 4 test.t1.period #
598
select period from t1;
601
select period from t1 where period=1900;
603
select fld3,period from t1,t2 where fld1 = 011401 order by period;
606
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
609
explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
610
id select_type table type possible_keys key key_len ref rows Extra
611
1 SIMPLE t2 const fld1 fld1 4 const #
612
1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const #
613
select fld3,period from t2,t1 where companynr*10 = 37*10;
846
electroencephalography 9410
954
interrelationships 9410
1203
select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;
1204
fld3 period price price2
1205
admonishing 1002 28357832 8723648
1206
analyzable 1002 28357832 8723648
1207
annihilates 1001 5987435 234724
1208
Antares 1002 28357832 8723648
1209
astound 1001 5987435 234724
1210
audiology 1001 5987435 234724
1211
Augustine 1002 28357832 8723648
1212
Baird 1002 28357832 8723648
1213
bewilderingly 1001 5987435 234724
1214
breaking 1001 5987435 234724
1215
Conley 1001 5987435 234724
1216
dentally 1002 28357832 8723648
1217
dissociate 1002 28357832 8723648
1218
elite 1001 5987435 234724
1219
eschew 1001 5987435 234724
1220
Eulerian 1001 5987435 234724
1221
flanking 1001 5987435 234724
1222
foldout 1002 28357832 8723648
1223
funereal 1002 28357832 8723648
1224
galling 1002 28357832 8723648
1225
Graves 1001 5987435 234724
1226
grazing 1001 5987435 234724
1227
groupings 1001 5987435 234724
1228
handgun 1001 5987435 234724
1229
humility 1002 28357832 8723648
1230
impulsive 1002 28357832 8723648
1231
inch 1001 5987435 234724
1232
intelligibility 1001 5987435 234724
1233
jarring 1001 5987435 234724
1234
lawgiver 1001 5987435 234724
1235
lectured 1002 28357832 8723648
1236
Merritt 1002 28357832 8723648
1237
neonatal 1001 5987435 234724
1238
offload 1002 28357832 8723648
1239
parters 1002 28357832 8723648
1240
pityingly 1002 28357832 8723648
1241
puddings 1002 28357832 8723648
1242
Punjab 1001 5987435 234724
1243
quitter 1002 28357832 8723648
1244
realtor 1001 5987435 234724
1245
relaxing 1001 5987435 234724
1246
repetitions 1001 5987435 234724
1247
resumes 1001 5987435 234724
1248
Romans 1002 28357832 8723648
1249
rusting 1001 5987435 234724
1250
scholastics 1001 5987435 234724
1251
skulking 1002 28357832 8723648
1252
stated 1002 28357832 8723648
1253
suites 1002 28357832 8723648
1254
sureties 1001 5987435 234724
1255
testicle 1002 28357832 8723648
1256
tinily 1002 28357832 8723648
1257
tragedies 1001 5987435 234724
1258
trimmings 1001 5987435 234724
1259
vacuuming 1001 5987435 234724
1260
ventilate 1001 5987435 234724
1261
wallet 1001 5987435 234724
1262
Weissmuller 1002 28357832 8723648
1263
Wotan 1002 28357832 8723648
1264
select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37;
1265
fld1 fld3 period price price2
1266
18201 relaxing 1001 5987435 234724
1267
18601 vacuuming 1001 5987435 234724
1268
18801 inch 1001 5987435 234724
1269
18811 repetitions 1001 5987435 234724
1270
create temporary table t4 (
1271
companynr int NOT NULL default '00',
1272
companyname char(30) NOT NULL default '',
1273
PRIMARY KEY (companynr),
1274
UNIQUE KEY companyname(companyname)
1275
) ENGINE=MyISAM COMMENT='companynames';
1276
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1277
companynr companyname
1290
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1291
companynr companyname
1304
select * from t1,t1 t12;
1305
Period Varor_period Period Varor_period
1307
select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
1334
insert into t2 (fld1, companynr) values (999999,99);
1335
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1336
companynr companyname
1338
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1341
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1342
id select_type table type possible_keys key key_len ref rows Extra
1343
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1344
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr # Using where; Not exists
1345
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1346
id select_type table type possible_keys key key_len ref rows Extra
1347
1 SIMPLE t4 ALL NULL NULL NULL NULL #
1348
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where; Not exists
1349
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1350
companynr companyname
1351
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1354
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1355
id select_type table type possible_keys key key_len ref rows Extra
1356
1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE
1357
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1358
id select_type table type possible_keys key key_len ref rows Extra
1359
1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE
1360
delete from t2 where fld1=999999;
1361
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1362
id select_type table type possible_keys key key_len ref rows Extra
1363
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
1364
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr #
1365
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1366
id select_type table type possible_keys key key_len ref rows Extra
1367
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
1368
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr #
1369
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1370
id select_type table type possible_keys key key_len ref rows Extra
1371
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
1372
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr #
1373
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1374
id select_type table type possible_keys key key_len ref rows Extra
1375
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where
1376
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1377
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1378
id select_type table type possible_keys key key_len ref rows Extra
1379
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where
1380
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1381
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1382
id select_type table type possible_keys key key_len ref rows Extra
1383
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where
1384
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1385
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1386
id select_type table type possible_keys key key_len ref rows Extra
1387
1 SIMPLE t4 ALL NULL NULL NULL NULL #
1388
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
1389
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1390
id select_type table type possible_keys key key_len ref rows Extra
1391
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL #
1392
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
1393
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1394
id select_type table type possible_keys key key_len ref rows Extra
1395
1 SIMPLE t4 ALL NULL NULL NULL NULL #
1396
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where
1397
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1398
id select_type table type possible_keys key key_len ref rows Extra
1399
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where
1400
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1401
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1402
id select_type table type possible_keys key key_len ref rows Extra
1403
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL # Using where
1404
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1405
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1406
id select_type table type possible_keys key key_len ref rows Extra
1407
1 SIMPLE t4 ALL NULL NULL NULL NULL # Using where
1408
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1409
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1413
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1414
id select_type table type possible_keys key key_len ref rows Extra
1415
1 SIMPLE t4 index NULL PRIMARY 4 NULL # Using index; Using temporary
1416
1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where; Using join buffer
1417
select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
1418
fld1 companynr fld3 period
1419
38008 37 reporters 1008
1420
38208 37 Selfridge 1008
1421
select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1422
fld1 companynr fld3 period
1423
38008 37 reporters 1008
1424
38208 37 Selfridge 1008
1425
select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1426
fld1 companynr fld3 period
1427
38008 37 reporters 1008
1428
38208 37 Selfridge 1008
1429
select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);
1432
select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6)));
1435
select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1;
1441
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1445
select fld1 from t2 where fld1 between 250502 and 250504;
1450
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1461
select count(*) from t1;
1464
select companynr,count(*),sum(fld1) from t2 group by companynr;
1465
companynr count(*) sum(fld1)
1478
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1485
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1486
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1487
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1488
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1489
id select_type table type possible_keys key key_len ref rows filtered Extra
1490
1 SIMPLE t2 ALL NULL NULL NULL NULL # 100.00 Using where
1492
Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
1493
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1494
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1495
0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1496
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1497
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1498
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1499
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1500
37 1 1 5987435 5987435 5987435 5987435.0000
1501
37 2 1 28357832 28357832 28357832 28357832.0000
1502
37 3 1 39654943 39654943 39654943 39654943.0000
1503
37 11 1 5987435 5987435 5987435 5987435.0000
1504
37 12 1 28357832 28357832 28357832 28357832.0000
1505
37 13 1 39654943 39654943 39654943 39654943.0000
1506
37 21 1 5987435 5987435 5987435 5987435.0000
1507
37 22 1 28357832 28357832 28357832 28357832.0000
1508
37 23 1 39654943 39654943 39654943 39654943.0000
1509
37 31 1 5987435 5987435 5987435 5987435.0000
1510
select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1511
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1512
37 1 1 5987435 5987435 5987435 5987435.0000
1513
37 2 1 28357832 28357832 28357832 28357832.0000
1514
37 3 1 39654943 39654943 39654943 39654943.0000
1515
37 11 1 5987435 5987435 5987435 5987435.0000
1516
37 12 1 28357832 28357832 28357832 28357832.0000
1517
37 13 1 39654943 39654943 39654943 39654943.0000
1518
37 21 1 5987435 5987435 5987435 5987435.0000
1519
37 22 1 28357832 28357832 28357832 28357832.0000
1520
37 23 1 39654943 39654943 39654943 39654943.0000
1521
37 31 1 5987435 5987435 5987435 5987435.0000
1522
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1523
companynr count(price) sum(price) min(price) max(price) avg(price)
1524
37 12543 309394878010 5987435 39654943 24666736.6667
1525
78 8362 414611089292 726498 98439034 49582766.0000
1526
101 4181 3489454238 834598 834598 834598.0000
1527
154 4181 4112197254950 983543950 983543950 983543950.0000
1528
311 4181 979599938 234298 234298 234298.0000
1529
447 4181 9929180954 2374834 2374834 2374834.0000
1530
512 4181 3288532102 786542 786542 786542.0000
1531
select distinct mod(companynr,10) from t4 group by companynr;
1542
select distinct 1 from t4 group by companynr;
1545
select count(distinct fld1) from t2;
1546
count(distinct fld1)
1548
select companynr,count(distinct fld1) from t2 group by companynr;
1549
companynr count(distinct fld1)
1562
select companynr,count(*) from t2 group by companynr;
1576
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1577
companynr count(distinct concat(fld1,repeat(65,1000)))
1590
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1591
companynr count(distinct concat(fld1,repeat(65,200)))
1604
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1605
companynr count(distinct floor(fld1/100))
1618
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1619
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1632
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1635
select name,count(*) from t3 where name='cloakroom' group by name;
1638
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1641
select count(*) from t3 where name='cloakroom' and price2=823742;
1644
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1647
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1656
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1659
select companynr,companyname from t4 group by 1;
1660
companynr companyname
1673
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1674
companynr companyname count(*)
1687
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1690
select sum(Period)/count(*) from t1;
1691
sum(Period)/count(*)
1693
select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr;
1694
companynr count sum diff func
1695
37 12543 309394878010 0.0000 464091
1696
78 8362 414611089292 0.0000 652236
1697
101 4181 3489454238 0.0000 422281
1698
154 4181 4112197254950 0.0000 643874
1699
311 4181 979599938 0.0000 1300291
1700
447 4181 9929180954 0.0000 1868907
1701
512 4181 3288532102 0.0000 2140672
1702
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1705
select companynr,count(*) from t2 group by companynr order by 2 desc;
1719
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1727
select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;
1728
fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1729
teethe 1 1 5987435 5987435 5987435 5987435.0000
1730
dreaded 11401 1 5987435 5987435 5987435 5987435.0000
1731
scholastics 11402 1 28357832 28357832 28357832 28357832.0000
1732
audiology 11403 1 39654943 39654943 39654943 39654943.0000
1733
wallet 11501 1 5987435 5987435 5987435 5987435.0000
1734
parters 11701 1 5987435 5987435 5987435 5987435.0000
1735
eschew 11702 1 28357832 28357832 28357832 28357832.0000
1736
quitter 11703 1 39654943 39654943 39654943 39654943.0000
1737
neat 12001 1 5987435 5987435 5987435 5987435.0000
1738
Steinberg 12003 1 39654943 39654943 39654943 39654943.0000
1739
balled 12301 1 5987435 5987435 5987435 5987435.0000
1740
persist 12302 1 28357832 28357832 28357832 28357832.0000
1741
attainments 12303 1 39654943 39654943 39654943 39654943.0000
1742
capably 12501 1 5987435 5987435 5987435 5987435.0000
1743
impulsive 12602 1 28357832 28357832 28357832 28357832.0000
1744
starlet 12603 1 39654943 39654943 39654943 39654943.0000
1745
featherweight 12701 1 5987435 5987435 5987435 5987435.0000
1746
pessimist 12702 1 28357832 28357832 28357832 28357832.0000
1747
daughter 12703 1 39654943 39654943 39654943 39654943.0000
1748
lawgiver 13601 1 5987435 5987435 5987435 5987435.0000
1749
stated 13602 1 28357832 28357832 28357832 28357832.0000
1750
readable 13603 1 39654943 39654943 39654943 39654943.0000
1751
testicle 13801 1 5987435 5987435 5987435 5987435.0000
1752
Parsifal 13802 1 28357832 28357832 28357832 28357832.0000
1753
leavings 13803 1 39654943 39654943 39654943 39654943.0000
1754
squeaking 13901 1 5987435 5987435 5987435 5987435.0000
1755
contrasted 16001 1 5987435 5987435 5987435 5987435.0000
1756
leftover 16201 1 5987435 5987435 5987435 5987435.0000
1757
whiteners 16202 1 28357832 28357832 28357832 28357832.0000
1758
erases 16301 1 5987435 5987435 5987435 5987435.0000
1759
Punjab 16302 1 28357832 28357832 28357832 28357832.0000
1760
Merritt 16303 1 39654943 39654943 39654943 39654943.0000
1761
sweetish 18001 1 5987435 5987435 5987435 5987435.0000
1762
dogging 18002 1 28357832 28357832 28357832 28357832.0000
1763
scornfully 18003 1 39654943 39654943 39654943 39654943.0000
1764
fetters 18012 1 28357832 28357832 28357832 28357832.0000
1765
bivalves 18013 1 39654943 39654943 39654943 39654943.0000
1766
skulking 18021 1 5987435 5987435 5987435 5987435.0000
1767
flint 18022 1 28357832 28357832 28357832 28357832.0000
1768
flopping 18023 1 39654943 39654943 39654943 39654943.0000
1769
Judas 18032 1 28357832 28357832 28357832 28357832.0000
1770
vacuuming 18033 1 39654943 39654943 39654943 39654943.0000
1771
medical 18041 1 5987435 5987435 5987435 5987435.0000
1772
bloodbath 18042 1 28357832 28357832 28357832 28357832.0000
1773
subschema 18043 1 39654943 39654943 39654943 39654943.0000
1774
interdependent 18051 1 5987435 5987435 5987435 5987435.0000
1775
Graves 18052 1 28357832 28357832 28357832 28357832.0000
1776
neonatal 18053 1 39654943 39654943 39654943 39654943.0000
1777
sorters 18061 1 5987435 5987435 5987435 5987435.0000
1778
epistle 18062 1 28357832 28357832 28357832 28357832.0000
1779
Conley 18101 1 5987435 5987435 5987435 5987435.0000
1780
lectured 18102 1 28357832 28357832 28357832 28357832.0000
1781
Abraham 18103 1 39654943 39654943 39654943 39654943.0000
1782
cage 18201 1 5987435 5987435 5987435 5987435.0000
1783
hushes 18202 1 28357832 28357832 28357832 28357832.0000
1784
Simla 18402 1 28357832 28357832 28357832 28357832.0000
1785
reporters 18403 1 39654943 39654943 39654943 39654943.0000
1786
coexist 18601 1 5987435 5987435 5987435 5987435.0000
1787
Beebe 18602 1 28357832 28357832 28357832 28357832.0000
1788
Taoism 18603 1 39654943 39654943 39654943 39654943.0000
1789
Connally 18801 1 5987435 5987435 5987435 5987435.0000
1790
fetched 18802 1 28357832 28357832 28357832 28357832.0000
1791
checkpoints 18803 1 39654943 39654943 39654943 39654943.0000
1792
gritty 18811 1 5987435 5987435 5987435 5987435.0000
1793
firearm 18812 1 28357832 28357832 28357832 28357832.0000
1794
minima 19101 1 5987435 5987435 5987435 5987435.0000
1795
Selfridge 19102 1 28357832 28357832 28357832 28357832.0000
1796
disable 19103 1 39654943 39654943 39654943 39654943.0000
1797
witchcraft 19201 1 5987435 5987435 5987435 5987435.0000
1798
betroth 30501 1 5987435 5987435 5987435 5987435.0000
1799
Manhattanize 30502 1 28357832 28357832 28357832 28357832.0000
1800
imprint 30503 1 39654943 39654943 39654943 39654943.0000
1801
swelling 31901 1 5987435 5987435 5987435 5987435.0000
1802
interrelationships 36001 1 5987435 5987435 5987435 5987435.0000
1803
riser 36002 1 28357832 28357832 28357832 28357832.0000
1804
bee 38001 1 5987435 5987435 5987435 5987435.0000
1805
kanji 38002 1 28357832 28357832 28357832 28357832.0000
1806
dental 38003 1 39654943 39654943 39654943 39654943.0000
1807
railway 38011 1 5987435 5987435 5987435 5987435.0000
1808
validate 38012 1 28357832 28357832 28357832 28357832.0000
1809
normalizes 38013 1 39654943 39654943 39654943 39654943.0000
1810
Kline 38101 1 5987435 5987435 5987435 5987435.0000
1811
Anatole 38102 1 28357832 28357832 28357832 28357832.0000
1812
partridges 38103 1 39654943 39654943 39654943 39654943.0000
1813
recruited 38201 1 5987435 5987435 5987435 5987435.0000
1814
dimensions 38202 1 28357832 28357832 28357832 28357832.0000
1815
Chicana 38203 1 39654943 39654943 39654943 39654943.0000
1816
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1817
companynr fld3 sum(price)
1822
512 descendants 786542
1825
512 Micronesia 786542
1828
select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;
1829
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1830
0 1 Omaha Omaha 5987435 5987435.0000
1831
36 1 dubbed dubbed 28357832 28357832.0000
1832
37 83 Abraham Wotan 1908978016 22999735.1325
1833
50 2 scribbled tapestry 68012775 34006387.5000
1834
select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1;
1835
t3.companynr+0 t2nr fld3 sum(price)
1837
37 11401 breaking 5987435
1838
37 11402 Romans 28357832
1839
37 11403 intercepted 39654943
1840
37 11501 bewilderingly 5987435
1841
37 11701 astound 5987435
1842
37 11702 admonishing 28357832
1843
37 11703 sumac 39654943
1844
37 12001 flanking 5987435
1845
37 12003 combed 39654943
1846
37 12301 Eulerian 5987435
1847
37 12302 dubbed 28357832
1848
37 12303 Kane 39654943
1849
37 12501 annihilates 5987435
1850
37 12602 Wotan 28357832
1851
37 12603 snatching 39654943
1852
37 12701 grazing 5987435
1853
37 12702 Baird 28357832
1854
37 12703 celery 39654943
1855
37 13601 handgun 5987435
1856
37 13602 foldout 28357832
1857
37 13603 mystic 39654943
1858
37 13801 intelligibility 5987435
1859
37 13802 Augustine 28357832
1860
37 13803 teethe 39654943
1861
37 13901 scholastics 5987435
1862
37 16001 audiology 5987435
1863
37 16201 wallet 5987435
1864
37 16202 parters 28357832
1865
37 16301 eschew 5987435
1866
37 16302 quitter 28357832
1867
37 16303 neat 39654943
1868
37 18001 jarring 5987435
1869
37 18002 tinily 28357832
1870
37 18003 balled 39654943
1871
37 18012 impulsive 28357832
1872
37 18013 starlet 39654943
1873
37 18021 lawgiver 5987435
1874
37 18022 stated 28357832
1875
37 18023 readable 39654943
1876
37 18032 testicle 28357832
1877
37 18033 Parsifal 39654943
1878
37 18041 Punjab 5987435
1879
37 18042 Merritt 28357832
1880
37 18043 Quixotism 39654943
1881
37 18051 sureties 5987435
1882
37 18052 puddings 28357832
1883
37 18053 tapestry 39654943
1884
37 18061 trimmings 5987435
1885
37 18062 humility 28357832
1886
37 18101 tragedies 5987435
1887
37 18102 skulking 28357832
1888
37 18103 flint 39654943
1889
37 18201 relaxing 5987435
1890
37 18202 offload 28357832
1891
37 18402 suites 28357832
1892
37 18403 lists 39654943
1893
37 18601 vacuuming 5987435
1894
37 18602 dentally 28357832
1895
37 18603 humanness 39654943
1896
37 18801 inch 5987435
1897
37 18802 Weissmuller 28357832
1898
37 18803 irresponsibly 39654943
1899
37 18811 repetitions 5987435
1900
37 18812 Antares 28357832
1901
37 19101 ventilate 5987435
1902
37 19102 pityingly 28357832
1903
37 19103 interdependent 39654943
1904
37 19201 Graves 5987435
1905
37 30501 neonatal 5987435
1906
37 30502 scribbled 28357832
1907
37 30503 chafe 39654943
1908
37 31901 realtor 5987435
1909
37 36001 elite 5987435
1910
37 36002 funereal 28357832
1911
37 38001 Conley 5987435
1912
37 38002 lectured 28357832
1913
37 38003 Abraham 39654943
1914
37 38011 groupings 5987435
1915
37 38012 dissociate 28357832
1916
37 38013 coexist 39654943
1917
37 38101 rusting 5987435
1918
37 38102 galling 28357832
1919
37 38103 obliterates 39654943
1920
37 38201 resumes 5987435
1921
37 38202 analyzable 28357832
1922
37 38203 terminator 39654943
1923
select sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1= t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008;
1926
select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;
1929
explain select fld3 from t2 where 1>2 or 2>3;
1930
id select_type table type possible_keys key key_len ref rows Extra
1931
1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE
1932
explain select fld3 from t2 where fld1=fld1;
1933
id select_type table type possible_keys key key_len ref rows Extra
1934
1 SIMPLE t2 ALL NULL NULL NULL NULL #
1935
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1939
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1943
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1950
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1957
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1958
companynr companyname count(*)
1966
select count(*) from t2;
1969
select count(*) from t2 where fld1 < 098024;
1972
select min(fld1) from t2 where fld1>= 098024;
1975
select max(fld1) from t2 where fld1>= 098024;
1978
select count(*) from t3 where price2=76234234;
1981
select count(*) from t3 where companynr=512 and price2=76234234;
1984
explain select min(fld1),max(fld1),count(*) from t2;
1985
id select_type table type possible_keys key key_len ref rows Extra
1986
1 SIMPLE t2 index NULL fld1 4 NULL # Using index
1987
select min(fld1),max(fld1),count(*) from t2;
1988
min(fld1) max(fld1) count(*)
1990
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
1993
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
1994
count(*) min(t2nr) max(t2nr)
1996
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2018
select max(t2nr) from t3 where price=983543950;
2021
select t1.period from t3 = t1 limit 1;
2024
select t1.period from t1 as t1 limit 1;
2027
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2030
select period as ok_period from t1 limit 1;
2033
select period as ok_period from t1 group by ok_period limit 1;
2036
select 1+1 as summa from t1 group by summa limit 1;
2039
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2047
show tables from test like "s%";
2049
show tables from test like "t?";
2051
show full columns from t2;
2052
Field Type Collation Null Key Default Extra Privileges Comment
2053
auto int NULL NO PRI NULL auto_increment #
2054
fld1 int NULL NO UNI 0 #
2055
companynr int NULL NO 0 #
2056
fld3 varchar(30) utf8_general_ci NO MUL #
2057
fld4 varchar(35) utf8_general_ci NO #
2058
fld5 varchar(35) utf8_general_ci NO #
2059
fld6 varchar(4) utf8_general_ci NO #
2060
show full columns from t2 from test like 'f%';
2061
Field Type Collation Null Key Default Extra Privileges Comment
2062
fld1 int NULL NO UNI 0 #
2063
fld3 varchar(30) utf8_general_ci NO MUL #
2064
fld4 varchar(35) utf8_general_ci NO #
2065
fld5 varchar(35) utf8_general_ci NO #
2066
fld6 varchar(4) utf8_general_ci NO #
2067
show full columns from t2 from test like 's%';
2068
Field Type Collation Null Key Default Extra Privileges Comment
2070
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
2071
t2 0 PRIMARY 1 auto A # NULL NULL BTREE
2072
t2 0 fld1 1 fld1 A # NULL NULL BTREE
2073
t2 1 fld3 1 fld3 A # NULL NULL BTREE
2074
drop table t4, t3, t2, t1;
2075
CREATE TEMPORARY TABLE t1 (
2076
cont_nr int NOT NULL auto_increment,
2077
ver_nr int NOT NULL default '0',
2078
aufnr int NOT NULL default '0',
2079
username varchar(50) NOT NULL default '',
2080
hdl_nr int NOT NULL default '0',
2082
st_klasse varchar(40) NOT NULL default '',
2083
st_wert varchar(40) NOT NULL default '',
2084
st_zusatz varchar(40) NOT NULL default '',
2085
st_bemerkung varchar(255) NOT NULL default '',
2086
kunden_art varchar(40) NOT NULL default '',
2087
mcbs_knr int default NULL,
2088
mcbs_aufnr int NOT NULL default '0',
2089
schufa_status char(1) default '?',
2092
wf_igz int NOT NULL default '0',
2093
tarifcode varchar(80) default NULL,
2094
recycle char(1) default NULL,
2095
sim varchar(30) default NULL,
2096
mcbs_tpl varchar(30) default NULL,
2097
emp_nr int NOT NULL default '0',
2098
laufzeit int default NULL,
2099
hdl_name varchar(30) default NULL,
2100
prov_hdl_nr int NOT NULL default '0',
2101
auto_wirknetz varchar(50) default NULL,
2102
auto_billing varchar(50) default NULL,
2103
touch timestamp NOT NULL,
2104
kategorie varchar(50) default NULL,
2105
kundentyp varchar(20) NOT NULL default '',
2106
sammel_rech_msisdn varchar(30) NOT NULL default '',
2107
p_nr varchar(9) NOT NULL default '',
2108
suffix char(3) NOT NULL default '',
2109
PRIMARY KEY (cont_nr),
2110
KEY idx_aufnr(aufnr),
2111
KEY idx_hdl_nr(hdl_nr),
2112
KEY idx_st_klasse(st_klasse),
2114
KEY eintrag_idx(eintrag),
2115
KEY emp_nr_idx(emp_nr),
2118
KEY hdl_tag(eintrag,hdl_nr),
2119
KEY prov_hdl_nr(prov_hdl_nr),
2120
KEY mcbs_aufnr(mcbs_aufnr),
2121
KEY kundentyp(kundentyp),
2122
KEY p_nr(p_nr,suffix)
2124
INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2125
INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2126
INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2127
INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2128
INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007');
2129
INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2130
INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2131
SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie;
2133
Privat (Private Nutzung) Mobilfunk
2135
Warning 1052 Column 'kundentyp' in group statement is ambiguous
added
removed
tests/r/pbxt/pool_of_threads.result
