1
drop table if exists t1,t2,t3,t4;
3
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
4
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
6
INSERT INTO t1 VALUES (9410,9412);
17
auto int not null auto_increment,
18
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
19
companynr tinyint(2) unsigned zerofill 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 30 const 1 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 1199 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 1199 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 30 const 1 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 30 const 1 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 30 NULL 2 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 2 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 4 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%";
286
select distinct fld3 from t2 where companynr = 34 order by fld3;
358
select distinct fld3 from t2 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 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;
470
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
484
name char(32) not null,
485
companynr int not null,
491
create temporary table tmp engine = myisam 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
insert into tmp select * from t3;
498
insert into t3 select * from tmp;
499
insert into tmp select * from t3;
500
insert into t3 select * from tmp;
501
insert into tmp select * from t3;
502
insert into t3 select * from tmp;
503
insert into tmp select * from t3;
504
insert into t3 select * from tmp;
505
insert into tmp select * from t3;
506
insert into t3 select * from tmp;
507
insert into tmp select * from t3;
508
insert into t3 select * from tmp;
509
alter table t3 add t2nr int not null auto_increment primary key first;
511
SET SQL_BIG_TABLES=1;
512
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
515
abrogating abrogating
516
admonishing admonishing
521
analyzable analyzable
524
SET SQL_BIG_TABLES=0;
525
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
526
concat(fld3," ",fld3)
528
abrogating abrogating
529
admonishing admonishing
534
analyzable analyzable
537
select distinct fld5 from t2 limit 10;
549
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
561
SET SQL_BIG_TABLES=1;
562
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
574
SET SQL_BIG_TABLES=0;
575
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
576
fld3 repeat("a",length(fld3)) count(*)
580
congresswoman aaaaaaaaaaaaa 1
581
contrition aaaaaaaaaa 1
583
cultivation aaaaaaaaaaa 1
584
definiteness aaaaaaaaaaaa 1
585
demultiplex aaaaaaaaaaa 1
586
disappointing aaaaaaaaaaaaa 1
587
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
588
companynr rtrim(space(512+companynr))
596
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
598
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
599
id select_type table type possible_keys key key_len ref rows Extra
600
1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
601
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
602
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
603
id select_type table type possible_keys key key_len ref rows Extra
604
1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
605
1 SIMPLE t3 ref period period 4 test.t1.period 4181
606
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
607
id select_type table type possible_keys key key_len ref rows Extra
608
1 SIMPLE t3 index period period 4 NULL 1
609
1 SIMPLE t1 ref period period 4 test.t3.period 4181
610
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
611
id select_type table type possible_keys key key_len ref rows Extra
612
1 SIMPLE t1 index period period 4 NULL 1
613
1 SIMPLE t3 ref period period 4 test.t1.period 4181
614
select period from t1;
617
select period from t1 where period=1900;
619
select fld3,period from t1,t2 where fld1 = 011401 order by period;
622
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
625
explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
626
id select_type table type possible_keys key key_len ref rows Extra
627
1 SIMPLE t2 const fld1 fld1 4 const 1
628
1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1
629
select fld3,period from t2,t1 where companynr*10 = 37*10;
809
interrelationships 9410
1009
electroencephalography 9410
1219
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;
1220
fld3 period price price2
1221
admonishing 1002 28357832 8723648
1222
analyzable 1002 28357832 8723648
1223
annihilates 1001 5987435 234724
1224
Antares 1002 28357832 8723648
1225
astound 1001 5987435 234724
1226
audiology 1001 5987435 234724
1227
Augustine 1002 28357832 8723648
1228
Baird 1002 28357832 8723648
1229
bewilderingly 1001 5987435 234724
1230
breaking 1001 5987435 234724
1231
Conley 1001 5987435 234724
1232
dentally 1002 28357832 8723648
1233
dissociate 1002 28357832 8723648
1234
elite 1001 5987435 234724
1235
eschew 1001 5987435 234724
1236
Eulerian 1001 5987435 234724
1237
flanking 1001 5987435 234724
1238
foldout 1002 28357832 8723648
1239
funereal 1002 28357832 8723648
1240
galling 1002 28357832 8723648
1241
Graves 1001 5987435 234724
1242
grazing 1001 5987435 234724
1243
groupings 1001 5987435 234724
1244
handgun 1001 5987435 234724
1245
humility 1002 28357832 8723648
1246
impulsive 1002 28357832 8723648
1247
inch 1001 5987435 234724
1248
intelligibility 1001 5987435 234724
1249
jarring 1001 5987435 234724
1250
lawgiver 1001 5987435 234724
1251
lectured 1002 28357832 8723648
1252
Merritt 1002 28357832 8723648
1253
neonatal 1001 5987435 234724
1254
offload 1002 28357832 8723648
1255
parters 1002 28357832 8723648
1256
pityingly 1002 28357832 8723648
1257
puddings 1002 28357832 8723648
1258
Punjab 1001 5987435 234724
1259
quitter 1002 28357832 8723648
1260
realtor 1001 5987435 234724
1261
relaxing 1001 5987435 234724
1262
repetitions 1001 5987435 234724
1263
resumes 1001 5987435 234724
1264
Romans 1002 28357832 8723648
1265
rusting 1001 5987435 234724
1266
scholastics 1001 5987435 234724
1267
skulking 1002 28357832 8723648
1268
stated 1002 28357832 8723648
1269
suites 1002 28357832 8723648
1270
sureties 1001 5987435 234724
1271
testicle 1002 28357832 8723648
1272
tinily 1002 28357832 8723648
1273
tragedies 1001 5987435 234724
1274
trimmings 1001 5987435 234724
1275
vacuuming 1001 5987435 234724
1276
ventilate 1001 5987435 234724
1277
wallet 1001 5987435 234724
1278
Weissmuller 1002 28357832 8723648
1279
Wotan 1002 28357832 8723648
1280
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;
1281
fld1 fld3 period price price2
1282
018201 relaxing 1001 5987435 234724
1283
018601 vacuuming 1001 5987435 234724
1284
018801 inch 1001 5987435 234724
1285
018811 repetitions 1001 5987435 234724
1287
companynr tinyint(2) unsigned zerofill NOT NULL default '00',
1288
companyname char(30) NOT NULL default '',
1289
PRIMARY KEY (companynr),
1290
UNIQUE KEY companyname(companyname)
1291
) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1292
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1293
companynr companyname
1306
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1307
companynr companyname
1320
select * from t1,t1 t12;
1321
Period Varor_period Period Varor_period
1323
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;
1350
insert into t2 (fld1, companynr) values (999999,99);
1351
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1352
companynr companyname
1354
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1357
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1358
id select_type table type possible_keys key key_len ref rows Extra
1359
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200
1360
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists
1361
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1362
id select_type table type possible_keys key key_len ref rows Extra
1363
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1364
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1365
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1366
companynr companyname
1367
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1370
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1371
id select_type table type possible_keys key key_len ref rows Extra
1372
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1373
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1374
id select_type table type possible_keys key key_len ref rows Extra
1375
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1376
delete from t2 where fld1=999999;
1377
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1378
id select_type table type possible_keys key key_len ref rows Extra
1379
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1380
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1381
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1382
id select_type table type possible_keys key key_len ref rows Extra
1383
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1384
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1385
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1386
id select_type table type possible_keys key key_len ref rows Extra
1387
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1388
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1389
explain select companynr,companyname from t4 left join t2 using (companynr) where 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 12 Using where
1392
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1393
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1394
id select_type table type possible_keys key key_len ref rows Extra
1395
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1396
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1397
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1398
id select_type table type possible_keys key key_len ref rows Extra
1399
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1400
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1401
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1402
id select_type table type possible_keys key key_len ref rows Extra
1403
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1404
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1405
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1406
id select_type table type possible_keys key key_len ref rows Extra
1407
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1408
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1409
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1410
id select_type table type possible_keys key key_len ref rows Extra
1411
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1412
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1413
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1414
id select_type table type possible_keys key key_len ref rows Extra
1415
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1416
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1417
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1418
id select_type table type possible_keys key key_len ref rows Extra
1419
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1420
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1421
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1422
id select_type table type possible_keys key key_len ref rows Extra
1423
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
1424
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1425
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1429
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1430
id select_type table type possible_keys key key_len ref rows Extra
1431
1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary
1432
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
1433
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;
1434
fld1 companynr fld3 period
1435
038008 37 reporters 1008
1436
038208 37 Selfridge 1008
1437
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;
1438
fld1 companynr fld3 period
1439
038008 37 reporters 1008
1440
038208 37 Selfridge 1008
1441
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;
1442
fld1 companynr fld3 period
1443
038008 37 reporters 1008
1444
038208 37 Selfridge 1008
1445
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);
1448
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)));
1451
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;
1457
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1461
select fld1 from t2 where fld1 between 250502 and 250504;
1466
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1477
select count(*) from t1;
1480
select companynr,count(*),sum(fld1) from t2 group by companynr;
1481
companynr count(*) sum(fld1)
1494
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1501
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1502
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1503
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1504
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1505
id select_type table type possible_keys key key_len ref rows filtered Extra
1506
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
1508
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` <> ''))
1509
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1510
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1511
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1512
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1513
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1514
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1515
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1516
37 1 1 5987435 5987435 5987435 5987435.0000
1517
37 2 1 28357832 28357832 28357832 28357832.0000
1518
37 3 1 39654943 39654943 39654943 39654943.0000
1519
37 11 1 5987435 5987435 5987435 5987435.0000
1520
37 12 1 28357832 28357832 28357832 28357832.0000
1521
37 13 1 39654943 39654943 39654943 39654943.0000
1522
37 21 1 5987435 5987435 5987435 5987435.0000
1523
37 22 1 28357832 28357832 28357832 28357832.0000
1524
37 23 1 39654943 39654943 39654943 39654943.0000
1525
37 31 1 5987435 5987435 5987435 5987435.0000
1526
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;
1527
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1528
37 1 1 5987435 5987435 5987435 5987435.0000
1529
37 2 1 28357832 28357832 28357832 28357832.0000
1530
37 3 1 39654943 39654943 39654943 39654943.0000
1531
37 11 1 5987435 5987435 5987435 5987435.0000
1532
37 12 1 28357832 28357832 28357832 28357832.0000
1533
37 13 1 39654943 39654943 39654943 39654943.0000
1534
37 21 1 5987435 5987435 5987435 5987435.0000
1535
37 22 1 28357832 28357832 28357832 28357832.0000
1536
37 23 1 39654943 39654943 39654943 39654943.0000
1537
37 31 1 5987435 5987435 5987435 5987435.0000
1538
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1539
companynr count(price) sum(price) min(price) max(price) avg(price)
1540
37 12543 309394878010 5987435 39654943 24666736.6667
1541
78 8362 414611089292 726498 98439034 49582766.0000
1542
101 4181 3489454238 834598 834598 834598.0000
1543
154 4181 4112197254950 983543950 983543950 983543950.0000
1544
311 4181 979599938 234298 234298 234298.0000
1545
447 4181 9929180954 2374834 2374834 2374834.0000
1546
512 4181 3288532102 786542 786542 786542.0000
1547
select distinct mod(companynr,10) from t4 group by companynr;
1558
select distinct 1 from t4 group by companynr;
1561
select count(distinct fld1) from t2;
1562
count(distinct fld1)
1564
select companynr,count(distinct fld1) from t2 group by companynr;
1565
companynr count(distinct fld1)
1578
select companynr,count(*) from t2 group by companynr;
1592
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1593
companynr count(distinct concat(fld1,repeat(65,1000)))
1606
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1607
companynr count(distinct concat(fld1,repeat(65,200)))
1620
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1621
companynr count(distinct floor(fld1/100))
1634
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1635
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1648
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1651
select name,count(*) from t3 where name='cloakroom' group by name;
1654
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1657
select count(*) from t3 where name='cloakroom' and price2=823742;
1660
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1663
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1672
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1675
select companynr|0,companyname from t4 group by 1;
1676
companynr|0 companyname
1689
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1690
companynr companyname count(*)
1703
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1706
select sum(Period)/count(*) from t1;
1707
sum(Period)/count(*)
1709
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;
1710
companynr count sum diff func
1711
37 12543 309394878010 0.0000 464091
1712
78 8362 414611089292 0.0000 652236
1713
101 4181 3489454238 0.0000 422281
1714
154 4181 4112197254950 0.0000 643874
1715
311 4181 979599938 0.0000 1300291
1716
447 4181 9929180954 0.0000 1868907
1717
512 4181 3288532102 0.0000 2140672
1718
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1721
select companynr,count(*) from t2 group by companynr order by 2 desc;
1735
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1743
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;
1744
fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1745
teethe 000001 1 5987435 5987435 5987435 5987435.0000
1746
dreaded 011401 1 5987435 5987435 5987435 5987435.0000
1747
scholastics 011402 1 28357832 28357832 28357832 28357832.0000
1748
audiology 011403 1 39654943 39654943 39654943 39654943.0000
1749
wallet 011501 1 5987435 5987435 5987435 5987435.0000
1750
parters 011701 1 5987435 5987435 5987435 5987435.0000
1751
eschew 011702 1 28357832 28357832 28357832 28357832.0000
1752
quitter 011703 1 39654943 39654943 39654943 39654943.0000
1753
neat 012001 1 5987435 5987435 5987435 5987435.0000
1754
Steinberg 012003 1 39654943 39654943 39654943 39654943.0000
1755
balled 012301 1 5987435 5987435 5987435 5987435.0000
1756
persist 012302 1 28357832 28357832 28357832 28357832.0000
1757
attainments 012303 1 39654943 39654943 39654943 39654943.0000
1758
capably 012501 1 5987435 5987435 5987435 5987435.0000
1759
impulsive 012602 1 28357832 28357832 28357832 28357832.0000
1760
starlet 012603 1 39654943 39654943 39654943 39654943.0000
1761
featherweight 012701 1 5987435 5987435 5987435 5987435.0000
1762
pessimist 012702 1 28357832 28357832 28357832 28357832.0000
1763
daughter 012703 1 39654943 39654943 39654943 39654943.0000
1764
lawgiver 013601 1 5987435 5987435 5987435 5987435.0000
1765
stated 013602 1 28357832 28357832 28357832 28357832.0000
1766
readable 013603 1 39654943 39654943 39654943 39654943.0000
1767
testicle 013801 1 5987435 5987435 5987435 5987435.0000
1768
Parsifal 013802 1 28357832 28357832 28357832 28357832.0000
1769
leavings 013803 1 39654943 39654943 39654943 39654943.0000
1770
squeaking 013901 1 5987435 5987435 5987435 5987435.0000
1771
contrasted 016001 1 5987435 5987435 5987435 5987435.0000
1772
leftover 016201 1 5987435 5987435 5987435 5987435.0000
1773
whiteners 016202 1 28357832 28357832 28357832 28357832.0000
1774
erases 016301 1 5987435 5987435 5987435 5987435.0000
1775
Punjab 016302 1 28357832 28357832 28357832 28357832.0000
1776
Merritt 016303 1 39654943 39654943 39654943 39654943.0000
1777
sweetish 018001 1 5987435 5987435 5987435 5987435.0000
1778
dogging 018002 1 28357832 28357832 28357832 28357832.0000
1779
scornfully 018003 1 39654943 39654943 39654943 39654943.0000
1780
fetters 018012 1 28357832 28357832 28357832 28357832.0000
1781
bivalves 018013 1 39654943 39654943 39654943 39654943.0000
1782
skulking 018021 1 5987435 5987435 5987435 5987435.0000
1783
flint 018022 1 28357832 28357832 28357832 28357832.0000
1784
flopping 018023 1 39654943 39654943 39654943 39654943.0000
1785
Judas 018032 1 28357832 28357832 28357832 28357832.0000
1786
vacuuming 018033 1 39654943 39654943 39654943 39654943.0000
1787
medical 018041 1 5987435 5987435 5987435 5987435.0000
1788
bloodbath 018042 1 28357832 28357832 28357832 28357832.0000
1789
subschema 018043 1 39654943 39654943 39654943 39654943.0000
1790
interdependent 018051 1 5987435 5987435 5987435 5987435.0000
1791
Graves 018052 1 28357832 28357832 28357832 28357832.0000
1792
neonatal 018053 1 39654943 39654943 39654943 39654943.0000
1793
sorters 018061 1 5987435 5987435 5987435 5987435.0000
1794
epistle 018062 1 28357832 28357832 28357832 28357832.0000
1795
Conley 018101 1 5987435 5987435 5987435 5987435.0000
1796
lectured 018102 1 28357832 28357832 28357832 28357832.0000
1797
Abraham 018103 1 39654943 39654943 39654943 39654943.0000
1798
cage 018201 1 5987435 5987435 5987435 5987435.0000
1799
hushes 018202 1 28357832 28357832 28357832 28357832.0000
1800
Simla 018402 1 28357832 28357832 28357832 28357832.0000
1801
reporters 018403 1 39654943 39654943 39654943 39654943.0000
1802
coexist 018601 1 5987435 5987435 5987435 5987435.0000
1803
Beebe 018602 1 28357832 28357832 28357832 28357832.0000
1804
Taoism 018603 1 39654943 39654943 39654943 39654943.0000
1805
Connally 018801 1 5987435 5987435 5987435 5987435.0000
1806
fetched 018802 1 28357832 28357832 28357832 28357832.0000
1807
checkpoints 018803 1 39654943 39654943 39654943 39654943.0000
1808
gritty 018811 1 5987435 5987435 5987435 5987435.0000
1809
firearm 018812 1 28357832 28357832 28357832 28357832.0000
1810
minima 019101 1 5987435 5987435 5987435 5987435.0000
1811
Selfridge 019102 1 28357832 28357832 28357832 28357832.0000
1812
disable 019103 1 39654943 39654943 39654943 39654943.0000
1813
witchcraft 019201 1 5987435 5987435 5987435 5987435.0000
1814
betroth 030501 1 5987435 5987435 5987435 5987435.0000
1815
Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000
1816
imprint 030503 1 39654943 39654943 39654943 39654943.0000
1817
swelling 031901 1 5987435 5987435 5987435 5987435.0000
1818
interrelationships 036001 1 5987435 5987435 5987435 5987435.0000
1819
riser 036002 1 28357832 28357832 28357832 28357832.0000
1820
bee 038001 1 5987435 5987435 5987435 5987435.0000
1821
kanji 038002 1 28357832 28357832 28357832 28357832.0000
1822
dental 038003 1 39654943 39654943 39654943 39654943.0000
1823
railway 038011 1 5987435 5987435 5987435 5987435.0000
1824
validate 038012 1 28357832 28357832 28357832 28357832.0000
1825
normalizes 038013 1 39654943 39654943 39654943 39654943.0000
1826
Kline 038101 1 5987435 5987435 5987435 5987435.0000
1827
Anatole 038102 1 28357832 28357832 28357832 28357832.0000
1828
partridges 038103 1 39654943 39654943 39654943 39654943.0000
1829
recruited 038201 1 5987435 5987435 5987435 5987435.0000
1830
dimensions 038202 1 28357832 28357832 28357832 28357832.0000
1831
Chicana 038203 1 39654943 39654943 39654943 39654943.0000
1832
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1833
companynr fld3 sum(price)
1838
512 descendants 786542
1841
512 Micronesia 786542
1844
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;
1845
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1846
00 1 Omaha Omaha 5987435 5987435.0000
1847
36 1 dubbed dubbed 28357832 28357832.0000
1848
37 83 Abraham Wotan 1908978016 22999735.1325
1849
50 2 scribbled tapestry 68012775 34006387.5000
1850
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;
1851
t3.companynr+0 t2nr fld3 sum(price)
1853
37 11401 breaking 5987435
1854
37 11402 Romans 28357832
1855
37 11403 intercepted 39654943
1856
37 11501 bewilderingly 5987435
1857
37 11701 astound 5987435
1858
37 11702 admonishing 28357832
1859
37 11703 sumac 39654943
1860
37 12001 flanking 5987435
1861
37 12003 combed 39654943
1862
37 12301 Eulerian 5987435
1863
37 12302 dubbed 28357832
1864
37 12303 Kane 39654943
1865
37 12501 annihilates 5987435
1866
37 12602 Wotan 28357832
1867
37 12603 snatching 39654943
1868
37 12701 grazing 5987435
1869
37 12702 Baird 28357832
1870
37 12703 celery 39654943
1871
37 13601 handgun 5987435
1872
37 13602 foldout 28357832
1873
37 13603 mystic 39654943
1874
37 13801 intelligibility 5987435
1875
37 13802 Augustine 28357832
1876
37 13803 teethe 39654943
1877
37 13901 scholastics 5987435
1878
37 16001 audiology 5987435
1879
37 16201 wallet 5987435
1880
37 16202 parters 28357832
1881
37 16301 eschew 5987435
1882
37 16302 quitter 28357832
1883
37 16303 neat 39654943
1884
37 18001 jarring 5987435
1885
37 18002 tinily 28357832
1886
37 18003 balled 39654943
1887
37 18012 impulsive 28357832
1888
37 18013 starlet 39654943
1889
37 18021 lawgiver 5987435
1890
37 18022 stated 28357832
1891
37 18023 readable 39654943
1892
37 18032 testicle 28357832
1893
37 18033 Parsifal 39654943
1894
37 18041 Punjab 5987435
1895
37 18042 Merritt 28357832
1896
37 18043 Quixotism 39654943
1897
37 18051 sureties 5987435
1898
37 18052 puddings 28357832
1899
37 18053 tapestry 39654943
1900
37 18061 trimmings 5987435
1901
37 18062 humility 28357832
1902
37 18101 tragedies 5987435
1903
37 18102 skulking 28357832
1904
37 18103 flint 39654943
1905
37 18201 relaxing 5987435
1906
37 18202 offload 28357832
1907
37 18402 suites 28357832
1908
37 18403 lists 39654943
1909
37 18601 vacuuming 5987435
1910
37 18602 dentally 28357832
1911
37 18603 humanness 39654943
1912
37 18801 inch 5987435
1913
37 18802 Weissmuller 28357832
1914
37 18803 irresponsibly 39654943
1915
37 18811 repetitions 5987435
1916
37 18812 Antares 28357832
1917
37 19101 ventilate 5987435
1918
37 19102 pityingly 28357832
1919
37 19103 interdependent 39654943
1920
37 19201 Graves 5987435
1921
37 30501 neonatal 5987435
1922
37 30502 scribbled 28357832
1923
37 30503 chafe 39654943
1924
37 31901 realtor 5987435
1925
37 36001 elite 5987435
1926
37 36002 funereal 28357832
1927
37 38001 Conley 5987435
1928
37 38002 lectured 28357832
1929
37 38003 Abraham 39654943
1930
37 38011 groupings 5987435
1931
37 38012 dissociate 28357832
1932
37 38013 coexist 39654943
1933
37 38101 rusting 5987435
1934
37 38102 galling 28357832
1935
37 38103 obliterates 39654943
1936
37 38201 resumes 5987435
1937
37 38202 analyzable 28357832
1938
37 38203 terminator 39654943
1939
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;
1942
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;
1945
explain select fld3 from t2 where 1>2 or 2>3;
1946
id select_type table type possible_keys key key_len ref rows Extra
1947
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1948
explain select fld3 from t2 where fld1=fld1;
1949
id select_type table type possible_keys key key_len ref rows Extra
1950
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1951
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1955
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1959
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1966
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1973
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1974
companynr companyname count(*)
1982
select count(*) from t2;
1985
select count(*) from t2 where fld1 < 098024;
1988
select min(fld1) from t2 where fld1>= 098024;
1991
select max(fld1) from t2 where fld1>= 098024;
1994
select count(*) from t3 where price2=76234234;
1997
select count(*) from t3 where companynr=512 and price2=76234234;
2000
explain select min(fld1),max(fld1),count(*) from t2;
2001
id select_type table type possible_keys key key_len ref rows Extra
2002
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2003
select min(fld1),max(fld1),count(*) from t2;
2004
min(fld1) max(fld1) count(*)
2006
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2009
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2010
count(*) min(t2nr) max(t2nr)
2012
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2034
select max(t2nr) from t3 where price=983543950;
2037
select t1.period from t3 = t1 limit 1;
2040
select t1.period from t1 as t1 limit 1;
2043
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2046
select period as ok_period from t1 limit 1;
2049
select period as ok_period from t1 group by ok_period limit 1;
2052
select 1+1 as summa from t1 group by summa limit 1;
2055
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2064
show tables from test like "s%";
2066
show tables from test like "t?";
2068
show full columns from t2;
2069
Field Type Collation Null Key Default Extra Privileges Comment
2070
auto int(11) NULL NO PRI NULL auto_increment #
2071
fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2072
companynr tinyint(2) unsigned zerofill NULL NO 00 #
2073
fld3 char(30) latin1_swedish_ci NO MUL #
2074
fld4 char(35) latin1_swedish_ci NO #
2075
fld5 char(35) latin1_swedish_ci NO #
2076
fld6 char(4) latin1_swedish_ci NO #
2077
show full columns from t2 from test like 'f%';
2078
Field Type Collation Null Key Default Extra Privileges Comment
2079
fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2080
fld3 char(30) latin1_swedish_ci NO MUL #
2081
fld4 char(35) latin1_swedish_ci NO #
2082
fld5 char(35) latin1_swedish_ci NO #
2083
fld6 char(4) latin1_swedish_ci NO #
2084
show full columns from t2 from test like 's%';
2085
Field Type Collation Null Key Default Extra Privileges Comment
2087
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
2088
t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2089
t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2090
t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2091
drop table t4, t3, t2, t1;
2093
cont_nr int(11) NOT NULL auto_increment,
2094
ver_nr int(11) NOT NULL default '0',
2095
aufnr int(11) NOT NULL default '0',
2096
username varchar(50) NOT NULL default '',
2097
hdl_nr int(11) NOT NULL default '0',
2098
eintrag date NOT NULL default '0000-00-00',
2099
st_klasse varchar(40) NOT NULL default '',
2100
st_wert varchar(40) NOT NULL default '',
2101
st_zusatz varchar(40) NOT NULL default '',
2102
st_bemerkung varchar(255) NOT NULL default '',
2103
kunden_art varchar(40) NOT NULL default '',
2104
mcbs_knr int(11) default NULL,
2105
mcbs_aufnr int(11) NOT NULL default '0',
2106
schufa_status char(1) default '?',
2109
wf_igz int(11) NOT NULL default '0',
2110
tarifcode varchar(80) default NULL,
2111
recycle char(1) default NULL,
2112
sim varchar(30) default NULL,
2113
mcbs_tpl varchar(30) default NULL,
2114
emp_nr int(11) NOT NULL default '0',
2115
laufzeit int(11) default NULL,
2116
hdl_name varchar(30) default NULL,
2117
prov_hdl_nr int(11) NOT NULL default '0',
2118
auto_wirknetz varchar(50) default NULL,
2119
auto_billing varchar(50) default NULL,
2120
touch timestamp NOT NULL,
2121
kategorie varchar(50) default NULL,
2122
kundentyp varchar(20) NOT NULL default '',
2123
sammel_rech_msisdn varchar(30) NOT NULL default '',
2124
p_nr varchar(9) NOT NULL default '',
2125
suffix char(3) NOT NULL default '',
2126
PRIMARY KEY (cont_nr),
2127
KEY idx_aufnr(aufnr),
2128
KEY idx_hdl_nr(hdl_nr),
2129
KEY idx_st_klasse(st_klasse),
2131
KEY eintrag_idx(eintrag),
2132
KEY emp_nr_idx(emp_nr),
2135
KEY hdl_tag(eintrag,hdl_nr),
2136
KEY prov_hdl_nr(prov_hdl_nr),
2137
KEY mcbs_aufnr(mcbs_aufnr),
2138
KEY kundentyp(kundentyp),
2139
KEY p_nr(p_nr,suffix)
2141
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','','','');
2142
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','','','');
2143
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','','','');
2144
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','','','');
2145
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');
2146
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','','','');
2147
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','','','');
2148
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;
2150
Privat (Private Nutzung) Mobilfunk
2152
Warning 1052 Column 'kundentyp' in group statement is ambiguous
added
removed
mysql-test/r/shm.result
