~drizzle-trunk/drizzle/development

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;

1851

1281

fld1 fld3 period price price2

1852

18201 relaxing 1001 5987435 234724

1853

18601 vacuuming 1001 5987435 234724

1854

18801 inch 1001 5987435 234724

1855

18811 repetitions 1001 5987435 234724

1856

19101 ventilate 1001 5987435 234724

1857

19201 Graves 1001 5987435 234724

1858

30501 neonatal 1001 5987435 234724

1859

31901 realtor 1001 5987435 234724

1860

36001 elite 1001 5987435 234724

1861

38001 Conley 1001 5987435 234724

1862

38011 groupings 1001 5987435 234724

1863

38101 rusting 1001 5987435 234724

1864

38201 resumes 1001 5987435 234724

1282

018201 relaxing 1001 5987435 234724

1283

018601 vacuuming 1001 5987435 234724

1284

018801 inch 1001 5987435 234724

1285

018811 repetitions 1001 5987435 234724

1865

1286

create table t4 (

1866

companynr int NOT NULL default '00',

1287

companynr tinyint(2) unsigned zerofill NOT NULL default '00',

1867

1288

companyname char(30) NOT NULL default '',

1868

1289

PRIMARY KEY (companynr),

1869

1290

UNIQUE KEY companyname(companyname)

1870

1291

) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';

1871

1292

select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1872

1293

companynr companyname

1873

0 Unknown

1294

00 Unknown

1874

1295

29 company 1

1875

1296

34 company 2

1876

1297

36 company 3

1884

1305

68 company 10

1885

1306

select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1886

1307

companynr companyname

1887

0 Unknown

1308

00 Unknown

1888

1309

29 company 1

1889

1310

34 company 2

1890

1311

36 company 3

1936

1357

explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;

1937

1358

id select_type table type possible_keys key key_len ref rows Extra

1938

1359

1 SIMPLE t2 ALL NULL NULL NULL NULL 1200

1939

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1 Using where; Not exists

1360

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists

1940

1361

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;

1941

1362

id select_type table type possible_keys key key_len ref rows Extra

1942

1363

1 SIMPLE t4 ALL NULL NULL NULL NULL 12

1956

1377

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;

1957

1378

id select_type table type possible_keys key key_len ref rows Extra

1958

1379

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1959

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1

1380

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1960

1381

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;

1961

1382

id select_type table type possible_keys key key_len ref rows Extra

1962

1383

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1963

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1

1384

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1964

1385

explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;

1965

1386

id select_type table type possible_keys key key_len ref rows Extra

1966

1387

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where

1967

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1

1388

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1

1968

1389

explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;

1969

1390

id select_type table type possible_keys key key_len ref rows Extra

1970

1391

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where

2007

1428

41 40

2008

1429

explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;

2009

1430

id select_type table type possible_keys key key_len ref rows Extra

2010

1 SIMPLE t4 index NULL PRIMARY 4 NULL 12 Using index; Using temporary

1431

1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary

2011

1432

1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer

2012

1433

select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;

2013

1434

fld1 companynr fld3 period

2014

38008 37 reporters 1008

2015

38208 37 Selfridge 1008

1435

038008 37 reporters 1008

1436

038208 37 Selfridge 1008

2016

1437

select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;

2017

1438

fld1 companynr fld3 period

2018

38008 37 reporters 1008

2019

38208 37 Selfridge 1008

1439

038008 37 reporters 1008

1440

038208 37 Selfridge 1008

2020

1441

select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;

2021

1442

fld1 companynr fld3 period

2022

38008 37 reporters 1008

2023

38208 37 Selfridge 1008

1443

038008 37 reporters 1008

1444

038208 37 Selfridge 1008

2024

1445

select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);

2025

1446

period

2026

1447

9410

2058

1479

2059

1480

select companynr,count(*),sum(fld1) from t2 group by companynr;

2060

1481

companynr count(*) sum(fld1)

2061

0 82 10355753

1482

00 82 10355753

2062

1483

29 95 14473298

2063

1484

34 70 17788966

2064

1485

36 215 22786296

2087

1508

Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))

2088

1509

select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;

2089

1510

companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)

2090

0 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

1511

00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087

2091

1512

29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026

2092

1513

34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069

2093

1514

select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;

2142

1563

1199

2143

1564

