oceanbase

two_order_by.result
176 строк · 7.2 Кб
Перенос по словам
1
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) union all (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1);
2
pk1	c1
3
1	44
4
2	33
5
3	22
6
3	22
7
4	11
8
4	11
9
select * from ((select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) union all (select /*+index(t1 i1) */ pk1,c1 from t2 where c1 > 10 order by c1) ) a order by pk1, a.c1;
10
pk1	c1
11
1	44
12
3	22
13
3	22
14
4	11
15
4	11
16
5	33
17
6	33
18
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) union  (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1);
19
pk1	c1
20
1	44
21
2	33
22
3	22
23
4	11
24
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) except (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1);
25
pk1	c1
26
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) intersect (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1);
27
pk1	c1
28
3	22
29
4	11
30
(select c1,c2,c3 from t1 where c1=11 order by c2) union (select c1,c2,c3 from t1 where c1=22 and c2 like 'column3_' order by c1) union distinct select c1,c2,c3 from t1 where c1>22 and c2 is not null and c3>='2013-12-11 11:11:11' union all (select c1,c2,c3 from t1 where c4<2 and c5>1 and c6=3 order by c4) except  (select c1,c2,c3 from t1 where c3 <= '2013-12-11 11:11:11' order by c3);
31
c1	c2	c3
32
11	column42	2013-12-11 11:11:14.000000
33
22	column32	2013-12-11 11:11:13.000000
34
33	column21	2013-12-11 11:11:12.000000
35
select * from ((select * from t1 where c1=44) union (select * from t1 where c1=33 and c2 like 'column%') union all (select * from t1 where c4>2 and c5<3 and c6 != 2)) as a;
36
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
37
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
38
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
39
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
40
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
41
select * from ((select * from t1 where c1=44) union all (select * from t1 where c1=33 and c2 like 'column%') except (select * from t1 where c4>2 and c5<3 and c6 != 2)) as a;
42
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
43
select * from ((select * from t1 where c1=44) intersect (select * from t1 where c1=33 and c2 like 'column%') union all (select * from t1 where c4>2 and c5<3 and c6 != 2)) as a;
44
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
45
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
46
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
47
(select c1,c4 from t1 where c1 in (33,11) order by c1) union all select c1,c4 from t1 where c1=44;
48
c1	c4
49
11	1
50
33	3
51
44	4
52
(select c1,c4 from t1 where c1 in (1,44) order by c1)  union all (select c1,c4 from t1 where c1 in (33,11) order by c1);
53
c1	c4
54
11	1
55
33	3
56
44	4
57
(select c1,c4 from t1 where c1 in (1,44) order by c1)  union all (select c1,c4 from t1 where c1 =33 order by c4);
58
c1	c4
59
33	3
60
44	4
61
select c1,c4 from t1 where c1=44 union (select c1,c4 from t1 where c1 not in (33,11) order by c1);
62
c1	c4
63
22	2
64
44	4
65
(select c1,c4 from t1 where c1 not in (33,11) order by c1) except select c1,c4 from t1 where c1=44;
66
c1	c4
67
22	2
68
(select c1,c4 from t1 where c1 not in (33,11) order by c1) intersect select c1,c4 from t1 where c1=44;
69
c1	c4
70
44	4
71
(select c4,c5,c6 from t1 order by c1 limit 2)  union all (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
72
c4	c5	c6
73
2	1	2
74
1	3	2
75
1	3	2
76
(select c4,c5,c6 from t1 order by c4 limit 2)  union (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
77
c4	c5	c6
78
2	1	2
79
1	3	2
80
(select c4,c5,c6 from t1 order by c2 limit 2)  except (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
81
c4	c5	c6
82
4	2	3
83
3	1	3
84
(select c4,c5,c6 from t1  order by c4 limit 2)  intersect (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
85
c4	c5	c6
86
1	3	2
87
select /*+index(t1 i1)*/ pk1,c1 from t1 union select /*+index(t2 i11)*/ pk1,c1 from t2;
88
pk1	c1
89
1	44
90
2	10
91
2	33
92
3	22
93
4	11
94
5	33
95
6	33
96
select /*+index(t1 i1)*/ pk1,c1 from t1 union all select /*+index(t2 i11)*/ pk1,c1 from t2;
97
pk1	c1
98
1	44
99
1	44
100
2	10
101
2	33
102
3	22
103
3	22
104
4	11
105
4	11
106
5	33
107
6	33
108
select /*+index(t1 i1)*/ pk1,c1 from t1 except select /*+index(t2 i11)*/ pk1,c1 from t2;
109
pk1	c1
110
2	33
111
select /*+index(t1 i1)*/ pk1,c1 from t1 intersect select /*+index(t2 i11)*/ pk1,c1 from t2;
112
pk1	c1
113
1	44
114
3	22
115
4	11
116
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t1.c1) union (select c1,c4 from t2 where c1=44 order by c1);
117
c1	c4
118
11	1
119
22	2
120
33	0
121
33	3
122
44	4
123
(select c1,c4 from t2 where c1=44 order by c1) union (select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t1.c1);
124
c1	c4
125
11	1
126
22	2
127
33	0
128
33	3
129
44	4
130
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t2.c1) union all (select c1,c4 from t2 where c1=44 order by c1);
131
c1	c4
132
11	1
133
22	2
134
33	0
135
33	3
136
44	4
137
44	4
138
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t1.c1) except (select c1,c4 from t2 where c1=44 order by c2);
139
c1	c4
140
11	1
141
22	2
142
33	0
143
33	3
144
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t2.c1) intersect (select c1,c4 from t2 where c1=44 order by c1);
145
c1	c4
146
44	4
147
select * from (select * from t1 where pk1 in(1,2,3,4,5,6)) a order by pk2;
148
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
149
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
150
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
151
3	primarykey3	2013-12-11 01:01:03.000000	22	column32	2013-12-11 11:11:13.000000	2	1	2
152
4	primarykey4	2013-12-11 01:01:04.000000	11	column42	2013-12-11 11:11:14.000000	1	3	2
153
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a order by pk2;
154
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
155
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
156
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
157
3	primarykey3	2013-12-11 01:01:03.000000	22	column32	2013-12-11 11:11:13.000000	2	1	2
158
4	primarykey4	2013-12-11 01:01:04.000000	11	column42	2013-12-11 11:11:14.000000	1	3	2
159
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a order by pk1;
160
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
161
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
162
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
163
3	primarykey3	2013-12-11 01:01:03.000000	22	column32	2013-12-11 11:11:13.000000	2	1	2
164
4	primarykey4	2013-12-11 01:01:04.000000	11	column42	2013-12-11 11:11:14.000000	1	3	2
165
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a  where a.c2>='abc' order by pk1;
166
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
167
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
168
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
169
3	primarykey3	2013-12-11 01:01:03.000000	22	column32	2013-12-11 11:11:13.000000	2	1	2
170
4	primarykey4	2013-12-11 01:01:04.000000	11	column42	2013-12-11 11:11:14.000000	1	3	2
171
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a  where a.c2>='abc' order by pk2;
172
pk1	pk2	pk3	c1	c2	c3	c4	c5	c6
173
1	primarykey1	2013-12-11 01:01:01.000000	44	column11	2013-12-11 11:11:11.000000	4	2	3
174
2	primarykey2	2013-12-11 01:01:02.000000	33	column21	2013-12-11 11:11:12.000000	3	1	3
175
3	primarykey3	2013-12-11 01:01:03.000000	22	column32	2013-12-11 11:11:13.000000	2	1	2
176
4	primarykey4	2013-12-11 01:01:04.000000	11	column42	2013-12-11 11:11:14.000000	1	3	2
177
oceanbase

Использование cookies
