« back to all changes in this revision
Viewing changes to tests/r/pbxt/pool_of_threads.result
- browse files at revision 1457.1.1
- compare with another revision
- compare with revision 2221.1.6
- download diff
- download tarball
- view history from revision 1457.1.1
- Committer: Brian Aker
- Date: 2010-04-08 17:52:00 UTC
- mfrom: (1455.3.1 drizzle)
- mto: (1455.3.3 drizzle)
- mto: This revision was merged to the branch mainline in revision 1462.
- Revision ID: brian@gaz-20100408175200-jje1xrbvn5vu38cj
Merge PBXT.
- files added:
- plugin/pbxt
- plugin/pbxt/bin
- plugin/pbxt/src
- tests/r/pbxt
- files modified:
- drizzled/sql_table.cc
added
removed
tests/r/pbxt/pool_of_threads.result
1
drop table if exists t1,t2,t3,t4;
2
CREATE TABLE t1 (
3
Period int DEFAULT '0000' NOT NULL,
4
Varor_period int DEFAULT '0' NOT NULL
5
);
6
INSERT INTO t1 VALUES (9410,9412);
7
select period from t1;
8
period
9
9410
10
select * from t1;
11
Period Varor_period
12
9410 9412
13
select t1.* from t1;
14
Period Varor_period
15
9410 9412
16
CREATE TABLE t2 (
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,
24
UNIQUE fld1 (fld1),
25
KEY fld3 (fld3),
26
PRIMARY KEY (auto)
27
);
28
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
29
fld3
30
imaginable
31
select fld3 from t2 where fld3 like "%cultivation" ;
32
fld3
33
cultivation
34
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
35
fld3 companynr
36
concoct 58
37
druggists 58
38
engrossing 58
39
Eurydice 58
40
exclaimers 58
41
ferociousness 58
42
hopelessness 58
43
Huey 58
44
imaginable 58
45
judges 58
46
merging 58
47
ostrich 58
48
peering 58
49
Phelps 58
50
presumes 58
51
Ruth 58
52
sentences 58
53
Shylock 58
54
straggled 58
55
synergy 58
56
thanking 58
57
tying 58
58
unlocks 58
59
select fld3,companynr from t2 where companynr = 58 order by fld3;
60
fld3 companynr
61
concoct 58
62
druggists 58
63
engrossing 58
64
Eurydice 58
65
exclaimers 58
66
ferociousness 58
67
hopelessness 58
68
Huey 58
69
imaginable 58
70
judges 58
71
merging 58
72
ostrich 58
73
peering 58
74
Phelps 58
75
presumes 58
76
Ruth 58
77
sentences 58
78
Shylock 58
79
straggled 58
80
synergy 58
81
thanking 58
82
tying 58
83
unlocks 58
84
select fld3 from t2 order by fld3 desc limit 10;
85
fld3
86
youthfulness
87
yelped
88
Wotan
89
workers
90
Witt
91
witchcraft
92
Winsett
93
Willy
94
willed
95
wildcats
96
select fld3 from t2 order by fld3 desc limit 5;
97
fld3
98
youthfulness
99
yelped
100
Wotan
101
workers
102
Witt
103
select fld3 from t2 order by fld3 desc limit 5,5;
104
fld3
105
witchcraft
106
Winsett
107
Willy
108
willed
109
wildcats
110
select t2.fld3 from t2 where fld3 = 'honeysuckle';
111
fld3
112
honeysuckle
113
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
114
fld3
115
honeysuckle
116
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
117
fld3
118
honeysuckle
119
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
120
fld3
121
honeysuckle
122
select t2.fld3 from t2 where fld3 LIKE 'h%le';
123
fld3
124
honeysuckle
125
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
126
fld3
127
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
128
fld3
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;
149
fld3
150
honeysuckle
151
honoring
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;
156
fld1 fld3
157
148504 Colombo
158
68305 Colombo
159
0 nondecreasing
160
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
161
fld1 fld3
162
232605 appendixes
163
1232605 appendixes
164
1232606 appendixes
165
1232607 appendixes
166
1232608 appendixes
167
1232609 appendixes
168
select fld1 from t2 where fld1=250501 or fld1="250502";
169
fld1
170
250501
171
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;
176
fld1
177
250501
178
250502
179
250505
180
250601
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%';
185
fld1 fld3
186
218401 faithful
187
18007 fanatic
188
228311 fated
189
18017 featherweight
190
218022 feed
191
88303 feminine
192
58004 Fenton
193
38017 fetched
194
18054 fetters
195
208101 fiftieth
196
238007 filial
197
13606 fingerings
198
218008 finishers
199
38205 firearm
200
188505 fitting
201
202301 Fitzpatrick
202
238008 fixedly
203
12001 flanking
204
18103 flint
205
18104 flopping
206
188007 flurried
207
13602 foldout
208
226205 foothill
209
232102 forgivably
210
228306 forthcoming
211
186002 freakish
212
208113 freest
213
231315 freezes
214
36002 funereal
215
226209 furnishings
216
198006 furthermore
217
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
218
fld3
219
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
220
fld3
221
Chantilly
222
select fld1,fld3 from t2 where fld1 like "25050%";
223
fld1 fld3
224
250501 poisoning
225
250502 Iraqis
226
250503 heaving
227
250504 population
228
250505 bomb
229
select fld1,fld3 from t2 where fld1 like "25050_";
230
fld1 fld3
231
250501 poisoning
232
250502 Iraqis
233
250503 heaving
234
250504 population
235
250505 bomb
236
select distinct companynr from t2;
237
companynr
238
0
239
37
240
36
241
50
242
58
243
29
244
40
245
53
246
65
247
41
248
34
249
68
250
select distinct companynr from t2 order by companynr;
251
companynr
252
0
253
29
254
34
255
36
256
37
257
40
258
41
259
50
260
53
261
58
262
65
263
68
264
select distinct companynr from t2 order by companynr desc;
265
companynr
266
68
267
65
268
58
269
53
270
50
271
41
272
40
273
37
274
36
275
34
276
29
277
0
278
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%" ORDER BY t2.fld3,period;
279
fld3 period
280
obliterates 9410
281
offload 9410
282
opaquely 9410
283
organizer 9410
284
overestimating 9410
285
overlay 9410
286
select distinct fld3 from t2 where companynr = 34 order by fld3;
287
fld3
288
absentee
289
accessed
290
ahead
291
alphabetic
292
Asiaticizations
293
attitude
294
aye
295
bankruptcies
296
belays
297
Blythe
298
bomb
299
boulevard
300
bulldozes
301
cannot
302
caressing
303
charcoal
304
checksumming
305
chess
306
clubroom
307
colorful
308
cosy
309
creator
310
crying
311
Darius
312
diffusing
313
duality
314
Eiffel
315
Epiphany
316
Ernestine
317
explorers
318
exterminated
319
famine
320
forked
321
Gershwins
322
heaving
323
Hodges
324
Iraqis
325
Italianization
326
Lagos
327
landslide
328
libretto
329
Majorca
330
mastering
331
narrowed
332
occurred
333
offerers
334
Palestine
335
Peruvianizes
336
pharmaceutic
337
poisoning
338
population
339
Pygmalion
340
rats
341
realest
342
recording
343
regimented
344
retransmitting
345
reviver
346
rouses
347
scars
348
sicker
349
sleepwalk
350
stopped
351
sugars
352
translatable
353
uncles
354
unexpected
355
uprisings
356
versatility
357
vest
358
select distinct fld3 from t2 ORDER BY fld3 limit 10;
359
fld3
360
abates
361
abiding
362
Abraham
363
abrogating
364
absentee
365
abut
366
accessed
367
accruing
368
accumulating
369
accuracies
370
select distinct fld3 from t2 having fld3 like "A%" limit 10;
371
fld3
372
abates
373
abiding
374
Abraham
375
abrogating
376
absentee
377
abut
378
accessed
379
accruing
380
accumulating
381
accuracies
382
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
383
substring(fld3,1,3)
384
aba
385
abi
386
Abr
387
abs
388
abu
389
acc
390
acq
391
acu
392
Ade
393
adj
394
Adl
395
adm
396
Ado
397
ads
398
adv
399
aer
400
aff
401
afi
402
afl
403
afo
404
agi
405
ahe
406
aim
407
air
408
Ald
409
alg
410
ali
411
all
412
alp
413
alr
414
ama
415
ame
416
amm
417
ana
418
and
419
ane
420
Ang
421
ani
422
Ann
423
Ant
424
api
425
app
426
aqu
427
Ara
428
arc
429
Arm
430
arr
431
Art
432
Asi
433
ask
434
asp
435
ass
436
ast
437
att
438
aud
439
Aug
440
aut
441
ave
442
avo
443
awe
444
aye
445
Azt
446
select distinct lower(substring(fld3,1,3)) as a from t2 having a like "A%" order by a limit 10;
447
a
448
aba
449
abi
450
abr
451
abs
452
abu
453
acc
454
acq
455
acu
456
ade
457
adj
458
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
459
substring(fld3,1,3)
460
aba
461
abi
462
Abr
463
abs
464
abu
465
acc
466
acq
467
acu
468
Ade
469
adj
470
create table t3 (
471
period int not null,
472
name char(32) not null,
473
companynr int not null,
474
price double(11,0),
475
price2 double(11,0),
476
key (period),
477
key (name)
478
);
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;
498
drop table tmp;
499
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
500
namn
501
Abraham Abraham
502
abrogating abrogating
503
admonishing admonishing
504
Adolph Adolph
505
afield afield
506
aging aging
507
ammonium ammonium
508
analyzable analyzable
509
animals animals
510
animized animized
511
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
512
concat(fld3," ",fld3)
513
Abraham Abraham
514
abrogating abrogating
515
admonishing admonishing
516
Adolph Adolph
517
afield afield
518
aging aging
519
ammonium ammonium
520
analyzable analyzable
521
animals animals
522
animized animized
523
select distinct fld5 from t2 limit 10;
524
fld5
525
neat
526
Steinberg
527
jarring
528
tinily
529
balled
530
persist
531
attainments
532
fanatic
533
measures
534
rightfulness
535
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
536
fld3 count(*)
537
affixed 1
538
and 1
539
annoyers 1
540
Anthony 1
541
assayed 1
542
assurers 1
543
attendants 1
544
bedlam 1
545
bedpost 1
546
boasted 1
547
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
548
fld3 count(*)
549
affixed 1
550
and 1
551
annoyers 1
552
Anthony 1
553
assayed 1
554
assurers 1
555
attendants 1
556
bedlam 1
557
bedpost 1
558
boasted 1
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(*)
561
circus aaaaaa 1
562
cited aaaaa 1
563
Colombo aaaaaaa 1
564
congresswoman aaaaaaaaaaaaa 1
565
contrition aaaaaaaaaa 1
566
corny aaaaa 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))
573
37
574
78
575
101
576
154
577
311
578
447
579
512
580
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
581
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;
599
period
600
9410
601
select period from t1 where period=1900;
602
period
603
select fld3,period from t1,t2 where fld1 = 011401 order by period;
604
fld3 period
605
breaking 9410
606
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
607
fld3 period
608
breaking 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;
614
fld3 period
615
Abraham 9410
616
Aden 9410
617
Adolph 9410
618
Aldrich 9410
619
Alison 9410
620
Anatole 9410
621
Antarctica 9410
622
Antares 9410
623
Arabia 9410
624
Artemia 9410
625
Augustine 9410
626
Baird 9410
627
Beebe 9410
628
Butterfield 9410
629
CERN 9410
630
Cassites 9410
631
Chicana 9410
632
Chippewa 9410
633
Clayton 9410
634
Conley 9410
635
Connally 9410
636
Crays 9410
637
DiMaggio 9410
638
Dutchman 9410
639
Eulerian 9410
640
Evanston 9410
641
Everhart 9410
642
Fenton 9410
643
Fitzpatrick 9410
644
Galatean 9410
645
Gandhian 9410
646
Ganymede 9410
647
Goldstine 9410
648
Gothicism 9410
649
Graves 9410
650
Greenberg 9410
651
Gurkha 9410
652
Hawaii 9410
653
Hegelian 9410
654
Hornblower 9410
655
Huffman 9410
656
Hunter 9410
657
Joplin 9410
658
Judas 9410
659
Kane 9410
660
Kantian 9410
661
Kevin 9410
662
Kinsey 9410
663
Kline 9410
664
Lars 9410
665
Latinizes 9410
666
Lillian 9410
667
Lizzy 9410
668
Majorca 9410
669
Manhattanize 9410
670
McGovern 9410
671
Melinda 9410
672
Merritt 9410
673
Micronesia 9410
674
Miles 9410
675
Miltonism 9410
676
Nabisco 9410
677
Nazis 9410
678
Newtonian 9410
679
Norwalk 9410
680
Pandora 9410
681
Parsifal 9410
682
Peruvian 9410
683
Punjab 9410
684
Pyle 9410
685
Quixotism 9410
686
Romano 9410
687
Romans 9410
688
Sabine 9410
689
Sault 9410
690
Saxony 9410
691
Selfridge 9410
692
Shanghais 9410
693
Simla 9410
694
Simon 9410
695
Stalin 9410
696
Steinberg 9410
697
Taoism 9410
698
Teresa 9410
699
Tipperary 9410
700
Weissmuller 9410
701
Winsett 9410
702
Wotan 9410
703
abates 9410
704
abrogating 9410
705
accessed 9410
706
admiring 9410
707
admonishing 9410
708
afield 9410
709
afore 9410
710
aging 9410
711
airships 9410
712
alike 9410
713
allot 9410
714
already 9410
715
amenities 9410
716
ammonium 9410
717
analogy 9410
718
analyzable 9410
719
animals 9410
720
animized 9410
721
annihilates 9410
722
announced 9410
723
announces 9410
724
apiary 9410
725
appendixes 9410
726
appendixes 9410
727
appendixes 9410
728
appendixes 9410
729
appendixes 9410
730
appendixes 9410
731
arriving 9410
732
arteriole 9410
733
assails 9410
734
astound 9410
735
attainments 9410
736
attrition 9410
737
audiology 9410
738
avenge 9410
739
avoidable 9410
740
babies 9410
741
babysitting 9410
742
balled 9410
743
beaner 9410
744
beaters 9410
745
bee 9410
746
befouled 9410
747
bellow 9410
748
bestseller 9410
749
betroth 9410
750
bewilderingly 9410
751
bills 9410
752
bitterroot 9410
753
bivalves 9410
754
bloater 9410
755
bloodbath 9410
756
boat 9410
757
boom 9410
758
boorish 9410
759
boulder 9410
760
breaking 9410
761
brunch 9410
762
buckboards 9410
763
burlesque 9410
764
cage 9410
765
capably 9410
766
capped 9410
767
cascade 9410
768
causality 9410
769
cautioned 9410
770
ceiling 9410
771
celery 9410
772
certificates 9410
773
chafe 9410
774
chaperone 9410
775
charges 9410
776
chasm 9410
777
checkpoints 9410
778
chewing 9410
779
chews 9410
780
chillingly 9410
781
chronicle 9410
782
ciphers 9410
783
civics 9410
784
clamored 9410
785
clenched 9410
786
clockers 9410
787
coexist 9410
788
cokes 9410
789
combed 9410
790
coming 9410
791
commencements 9410
792
commonplace 9410
793
communicants 9410
794
compartment 9410
795
comprehensive 9410
796
comprised 9410
797
conceptions 9410
798
concludes 9410
799
congregates 9410
800
contrary 9410
801
contrasted 9410
802
convenient 9410
803
convulsion 9410
804
corset 9410
805
count 9410
806
coverings 9410
807
craziness 9410
808
creak 9410
809
creek 9410
810
critiques 9410
811
crunches 9410
812
culled 9410
813
cult 9410
814
cupboard 9410
815
cured 9410
816
cute 9410
817
daughter 9410
818
decliner 9410
819
decomposition 9410
820
deductions 9410
821
dehydrate 9410
822
deludes 9410
823
denizen 9410
824
denotative 9410
825
denounces 9410
826
dental 9410
827
dentally 9410
828
descendants 9410
829
despot 9410
830
destroyer 9410
831
detectably 9410
832
dialysis 9410
833
dimensions 9410
834
disable 9410
835
discounts 9410
836
disentangle 9410
837
disobedience 9410
838
dissociate 9410
839
dogging 9410
840
dopers 9410
841
drains 9410
842
dreaded 9410
843
ducks 9410
844
dusted 9410
845
effortlessly 9410
846
electroencephalography 9410
847
elite 9410
848
embassies 9410
849
employing 9410
850
encompass 9410
851
encompasses 9410
852
environing 9410
853
epistle 9410
854
equilibrium 9410
855
erases 9410
856
error 9410
857
eschew 9410
858
eternal 9410
859
evened 9410
860
evenhandedly 9410
861
eventful 9410
862
excises 9410
863
exclamation 9410
864
excrete 9410
865
exhausts 9410
866
expelled 9410
867
extents 9410
868
externally 9410
869
extracted 9410
870
faithful 9410
871
fanatic 9410
872
fated 9410
873
featherweight 9410
874
feed 9410
875
feminine 9410
876
fetched 9410
877
fetters 9410
878
fiftieth 9410
879
filial 9410
880
fingerings 9410
881
finishers 9410
882
firearm 9410
883
fitting 9410
884
fixedly 9410
885
flanking 9410
886
flint 9410
887
flopping 9410
888
flurried 9410
889
foldout 9410
890
foothill 9410
891
forgivably 9410
892
forthcoming 9410
893
freakish 9410
894
freest 9410
895
freezes 9410
896
funereal 9410
897
furnishings 9410
898
furthermore 9410
899
gadfly 9410
900
gainful 9410
901
galling 9410
902
garage 9410
903
gentleman 9410
904
gifted 9410
905
gleaning 9410
906
glut 9410
907
goblins 9410
908
governing 9410
909
gradually 9410
910
grazing 9410
911
gritty 9410
912
groupings 9410
913
guides 9410
914
guitars 9410
915
handgun 9410
916
handy 9410
917
heiress 9410
918
hoarder 9410
919
honoring 9410
920
hostess 9410
921
humanness 9410
922
humiliation 9410
923
humility 9410
924
hushes 9410
925
husky 9410
926
hypothesizer 9410
927
icon 9410
928
ideas 9410
929
impelling 9410
930
impending 9410
931
imperial 9410
932
imperiously 9410
933
imprint 9410
934
impulsive 9410
935
inaccuracy 9410
936
inch 9410
937
incidentals 9410
938
incorrectly 9410
939
incurring 9410
940
index 9410
941
indulge 9410
942
indulgences 9410
943
ineffective 9410
944
infallibly 9410
945
infest 9410
946
inform 9410
947
inmate 9410
948
insolence 9410
949
instruments 9410
950
intelligibility 9410
951
intentness 9410
952
intercepted 9410
953
interdependent 9410
954
interrelationships 9410
955
interrogate 9410
956
investigations 9410
957
irresponsibly 9410
958
jarring 9410
959
journalizing 9410
960
juveniles 9410
961
kanji 9410
962
kingdom 9410
963
kiting 9410
964
labeled 9410
965
languages 9410
966
laterally 9410
967
lawgiver 9410
968
leaflet 9410
969
leavings 9410
970
lectured 9410
971
leftover 9410
972
lewdly 9410
973
lied 9410
974
linear 9410
975
lists 9410
976
lithograph 9410
977
lore 9410
978
luckily 9410
979
males 9410
980
marginal 9410
981
mastering 9410
982
mayoral 9410
983
meanwhile 9410
984
measures 9410
985
measures 9410
986
mechanizing 9410
987
medical 9410
988
meditation 9410
989
metaphysically 9410
990
mineral 9410
991
miniaturizes 9410
992
minima 9410
993
minion 9410
994
minting 9410
995
misted 9410
996
misunderstander 9410
997
mixture 9410
998
motors 9410
999
mournfulness 9410
1000
multilayer 9410
1001
mumbles 9410
1002
mushrooms 9410
1003
mystic 9410
1004
navies 9410
1005
navigate 9410
1006
neat 9410
1007
neonatal 9410
1008
nested 9410
1009
noncritical 9410
1010
normalizes 9410
1011
obliterates 9410
1012
offload 9410
1013
opaquely 9410
1014
organizer 9410
1015
overestimating 9410
1016
overlay 9410
1017
parametrized 9410
1018
parenthood 9410
1019
parters 9410
1020
participated 9410
1021
partridges 9410
1022
peacock 9410
1023
peeked 9410
1024
pellagra 9410
1025
percentage 9410
1026
percentage 9410
1027
persist 9410
1028
perturb 9410
1029
pessimist 9410
1030
pests 9410
1031
petted 9410
1032
pictures 9410
1033
pithed 9410
1034
pityingly 9410
1035
poison 9410
1036
posed 9410
1037
positioning 9410
1038
postulation 9410
1039
praised 9410
1040
precaution 9410
1041
precipitable 9410
1042
preclude 9410
1043
presentation 9410
1044
pressure 9410
1045
previewing 9410
1046
priceless 9410
1047
primary 9410
1048
psychic 9410
1049
publicly 9410
1050
puddings 9410
1051
quagmire 9410
1052
quitter 9410
1053
railway 9410
1054
raining 9410
1055
rains 9410
1056
ravines 9410
1057
readable 9410
1058
realized 9410
1059
realtor 9410
1060
reassigned 9410
1061
recruited 9410
1062
reduce 9410
1063
regimented 9410
1064
registration 9410
1065
relatively 9410
1066
relaxing 9410
1067
relishing 9410
1068
relives 9410
1069
renew 9410
1070
repelled 9410
1071
repetitions 9410
1072
reporters 9410
1073
reporters 9410
1074
repressions 9410
1075
resplendent 9410
1076
resumes 9410
1077
rifles 9410
1078
rightful 9410
1079
rightfully 9410
1080
rightfulness 9410
1081
ripeness 9410
1082
riser 9410
1083
roped 9410
1084
rudeness 9410
1085
rules 9410
1086
rural 9410
1087
rusting 9410
1088
sadly 9410
1089
sags 9410
1090
sanding 9410
1091
saplings 9410
1092
sating 9410
1093
save 9410
1094
sawtooth 9410
1095
scarf 9410
1096
scatterbrain 9410
1097
scheduling 9410
1098
schemer 9410
1099
scholastics 9410
1100
scornfully 9410
1101
secures 9410
1102
securing 9410
1103
seminaries 9410
1104
serializations 9410
1105
serpents 9410
1106
serving 9410
1107
severely 9410
1108
sews 9410
1109
shapelessly 9410
1110
shipyard 9410
1111
shooter 9410
1112
similarities 9410
1113
skulking 9410
1114
slaughter 9410
1115
sloping 9410
1116
smoothed 9410
1117
snatching 9410
1118
socializes 9410
1119
sophomore 9410
1120
sorters 9410
1121
spatial 9410
1122
specification 9410
1123
specifics 9410
1124
spongers 9410
1125
spools 9410
1126
sportswriting 9410
1127
sporty 9410
1128
squabbled 9410
1129
squeaking 9410
1130
squeezes 9410
1131
stabilizes 9410
1132
stairway 9410
1133
standardizes 9410
1134
star 9410
1135
starlet 9410
1136
stated 9410
1137
stint 9410
1138
stodgy 9410
1139
store 9410
1140
straight 9410
1141
stranglings 9410
1142
subdirectory 9410
1143
subjective 9410
1144
subschema 9410
1145
succumbed 9410
1146
suites 9410
1147
sumac 9410
1148
sureties 9410
1149
swaying 9410
1150
sweetish 9410
1151
swelling 9410
1152
syndicate 9410
1153
taxonomically 9410
1154
techniques 9410
1155
teem 9410
1156
teethe 9410
1157
tempering 9410
1158
terminal 9410
1159
terminator 9410
1160
terminators 9410
1161
test 9410
1162
testicle 9410
1163
textures 9410
1164
theorizers 9410
1165
throttles 9410
1166
tidiness 9410
1167
timesharing 9410
1168
tinily 9410
1169
tinting 9410
1170
title 9410
1171
tragedies 9410
1172
traitor 9410
1173
trimmings 9410
1174
tropics 9410
1175
unaffected 9410
1176
uncovering 9410
1177
undoes 9410
1178
ungrateful 9410
1179
universals 9410
1180
unplug 9410
1181
unruly 9410
1182
untying 9410
1183
unwilling 9410
1184
vacuuming 9410
1185
validate 9410
1186
vanish 9410
1187
ventilate 9410
1188
veranda 9410
1189
vests 9410
1190
wallet 9410
1191
waltz 9410
1192
warm 9410
1193
warningly 9410
1194
watering 9410
1195
weasels 9410
1196
western 9410
1197
whiteners 9410
1198
widens 9410
1199
witchcraft 9410
1200
workers 9410
1201
yelped 9410
1202
youthfulness 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
1278
0 Unknown
1279
29 company 1
1280
34 company 2
1281
36 company 3
1282
37 company 4
1283
40 company 5
1284
41 company 6
1285
50 company 11
1286
53 company 7
1287
58 company 8
1288
65 company 9
1289
68 company 10
1290
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1291
companynr companyname
1292
0 Unknown
1293
29 company 1
1294
34 company 2
1295
36 company 3
1296
37 company 4
1297
40 company 5
1298
41 company 6
1299
50 company 11
1300
53 company 7
1301
58 company 8
1302
65 company 9
1303
68 company 10
1304
select * from t1,t1 t12;
1305
Period Varor_period Period Varor_period
1306
9410 9412 9410 9412
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;
1308
fld1 fld1
1309
250501 250501
1310
250502 250501
1311
250503 250501
1312
250504 250501
1313
250505 250501
1314
250501 250502
1315
250502 250502
1316
250503 250502
1317
250504 250502
1318
250505 250502
1319
250501 250503
1320
250502 250503
1321
250503 250503
1322
250504 250503
1323
250505 250503
1324
250501 250504
1325
250502 250504
1326
250503 250504
1327
250504 250504
1328
250505 250504
1329
250501 250505
1330
250502 250505
1331
250503 250505
1332
250504 250505
1333
250505 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
1337
99 NULL
1338
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1339
count(*)
1340
1199
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;
1352
count(*)
1353
1200
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;
1410
companynr companynr
1411
37 36
1412
41 40
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);
1430
period
1431
9410
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)));
1433
period
1434
9410
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;
1436
fld1
1437
250501
1438
250502
1439
250503
1440
250505
1441
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1442
fld1
1443
250502
1444
250503
1445
select fld1 from t2 where fld1 between 250502 and 250504;
1446
fld1
1447
250502
1448
250503
1449
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%" ;
1451
fld3
1452
label
1453
labeled
1454
labeled
1455
landslide
1456
laterally
1457
leaflet
1458
lewdly
1459
Lillian
1460
luckily
1461
select count(*) from t1;
1462
count(*)
1463
1
1464
select companynr,count(*),sum(fld1) from t2 group by companynr;
1465
companynr count(*) sum(fld1)
1466
0 82 10355753
1467
29 95 14473298
1468
34 70 17788966
1469
36 215 22786296
1470
37 588 83602098
1471
40 37 6618386
1472
41 52 12816335
1473
50 11 1595438
1474
53 4 793210
1475
58 23 2254293
1476
65 10 2284055
1477
68 12 3097288
1478
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1479
companynr count(*)
1480
68 12
1481
65 10
1482
58 23
1483
53 4
1484
50 11
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
1491
Warnings:
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;
1532
mod(companynr,10)
1533
0
1534
9
1535
4
1536
6
1537
7
1538
1
1539
3
1540
8
1541
5
1542
select distinct 1 from t4 group by companynr;
1543
1
1544
1
1545
select count(distinct fld1) from t2;
1546
count(distinct fld1)
1547
1199
1548
select companynr,count(distinct fld1) from t2 group by companynr;
1549
companynr count(distinct fld1)
1550
0 82
1551
29 95
1552
34 70
1553
36 215
1554
37 588
1555
40 37
1556
41 52
1557
50 11
1558
53 4
1559
58 23
1560
65 10
1561
68 12
1562
select companynr,count(*) from t2 group by companynr;
1563
companynr count(*)
1564
0 82
1565
29 95
1566
34 70
1567
36 215
1568
37 588
1569
40 37
1570
41 52
1571
50 11
1572
53 4
1573
58 23
1574
65 10
1575
68 12
1576
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1577
companynr count(distinct concat(fld1,repeat(65,1000)))
1578
0 82
1579
29 95
1580
34 70
1581
36 215
1582
37 588
1583
40 37
1584
41 52
1585
50 11
1586
53 4
1587
58 23
1588
65 10
1589
68 12
1590
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1591
companynr count(distinct concat(fld1,repeat(65,200)))
1592
0 82
1593
29 95
1594
34 70
1595
36 215
1596
37 588
1597
40 37
1598
41 52
1599
50 11
1600
53 4
1601
58 23
1602
65 10
1603
68 12
1604
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1605
companynr count(distinct floor(fld1/100))
1606
0 47
1607
29 35
1608
34 14
1609
36 69
1610
37 108
1611
40 16
1612
41 11
1613
50 9
1614
53 1
1615
58 1
1616
65 1
1617
68 1
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)))
1620
0 47
1621
29 35
1622
34 14
1623
36 69
1624
37 108
1625
40 16
1626
41 11
1627
50 9
1628
53 1
1629
58 1
1630
65 1
1631
68 1
1632
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1633
sum(fld1) fld3
1634
11402 Romans
1635
select name,count(*) from t3 where name='cloakroom' group by name;
1636
name count(*)
1637
cloakroom 4181
1638
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1639
name count(*)
1640
cloakroom 4181
1641
select count(*) from t3 where name='cloakroom' and price2=823742;
1642
count(*)
1643
4181
1644
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1645
name count(*)
1646
cloakroom 4181
1647
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1648
name count(*)
1649
extramarital 4181
1650
gazer 4181
1651
gems 4181
1652
Iranizes 4181
1653
spates 4181
1654
tucked 4181
1655
violinist 4181
1656
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1657
fld3 count(*)
1658
spates 4181
1659
select companynr,companyname from t4 group by 1;
1660
companynr companyname
1661
0 Unknown
1662
29 company 1
1663
34 company 2
1664
36 company 3
1665
37 company 4
1666
40 company 5
1667
41 company 6
1668
50 company 11
1669
53 company 7
1670
58 company 8
1671
65 company 9
1672
68 company 10
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(*)
1675
29 company 1 95
1676
68 company 10 12
1677
50 company 11 11
1678
34 company 2 70
1679
36 company 3 215
1680
37 company 4 588
1681
40 company 5 37
1682
41 company 6 52
1683
53 company 7 4
1684
58 company 8 23
1685
65 company 9 10
1686
0 Unknown 82
1687
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1688
fld1 count(*)
1689
158402 4181
1690
select sum(Period)/count(*) from t1;
1691
sum(Period)/count(*)
1692
9410.0000
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;
1703
companynr avg
1704
154 983543950.0000
1705
select companynr,count(*) from t2 group by companynr order by 2 desc;
1706
companynr count(*)
1707
37 588
1708
36 215
1709
29 95
1710
0 82
1711
34 70
1712
41 52
1713
40 37
1714
58 23
1715
68 12
1716
50 11
1717
65 10
1718
53 4
1719
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1720
companynr count(*)
1721
41 52
1722
58 23
1723
68 12
1724
50 11
1725
65 10
1726
53 4
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)
1818
512 boat 786542
1819
512 capably 786542
1820
512 cupboard 786542
1821
512 decliner 786542
1822
512 descendants 786542
1823
512 dopers 786542
1824
512 erases 786542
1825
512 Micronesia 786542
1826
512 Miles 786542
1827
512 skies 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)
1836
37 1 Omaha 5987435
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;
1924
sum(price)
1925
234298
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;
1927
fld1 sum(price)
1928
38008 234298
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;
1936
companynr fld1
1937
34 250501
1938
34 250502
1939
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1940
companynr fld1
1941
34 250501
1942
34 250502
1943
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1944
companynr count sum
1945
0 82 10355753
1946
29 95 14473298
1947
34 70 17788966
1948
37 588 83602098
1949
41 52 12816335
1950
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1951
companynr
1952
0
1953
29
1954
34
1955
37
1956
41
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(*)
1959
68 company 10 12
1960
50 company 11 11
1961
40 company 5 37
1962
41 company 6 52
1963
53 company 7 4
1964
58 company 8 23
1965
65 company 9 10
1966
select count(*) from t2;
1967
count(*)
1968
1199
1969
select count(*) from t2 where fld1 < 098024;
1970
count(*)
1971
387
1972
select min(fld1) from t2 where fld1>= 098024;
1973
min(fld1)
1974
98024
1975
select max(fld1) from t2 where fld1>= 098024;
1976
max(fld1)
1977
1232609
1978
select count(*) from t3 where price2=76234234;
1979
count(*)
1980
4181
1981
select count(*) from t3 where companynr=512 and price2=76234234;
1982
count(*)
1983
4181
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(*)
1989
0 1232609 1199
1990
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
1991
min(t2nr) max(t2nr)
1992
2115 2115
1993
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
1994
count(*) min(t2nr) max(t2nr)
1995
4181 4 41804
1996
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
1997
t2nr count(*)
1998
9 1
1999
19 1
2000
29 1
2001
39 1
2002
49 1
2003
59 1
2004
69 1
2005
79 1
2006
89 1
2007
99 1
2008
109 1
2009
119 1
2010
129 1
2011
139 1
2012
149 1
2013
159 1
2014
169 1
2015
179 1
2016
189 1
2017
199 1
2018
select max(t2nr) from t3 where price=983543950;
2019
max(t2nr)
2020
41807
2021
select t1.period from t3 = t1 limit 1;
2022
period
2023
1001
2024
select t1.period from t1 as t1 limit 1;
2025
period
2026
9410
2027
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2028
Nuvarande period
2029
9410
2030
select period as ok_period from t1 limit 1;
2031
ok_period
2032
9410
2033
select period as ok_period from t1 group by ok_period limit 1;
2034
ok_period
2035
9410
2036
select 1+1 as summa from t1 group by summa limit 1;
2037
summa
2038
2
2039
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2040
Nuvarande period
2041
9410
2042
show tables;
2043
Tables_in_test
2044
t1
2045
t2
2046
t3
2047
show tables from test like "s%";
2048
Tables_in_test (s%)
2049
show tables from test like "t?";
2050
Tables_in_test (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
2069
show keys from t2;
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',
2081
eintrag date NULL,
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 '?',
2090
bemerkung text,
2091
wirknetz text,
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),
2113
KEY ver_nr(ver_nr),
2114
KEY eintrag_idx(eintrag),
2115
KEY emp_nr_idx(emp_nr),
2116
KEY wf_igz(wf_igz),
2117
KEY touch(touch),
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)
2123
) ENGINE=MyISAM;
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;
2132
Kundentyp kategorie
2133
Privat (Private Nutzung) Mobilfunk
2134
Warnings:
2135
Warning 1052 Column 'kundentyp' in group statement is ambiguous
2136
drop table t1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1