select companynr,count(distinct fld1) from t2 group by companynr;

2144

1565

companynr count(distinct fld1)

2145

0 82

1566

00 82

2146

1567

29 95

2147

1568

34 70

2148

1569

36 215

2156

1577

68 12

2157

1578

select companynr,count(*) from t2 group by companynr;

2158

1579

companynr count(*)

2159

0 82

1580

00 82

2160

1581

29 95

2161

1582

34 70

2162

1583

36 215

2170

1591

68 12

2171

1592

select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;

2172

1593

companynr count(distinct concat(fld1,repeat(65,1000)))

2173

0 82

1594

00 82

2174

1595

29 95

2175

1596

34 70

2176

1597

36 215

2184

1605

68 12

2185

1606

select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;

2186

1607

companynr count(distinct concat(fld1,repeat(65,200)))

2187

0 82

1608

00 82

2188

1609

29 95

2189

1610

34 70

2190

1611

36 215

2198

1619

68 12

2199

1620

select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;

2200

1621

companynr count(distinct floor(fld1/100))

2201

0 47

1622

00 47

2202

1623

29 35

2203

1624

34 14

2204

1625

36 69

2212

1633

68 1

2213

1634

select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;

2214

1635

companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))

2215

0 47

1636

00 47

2216

1637

29 35

2217

1638

34 14

2218

1639

36 69

2251

1672

select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;

2252

1673

fld3 count(*)

2253

1674

spates 4181

2254

select companynr,companyname from t4 group by 1;

2255

companynr companyname

1675

select companynr|0,companyname from t4 group by 1;

1676

companynr|0 companyname

2256

1677

0 Unknown

2257

1678

29 company 1

2258

1679

34 company 2

2278

1699

53 company 7 4

2279

1700

58 company 8 23

2280

1701

65 company 9 10

2281

0 Unknown 82

1702

00 Unknown 82

2282

1703

select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;

2283

1704

fld1 count(*)

2284

1705

158402 4181

2302

1723

37 588

2303

1724

36 215

2304

1725

29 95

2305

0 82

1726

00 82

2306

1727

34 70

2307

1728

41 52

2308

1729

40 37

2321

1742

53 4

2322

1743

select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;

2323

1744

fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)

2324

teethe 1 1 5987435 5987435 5987435 5987435.0000

2325

dreaded 11401 1 5987435 5987435 5987435 5987435.0000

2326

scholastics 11402 1 28357832 28357832 28357832 28357832.0000

2327

audiology 11403 1 39654943 39654943 39654943 39654943.0000

2328

wallet 11501 1 5987435 5987435 5987435 5987435.0000

2329

parters 11701 1 5987435 5987435 5987435 5987435.0000

2330

eschew 11702 1 28357832 28357832 28357832 28357832.0000

2331

quitter 11703 1 39654943 39654943 39654943 39654943.0000

2332

neat 12001 1 5987435 5987435 5987435 5987435.0000

2333

Steinberg 12003 1 39654943 39654943 39654943 39654943.0000

2334

balled 12301 1 5987435 5987435 5987435 5987435.0000

2335

persist 12302 1 28357832 28357832 28357832 28357832.0000

2336

attainments 12303 1 39654943 39654943 39654943 39654943.0000

2337

capably 12501 1 5987435 5987435 5987435 5987435.0000

2338

impulsive 12602 1 28357832 28357832 28357832 28357832.0000

2339

starlet 12603 1 39654943 39654943 39654943 39654943.0000

2340

featherweight 12701 1 5987435 5987435 5987435 5987435.0000

2341

pessimist 12702 1 28357832 28357832 28357832 28357832.0000

2342

daughter 12703 1 39654943 39654943 39654943 39654943.0000

2343

lawgiver 13601 1 5987435 5987435 5987435 5987435.0000

2344

stated 13602 1 28357832 28357832 28357832 28357832.0000

2345

readable 13603 1 39654943 39654943 39654943 39654943.0000

2346

testicle 13801 1 5987435 5987435 5987435 5987435.0000

2347

Parsifal 13802 1 28357832 28357832 28357832 28357832.0000

2348

leavings 13803 1 39654943 39654943 39654943 39654943.0000

2349

squeaking 13901 1 5987435 5987435 5987435 5987435.0000

2350

contrasted 16001 1 5987435 5987435 5987435 5987435.0000

