SQL语句汇总(一)

最新推荐文章于 2024-08-16 18:04:14 发布

转载最新推荐文章于 2024-08-16 18:04:14 发布 · 487 阅读

文章标签：

#sql #c #null

Oracle 专栏收录该内容

58 篇文章

订阅专栏

本文介绍SQL中的高级查询技巧，包括去除重复记录、分页查询、实现分组汇总、行列转换及OrderBy子句的使用等。这些技巧对于提高SQL查询效率和解决复杂的数据处理问题非常实用。

转自: http://www.cnblogs.com/HondaHsu/archive/2007/06/22/792856.html

1.查询记录相同的其中一笔记录
SELECT mat12id, mat12name , bat22id,PRICE
  FROM (SELECT mat12id, mat12name , bat22id,PRICE,
         ROW_NUMBER() OVER (PARTITION BY mat12id ORDER BY bat22id DESC) RN
         FROM bat22)
WHERE RN = 1
2.查询从多少行到多少行的记录
SELECT * FROM
(SELECT A.*,  rownum r  FROM  ( SELECT *   FROM TableName   )A  WHERE rownum  <90 ) B
WHERE r > 70
或者:
select a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;

3.实现分组（小计，合计）

•DEPTNO EMPNO ENAME TOTAL_SAL

•---------- ----- ---------- ----------

•10 7782 CLARK 2450

•10 7839 KING 5000

•小计 7450

•20 7566 JONES 2975

•20 7788 SCOTT 3000

•20 7902 FORD 3000

•小计 8975

•30 7698 BLAKE 2850

•小计 2850

•合计 19275

SQL如下:
SELECT CASE
         WHEN a.deptno IS NULLTHEN '合计'
         WHEN a.deptno IS NOT NULL AND a.empno IS NULLTHEN '小计'
         ELSE '' || a.deptno
         END deptno,a.empno,a.ename, SUM(a.sal) total_sal
FROM scott.emp a
WHERE a.sal > 2000
GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());
4.行列转换

•数据格式一

•CARD_CODE Q BAL

•--------- ---------- ----------

•001 1 27

•001 2 10

•001 3 36

•001 4 97

•002 1 96

•002 2 12

•002 3 15

•002 4 32

•数据格式二

•CARD_CODE Q1 Q2 Q3 Q4

•--------- ---------- ---------- ---------- ----------

•001 27 10 36 97

•002 96 12 15 32

从格式一到格式二

SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code

ORDER BY 1;

5. Order By

select * from courses union select * from minors order by 1 对第一个字段进行升序排序
select * from courses union select * from minors order by desc 错误,加个表中存在(或转换过后的)的字段名(或转换过后的)
例如

select * from courses union select * from minors order by id desc select substr(id , 1 , 2) new_id, * from courses union select * from minors order by substr(id , 1 , 2) desc

ORDER BY 1 表示所select 的字段按第一个字段排序
ORDER BY ASC应该没有这样写法,ORDER BY 后面不是字段就是数字,
可以ORDER BY 1 ASC 或者ORDER BY COL1 ASC

ASC表示按升序排序,DESC表示按降序排序

转自; http://blog.youkuaiyun.com/lingxyd_0/article/details/2653187

--先看一下例子
SQL>   select   *   from   test_tab   order   by   1;

COL_A                       COL_B
----------   ----------
A                                       1
A                                       2
B                                       2
B                                       3
C                                       4
D                                       1
D                                     12
D                                   121
D                                     12

9   rows   selected

SQL>   select   *   from   test_tab   order   by   2;

COL_A                       COL_B
----------   ----------
A                                       1
D                                       1
A                                       2
B                                       2
B                                       3
C                                       4
D                                     12
D                                     12
D                                   121

9   rows   selected
SQL>   select   *   from   test_tab   order   by   3;

select   *   from   test_tab   order   by   3

ORA-01785:   ORDER   BY   项必须是   SELECT-list   表达式的数目

以上看来:1表示第一个栏位,2表示第二栏位   依此类推
当表中只有2个栏位时,ORDER   BY   3就会出错