1connect comp_con,localhost,root,,,,,COMPRESS; 2SHOW STATUS LIKE 'Compression'; 3Variable_name Value 4Compression ON 5select * from information_schema.session_status where variable_name= 'COMPRESSION'; 6VARIABLE_NAME VARIABLE_VALUE 7COMPRESSION ON 8drop table if exists t1,t2,t3,t4; 9CREATE TABLE t1 ( 10Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 11Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 12); 13INSERT INTO t1 VALUES (9410,9412); 14select period from t1; 15period 169410 17select * from t1; 18Period Varor_period 199410 9412 20select t1.* from t1; 21Period Varor_period 229410 9412 23CREATE TABLE t2 ( 24auto int not null auto_increment, 25fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 26companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 27fld3 char(30) DEFAULT '' NOT NULL, 28fld4 char(35) DEFAULT '' NOT NULL, 29fld5 char(35) DEFAULT '' NOT NULL, 30fld6 char(4) DEFAULT '' NOT NULL, 31UNIQUE fld1 (fld1), 32KEY fld3 (fld3), 33PRIMARY KEY (auto) 34); 35select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 36fld3 37imaginable 38select fld3 from t2 where fld3 like "%cultivation" ; 39fld3 40cultivation 41select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 42fld3 companynr 43concoct 58 44druggists 58 45engrossing 58 46Eurydice 58 47exclaimers 58 48ferociousness 58 49hopelessness 58 50Huey 58 51imaginable 58 52judges 58 53merging 58 54ostrich 58 55peering 58 56Phelps 58 57presumes 58 58Ruth 58 59sentences 58 60Shylock 58 61straggled 58 62synergy 58 63thanking 58 64tying 58 65unlocks 58 66select fld3,companynr from t2 where companynr = 58 order by fld3; 67fld3 companynr 68concoct 58 69druggists 58 70engrossing 58 71Eurydice 58 72exclaimers 58 73ferociousness 58 74hopelessness 58 75Huey 58 76imaginable 58 77judges 58 78merging 58 79ostrich 58 80peering 58 81Phelps 58 82presumes 58 83Ruth 58 84sentences 58 85Shylock 58 86straggled 58 87synergy 58 88thanking 58 89tying 58 90unlocks 58 91select fld3 from t2 order by fld3 desc limit 10; 92fld3 93youthfulness 94yelped 95Wotan 96workers 97Witt 98witchcraft 99Winsett 100Willy 101willed 102wildcats 103select fld3 from t2 order by fld3 desc limit 5; 104fld3 105youthfulness 106yelped 107Wotan 108workers 109Witt 110select fld3 from t2 order by fld3 desc limit 5,5; 111fld3 112witchcraft 113Winsett 114Willy 115willed 116wildcats 117select t2.fld3 from t2 where fld3 = 'honeysuckle'; 118fld3 119honeysuckle 120select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 121fld3 122honeysuckle 123select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 124fld3 125honeysuckle 126select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 127fld3 128honeysuckle 129select t2.fld3 from t2 where fld3 LIKE 'h%le'; 130fld3 131honeysuckle 132select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 133fld3 134select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 135fld3 136explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 137id select_type table type possible_keys key key_len ref rows Extra 1381 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 139explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 140id select_type table type possible_keys key key_len ref rows Extra 1411 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 142explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 143id select_type table type possible_keys key key_len ref rows Extra 1441 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 145explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 148explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 149id select_type table type possible_keys key key_len ref rows Extra 1501 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 151explain select fld3 from t2 ignore index (fld3,not_used); 152ERROR 42000: Key 'not_used' doesn't exist in table 't2' 153explain select fld3 from t2 use index (not_used); 154ERROR 42000: Key 'not_used' doesn't exist in table 't2' 155select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 156fld3 157honeysuckle 158honoring 159explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 160id select_type table type possible_keys key key_len ref rows Extra 1611 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index 162select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 163fld1 fld3 164148504 Colombo 165068305 Colombo 166000000 nondecreasing 167select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 168fld1 fld3 169232605 appendixes 1701232605 appendixes 1711232606 appendixes 1721232607 appendixes 1731232608 appendixes 1741232609 appendixes 175select fld1 from t2 where fld1=250501 or fld1="250502"; 176fld1 177250501 178250502 179explain select fld1 from t2 where fld1=250501 or fld1="250502"; 180id select_type table type possible_keys key key_len ref rows Extra 1811 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index 182select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 183fld1 184250501 185250502 186250505 187250601 188explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 189id select_type table type possible_keys key key_len ref rows Extra 1901 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index 191select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 192fld1 fld3 193012001 flanking 194013602 foldout 195013606 fingerings 196018007 fanatic 197018017 featherweight 198018054 fetters 199018103 flint 200018104 flopping 201036002 funereal 202038017 fetched 203038205 firearm 204058004 Fenton 205088303 feminine 206186002 freakish 207188007 flurried 208188505 fitting 209198006 furthermore 210202301 Fitzpatrick 211208101 fiftieth 212208113 freest 213218008 finishers 214218022 feed 215218401 faithful 216226205 foothill 217226209 furnishings 218228306 forthcoming 219228311 fated 220231315 freezes 221232102 forgivably 222238007 filial 223238008 fixedly 224select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 225fld3 226select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 227fld3 228Chantilly 229select fld1,fld3 from t2 where fld1 like "25050%"; 230fld1 fld3 231250501 poisoning 232250502 Iraqis 233250503 heaving 234250504 population 235250505 bomb 236select fld1,fld3 from t2 where fld1 like "25050_"; 237fld1 fld3 238250501 poisoning 239250502 Iraqis 240250503 heaving 241250504 population 242250505 bomb 243select distinct companynr from t2; 244companynr 24500 24637 24736 24850 24958 25029 25140 25253 25365 25441 25534 25668 257select distinct companynr from t2 order by companynr; 258companynr 25900 26029 26134 26236 26337 26440 26541 26650 26753 26858 26965 27068 271select distinct companynr from t2 order by companynr desc; 272companynr 27368 27465 27558 27653 27750 27841 27940 28037 28136 28234 28329 28400 285select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 286fld3 period 287obliterates 9410 288offload 9410 289opaquely 9410 290organizer 9410 291overestimating 9410 292overlay 9410 293select distinct fld3 from t2 where companynr = 34 order by fld3; 294fld3 295absentee 296accessed 297ahead 298alphabetic 299Asiaticizations 300attitude 301aye 302bankruptcies 303belays 304Blythe 305bomb 306boulevard 307bulldozes 308cannot 309caressing 310charcoal 311checksumming 312chess 313clubroom 314colorful 315cosy 316creator 317crying 318Darius 319diffusing 320duality 321Eiffel 322Epiphany 323Ernestine 324explorers 325exterminated 326famine 327forked 328Gershwins 329heaving 330Hodges 331Iraqis 332Italianization 333Lagos 334landslide 335libretto 336Majorca 337mastering 338narrowed 339occurred 340offerers 341Palestine 342Peruvianizes 343pharmaceutic 344poisoning 345population 346Pygmalion 347rats 348realest 349recording 350regimented 351retransmitting 352reviver 353rouses 354scars 355sicker 356sleepwalk 357stopped 358sugars 359translatable 360uncles 361unexpected 362uprisings 363versatility 364vest 365select distinct fld3 from t2 limit 10; 366fld3 367abates 368abiding 369Abraham 370abrogating 371absentee 372abut 373accessed 374accruing 375accumulating 376accuracies 377select distinct fld3 from t2 having fld3 like "A%" limit 10; 378fld3 379abates 380abiding 381Abraham 382abrogating 383absentee 384abut 385accessed 386accruing 387accumulating 388accuracies 389select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 390substring(fld3,1,3) 391aba 392abi 393Abr 394abs 395abu 396acc 397acq 398acu 399Ade 400adj 401Adl 402adm 403Ado 404ads 405adv 406aer 407aff 408afi 409afl 410afo 411agi 412ahe 413aim 414air 415Ald 416alg 417ali 418all 419alp 420alr 421ama 422ame 423amm 424ana 425and 426ane 427Ang 428ani 429Ann 430Ant 431api 432app 433aqu 434Ara 435arc 436Arm 437arr 438Art 439Asi 440ask 441asp 442ass 443ast 444att 445aud 446Aug 447aut 448ave 449avo 450awe 451aye 452Azt 453select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 454a 455aba 456abi 457Abr 458abs 459abu 460acc 461acq 462acu 463Ade 464adj 465select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 466substring(fld3,1,3) 467aba 468abi 469Abr 470abs 471abu 472acc 473acq 474acu 475Ade 476adj 477select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 478a 479aba 480abi 481Abr 482abs 483abu 484acc 485acq 486acu 487Ade 488adj 489create table t3 ( 490period int not null, 491name char(32) not null, 492companynr int not null, 493price double(11,0), 494price2 double(11,0), 495key (period), 496key (name) 497); 498create temporary table tmp engine = myisam select * from t3; 499insert into t3 select * from tmp; 500insert into tmp select * from t3; 501insert into t3 select * from tmp; 502insert into tmp select * from t3; 503insert into t3 select * from tmp; 504insert into tmp select * from t3; 505insert into t3 select * from tmp; 506insert into tmp select * from t3; 507insert into t3 select * from tmp; 508insert into tmp select * from t3; 509insert into t3 select * from tmp; 510insert into tmp select * from t3; 511insert into t3 select * from tmp; 512insert into tmp select * from t3; 513insert into t3 select * from tmp; 514insert into tmp select * from t3; 515insert into t3 select * from tmp; 516alter table t3 add t2nr int not null auto_increment primary key first; 517drop table tmp; 518set tmp_memory_table_size=0; 519select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 520namn 521Abraham Abraham 522abrogating abrogating 523admonishing admonishing 524Adolph Adolph 525afield afield 526aging aging 527ammonium ammonium 528analyzable analyzable 529animals animals 530animized animized 531set tmp_memory_table_size=default; 532select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 533concat(fld3," ",fld3) 534Abraham Abraham 535abrogating abrogating 536admonishing admonishing 537Adolph Adolph 538afield afield 539aging aging 540ammonium ammonium 541analyzable analyzable 542animals animals 543animized animized 544select distinct fld5 from t2 limit 10; 545fld5 546neat 547Steinberg 548jarring 549tinily 550balled 551persist 552attainments 553fanatic 554measures 555rightfulness 556select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 557fld3 count(*) 558affixed 1 559and 1 560annoyers 1 561Anthony 1 562assayed 1 563assurers 1 564attendants 1 565bedlam 1 566bedpost 1 567boasted 1 568set tmp_memory_table_size=0; 569select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 570fld3 count(*) 571affixed 1 572and 1 573annoyers 1 574Anthony 1 575assayed 1 576assurers 1 577attendants 1 578bedlam 1 579bedpost 1 580boasted 1 581set tmp_memory_table_size=default; 582select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 583fld3 repeat("a",length(fld3)) count(*) 584circus aaaaaa 1 585cited aaaaa 1 586Colombo aaaaaaa 1 587congresswoman aaaaaaaaaaaaa 1 588contrition aaaaaaaaaa 1 589corny aaaaa 1 590cultivation aaaaaaaaaaa 1 591definiteness aaaaaaaaaaaa 1 592demultiplex aaaaaaaaaaa 1 593disappointing aaaaaaaaaaaaa 1 594select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 595companynr rtrim(space(512+companynr)) 59637 59778 598101 599154 600311 601447 602512 603select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 604fld3 605explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 606id select_type table type possible_keys key key_len ref rows Extra 6071 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort 6081 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index 609explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 610id select_type table type possible_keys key key_len ref rows Extra 6111 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort 6121 SIMPLE t3 ref period period 4 test.t1.period 4181 613explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 614id select_type table type possible_keys key key_len ref rows Extra 6151 SIMPLE t3 index period period 4 NULL 1 6161 SIMPLE t1 ref period period 4 test.t3.period 4181 617explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 618id select_type table type possible_keys key key_len ref rows Extra 6191 SIMPLE t1 index period period 4 NULL 1 6201 SIMPLE t3 ref period period 4 test.t1.period 4181 621select period from t1; 622period 6239410 624select period from t1 where period=1900; 625period 626select fld3,period from t1,t2 where fld1 = 011401 order by period; 627fld3 period 628breaking 9410 629select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 630fld3 period 631breaking 1001 632explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 633id select_type table type possible_keys key key_len ref rows Extra 6341 SIMPLE t2 const fld1 fld1 4 const 1 6351 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 636select fld3,period from t2,t1 where companynr*10 = 37*10; 637fld3 period 638breaking 9410 639Romans 9410 640intercepted 9410 641bewilderingly 9410 642astound 9410 643admonishing 9410 644sumac 9410 645flanking 9410 646combed 9410 647subjective 9410 648scatterbrain 9410 649Eulerian 9410 650Kane 9410 651overlay 9410 652perturb 9410 653goblins 9410 654annihilates 9410 655Wotan 9410 656snatching 9410 657concludes 9410 658laterally 9410 659yelped 9410 660grazing 9410 661Baird 9410 662celery 9410 663misunderstander 9410 664handgun 9410 665foldout 9410 666mystic 9410 667succumbed 9410 668Nabisco 9410 669fingerings 9410 670aging 9410 671afield 9410 672ammonium 9410 673boat 9410 674intelligibility 9410 675Augustine 9410 676teethe 9410 677dreaded 9410 678scholastics 9410 679audiology 9410 680wallet 9410 681parters 9410 682eschew 9410 683quitter 9410 684neat 9410 685Steinberg 9410 686jarring 9410 687tinily 9410 688balled 9410 689persist 9410 690attainments 9410 691fanatic 9410 692measures 9410 693rightfulness 9410 694capably 9410 695impulsive 9410 696starlet 9410 697terminators 9410 698untying 9410 699announces 9410 700featherweight 9410 701pessimist 9410 702daughter 9410 703decliner 9410 704lawgiver 9410 705stated 9410 706readable 9410 707attrition 9410 708cascade 9410 709motors 9410 710interrogate 9410 711pests 9410 712stairway 9410 713dopers 9410 714testicle 9410 715Parsifal 9410 716leavings 9410 717postulation 9410 718squeaking 9410 719contrasted 9410 720leftover 9410 721whiteners 9410 722erases 9410 723Punjab 9410 724Merritt 9410 725Quixotism 9410 726sweetish 9410 727dogging 9410 728scornfully 9410 729bellow 9410 730bills 9410 731cupboard 9410 732sureties 9410 733puddings 9410 734fetters 9410 735bivalves 9410 736incurring 9410 737Adolph 9410 738pithed 9410 739Miles 9410 740trimmings 9410 741tragedies 9410 742skulking 9410 743flint 9410 744flopping 9410 745relaxing 9410 746offload 9410 747suites 9410 748lists 9410 749animized 9410 750multilayer 9410 751standardizes 9410 752Judas 9410 753vacuuming 9410 754dentally 9410 755humanness 9410 756inch 9410 757Weissmuller 9410 758irresponsibly 9410 759luckily 9410 760culled 9410 761medical 9410 762bloodbath 9410 763subschema 9410 764animals 9410 765Micronesia 9410 766repetitions 9410 767Antares 9410 768ventilate 9410 769pityingly 9410 770interdependent 9410 771Graves 9410 772neonatal 9410 773chafe 9410 774honoring 9410 775realtor 9410 776elite 9410 777funereal 9410 778abrogating 9410 779sorters 9410 780Conley 9410 781lectured 9410 782Abraham 9410 783Hawaii 9410 784cage 9410 785hushes 9410 786Simla 9410 787reporters 9410 788Dutchman 9410 789descendants 9410 790groupings 9410 791dissociate 9410 792coexist 9410 793Beebe 9410 794Taoism 9410 795Connally 9410 796fetched 9410 797checkpoints 9410 798rusting 9410 799galling 9410 800obliterates 9410 801traitor 9410 802resumes 9410 803analyzable 9410 804terminator 9410 805gritty 9410 806firearm 9410 807minima 9410 808Selfridge 9410 809disable 9410 810witchcraft 9410 811betroth 9410 812Manhattanize 9410 813imprint 9410 814peeked 9410 815swelling 9410 816interrelationships 9410 817riser 9410 818Gandhian 9410 819peacock 9410 820bee 9410 821kanji 9410 822dental 9410 823scarf 9410 824chasm 9410 825insolence 9410 826syndicate 9410 827alike 9410 828imperial 9410 829convulsion 9410 830railway 9410 831validate 9410 832normalizes 9410 833comprehensive 9410 834chewing 9410 835denizen 9410 836schemer 9410 837chronicle 9410 838Kline 9410 839Anatole 9410 840partridges 9410 841brunch 9410 842recruited 9410 843dimensions 9410 844Chicana 9410 845announced 9410 846praised 9410 847employing 9410 848linear 9410 849quagmire 9410 850western 9410 851relishing 9410 852serving 9410 853scheduling 9410 854lore 9410 855eventful 9410 856arteriole 9410 857disentangle 9410 858cured 9410 859Fenton 9410 860avoidable 9410 861drains 9410 862detectably 9410 863husky 9410 864impelling 9410 865undoes 9410 866evened 9410 867squeezes 9410 868destroyer 9410 869rudeness 9410 870beaner 9410 871boorish 9410 872Everhart 9410 873encompass 9410 874mushrooms 9410 875Alison 9410 876externally 9410 877pellagra 9410 878cult 9410 879creek 9410 880Huffman 9410 881Majorca 9410 882governing 9410 883gadfly 9410 884reassigned 9410 885intentness 9410 886craziness 9410 887psychic 9410 888squabbled 9410 889burlesque 9410 890capped 9410 891extracted 9410 892DiMaggio 9410 893exclamation 9410 894subdirectory 9410 895Gothicism 9410 896feminine 9410 897metaphysically 9410 898sanding 9410 899Miltonism 9410 900freakish 9410 901index 9410 902straight 9410 903flurried 9410 904denotative 9410 905coming 9410 906commencements 9410 907gentleman 9410 908gifted 9410 909Shanghais 9410 910sportswriting 9410 911sloping 9410 912navies 9410 913leaflet 9410 914shooter 9410 915Joplin 9410 916babies 9410 917assails 9410 918admiring 9410 919swaying 9410 920Goldstine 9410 921fitting 9410 922Norwalk 9410 923analogy 9410 924deludes 9410 925cokes 9410 926Clayton 9410 927exhausts 9410 928causality 9410 929sating 9410 930icon 9410 931throttles 9410 932communicants 9410 933dehydrate 9410 934priceless 9410 935publicly 9410 936incidentals 9410 937commonplace 9410 938mumbles 9410 939furthermore 9410 940cautioned 9410 941parametrized 9410 942registration 9410 943sadly 9410 944positioning 9410 945babysitting 9410 946eternal 9410 947hoarder 9410 948congregates 9410 949rains 9410 950workers 9410 951sags 9410 952unplug 9410 953garage 9410 954boulder 9410 955specifics 9410 956Teresa 9410 957Winsett 9410 958convenient 9410 959buckboards 9410 960amenities 9410 961resplendent 9410 962sews 9410 963participated 9410 964Simon 9410 965certificates 9410 966Fitzpatrick 9410 967Evanston 9410 968misted 9410 969textures 9410 970save 9410 971count 9410 972rightful 9410 973chaperone 9410 974Lizzy 9410 975clenched 9410 976effortlessly 9410 977accessed 9410 978beaters 9410 979Hornblower 9410 980vests 9410 981indulgences 9410 982infallibly 9410 983unwilling 9410 984excrete 9410 985spools 9410 986crunches 9410 987overestimating 9410 988ineffective 9410 989humiliation 9410 990sophomore 9410 991star 9410 992rifles 9410 993dialysis 9410 994arriving 9410 995indulge 9410 996clockers 9410 997languages 9410 998Antarctica 9410 999percentage 9410 1000ceiling 9410 1001specification 9410 1002regimented 9410 1003ciphers 9410 1004pictures 9410 1005serpents 9410 1006allot 9410 1007realized 9410 1008mayoral 9410 1009opaquely 9410 1010hostess 9410 1011fiftieth 9410 1012incorrectly 9410 1013decomposition 9410 1014stranglings 9410 1015mixture 9410 1016electroencephalography 9410 1017similarities 9410 1018charges 9410 1019freest 9410 1020Greenberg 9410 1021tinting 9410 1022expelled 9410 1023warm 9410 1024smoothed 9410 1025deductions 9410 1026Romano 9410 1027bitterroot 9410 1028corset 9410 1029securing 9410 1030environing 9410 1031cute 9410 1032Crays 9410 1033heiress 9410 1034inform 9410 1035avenge 9410 1036universals 9410 1037Kinsey 9410 1038ravines 9410 1039bestseller 9410 1040equilibrium 9410 1041extents 9410 1042relatively 9410 1043pressure 9410 1044critiques 9410 1045befouled 9410 1046rightfully 9410 1047mechanizing 9410 1048Latinizes 9410 1049timesharing 9410 1050Aden 9410 1051embassies 9410 1052males 9410 1053shapelessly 9410 1054mastering 9410 1055Newtonian 9410 1056finishers 9410 1057abates 9410 1058teem 9410 1059kiting 9410 1060stodgy 9410 1061feed 9410 1062guitars 9410 1063airships 9410 1064store 9410 1065denounces 9410 1066Pyle 9410 1067Saxony 9410 1068serializations 9410 1069Peruvian 9410 1070taxonomically 9410 1071kingdom 9410 1072stint 9410 1073Sault 9410 1074faithful 9410 1075Ganymede 9410 1076tidiness 9410 1077gainful 9410 1078contrary 9410 1079Tipperary 9410 1080tropics 9410 1081theorizers 9410 1082renew 9410 1083already 9410 1084terminal 9410 1085Hegelian 9410 1086hypothesizer 9410 1087warningly 9410 1088journalizing 9410 1089nested 9410 1090Lars 9410 1091saplings 9410 1092foothill 9410 1093labeled 9410 1094imperiously 9410 1095reporters 9410 1096furnishings 9410 1097precipitable 9410 1098discounts 9410 1099excises 9410 1100Stalin 9410 1101despot 9410 1102ripeness 9410 1103Arabia 9410 1104unruly 9410 1105mournfulness 9410 1106boom 9410 1107slaughter 9410 1108Sabine 9410 1109handy 9410 1110rural 9410 1111organizer 9410 1112shipyard 9410 1113civics 9410 1114inaccuracy 9410 1115rules 9410 1116juveniles 9410 1117comprised 9410 1118investigations 9410 1119stabilizes 9410 1120seminaries 9410 1121Hunter 9410 1122sporty 9410 1123test 9410 1124weasels 9410 1125CERN 9410 1126tempering 9410 1127afore 9410 1128Galatean 9410 1129techniques 9410 1130error 9410 1131veranda 9410 1132severely 9410 1133Cassites 9410 1134forthcoming 9410 1135guides 9410 1136vanish 9410 1137lied 9410 1138sawtooth 9410 1139fated 9410 1140gradually 9410 1141widens 9410 1142preclude 9410 1143evenhandedly 9410 1144percentage 9410 1145disobedience 9410 1146humility 9410 1147gleaning 9410 1148petted 9410 1149bloater 9410 1150minion 9410 1151marginal 9410 1152apiary 9410 1153measures 9410 1154precaution 9410 1155repelled 9410 1156primary 9410 1157coverings 9410 1158Artemia 9410 1159navigate 9410 1160spatial 9410 1161Gurkha 9410 1162meanwhile 9410 1163Melinda 9410 1164Butterfield 9410 1165Aldrich 9410 1166previewing 9410 1167glut 9410 1168unaffected 9410 1169inmate 9410 1170mineral 9410 1171impending 9410 1172meditation 9410 1173ideas 9410 1174miniaturizes 9410 1175lewdly 9410 1176title 9410 1177youthfulness 9410 1178creak 9410 1179Chippewa 9410 1180clamored 9410 1181freezes 9410 1182forgivably 9410 1183reduce 9410 1184McGovern 9410 1185Nazis 9410 1186epistle 9410 1187socializes 9410 1188conceptions 9410 1189Kevin 9410 1190uncovering 9410 1191chews 9410 1192appendixes 9410 1193appendixes 9410 1194appendixes 9410 1195appendixes 9410 1196appendixes 9410 1197appendixes 9410 1198raining 9410 1199infest 9410 1200compartment 9410 1201minting 9410 1202ducks 9410 1203roped 9410 1204waltz 9410 1205Lillian 9410 1206repressions 9410 1207chillingly 9410 1208noncritical 9410 1209lithograph 9410 1210spongers 9410 1211parenthood 9410 1212posed 9410 1213instruments 9410 1214filial 9410 1215fixedly 9410 1216relives 9410 1217Pandora 9410 1218watering 9410 1219ungrateful 9410 1220secures 9410 1221poison 9410 1222dusted 9410 1223encompasses 9410 1224presentation 9410 1225Kantian 9410 1226select 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; 1227fld3 period price price2 1228admonishing 1002 28357832 8723648 1229analyzable 1002 28357832 8723648 1230annihilates 1001 5987435 234724 1231Antares 1002 28357832 8723648 1232astound 1001 5987435 234724 1233audiology 1001 5987435 234724 1234Augustine 1002 28357832 8723648 1235Baird 1002 28357832 8723648 1236bewilderingly 1001 5987435 234724 1237breaking 1001 5987435 234724 1238Conley 1001 5987435 234724 1239dentally 1002 28357832 8723648 1240dissociate 1002 28357832 8723648 1241elite 1001 5987435 234724 1242eschew 1001 5987435 234724 1243Eulerian 1001 5987435 234724 1244flanking 1001 5987435 234724 1245foldout 1002 28357832 8723648 1246funereal 1002 28357832 8723648 1247galling 1002 28357832 8723648 1248Graves 1001 5987435 234724 1249grazing 1001 5987435 234724 1250groupings 1001 5987435 234724 1251handgun 1001 5987435 234724 1252humility 1002 28357832 8723648 1253impulsive 1002 28357832 8723648 1254inch 1001 5987435 234724 1255intelligibility 1001 5987435 234724 1256jarring 1001 5987435 234724 1257lawgiver 1001 5987435 234724 1258lectured 1002 28357832 8723648 1259Merritt 1002 28357832 8723648 1260neonatal 1001 5987435 234724 1261offload 1002 28357832 8723648 1262parters 1002 28357832 8723648 1263pityingly 1002 28357832 8723648 1264puddings 1002 28357832 8723648 1265Punjab 1001 5987435 234724 1266quitter 1002 28357832 8723648 1267realtor 1001 5987435 234724 1268relaxing 1001 5987435 234724 1269repetitions 1001 5987435 234724 1270resumes 1001 5987435 234724 1271Romans 1002 28357832 8723648 1272rusting 1001 5987435 234724 1273scholastics 1001 5987435 234724 1274skulking 1002 28357832 8723648 1275stated 1002 28357832 8723648 1276suites 1002 28357832 8723648 1277sureties 1001 5987435 234724 1278testicle 1002 28357832 8723648 1279tinily 1002 28357832 8723648 1280tragedies 1001 5987435 234724 1281trimmings 1001 5987435 234724 1282vacuuming 1001 5987435 234724 1283ventilate 1001 5987435 234724 1284wallet 1001 5987435 234724 1285Weissmuller 1002 28357832 8723648 1286Wotan 1002 28357832 8723648 1287select 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; 1288fld1 fld3 period price price2 1289018201 relaxing 1001 5987435 234724 1290018601 vacuuming 1001 5987435 234724 1291018801 inch 1001 5987435 234724 1292018811 repetitions 1001 5987435 234724 1293create table t4 ( 1294companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1295companyname char(30) NOT NULL default '', 1296PRIMARY KEY (companynr), 1297UNIQUE KEY companyname(companyname) 1298) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1299select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1300companynr companyname 130100 Unknown 130229 company 1 130334 company 2 130436 company 3 130537 company 4 130640 company 5 130741 company 6 130850 company 11 130953 company 7 131058 company 8 131165 company 9 131268 company 10 1313select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1314companynr companyname 131500 Unknown 131629 company 1 131734 company 2 131836 company 3 131937 company 4 132040 company 5 132141 company 6 132250 company 11 132353 company 7 132458 company 8 132565 company 9 132668 company 10 1327select * from t1,t1 t12; 1328Period Varor_period Period Varor_period 13299410 9412 9410 9412 1330select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1331fld1 fld1 1332250501 250501 1333250502 250501 1334250503 250501 1335250504 250501 1336250505 250501 1337250501 250502 1338250502 250502 1339250503 250502 1340250504 250502 1341250505 250502 1342250501 250503 1343250502 250503 1344250503 250503 1345250504 250503 1346250505 250503 1347250501 250504 1348250502 250504 1349250503 250504 1350250504 250504 1351250505 250504 1352250501 250505 1353250502 250505 1354250503 250505 1355250504 250505 1356250505 250505 1357SET @local_optimizer_switch=@@optimizer_switch; 1358set @local_join_cache_level=@@join_cache_level; 1359set @@join_cache_level=2; 1360set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; 1361insert into t2 (fld1, companynr) values (999999,99); 1362select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1363companynr companyname 136499 NULL 1365select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1366count(*) 13671199 1368explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1369id select_type table type possible_keys key key_len ref rows Extra 13701 SIMPLE t2 ALL NULL NULL NULL NULL 1200 13711 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists 1372explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1373id select_type table type possible_keys key key_len ref rows Extra 13741 SIMPLE t4 ALL NULL NULL NULL NULL 12 13751 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists 1376select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1377companynr companyname 1378select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1379count(*) 13801200 1381explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1382id select_type table type possible_keys key key_len ref rows Extra 13831 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1384explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1385id select_type table type possible_keys key key_len ref rows Extra 13861 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1387delete from t2 where fld1=999999; 1388explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1389id select_type table type possible_keys key key_len ref rows Extra 13901 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13911 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 1392explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1393id select_type table type possible_keys key key_len ref rows Extra 13941 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13951 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 1396explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1397id select_type table type possible_keys key key_len ref rows Extra 13981 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13991 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 1400explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1401id select_type table type possible_keys key key_len ref rows Extra 14021 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14031 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1404explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1405id select_type table type possible_keys key key_len ref rows Extra 14061 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14071 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1408explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1409id select_type table type possible_keys key key_len ref rows Extra 14101 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14111 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1412explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1413id select_type table type possible_keys key key_len ref rows Extra 14141 SIMPLE t4 ALL NULL NULL NULL NULL 12 14151 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1416explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1417id select_type table type possible_keys key key_len ref rows Extra 14181 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 14191 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1420explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1421id select_type table type possible_keys key key_len ref rows Extra 14221 SIMPLE t4 ALL NULL NULL NULL NULL 12 14231 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1424explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1425id select_type table type possible_keys key key_len ref rows Extra 14261 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14271 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1428explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1429id select_type table type possible_keys key key_len ref rows Extra 14301 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14311 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1432explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1433id select_type table type possible_keys key key_len ref rows Extra 14341 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 14351 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 1436SET @@optimizer_switch=@local_optimizer_switch; 1437select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1438companynr companynr 143937 36 144041 40 1441explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1442id select_type table type possible_keys key key_len ref rows Extra 14431 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 14441 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) 1445SET @@join_cache_level=@local_join_cache_level; 1446select 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; 1447fld1 companynr fld3 period 1448038008 37 reporters 1008 1449038208 37 Selfridge 1008 1450select 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; 1451fld1 companynr fld3 period 1452038008 37 reporters 1008 1453038208 37 Selfridge 1008 1454select 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; 1455fld1 companynr fld3 period 1456038008 37 reporters 1008 1457038208 37 Selfridge 1008 1458select 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); 1459period 14609410 1461select 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))); 1462period 14639410 1464select 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; 1465fld1 1466250501 1467250502 1468250503 1469250505 1470select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1471fld1 1472250502 1473250503 1474select fld1 from t2 where fld1 between 250502 and 250504; 1475fld1 1476250502 1477250503 1478250504 1479select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1480fld3 1481label 1482labeled 1483labeled 1484landslide 1485laterally 1486leaflet 1487lewdly 1488Lillian 1489luckily 1490select count(*) from t1; 1491count(*) 14921 1493select companynr,count(*),sum(fld1) from t2 group by companynr; 1494companynr count(*) sum(fld1) 149500 82 10355753 149629 95 14473298 149734 70 17788966 149836 215 22786296 149937 588 83602098 150040 37 6618386 150141 52 12816335 150250 11 1595438 150353 4 793210 150458 23 2254293 150565 10 2284055 150668 12 3097288 1507select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1508companynr count(*) 150968 12 151065 10 151158 23 151253 4 151350 11 1514select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1515count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 151670 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 1517explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1518id select_type table type possible_keys key key_len ref rows filtered Extra 15191 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where 1520Warnings: 1521Note 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` <> '' 1522select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1523companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 152400 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 152529 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026 152634 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 1527select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1528companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 152937 1 1 5987435 5987435 5987435 5987435.0000 153037 2 1 28357832 28357832 28357832 28357832.0000 153137 3 1 39654943 39654943 39654943 39654943.0000 153237 11 1 5987435 5987435 5987435 5987435.0000 153337 12 1 28357832 28357832 28357832 28357832.0000 153437 13 1 39654943 39654943 39654943 39654943.0000 153537 21 1 5987435 5987435 5987435 5987435.0000 153637 22 1 28357832 28357832 28357832 28357832.0000 153737 23 1 39654943 39654943 39654943 39654943.0000 153837 31 1 5987435 5987435 5987435 5987435.0000 1539select /*! 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; 1540companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 154137 1 1 5987435 5987435 5987435 5987435.0000 154237 2 1 28357832 28357832 28357832 28357832.0000 154337 3 1 39654943 39654943 39654943 39654943.0000 154437 11 1 5987435 5987435 5987435 5987435.0000 154537 12 1 28357832 28357832 28357832 28357832.0000 154637 13 1 39654943 39654943 39654943 39654943.0000 154737 21 1 5987435 5987435 5987435 5987435.0000 154837 22 1 28357832 28357832 28357832 28357832.0000 154937 23 1 39654943 39654943 39654943 39654943.0000 155037 31 1 5987435 5987435 5987435 5987435.0000 1551select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1552companynr count(price) sum(price) min(price) max(price) avg(price) 155337 12543 309394878010 5987435 39654943 24666736.6667 155478 8362 414611089292 726498 98439034 49582766.0000 1555101 4181 3489454238 834598 834598 834598.0000 1556154 4181 4112197254950 983543950 983543950 983543950.0000 1557311 4181 979599938 234298 234298 234298.0000 1558447 4181 9929180954 2374834 2374834 2374834.0000 1559512 4181 3288532102 786542 786542 786542.0000 1560select distinct mod(companynr,10) from t4 group by companynr; 1561mod(companynr,10) 15620 15639 15644 15656 15667 15671 15683 15698 15705 1571select distinct 1 from t4 group by companynr; 15721 15731 1574select count(distinct fld1) from t2; 1575count(distinct fld1) 15761199 1577select companynr,count(distinct fld1) from t2 group by companynr; 1578companynr count(distinct fld1) 157900 82 158029 95 158134 70 158236 215 158337 588 158440 37 158541 52 158650 11 158753 4 158858 23 158965 10 159068 12 1591select companynr,count(*) from t2 group by companynr; 1592companynr count(*) 159300 82 159429 95 159534 70 159636 215 159737 588 159840 37 159941 52 160050 11 160153 4 160258 23 160365 10 160468 12 1605select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1606companynr count(distinct concat(fld1,repeat(65,1000))) 160700 82 160829 95 160934 70 161036 215 161137 588 161240 37 161341 52 161450 11 161553 4 161658 23 161765 10 161868 12 1619select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1620companynr count(distinct concat(fld1,repeat(65,200))) 162100 82 162229 95 162334 70 162436 215 162537 588 162640 37 162741 52 162850 11 162953 4 163058 23 163165 10 163268 12 1633select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1634companynr count(distinct floor(fld1/100)) 163500 47 163629 35 163734 14 163836 69 163937 108 164040 16 164141 11 164250 9 164353 1 164458 1 164565 1 164668 1 1647select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1648companynr count(distinct concat(repeat(65,1000),floor(fld1/100))) 164900 47 165029 35 165134 14 165236 69 165337 108 165440 16 165541 11 165650 9 165753 1 165858 1 165965 1 166068 1 1661select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1662sum(fld1) fld3 166311402 Romans 1664select name,count(*) from t3 where name='cloakroom' group by name; 1665name count(*) 1666cloakroom 4181 1667select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1668name count(*) 1669cloakroom 4181 1670select count(*) from t3 where name='cloakroom' and price2=823742; 1671count(*) 16724181 1673select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1674name count(*) 1675cloakroom 4181 1676select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1677name count(*) 1678extramarital 4181 1679gazer 4181 1680gems 4181 1681Iranizes 4181 1682spates 4181 1683tucked 4181 1684violinist 4181 1685select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1686fld3 count(*) 1687spates 4181 1688select companynr|0,companyname from t4 group by 1; 1689companynr|0 companyname 16900 Unknown 169129 company 1 169234 company 2 169336 company 3 169437 company 4 169540 company 5 169641 company 6 169750 company 11 169853 company 7 169958 company 8 170065 company 9 170168 company 10 1702select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1703companynr companyname count(*) 170429 company 1 95 170568 company 10 12 170650 company 11 11 170734 company 2 70 170836 company 3 215 170937 company 4 588 171040 company 5 37 171141 company 6 52 171253 company 7 4 171358 company 8 23 171465 company 9 10 171500 Unknown 82 1716select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1717fld1 count(*) 1718158402 4181 1719select sum(Period)/count(*) from t1; 1720sum(Period)/count(*) 17219410.0000 1722select 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; 1723companynr count sum diff func 172437 12543 309394878010 0.0000 464091 172578 8362 414611089292 0.0000 652236 1726101 4181 3489454238 0.0000 422281 1727154 4181 4112197254950 0.0000 643874 1728311 4181 979599938 0.0000 1300291 1729447 4181 9929180954 0.0000 1868907 1730512 4181 3288532102 0.0000 2140672 1731select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1732companynr avg 1733154 983543950.0000 1734select companynr,count(*) from t2 group by companynr order by 2 desc; 1735companynr count(*) 173637 588 173736 215 173829 95 173900 82 174034 70 174141 52 174240 37 174358 23 174468 12 174550 11 174665 10 174753 4 1748select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1749companynr count(*) 175041 52 175158 23 175268 12 175350 11 175465 10 175553 4 1756select 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; 1757fld4 fld1 count(price) sum(price) min(price) max(price) avg(price) 1758teethe 000001 1 5987435 5987435 5987435 5987435.0000 1759dreaded 011401 1 5987435 5987435 5987435 5987435.0000 1760scholastics 011402 1 28357832 28357832 28357832 28357832.0000 1761audiology 011403 1 39654943 39654943 39654943 39654943.0000 1762wallet 011501 1 5987435 5987435 5987435 5987435.0000 1763parters 011701 1 5987435 5987435 5987435 5987435.0000 1764eschew 011702 1 28357832 28357832 28357832 28357832.0000 1765quitter 011703 1 39654943 39654943 39654943 39654943.0000 1766neat 012001 1 5987435 5987435 5987435 5987435.0000 1767Steinberg 012003 1 39654943 39654943 39654943 39654943.0000 1768balled 012301 1 5987435 5987435 5987435 5987435.0000 1769persist 012302 1 28357832 28357832 28357832 28357832.0000 1770attainments 012303 1 39654943 39654943 39654943 39654943.0000 1771capably 012501 1 5987435 5987435 5987435 5987435.0000 1772impulsive 012602 1 28357832 28357832 28357832 28357832.0000 1773starlet 012603 1 39654943 39654943 39654943 39654943.0000 1774featherweight 012701 1 5987435 5987435 5987435 5987435.0000 1775pessimist 012702 1 28357832 28357832 28357832 28357832.0000 1776daughter 012703 1 39654943 39654943 39654943 39654943.0000 1777lawgiver 013601 1 5987435 5987435 5987435 5987435.0000 1778stated 013602 1 28357832 28357832 28357832 28357832.0000 1779readable 013603 1 39654943 39654943 39654943 39654943.0000 1780testicle 013801 1 5987435 5987435 5987435 5987435.0000 1781Parsifal 013802 1 28357832 28357832 28357832 28357832.0000 1782leavings 013803 1 39654943 39654943 39654943 39654943.0000 1783squeaking 013901 1 5987435 5987435 5987435 5987435.0000 1784contrasted 016001 1 5987435 5987435 5987435 5987435.0000 1785leftover 016201 1 5987435 5987435 5987435 5987435.0000 1786whiteners 016202 1 28357832 28357832 28357832 28357832.0000 1787erases 016301 1 5987435 5987435 5987435 5987435.0000 1788Punjab 016302 1 28357832 28357832 28357832 28357832.0000 1789Merritt 016303 1 39654943 39654943 39654943 39654943.0000 1790sweetish 018001 1 5987435 5987435 5987435 5987435.0000 1791dogging 018002 1 28357832 28357832 28357832 28357832.0000 1792scornfully 018003 1 39654943 39654943 39654943 39654943.0000 1793fetters 018012 1 28357832 28357832 28357832 28357832.0000 1794bivalves 018013 1 39654943 39654943 39654943 39654943.0000 1795skulking 018021 1 5987435 5987435 5987435 5987435.0000 1796flint 018022 1 28357832 28357832 28357832 28357832.0000 1797flopping 018023 1 39654943 39654943 39654943 39654943.0000 1798Judas 018032 1 28357832 28357832 28357832 28357832.0000 1799vacuuming 018033 1 39654943 39654943 39654943 39654943.0000 1800medical 018041 1 5987435 5987435 5987435 5987435.0000 1801bloodbath 018042 1 28357832 28357832 28357832 28357832.0000 1802subschema 018043 1 39654943 39654943 39654943 39654943.0000 1803interdependent 018051 1 5987435 5987435 5987435 5987435.0000 1804Graves 018052 1 28357832 28357832 28357832 28357832.0000 1805neonatal 018053 1 39654943 39654943 39654943 39654943.0000 1806sorters 018061 1 5987435 5987435 5987435 5987435.0000 1807epistle 018062 1 28357832 28357832 28357832 28357832.0000 1808Conley 018101 1 5987435 5987435 5987435 5987435.0000 1809lectured 018102 1 28357832 28357832 28357832 28357832.0000 1810Abraham 018103 1 39654943 39654943 39654943 39654943.0000 1811cage 018201 1 5987435 5987435 5987435 5987435.0000 1812hushes 018202 1 28357832 28357832 28357832 28357832.0000 1813Simla 018402 1 28357832 28357832 28357832 28357832.0000 1814reporters 018403 1 39654943 39654943 39654943 39654943.0000 1815coexist 018601 1 5987435 5987435 5987435 5987435.0000 1816Beebe 018602 1 28357832 28357832 28357832 28357832.0000 1817Taoism 018603 1 39654943 39654943 39654943 39654943.0000 1818Connally 018801 1 5987435 5987435 5987435 5987435.0000 1819fetched 018802 1 28357832 28357832 28357832 28357832.0000 1820checkpoints 018803 1 39654943 39654943 39654943 39654943.0000 1821gritty 018811 1 5987435 5987435 5987435 5987435.0000 1822firearm 018812 1 28357832 28357832 28357832 28357832.0000 1823minima 019101 1 5987435 5987435 5987435 5987435.0000 1824Selfridge 019102 1 28357832 28357832 28357832 28357832.0000 1825disable 019103 1 39654943 39654943 39654943 39654943.0000 1826witchcraft 019201 1 5987435 5987435 5987435 5987435.0000 1827betroth 030501 1 5987435 5987435 5987435 5987435.0000 1828Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000 1829imprint 030503 1 39654943 39654943 39654943 39654943.0000 1830swelling 031901 1 5987435 5987435 5987435 5987435.0000 1831interrelationships 036001 1 5987435 5987435 5987435 5987435.0000 1832riser 036002 1 28357832 28357832 28357832 28357832.0000 1833bee 038001 1 5987435 5987435 5987435 5987435.0000 1834kanji 038002 1 28357832 28357832 28357832 28357832.0000 1835dental 038003 1 39654943 39654943 39654943 39654943.0000 1836railway 038011 1 5987435 5987435 5987435 5987435.0000 1837validate 038012 1 28357832 28357832 28357832 28357832.0000 1838normalizes 038013 1 39654943 39654943 39654943 39654943.0000 1839Kline 038101 1 5987435 5987435 5987435 5987435.0000 1840Anatole 038102 1 28357832 28357832 28357832 28357832.0000 1841partridges 038103 1 39654943 39654943 39654943 39654943.0000 1842recruited 038201 1 5987435 5987435 5987435 5987435.0000 1843dimensions 038202 1 28357832 28357832 28357832 28357832.0000 1844Chicana 038203 1 39654943 39654943 39654943 39654943.0000 1845select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1846companynr fld3 sum(price) 1847512 boat 786542 1848512 capably 786542 1849512 cupboard 786542 1850512 decliner 786542 1851512 descendants 786542 1852512 dopers 786542 1853512 erases 786542 1854512 Micronesia 786542 1855512 Miles 786542 1856512 skies 786542 1857select 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; 1858companynr count(*) min(fld3) max(fld3) sum(price) avg(price) 185900 1 Omaha Omaha 5987435 5987435.0000 186036 1 dubbed dubbed 28357832 28357832.0000 186137 83 Abraham Wotan 1908978016 22999735.1325 186250 2 scribbled tapestry 68012775 34006387.5000 1863select 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; 1864t3.companynr+0 t2nr fld3 sum(price) 186537 1 Omaha 5987435 186637 11401 breaking 5987435 186737 11402 Romans 28357832 186837 11403 intercepted 39654943 186937 11501 bewilderingly 5987435 187037 11701 astound 5987435 187137 11702 admonishing 28357832 187237 11703 sumac 39654943 187337 12001 flanking 5987435 187437 12003 combed 39654943 187537 12301 Eulerian 5987435 187637 12302 dubbed 28357832 187737 12303 Kane 39654943 187837 12501 annihilates 5987435 187937 12602 Wotan 28357832 188037 12603 snatching 39654943 188137 12701 grazing 5987435 188237 12702 Baird 28357832 188337 12703 celery 39654943 188437 13601 handgun 5987435 188537 13602 foldout 28357832 188637 13603 mystic 39654943 188737 13801 intelligibility 5987435 188837 13802 Augustine 28357832 188937 13803 teethe 39654943 189037 13901 scholastics 5987435 189137 16001 audiology 5987435 189237 16201 wallet 5987435 189337 16202 parters 28357832 189437 16301 eschew 5987435 189537 16302 quitter 28357832 189637 16303 neat 39654943 189737 18001 jarring 5987435 189837 18002 tinily 28357832 189937 18003 balled 39654943 190037 18012 impulsive 28357832 190137 18013 starlet 39654943 190237 18021 lawgiver 5987435 190337 18022 stated 28357832 190437 18023 readable 39654943 190537 18032 testicle 28357832 190637 18033 Parsifal 39654943 190737 18041 Punjab 5987435 190837 18042 Merritt 28357832 190937 18043 Quixotism 39654943 191037 18051 sureties 5987435 191137 18052 puddings 28357832 191237 18053 tapestry 39654943 191337 18061 trimmings 5987435 191437 18062 humility 28357832 191537 18101 tragedies 5987435 191637 18102 skulking 28357832 191737 18103 flint 39654943 191837 18201 relaxing 5987435 191937 18202 offload 28357832 192037 18402 suites 28357832 192137 18403 lists 39654943 192237 18601 vacuuming 5987435 192337 18602 dentally 28357832 192437 18603 humanness 39654943 192537 18801 inch 5987435 192637 18802 Weissmuller 28357832 192737 18803 irresponsibly 39654943 192837 18811 repetitions 5987435 192937 18812 Antares 28357832 193037 19101 ventilate 5987435 193137 19102 pityingly 28357832 193237 19103 interdependent 39654943 193337 19201 Graves 5987435 193437 30501 neonatal 5987435 193537 30502 scribbled 28357832 193637 30503 chafe 39654943 193737 31901 realtor 5987435 193837 36001 elite 5987435 193937 36002 funereal 28357832 194037 38001 Conley 5987435 194137 38002 lectured 28357832 194237 38003 Abraham 39654943 194337 38011 groupings 5987435 194437 38012 dissociate 28357832 194537 38013 coexist 39654943 194637 38101 rusting 5987435 194737 38102 galling 28357832 194837 38103 obliterates 39654943 194937 38201 resumes 5987435 195037 38202 analyzable 28357832 195137 38203 terminator 39654943 1952select 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; 1953sum(price) 1954234298 1955select 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; 1956fld1 sum(price) 1957038008 234298 1958explain select fld3 from t2 where 1>2 or 2>3; 1959id select_type table type possible_keys key key_len ref rows Extra 19601 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1961explain select fld3 from t2 where fld1=fld1; 1962id select_type table type possible_keys key key_len ref rows Extra 19631 SIMPLE t2 ALL NULL NULL NULL NULL 1199 1964select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 1965companynr fld1 196634 250501 196734 250502 1968select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 1969companynr fld1 197034 250501 197134 250502 1972select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 1973companynr count sum 197400 82 10355753 197529 95 14473298 197634 70 17788966 197737 588 83602098 197841 52 12816335 1979select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 1980companynr 198100 198229 198334 198437 198541 1986select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 1987companynr companyname count(*) 198868 company 10 12 198950 company 11 11 199040 company 5 37 199141 company 6 52 199253 company 7 4 199358 company 8 23 199465 company 9 10 1995select count(*) from t2; 1996count(*) 19971199 1998select count(*) from t2 where fld1 < 098024; 1999count(*) 2000387 2001select min(fld1) from t2 where fld1>= 098024; 2002min(fld1) 200398024 2004select max(fld1) from t2 where fld1>= 098024; 2005max(fld1) 20061232609 2007select count(*) from t3 where price2=76234234; 2008count(*) 20094181 2010select count(*) from t3 where companynr=512 and price2=76234234; 2011count(*) 20124181 2013explain select min(fld1),max(fld1),count(*) from t2; 2014id select_type table type possible_keys key key_len ref rows Extra 20151 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2016select min(fld1),max(fld1),count(*) from t2; 2017min(fld1) max(fld1) count(*) 20180 1232609 1199 2019select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 2020min(t2nr) max(t2nr) 20212115 2115 2022select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 2023count(*) min(t2nr) max(t2nr) 20244181 4 41804 2025select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 2026t2nr count(*) 20279 1 202819 1 202929 1 203039 1 203149 1 203259 1 203369 1 203479 1 203589 1 203699 1 2037109 1 2038119 1 2039129 1 2040139 1 2041149 1 2042159 1 2043169 1 2044179 1 2045189 1 2046199 1 2047select max(t2nr) from t3 where price=983543950; 2048max(t2nr) 204941807 2050select t1.period from t3 = t1 limit 1; 2051period 20521001 2053select t1.period from t1 as t1 limit 1; 2054period 20559410 2056select t1.period as "Nuvarande period" from t1 as t1 limit 1; 2057Nuvarande period 20589410 2059select period as ok_period from t1 limit 1; 2060ok_period 20619410 2062select period as ok_period from t1 group by ok_period limit 1; 2063ok_period 20649410 2065select 1+1 as summa from t1 group by summa limit 1; 2066summa 20672 2068select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 2069Nuvarande period 20709410 2071show tables; 2072Tables_in_test 2073t1 2074t2 2075t3 2076t4 2077show tables from test like "s%"; 2078Tables_in_test (s%) 2079show tables from test like "t?"; 2080Tables_in_test (t?) 2081show full columns from t2; 2082Field Type Collation Null Key Default Extra Privileges Comment 2083auto int(11) NULL NO PRI NULL auto_increment # 2084fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2085companynr tinyint(2) unsigned zerofill NULL NO 00 # 2086fld3 char(30) latin1_swedish_ci NO MUL # 2087fld4 char(35) latin1_swedish_ci NO # 2088fld5 char(35) latin1_swedish_ci NO # 2089fld6 char(4) latin1_swedish_ci NO # 2090show full columns from t2 from test like 'f%'; 2091Field Type Collation Null Key Default Extra Privileges Comment 2092fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2093fld3 char(30) latin1_swedish_ci NO MUL # 2094fld4 char(35) latin1_swedish_ci NO # 2095fld5 char(35) latin1_swedish_ci NO # 2096fld6 char(4) latin1_swedish_ci NO # 2097show full columns from t2 from test like 's%'; 2098Field Type Collation Null Key Default Extra Privileges Comment 2099show keys from t2; 2100Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2101t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE 2102t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE 2103t2 1 fld3 1 fld3 A NULL NULL NULL BTREE 2104drop table t4, t3, t2, t1; 2105CREATE TABLE t1 ( 2106cont_nr int(11) NOT NULL auto_increment, 2107ver_nr int(11) NOT NULL default '0', 2108aufnr int(11) NOT NULL default '0', 2109username varchar(50) NOT NULL default '', 2110hdl_nr int(11) NOT NULL default '0', 2111eintrag date NOT NULL default '0000-00-00', 2112st_klasse varchar(40) NOT NULL default '', 2113st_wert varchar(40) NOT NULL default '', 2114st_zusatz varchar(40) NOT NULL default '', 2115st_bemerkung varchar(255) NOT NULL default '', 2116kunden_art varchar(40) NOT NULL default '', 2117mcbs_knr int(11) default NULL, 2118mcbs_aufnr int(11) NOT NULL default '0', 2119schufa_status char(1) default '?', 2120bemerkung text, 2121wirknetz text, 2122wf_igz int(11) NOT NULL default '0', 2123tarifcode varchar(80) default NULL, 2124recycle char(1) default NULL, 2125sim varchar(30) default NULL, 2126mcbs_tpl varchar(30) default NULL, 2127emp_nr int(11) NOT NULL default '0', 2128laufzeit int(11) default NULL, 2129hdl_name varchar(30) default NULL, 2130prov_hdl_nr int(11) NOT NULL default '0', 2131auto_wirknetz varchar(50) default NULL, 2132auto_billing varchar(50) default NULL, 2133touch timestamp NOT NULL, 2134kategorie varchar(50) default NULL, 2135kundentyp varchar(20) NOT NULL default '', 2136sammel_rech_msisdn varchar(30) NOT NULL default '', 2137p_nr varchar(9) NOT NULL default '', 2138suffix char(3) NOT NULL default '', 2139PRIMARY KEY (cont_nr), 2140KEY idx_aufnr(aufnr), 2141KEY idx_hdl_nr(hdl_nr), 2142KEY idx_st_klasse(st_klasse), 2143KEY ver_nr(ver_nr), 2144KEY eintrag_idx(eintrag), 2145KEY emp_nr_idx(emp_nr), 2146KEY wf_igz(wf_igz), 2147KEY touch(touch), 2148KEY hdl_tag(eintrag,hdl_nr), 2149KEY prov_hdl_nr(prov_hdl_nr), 2150KEY mcbs_aufnr(mcbs_aufnr), 2151KEY kundentyp(kundentyp), 2152KEY p_nr(p_nr,suffix) 2153) ENGINE=MyISAM; 2154INSERT 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','','',''); 2155INSERT 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','','',''); 2156INSERT 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','','',''); 2157INSERT 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','','',''); 2158INSERT 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'); 2159INSERT 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','','',''); 2160INSERT 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','','',''); 2161SELECT 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; 2162Kundentyp kategorie 2163Privat (Private Nutzung) Mobilfunk 2164Warnings: 2165Warning 1052 Column 'kundentyp' in group statement is ambiguous 2166drop table t1; 2167SHOW STATUS LIKE 'Compression'; 2168Variable_name Value 2169Compression ON 2170connection default; 2171disconnect comp_con; 2172