2351

leftover 16201 1 5987435 5987435 5987435 5987435.0000

2352

whiteners 16202 1 28357832 28357832 28357832 28357832.0000

2353

erases 16301 1 5987435 5987435 5987435 5987435.0000

2354

Punjab 16302 1 28357832 28357832 28357832 28357832.0000

2355

Merritt 16303 1 39654943 39654943 39654943 39654943.0000

2356

sweetish 18001 1 5987435 5987435 5987435 5987435.0000

2357

dogging 18002 1 28357832 28357832 28357832 28357832.0000

2358

scornfully 18003 1 39654943 39654943 39654943 39654943.0000

2359

fetters 18012 1 28357832 28357832 28357832 28357832.0000

2360

bivalves 18013 1 39654943 39654943 39654943 39654943.0000

2361

skulking 18021 1 5987435 5987435 5987435 5987435.0000

2362

flint 18022 1 28357832 28357832 28357832 28357832.0000

2363

flopping 18023 1 39654943 39654943 39654943 39654943.0000

2364

Judas 18032 1 28357832 28357832 28357832 28357832.0000

2365

vacuuming 18033 1 39654943 39654943 39654943 39654943.0000

2366

medical 18041 1 5987435 5987435 5987435 5987435.0000

2367

bloodbath 18042 1 28357832 28357832 28357832 28357832.0000

2368

subschema 18043 1 39654943 39654943 39654943 39654943.0000

2369

interdependent 18051 1 5987435 5987435 5987435 5987435.0000

2370

Graves 18052 1 28357832 28357832 28357832 28357832.0000

2371

neonatal 18053 1 39654943 39654943 39654943 39654943.0000

2372

sorters 18061 1 5987435 5987435 5987435 5987435.0000

2373

epistle 18062 1 28357832 28357832 28357832 28357832.0000

2374

Conley 18101 1 5987435 5987435 5987435 5987435.0000

2375

lectured 18102 1 28357832 28357832 28357832 28357832.0000

2376

Abraham 18103 1 39654943 39654943 39654943 39654943.0000

2377

cage 18201 1 5987435 5987435 5987435 5987435.0000

2378

hushes 18202 1 28357832 28357832 28357832 28357832.0000

2379

Simla 18402 1 28357832 28357832 28357832 28357832.0000

2380

reporters 18403 1 39654943 39654943 39654943 39654943.0000

2381

coexist 18601 1 5987435 5987435 5987435 5987435.0000

2382

Beebe 18602 1 28357832 28357832 28357832 28357832.0000

2383

Taoism 18603 1 39654943 39654943 39654943 39654943.0000

2384

Connally 18801 1 5987435 5987435 5987435 5987435.0000

2385

fetched 18802 1 28357832 28357832 28357832 28357832.0000

2386

checkpoints 18803 1 39654943 39654943 39654943 39654943.0000

2387

gritty 18811 1 5987435 5987435 5987435 5987435.0000

2388

firearm 18812 1 28357832 28357832 28357832 28357832.0000

2389

minima 19101 1 5987435 5987435 5987435 5987435.0000

2390

Selfridge 19102 1 28357832 28357832 28357832 28357832.0000

2391

disable 19103 1 39654943 39654943 39654943 39654943.0000

2392

witchcraft 19201 1 5987435 5987435 5987435 5987435.0000

2393

betroth 30501 1 5987435 5987435 5987435 5987435.0000

2394

Manhattanize 30502 1 28357832 28357832 28357832 28357832.0000

2395

imprint 30503 1 39654943 39654943 39654943 39654943.0000

2396

swelling 31901 1 5987435 5987435 5987435 5987435.0000

2397

interrelationships 36001 1 5987435 5987435 5987435 5987435.0000

2398

riser 36002 1 28357832 28357832 28357832 28357832.0000

2399

bee 38001 1 5987435 5987435 5987435 5987435.0000

2400

kanji 38002 1 28357832 28357832 28357832 28357832.0000

2401

dental 38003 1 39654943 39654943 39654943 39654943.0000

2402

railway 38011 1 5987435 5987435 5987435 5987435.0000

2403

validate 38012 1 28357832 28357832 28357832 28357832.0000

2404

normalizes 38013 1 39654943 39654943 39654943 39654943.0000

2405

