1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
create table t1 (libname varchar(21) not null, city text, primary key (libname));
create table t2 (isbn varchar(21) not null, author text, title text, primary key (isbn));
create table t3 (isbn varchar(21) not null, libname varchar(21) not null, quantity int ,primary key (isbn,libname));
insert into t2 values ('001','Daffy','A duck''s life');
insert into t2 values ('002','Bugs','A rabbit\'s life');
insert into t2 values ('003','Cowboy','Life on the range');
insert into t2 values ('000','Anonymous','Wanna buy this book?');
insert into t2 values ('004','Best Seller','One Heckuva book');
insert into t2 values ('005','EveryoneBuys','This very book');
insert into t2 values ('006','San Fran','It is a san fran lifestyle');
insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
insert into t3 values('000','New York Public Libra','1');
insert into t3 values('001','New York Public Libra','2');
insert into t3 values('002','New York Public Libra','3');
insert into t3 values('003','New York Public Libra','4');
insert into t3 values('004','New York Public Libra','5');
insert into t3 values('005','New York Public Libra','6');
insert into t3 values('006','San Fransisco Public','5');
insert into t3 values('007','Berkeley Public1','3');
insert into t3 values('007','Berkeley Public2','3');
insert into t3 values('001','NYC Lib','8');
insert into t1 values ('New York Public Libra','New York');
insert into t1 values ('San Fransisco Public','San Fran');
insert into t1 values ('Berkeley Public1','Berkeley');
insert into t1 values ('Berkeley Public2','Berkeley');
insert into t1 values ('NYC Lib','New York');
select t2.isbn,city,t1.libname,count(t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city,t1.libname;
isbn city libname a
007 Berkeley Berkeley Public1 1
007 Berkeley Berkeley Public2 1
000 New York New York Public Libra 6
001 New York NYC Lib 1
006 San Fran San Fransisco Public 1
select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct t1.libname) > 1;
isbn city libname a
007 Berkeley Berkeley Public1 2
000 New York New York Public Libra 2
select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct concat(t1.libname,'a')) > 1;
isbn city libname a
007 Berkeley Berkeley Public1 2
000 New York New York Public Libra 2
select t2.isbn,city,@bar:=t1.libname,count(distinct t1.libname) as a
from t3 left join t1 on t3.libname=t1.libname left join t2
on t3.isbn=t2.isbn group by city having count(distinct
t1.libname) > 1;
isbn city @bar:=t1.libname a
007 Berkeley Berkeley Public1 2
000 New York New York Public Libra 2
SELECT @bar;
@bar
Berkeley Public2
select t2.isbn,city,concat(@bar:=t1.libname),count(distinct t1.libname) as a
from t3 left join t1 on t3.libname=t1.libname left join t2
on t3.isbn=t2.isbn group by city having count(distinct
t1.libname) > 1;
isbn city concat(@bar:=t1.libname) a
007 Berkeley Berkeley Public1 2
000 New York New York Public Libra 2
SELECT @bar;
@bar
Berkeley Public2
drop table t1, t2, t3;
create table t1 (f1 int);
insert into t1 values (1);
create table t2 (f1 int,f2 int);
select t1.f1,count(distinct t2.f2),count(distinct 1,NULL) from t1 left join t2 on t1.f1=t2.f1 group by t1.f1;
f1 count(distinct t2.f2) count(distinct 1,NULL)
1 0 0
drop table t1,t2;
create table t1 (f int);
select count(distinct f) from t1;
count(distinct f)
0
drop table t1;
create table t1 (a char(3), b char(20), primary key (a, b));
insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
select count(distinct a) from t1 group by b;
count(distinct a)
1
1
drop table t1;
create table t1 (f1 int, f2 int);
insert into t1 values (0,1),(1,2);
select count(distinct if(f1,3,f2)) from t1;
count(distinct if(f1,3,f2))
2
drop table t1;
create table t1 (i int);
insert into t1 values (0), (1);
create view v1 as select * from t1;
select count(distinct i) from v1;
count(distinct i)
2
drop table t1;
drop view v1;
create table t1 (user_id char(64) character set utf8);
insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17);
set @@tmp_table_size = 1024;
Warnings:
Warning 1292 Truncated incorrect tmp_table_size value: '1024'
select count(distinct user_id) from t1;
count(distinct user_id)
17
alter table t1 modify user_id char(128) character set utf8;
select count(distinct user_id) from t1;
count(distinct user_id)
17
drop table t1;
set @@tmp_table_size = default;
create table t1 (
a VARCHAR(1020),
b int
);
insert into t1 values
( 0 , 1 ),
( 1 , 2 ),
( 2 , 3 ),
( 3 , 4 ),
( 4 , 5 ),
( 5 , 6 ),
( 6 , 7 ),
( 7 , 8 ),
( 8 , 9 ),
( 9 , 10 ),
( 0 , 11 ),
( 1 , 12 ),
( 2 , 13 ),
( 3 , 14 );
set @@tmp_table_size=1024;
Warnings:
Warning 1292 Truncated incorrect tmp_table_size value: '1024'
select count(distinct a) from t1;
count(distinct a)
10
drop table t1;
set @@tmp_table_size = default;
#
# End of 5.5 tests
#
#
# MDEV-26452 SIGSEGV in Item::cleanup from Item::cleanup_processor
#
create table t (a int,b date,primary key(a,b));
select b,count(distinct a) from t group by b having b is null;
b count(distinct a)
drop table t;
#
# End of 10.7 tests
#
|
