peilinqian commented on issue #20084:
URL:
https://github.com/apache/shardingsphere/issues/20084#issuecomment-1211891894
opengauss
```
drop table t_order;
DROP TABLE
create table t_order (user_id int,order_id int ,ordername text);
CREATE TABLE
--insert into t_order
values(0,0,'00'),(0,1,'01'),(0,2,'02'),(0,3,''),(0,4,''),(1,0,'10'),(1,1,'11'),(1,2,'12'),(1,3,''),(1,4,''),(0,3,'03'),(4,4,'a44'),(4,5,'b45'),(4,6,'A56'),(5,4,'a44'),(5,5,'b45'),(5,6,'A56');
insert into t_order
values(0,0,'00'),(0,1,'01'),(0,2,'02'),(0,3,'a03'),(0,4,'b04'),(0,3,''),(0,4,''),(1,0,'10'),(1,1,'11'),(1,2,'12'),(1,3,'A13'),(1,3,'A13'),(1,4,'A14'),(1,3,''),(1,4,''),(0,3,'A03'),(4,4,'a44'),(4,5,'b45'),(4,6,'A56'),(5,4,'a44'),(5,5,'b45'),(5,6,'A56');
INSERT 0 22
--1、distinct，groupby
select distinct(user_id),count(ordername) as countnum from t_order group by
user_id order by user_id desc;
user_id | countnum
---------+----------
5 | 3
4 | 3
1 | 6
0 | 6
(4 rows)
--2、hint
explain (costs off,verbose) select /*+ tablescan(t_order)*/ * from t_order
where order_id>2 and user_id =1;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on public.t_order
Output: user_id, order_id, ordername
Filter: ((t_order.order_id > 2) AND (t_order.user_id = 1))
(3 rows)
--3、limit
select * from t_order where order_id >1 order by user_id,order_id desc limit
5;
user_id | order_id | ordername
---------+----------+-----------
0 | 4 |
0 | 4 | b04
0 | 3 |
0 | 3 | a03
0 | 3 | A03
(5 rows)
--4、limit offset
select * from t_order where order_id >1 order by user_id,order_id desc limit
1 offset 8;
user_id | order_id | ordername
---------+----------+-----------
1 | 3 |
(1 row)
--5、offset
select * from t_order where order_id >1 order by user_id,order_id desc
offset 6;
user_id | order_id | ordername
---------+----------+-----------
1 | 4 |
1 | 4 | A14
1 | 3 |
1 | 3 | A13
1 | 3 | A13
1 | 2 | 12
4 | 6 | A56
4 | 5 | b45
4 | 4 | a44
5 | 6 | A56
5 | 5 | b45
5 | 4 | a44
(12 rows)
--6、fetch
select * from t_order where order_id >=1 order by user_id,order_id desc
offset 1 fetch next 3 row only;
user_id | order_id | ordername
---------+----------+-----------
0 | 4 | b04
0 | 3 | a03
0 | 3 |
(3 rows)
--7、with
with temp_tb(i,j,k) as (select * from t_order) select * from temp_tb where j
=2;
i | j | k
---+---+----
0 | 2 | 02
1 | 2 | 12
(2 rows)
--8、order by null
select * from t_order order by ordername nulls last;
user_id | order_id | ordername
---------+----------+-----------
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
4 | 6 | A56
5 | 6 | A56
0 | 3 | a03
5 | 4 | a44
4 | 4 | a44
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
0 | 4 |
1 | 3 |
1 | 4 |
0 | 3 |
(22 rows)
select * from t_order order by ordername nulls first;
user_id | order_id | ordername
---------+----------+-----------
1 | 4 |
1 | 3 |
0 | 4 |
0 | 3 |
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
5 | 6 | A56
4 | 6 | A56
0 | 3 | a03
4 | 4 | a44
5 | 4 | a44
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
(22 rows)
--9、group by having
select user_id,count(order_id) from t_order group by user_id having
count(order_id)=6;
user_id | count
---------+-------
(0 rows)
select user_id,count(order_id) as num from t_order group by user_id having
count(order_id)>2;
user_id | num
---------+-----
1 | 8
4 | 3
0 | 8
5 | 3
(4 rows)
--10、group by cube
select user_id,count(order_id) from t_order group by cube(user_id) having
count(order_id)=4;
user_id | count
---------+-------
(0 rows)
--11、window
select user_id,order_id,count(ordername) over window1 typecount from t_order
where order_id = 0 window window1 as (partition by order_id);
user_id | order_id | typecount
---------+----------+-----------
0 | 0 | 2
1 | 0 | 2
(2 rows)
--12、union
select * from t_order where order_id =0 union select * from t_order where
order_id =3;
user_id | order_id | ordername
---------+----------+-----------
0 | 3 |
1 | 0 | 10
0 | 3 | a03
1 | 3 | A13
0 | 3 | A03
0 | 0 | 00
1 | 3 |
(7 rows)
--13、except
select order_id,ordername from t_order where order_id >0 except select
order_id,ordername from t_order where order_id >4;
order_id | ordername
----------+-----------
2 | 02
4 | a44
3 | A13
1 | 01
3 | A03
4 | A14
3 |
3 | a03
2 | 12
1 | 11
4 |
4 | b04
(12 rows)
--14、intersect
select * from t_order where order_id >2 intersect select * from t_order
where order_id <=4 order by user_id,order_id desc;
user_id | order_id | ordername
---------+----------+-----------
0 | 4 | b04
0 | 4 |
0 | 3 | a03
0 | 3 | A03
0 | 3 |
1 | 4 | A14
1 | 4 |
1 | 3 |
1 | 3 | A13
4 | 4 | a44
5 | 4 | a44
(11 rows)
--15、order byBLS_SORT--Case-insensitive sorting
select * from t_order order by NLSSORT(ordername,'NLS_SORT = generic_m_ci')
desc ,order_id,user_id;
user_id | order_id | ordername
---------+----------+-----------
0 | 3 |
1 | 3 |
0 | 4 |
1 | 4 |
4 | 5 | b45
5 | 5 | b45
0 | 4 | b04
4 | 6 | A56
5 | 6 | A56
4 | 4 | a44
5 | 4 | a44
1 | 4 | A14
1 | 3 | A13
1 | 3 | A13
0 | 3 | A03
0 | 3 | a03
1 | 2 | 12
1 | 1 | 11
1 | 0 | 10
0 | 2 | 02
0 | 1 | 01
0 | 0 | 00
(22 rows)
select * from t_order order by NLSSORT(ordername,'NLS_SORT =
generic_m_ci'),order_id,user_id;
user_id | order_id | ordername
---------+----------+-----------
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
0 | 3 | a03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
4 | 4 | a44
5 | 4 | a44
4 | 6 | A56
5 | 6 | A56
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
0 | 3 |
1 | 3 |
0 | 4 |
1 | 4 |
(22 rows)
--Case-sensitive sorting
select * from t_order order by ordername,user_id,order_id;
user_id | order_id | ordername
---------+----------+-----------
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
4 | 6 | A56
5 | 6 | A56
0 | 3 | a03
4 | 4 | a44
5 | 4 | a44
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
0 | 3 |
0 | 4 |
1 | 3 |
1 | 4 |
(22 rows)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