Kline 38101 1 5987435 5987435 5987435 5987435.0000

2406

Anatole 38102 1 28357832 28357832 28357832 28357832.0000

2407

partridges 38103 1 39654943 39654943 39654943 39654943.0000

2408

recruited 38201 1 5987435 5987435 5987435 5987435.0000

2409

dimensions 38202 1 28357832 28357832 28357832 28357832.0000

2410

Chicana 38203 1 39654943 39654943 39654943 39654943.0000

1745

teethe 000001 1 5987435 5987435 5987435 5987435.0000

1746

dreaded 011401 1 5987435 5987435 5987435 5987435.0000

1747

scholastics 011402 1 28357832 28357832 28357832 28357832.0000

1748

audiology 011403 1 39654943 39654943 39654943 39654943.0000

1749

wallet 011501 1 5987435 5987435 5987435 5987435.0000

1750

parters 011701 1 5987435 5987435 5987435 5987435.0000

1751

eschew 011702 1 28357832 28357832 28357832 28357832.0000

1752

quitter 011703 1 39654943 39654943 39654943 39654943.0000

1753

neat 012001 1 5987435 5987435 5987435 5987435.0000

1754

Steinberg 012003 1 39654943 39654943 39654943 39654943.0000

1755

balled 012301 1 5987435 5987435 5987435 5987435.0000

1756

persist 012302 1 28357832 28357832 28357832 28357832.0000

1757

attainments 012303 1 39654943 39654943 39654943 39654943.0000

1758

capably 012501 1 5987435 5987435 5987435 5987435.0000

1759

impulsive 012602 1 28357832 28357832 28357832 28357832.0000

1760

starlet 012603 1 39654943 39654943 39654943 39654943.0000

1761

featherweight 012701 1 5987435 5987435 5987435 5987435.0000

1762

pessimist 012702 1 28357832 28357832 28357832 28357832.0000

1763

daughter 012703 1 39654943 39654943 39654943 39654943.0000

1764

lawgiver 013601 1 5987435 5987435 5987435 5987435.0000

1765

stated 013602 1 28357832 28357832 28357832 28357832.0000

1766

readable 013603 1 39654943 39654943 39654943 39654943.0000

1767

testicle 013801 1 5987435 5987435 5987435 5987435.0000

1768

Parsifal 013802 1 28357832 28357832 28357832 28357832.0000

1769

leavings 013803 1 39654943 39654943 39654943 39654943.0000

1770

squeaking 013901 1 5987435 5987435 5987435 5987435.0000

1771

contrasted 016001 1 5987435 5987435 5987435 5987435.0000

1772

leftover 016201 1 5987435 5987435 5987435 5987435.0000

1773

whiteners 016202 1 28357832 28357832 28357832 28357832.0000

1774

erases 016301 1 5987435 5987435 5987435 5987435.0000

1775

Punjab 016302 1 28357832 28357832 28357832 28357832.0000

1776

Merritt 016303 1 39654943 39654943 39654943 39654943.0000

1777

sweetish 018001 1 5987435 5987435 5987435 5987435.0000

1778

dogging 018002 1 28357832 28357832 28357832 28357832.0000

1779

scornfully 018003 1 39654943 39654943 39654943 39654943.0000

1780

fetters 018012 1 28357832 28357832 28357832 28357832.0000

1781

bivalves 018013 1 39654943 39654943 39654943 39654943.0000

1782

skulking 018021 1 5987435 5987435 5987435 5987435.0000

1783

flint 018022 1 28357832 28357832 28357832 28357832.0000

1784

flopping 018023 1 39654943 39654943 39654943 39654943.0000

1785

Judas 018032 1 28357832 28357832 28357832 28357832.0000

1786

vacuuming 018033 1 39654943 39654943 39654943 39654943.0000

1787

medical 018041 1 5987435 5987435 5987435 5987435.0000

1788

bloodbath 018042 1 28357832 28357832 28357832 28357832.0000

1789

subschema 018043 1 39654943 39654943 39654943 39654943.0000

1790

interdependent 018051 1 5987435 5987435 5987435 5987435.0000

1791

Graves 018052 1 28357832 28357832 28357832 28357832.0000

1792

neonatal 018053 1 39654943 39654943 39654943 39654943.0000

1793

sorters 018061 1 5987435 5987435 5987435 5987435.0000

1794

