1
drop table if exists t1,t2,t3,t4;
3
Period int DEFAULT '0000' NOT NULL,
4
Varor_period int DEFAULT '0' NOT NULL
6
INSERT INTO t1 VALUES (9410,9412);
17
auto int not null auto_increment,
18
fld1 int DEFAULT '000000' NOT NULL,
19
companynr int DEFAULT '00' NOT NULL,
20
fld3 char(30) DEFAULT '' NOT NULL,
21
fld4 char(35) DEFAULT '' NOT NULL,
22
fld5 char(35) DEFAULT '' NOT NULL,
23
fld6 char(4) DEFAULT '' NOT NULL,
28
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
31
select fld3 from t2 where fld3 like "%cultivation" ;
34
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
59
select fld3,companynr from t2 where companynr = 58 order by fld3;
84
select fld3 from t2 order by fld3 desc limit 10;
96
select fld3 from t2 order by fld3 desc limit 5;
103
select fld3 from t2 order by fld3 desc limit 5,5;
110
select t2.fld3 from t2 where fld3 = 'honeysuckle';
113
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
116
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
119
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
122
select t2.fld3 from t2 where fld3 LIKE 'h%le';
125
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
127
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
129
explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
130
id select_type table type possible_keys key key_len ref rows Extra
131
1 SIMPLE t2 ref fld3 fld3 122 const 1 Using where; Using index
132
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
133
id select_type table type possible_keys key key_len ref rows Extra
134
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
135
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
136
id select_type table type possible_keys key key_len ref rows Extra
137
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
138
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
139
id select_type table type possible_keys key key_len ref rows Extra
140
1 SIMPLE t2 ref fld3 fld3 122 const 1 Using where; Using index
141
explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
142
id select_type table type possible_keys key key_len ref rows Extra
143
1 SIMPLE t2 ref fld3 fld3 122 const 1 Using where; Using index
144
explain select fld3 from t2 ignore index (fld3,not_used);
145
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
146
explain select fld3 from t2 use index (not_used);
147
ERROR 42000: Key 'not_used' doesn't exist in table 't2'
148
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
152
explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
153
id select_type table type possible_keys key key_len ref rows Extra
154
1 SIMPLE t2 range fld3 fld3 122 NULL 2 Using where; Using index
155
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
160
select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
168
select fld1 from t2 where fld1=250501 or fld1="250502";
172
explain select fld1 from t2 where fld1=250501 or fld1="250502";
173
id select_type table type possible_keys key key_len ref rows Extra
174
1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index
175
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
181
explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
182
id select_type table type possible_keys key key_len ref rows Extra
183
1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
184
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
201
12704 misunderstander
208
13801 intelligibility
307
48005 interrelationships
407
203402 overestimating
460
218202 serializations
501
228119 investigations
580
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
582
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
585
select fld1,fld3 from t2 where fld1 like "25050%";
592
select fld1,fld3 from t2 where fld1 like "25050_";
599
select distinct companynr from t2;
613
select distinct companynr from t2 order by companynr;
627
select distinct companynr from t2 order by companynr desc;
641
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
856
select distinct fld3 from t2 where companynr = 34 order by fld3;
928
select distinct fld3 from t2 limit 10;
940
select distinct fld3 from t2 having fld3 like "A%" limit 10;
952
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
1016
select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
1028
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
1040
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
1053
period int not null,
1054
name char(32) not null,
1055
companynr int not null,
1057
price2 double(11,0),
1061
create temporary table tmp engine = myisam select * from t3;
1062
insert into t3 select * from tmp;
1063
insert into tmp select * from t3;
1064
insert into t3 select * from tmp;
1065
insert into tmp select * from t3;
1066
insert into t3 select * from tmp;
1067
insert into tmp select * from t3;
1068
insert into t3 select * from tmp;
1069
insert into tmp select * from t3;
1070
insert into t3 select * from tmp;
1071
insert into tmp select * from t3;
1072
insert into t3 select * from tmp;
1073
insert into tmp select * from t3;
1074
insert into t3 select * from tmp;
1075
insert into tmp select * from t3;
1076
insert into t3 select * from tmp;
1077
insert into tmp select * from t3;
1078
insert into t3 select * from tmp;
1079
alter table t3 add t2nr int not null auto_increment primary key first;
1081
SET SQL_BIG_TABLES=1;
1082
select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
1085
abrogating abrogating
1086
admonishing admonishing
1091
analyzable analyzable
1094
SET SQL_BIG_TABLES=0;
1095
select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
1096
concat(fld3," ",fld3)
1098
abrogating abrogating
1099
admonishing admonishing
1104
analyzable analyzable
1107
select distinct fld5 from t2 limit 10;
1119
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
1131
SET SQL_BIG_TABLES=1;
1132
select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
1144
SET SQL_BIG_TABLES=0;
1145
select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
1146
fld3 repeat("a",length(fld3)) count(*)
1150
congresswoman aaaaaaaaaaaaa 1
1151
contrition aaaaaaaaaa 1
1153
cultivation aaaaaaaaaaa 1
1154
definiteness aaaaaaaaaaaa 1
1155
demultiplex aaaaaaaaaaa 1
1156
disappointing aaaaaaaaaaaaa 1
1157
select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
1158
companynr rtrim(space(512+companynr))
1166
select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
1168
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
1169
id select_type table type possible_keys key key_len ref rows Extra
1170
1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
1171
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using index
1172
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
1173
id select_type table type possible_keys key key_len ref rows Extra
1174
1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
1175
1 SIMPLE t3 ref period period 4 test.t1.period 4181
1176
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
1177
id select_type table type possible_keys key key_len ref rows Extra
1178
1 SIMPLE t3 index period period 4 NULL 1
1179
1 SIMPLE t1 ref period period 4 test.t3.period 4181
1180
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
1181
id select_type table type possible_keys key key_len ref rows Extra
1182
1 SIMPLE t1 index period period 4 NULL 1
1183
1 SIMPLE t3 ref period period 4 test.t1.period 4181
1184
select period from t1;
1187
select period from t1 where period=1900;
1189
select fld3,period from t1,t2 where fld1 = 011401 order by period;
1192
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
1195
explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
1196
id select_type table type possible_keys key key_len ref rows Extra
1197
1 SIMPLE t2 const fld1 fld1 4 const 1
1198
1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1
1199
select fld3,period from t2,t1 where companynr*10 = 37*10;
1226
misunderstander 9410
1237
intelligibility 9410
1379
interrelationships 9410
1579
electroencephalography 9410
1789
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;
1790
fld3 period price price2
1791
admonishing 1002 28357832 8723648
1792
analyzable 1002 28357832 8723648
1793
annihilates 1001 5987435 234724
1794
Antares 1002 28357832 8723648
1795
astound 1001 5987435 234724
1796
audiology 1001 5987435 234724
1797
Augustine 1002 28357832 8723648
1798
Baird 1002 28357832 8723648
1799
bewilderingly 1001 5987435 234724
1800
breaking 1001 5987435 234724
1801
Conley 1001 5987435 234724
1802
dentally 1002 28357832 8723648
1803
dissociate 1002 28357832 8723648
1804
elite 1001 5987435 234724
1805
eschew 1001 5987435 234724
1806
Eulerian 1001 5987435 234724
1807
flanking 1001 5987435 234724
1808
foldout 1002 28357832 8723648
1809
funereal 1002 28357832 8723648
1810
galling 1002 28357832 8723648
1811
Graves 1001 5987435 234724
1812
grazing 1001 5987435 234724
1813
groupings 1001 5987435 234724
1814
handgun 1001 5987435 234724
1815
humility 1002 28357832 8723648
1816
impulsive 1002 28357832 8723648
1817
inch 1001 5987435 234724
1818
intelligibility 1001 5987435 234724
1819
jarring 1001 5987435 234724
1820
lawgiver 1001 5987435 234724
1821
lectured 1002 28357832 8723648
1822
Merritt 1002 28357832 8723648
1823
neonatal 1001 5987435 234724
1824
offload 1002 28357832 8723648
1825
parters 1002 28357832 8723648
1826
pityingly 1002 28357832 8723648
1827
puddings 1002 28357832 8723648
1828
Punjab 1001 5987435 234724
1829
quitter 1002 28357832 8723648
1830
realtor 1001 5987435 234724
1831
relaxing 1001 5987435 234724
1832
repetitions 1001 5987435 234724
1833
resumes 1001 5987435 234724
1834
Romans 1002 28357832 8723648
1835
rusting 1001 5987435 234724
1836
scholastics 1001 5987435 234724
1837
skulking 1002 28357832 8723648
1838
stated 1002 28357832 8723648
1839
suites 1002 28357832 8723648
1840
sureties 1001 5987435 234724
1841
testicle 1002 28357832 8723648
1842
tinily 1002 28357832 8723648
1843
tragedies 1001 5987435 234724
1844
trimmings 1001 5987435 234724
1845
vacuuming 1001 5987435 234724
1846
ventilate 1001 5987435 234724
1847
wallet 1001 5987435 234724
1848
Weissmuller 1002 28357832 8723648
1849
Wotan 1002 28357832 8723648
1850
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
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
1866
companynr int NOT NULL default '00',
1867
companyname char(30) NOT NULL default '',
1868
PRIMARY KEY (companynr),
1869
UNIQUE KEY companyname(companyname)
1870
) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1871
select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1872
companynr companyname
1885
select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1886
companynr companyname
1899
select * from t1,t1 t12;
1900
Period Varor_period Period Varor_period
1902
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;
1929
insert into t2 (fld1, companynr) values (999999,99);
1930
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1931
companynr companyname
1933
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1936
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1937
id select_type table type possible_keys key key_len ref rows Extra
1938
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
1940
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1941
id select_type table type possible_keys key key_len ref rows Extra
1942
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1943
1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1944
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1945
companynr companyname
1946
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1949
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1950
id select_type table type possible_keys key key_len ref rows Extra
1951
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1952
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1953
id select_type table type possible_keys key key_len ref rows Extra
1954
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1955
delete from t2 where fld1=999999;
1956
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1957
id select_type table type possible_keys key key_len ref rows Extra
1958
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1959
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1
1960
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1961
id select_type table type possible_keys key key_len ref rows Extra
1962
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1963
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1
1964
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1965
id select_type table type possible_keys key key_len ref rows Extra
1966
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1967
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.companynr 1
1968
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1969
id select_type table type possible_keys key key_len ref rows Extra
1970
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1971
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1972
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1973
id select_type table type possible_keys key key_len ref rows Extra
1974
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1975
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1976
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1977
id select_type table type possible_keys key key_len ref rows Extra
1978
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1979
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1980
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1981
id select_type table type possible_keys key key_len ref rows Extra
1982
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1983
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1984
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1985
id select_type table type possible_keys key key_len ref rows Extra
1986
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1987
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1988
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1989
id select_type table type possible_keys key key_len ref rows Extra
1990
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1991
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1992
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1993
id select_type table type possible_keys key key_len ref rows Extra
1994
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1995
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1996
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1997
id select_type table type possible_keys key key_len ref rows Extra
1998
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1999
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
2000
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
2001
id select_type table type possible_keys key key_len ref rows Extra
2002
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
2003
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
2004
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
2008
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
2009
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
2011
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
2012
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
fld1 companynr fld3 period
2014
38008 37 reporters 1008
2015
38208 37 Selfridge 1008
2016
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
fld1 companynr fld3 period
2018
38008 37 reporters 1008
2019
38208 37 Selfridge 1008
2020
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
fld1 companynr fld3 period
2022
38008 37 reporters 1008
2023
38208 37 Selfridge 1008
2024
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);
2027
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)));
2030
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;
2036
select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
2040
select fld1 from t2 where fld1 between 250502 and 250504;
2045
select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
2056
select count(*) from t1;
2059
select companynr,count(*),sum(fld1) from t2 group by companynr;
2060
companynr count(*) sum(fld1)
2073
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
2080
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
2081
count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
2082
70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
2083
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
2084
id select_type table type possible_keys key key_len ref rows filtered Extra
2085
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
2087
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
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
2089
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
2091
29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
2092
34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
2093
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
2094
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
2095
37 1 1 5987435 5987435 5987435 5987435.0000
2096
37 2 1 28357832 28357832 28357832 28357832.0000
2097
37 3 1 39654943 39654943 39654943 39654943.0000
2098
37 11 1 5987435 5987435 5987435 5987435.0000
2099
37 12 1 28357832 28357832 28357832 28357832.0000
2100
37 13 1 39654943 39654943 39654943 39654943.0000
2101
37 21 1 5987435 5987435 5987435 5987435.0000
2102
37 22 1 28357832 28357832 28357832 28357832.0000
2103
37 23 1 39654943 39654943 39654943 39654943.0000
2104
37 31 1 5987435 5987435 5987435 5987435.0000
2105
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;
2106
companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
2107
37 1 1 5987435 5987435 5987435 5987435.0000
2108
37 2 1 28357832 28357832 28357832 28357832.0000
2109
37 3 1 39654943 39654943 39654943 39654943.0000
2110
37 11 1 5987435 5987435 5987435 5987435.0000
2111
37 12 1 28357832 28357832 28357832 28357832.0000
2112
37 13 1 39654943 39654943 39654943 39654943.0000
2113
37 21 1 5987435 5987435 5987435 5987435.0000
2114
37 22 1 28357832 28357832 28357832 28357832.0000
2115
37 23 1 39654943 39654943 39654943 39654943.0000
2116
37 31 1 5987435 5987435 5987435 5987435.0000
2117
select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
2118
companynr count(price) sum(price) min(price) max(price) avg(price)
2119
37 12543 309394878010 5987435 39654943 24666736.6667
2120
78 8362 414611089292 726498 98439034 49582766.0000
2121
101 4181 3489454238 834598 834598 834598.0000
2122
154 4181 4112197254950 983543950 983543950 983543950.0000
2123
311 4181 979599938 234298 234298 234298.0000
2124
447 4181 9929180954 2374834 2374834 2374834.0000
2125
512 4181 3288532102 786542 786542 786542.0000
2126
select distinct mod(companynr,10) from t4 group by companynr;
2137
select distinct 1 from t4 group by companynr;
2140
select count(distinct fld1) from t2;
2141
count(distinct fld1)
2143
select companynr,count(distinct fld1) from t2 group by companynr;
2144
companynr count(distinct fld1)
2157
select companynr,count(*) from t2 group by companynr;
2171
select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
2172
companynr count(distinct concat(fld1,repeat(65,1000)))
2185
select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
2186
companynr count(distinct concat(fld1,repeat(65,200)))
2199
select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
2200
companynr count(distinct floor(fld1/100))
2213
select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
2214
companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
2227
select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
2230
select name,count(*) from t3 where name='cloakroom' group by name;
2233
select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
2236
select count(*) from t3 where name='cloakroom' and price2=823742;
2239
select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
2242
select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
2251
select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
2254
select companynr,companyname from t4 group by 1;
2255
companynr companyname
2268
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
2269
companynr companyname count(*)
2282
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
2285
select sum(Period)/count(*) from t1;
2286
sum(Period)/count(*)
2288
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;
2289
companynr count sum diff func
2290
37 12543 309394878010 0.0000 464091
2291
78 8362 414611089292 0.0000 652236
2292
101 4181 3489454238 0.0000 422281
2293
154 4181 4112197254950 0.0000 643874
2294
311 4181 979599938 0.0000 1300291
2295
447 4181 9929180954 0.0000 1868907
2296
512 4181 3288532102 0.0000 2140672
2297
select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
2300
select companynr,count(*) from t2 group by companynr order by 2 desc;
2314
select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
2322
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
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
2411
select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
2412
companynr fld3 sum(price)
2417
512 descendants 786542
2420
512 Micronesia 786542
2423
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
companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
2425
0 1 Omaha Omaha 5987435 5987435.0000
2426
36 1 dubbed dubbed 28357832 28357832.0000
2427
37 83 Abraham Wotan 1908978016 22999735.1325
2428
50 2 scribbled tapestry 68012775 34006387.5000
2429
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;
2430
t3.companynr+0 t2nr fld3 sum(price)
2432
37 11401 breaking 5987435
2433
37 11402 Romans 28357832
2434
37 11403 intercepted 39654943
2435
37 11501 bewilderingly 5987435
2436
37 11701 astound 5987435
2437
37 11702 admonishing 28357832
2438
37 11703 sumac 39654943
2439
37 12001 flanking 5987435
2440
37 12003 combed 39654943
2441
37 12301 Eulerian 5987435
2442
37 12302 dubbed 28357832
2443
37 12303 Kane 39654943
2444
37 12501 annihilates 5987435
2445
37 12602 Wotan 28357832
2446
37 12603 snatching 39654943
2447
37 12701 grazing 5987435
2448
37 12702 Baird 28357832
2449
37 12703 celery 39654943
2450
37 13601 handgun 5987435
2451
37 13602 foldout 28357832
2452
37 13603 mystic 39654943
2453
37 13801 intelligibility 5987435
2454
37 13802 Augustine 28357832
2455
37 13803 teethe 39654943
2456
37 13901 scholastics 5987435
2457
37 16001 audiology 5987435
2458
37 16201 wallet 5987435
2459
37 16202 parters 28357832
2460
37 16301 eschew 5987435
2461
37 16302 quitter 28357832
2462
37 16303 neat 39654943
2463
37 18001 jarring 5987435
2464
37 18002 tinily 28357832
2465
37 18003 balled 39654943
2466
37 18012 impulsive 28357832
2467
37 18013 starlet 39654943
2468
37 18021 lawgiver 5987435
2469
37 18022 stated 28357832
2470
37 18023 readable 39654943
2471
37 18032 testicle 28357832
2472
37 18033 Parsifal 39654943
2473
37 18041 Punjab 5987435
2474
37 18042 Merritt 28357832
2475
37 18043 Quixotism 39654943
2476
37 18051 sureties 5987435
2477
37 18052 puddings 28357832
2478
37 18053 tapestry 39654943
2479
37 18061 trimmings 5987435
2480
37 18062 humility 28357832
2481
37 18101 tragedies 5987435
2482
37 18102 skulking 28357832
2483
37 18103 flint 39654943
2484
37 18201 relaxing 5987435
2485
37 18202 offload 28357832
2486
37 18402 suites 28357832
2487
37 18403 lists 39654943
2488
37 18601 vacuuming 5987435
2489
37 18602 dentally 28357832
2490
37 18603 humanness 39654943
2491
37 18801 inch 5987435
2492
37 18802 Weissmuller 28357832
2493
37 18803 irresponsibly 39654943
2494
37 18811 repetitions 5987435
2495
37 18812 Antares 28357832
2496
37 19101 ventilate 5987435
2497
37 19102 pityingly 28357832
2498
37 19103 interdependent 39654943
2499
37 19201 Graves 5987435
2500
37 30501 neonatal 5987435
2501
37 30502 scribbled 28357832
2502
37 30503 chafe 39654943
2503
37 31901 realtor 5987435
2504
37 36001 elite 5987435
2505
37 36002 funereal 28357832
2506
37 38001 Conley 5987435
2507
37 38002 lectured 28357832
2508
37 38003 Abraham 39654943
2509
37 38011 groupings 5987435
2510
37 38012 dissociate 28357832
2511
37 38013 coexist 39654943
2512
37 38101 rusting 5987435
2513
37 38102 galling 28357832
2514
37 38103 obliterates 39654943
2515
37 38201 resumes 5987435
2516
37 38202 analyzable 28357832
2517
37 38203 terminator 39654943
2518
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;
2521
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;
2524
explain select fld3 from t2 where 1>2 or 2>3;
2525
id select_type table type possible_keys key key_len ref rows Extra
2526
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2527
explain select fld3 from t2 where fld1=fld1;
2528
id select_type table type possible_keys key key_len ref rows Extra
2529
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
2530
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
2534
select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
2538
select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
2545
select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
2552
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
2553
companynr companyname count(*)
2561
select count(*) from t2;
2564
select count(*) from t2 where fld1 < 098024;
2567
select min(fld1) from t2 where fld1>= 098024;
2570
select max(fld1) from t2 where fld1>= 098024;
2573
select count(*) from t3 where price2=76234234;
2576
select count(*) from t3 where companynr=512 and price2=76234234;
2579
explain select min(fld1),max(fld1),count(*) from t2;
2580
id select_type table type possible_keys key key_len ref rows Extra
2581
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2582
select min(fld1),max(fld1),count(*) from t2;
2583
min(fld1) max(fld1) count(*)
2585
select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2588
select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2589
count(*) min(t2nr) max(t2nr)
2591
select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2613
select max(t2nr) from t3 where price=983543950;
2616
select t1.period from t3 = t1 limit 1;
2619
select t1.period from t1 as t1 limit 1;
2622
select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2625
select period as ok_period from t1 limit 1;
2628
select period as ok_period from t1 group by ok_period limit 1;
2631
select 1+1 as summa from t1 group by summa limit 1;
2634
select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2643
show tables from test like "s%";
2645
show tables from test like "t?";
2647
show full columns from t2;
2648
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 #
2656
show full columns from t2 from test like 'f%';
2657
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 #
2663
show full columns from t2 from test like 's%';
2664
Field Type Collation Null Key Default Extra Privileges Comment
2666
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
2667
t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2668
t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2669
t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2670
drop table t4, t3, t2, t1;
2672
cont_nr int NOT NULL auto_increment,
2673
ver_nr int NOT NULL default '0',
2674
aufnr int NOT NULL default '0',
2675
username varchar(50) NOT NULL default '',
2676
hdl_nr int NOT NULL default '0',
2677
eintrag date NOT NULL default '0000-00-00',
2678
st_klasse varchar(40) NOT NULL default '',
2679
st_wert varchar(40) NOT NULL default '',
2680
st_zusatz varchar(40) NOT NULL default '',
2681
st_bemerkung varchar(255) NOT NULL default '',
2682
kunden_art varchar(40) NOT NULL default '',
2683
mcbs_knr int default NULL,
2684
mcbs_aufnr int NOT NULL default '0',
2685
schufa_status char(1) default '?',
2688
wf_igz int NOT NULL default '0',
2689
tarifcode varchar(80) default NULL,
2690
recycle char(1) default NULL,
2691
sim varchar(30) default NULL,
2692
mcbs_tpl varchar(30) default NULL,
2693
emp_nr int NOT NULL default '0',
2694
laufzeit int default NULL,
2695
hdl_name varchar(30) default NULL,
2696
prov_hdl_nr int NOT NULL default '0',
2697
auto_wirknetz varchar(50) default NULL,
2698
auto_billing varchar(50) default NULL,
2699
touch timestamp NOT NULL,
2700
kategorie varchar(50) default NULL,
2701
kundentyp varchar(20) NOT NULL default '',
2702
sammel_rech_msisdn varchar(30) NOT NULL default '',
2703
p_nr varchar(9) NOT NULL default '',
2704
suffix char(3) NOT NULL default '',
2705
PRIMARY KEY (cont_nr),
2706
KEY idx_aufnr(aufnr),
2707
KEY idx_hdl_nr(hdl_nr),
2708
KEY idx_st_klasse(st_klasse),
2710
KEY eintrag_idx(eintrag),
2711
KEY emp_nr_idx(emp_nr),
2714
KEY hdl_tag(eintrag,hdl_nr),
2715
KEY prov_hdl_nr(prov_hdl_nr),
2716
KEY mcbs_aufnr(mcbs_aufnr),
2717
KEY kundentyp(kundentyp),
2718
KEY p_nr(p_nr,suffix)
2720
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','','','');
2721
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','','','');
2722
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','','','');
2723
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','','','');
2724
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');
2725
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','','','');
2726
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','','','');
2727
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;
2729
Privat (Private Nutzung) Mobilfunk
2731
Warning 1052 Column 'kundentyp' in group statement is ambiguous
added
removed
tests/r/pool_of_threads.result