epistle 018062 1 28357832 28357832 28357832 28357832.0000

1795

Conley 018101 1 5987435 5987435 5987435 5987435.0000

1796

lectured 018102 1 28357832 28357832 28357832 28357832.0000

1797

Abraham 018103 1 39654943 39654943 39654943 39654943.0000

1798

cage 018201 1 5987435 5987435 5987435 5987435.0000

1799

hushes 018202 1 28357832 28357832 28357832 28357832.0000

1800

Simla 018402 1 28357832 28357832 28357832 28357832.0000

1801

reporters 018403 1 39654943 39654943 39654943 39654943.0000

1802

coexist 018601 1 5987435 5987435 5987435 5987435.0000

1803

Beebe 018602 1 28357832 28357832 28357832 28357832.0000

1804

Taoism 018603 1 39654943 39654943 39654943 39654943.0000

1805

Connally 018801 1 5987435 5987435 5987435 5987435.0000

1806

fetched 018802 1 28357832 28357832 28357832 28357832.0000

1807

checkpoints 018803 1 39654943 39654943 39654943 39654943.0000

1808

gritty 018811 1 5987435 5987435 5987435 5987435.0000

1809

firearm 018812 1 28357832 28357832 28357832 28357832.0000

1810

minima 019101 1 5987435 5987435 5987435 5987435.0000

1811

Selfridge 019102 1 28357832 28357832 28357832 28357832.0000

1812

disable 019103 1 39654943 39654943 39654943 39654943.0000

1813

witchcraft 019201 1 5987435 5987435 5987435 5987435.0000

1814

betroth 030501 1 5987435 5987435 5987435 5987435.0000

1815

Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000

1816

imprint 030503 1 39654943 39654943 39654943 39654943.0000

1817

swelling 031901 1 5987435 5987435 5987435 5987435.0000

1818

interrelationships 036001 1 5987435 5987435 5987435 5987435.0000

1819

riser 036002 1 28357832 28357832 28357832 28357832.0000

1820

bee 038001 1 5987435 5987435 5987435 5987435.0000

1821

kanji 038002 1 28357832 28357832 28357832 28357832.0000

1822

dental 038003 1 39654943 39654943 39654943 39654943.0000

1823

railway 038011 1 5987435 5987435 5987435 5987435.0000

1824

validate 038012 1 28357832 28357832 28357832 28357832.0000

1825

normalizes 038013 1 39654943 39654943 39654943 39654943.0000

1826

Kline 038101 1 5987435 5987435 5987435 5987435.0000

1827

Anatole 038102 1 28357832 28357832 28357832 28357832.0000

1828

partridges 038103 1 39654943 39654943 39654943 39654943.0000

1829

recruited 038201 1 5987435 5987435 5987435 5987435.0000

1830

dimensions 038202 1 28357832 28357832 28357832 28357832.0000

1831

Chicana 038203 1 39654943 39654943 39654943 39654943.0000

2411

1832

select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;

2412

1833

companynr fld3 sum(price)

2413

1834

512 boat 786542

2422

1843

512 skies 786542

2423

1844

select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;

2424

1845

companynr count(*) min(fld3) max(fld3) sum(price) avg(price)

2425

0 1 Omaha Omaha 5987435 5987435.0000

1846

00 1 Omaha Omaha 5987435 5987435.0000

2426

1847

36 1 dubbed dubbed 28357832 28357832.0000

2427

1848

37 83 Abraham Wotan 1908978016 22999735.1325

2428

1849

50 2 scribbled tapestry 68012775 34006387.5000

2520

1941

234298

2521

1942

select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;

2522

1943

fld1 sum(price)

2523

38008 234298

1944

038008 234298

2524

1945

explain select fld3 from t2 where 1>2 or 2>3;

2525

1946

id select_type table type possible_keys key key_len ref rows Extra

2526

1947

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

2537

1958

34 250502

2538

1959

select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;

2539

1960

companynr count sum

2540

0 82 10355753

1961

00 82 10355753

2541

1962

29 95 14473298

2542

1963

34 70 17788966

2543

1964

37 588 83602098

2544

1965

41 52 12816335

2545

1966

select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;

2546

1967

companynr

2547

1968

2548

1969

2549

1970

2550

1971

2646

2067

Tables_in_test (t?)

2647

2068

show full columns from t2;

2648

2069

Field Type Collation Null Key Default Extra Privileges Comment

2649

auto int NULL NO PRI NULL auto_increment #

2650

fld1 int NULL NO UNI NULL #

2651

companynr int NULL NO NULL #

2652

fld3 varchar(30) utf8_general_ci NO MUL NULL #

2653

fld4 varchar(35) utf8_general_ci NO NULL #

2654

fld5 varchar(35) utf8_general_ci NO NULL #

2655

fld6 varchar(4) utf8_general_ci NO NULL #

2070

auto int(11) NULL NO PRI NULL auto_increment #

2071

fld1 int(6) unsigned zerofill NULL NO UNI 000000 #

2072

companynr tinyint(2) unsigned zerofill NULL NO 00 #

2073

fld3 char(30) latin1_swedish_ci NO MUL #

2074

fld4 char(35) latin1_swedish_ci NO #

2075

fld5 char(35) latin1_swedish_ci NO #

2076

fld6 char(4) latin1_swedish_ci NO #

2656

2077

show full columns from t2 from test like 'f%';

2657

2078

Field Type Collation Null Key Default Extra Privileges Comment

2658

fld1 int NULL NO UNI NULL #

2659

fld3 varchar(30) utf8_general_ci NO MUL NULL #

2660

fld4 varchar(35) utf8_general_ci NO NULL #

2661

fld5 varchar(35) utf8_general_ci NO NULL #

2662

fld6 varchar(4) utf8_general_ci NO NULL #

2079

fld1 int(6) unsigned zerofill NULL NO UNI 000000 #

2080

fld3 char(30) latin1_swedish_ci NO MUL #

2081

fld4 char(35) latin1_swedish_ci NO #

2082

fld5 char(35) latin1_swedish_ci NO #

2083

fld6 char(4) latin1_swedish_ci NO #

2663

2084

show full columns from t2 from test like 's%';

2664

2085

Field Type Collation Null Key Default Extra Privileges Comment

2665

2086

show keys from t2;

2669

2090

t2 1 fld3 1 fld3 A NULL NULL NULL BTREE

2670

2091

drop table t4, t3, t2, t1;

2671

2092

CREATE TABLE t1 (

2672

cont_nr int NOT NULL auto_increment,

2673

ver_nr int NOT NULL default '0',

2674

aufnr int NOT NULL default '0',

2093

cont_nr int(11) NOT NULL auto_increment,

2094

ver_nr int(11) NOT NULL default '0',

2095

aufnr int(11) NOT NULL default '0',

2675

2096

username varchar(50) NOT NULL default '',

2676

hdl_nr int NOT NULL default '0',

2097

hdl_nr int(11) NOT NULL default '0',

2677

2098

eintrag date NOT NULL default '0000-00-00',

2678

2099

st_klasse varchar(40) NOT NULL default '',

2679

2100

st_wert varchar(40) NOT NULL default '',

2680

2101

st_zusatz varchar(40) NOT NULL default '',

2681

2102

st_bemerkung varchar(255) NOT NULL default '',

2682

2103

kunden_art varchar(40) NOT NULL default '',

2683

mcbs_knr int default NULL,

2684

mcbs_aufnr int NOT NULL default '0',

2104

mcbs_knr int(11) default NULL,

2105

mcbs_aufnr int(11) NOT NULL default '0',

2685

2106

schufa_status char(1) default '?',

2686

2107

bemerkung text,

2687

2108

wirknetz text,

2688

wf_igz int NOT NULL default '0',

2109

wf_igz int(11) NOT NULL default '0',

2689

2110

tarifcode varchar(80) default NULL,

2690

2111

recycle char(1) default NULL,

2691

2112

sim varchar(30) default NULL,

2692

2113

mcbs_tpl varchar(30) default NULL,

2693

emp_nr int NOT NULL default '0',

2694

laufzeit int default NULL,

2114

emp_nr int(11) NOT NULL default '0',

2115

laufzeit int(11) default NULL,

2695

2116

hdl_name varchar(30) default NULL,

2696

prov_hdl_nr int NOT NULL default '0',

2117

prov_hdl_nr int(11) NOT NULL default '0',

2697

2118

auto_wirknetz varchar(50) default NULL,

2698

2119

auto_billing varchar(50) default NULL,

2699

2120

touch timestamp NOT NULL,

Older »