代码之家  ›  专栏  ›  技术社区  ›  echo

SQL select语句中Order By 1的用途是什么?

sql-order-by sql
  •  125
  • echo  · 技术社区  · 15 年前

    我正在阅读一些工作中的旧代码,并注意到有几个视图带有 order by 1 条款。这能完成什么?

    例子: 

    Create view v_payment_summary AS
SELECT A.PAYMENT_DATE,
       (SELECT SUM(paymentamount)
          FROM payment B
         WHERE PAYMENT_DATE = B.PAYMENT_DATE
           and SOME CONDITION) AS SUM_X,
       (SELECT SUM(paymentamount)
          FROM payment B
         WHERE PAYMENT_DATE = B.PAYMENT_DATE
           and SOME OTHER CONDITION) AS SUM_Y    
FROM payment A    
ORDER BY 1;
    8 回复  |  直到 11 年前
        1
  •  227
  •   OMG Ponies    15 年前 

    ORDER BY 1

    …称为“序号”-数字表示基于SELECT子句中定义的列数的列。在您提供的查询中,它表示: 

    ORDER BY A.PAYMENT_DATE

    这不是推荐的做法,因为:

    2. 如果列顺序发生了变化,查询仍然有效,因此您有可能按不希望的顺序进行排序
        2
  •  41
  •   Aakash Goel    15 年前

    这在使用基于集的运算符(例如union)时非常有用 

    select cola
  from tablea
union
select colb
  from tableb
order by 1;
        3
  •  12
  •   kshitij    10 年前

    它只意味着按查询结果的第一列对视图或表进行排序。

        4
  •  7
  •   Christoph Jonathan    15 年前

    我相信在甲骨文中,它的意思是按列1排序

        5
  •  7
  •   CTKeane    15 年前

    这将按返回的第一列对结果进行排序。在本例中,它将按付款日期排序。

        6
  •  4
  •   nicV    9 年前

    ORDER BY 1 按第一列排序。

    Name 

    SELECT Name, Name FROM Segment ORDER BY 1
        7
  •  -1
  •   kriss    10 年前

    下面是一个示例测试WAMP服务器database:- 

    mysql> select * from user_privileges;

| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
   +--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def           | SELECT                  | YES          |
| 'root'@'localhost' | def           | INSERT                  | YES          |
| 'root'@'localhost' | def           | UPDATE                  | YES          |
| 'root'@'localhost' | def           | DELETE                  | YES          |
| 'root'@'localhost' | def           | CREATE                  | YES          |
| 'root'@'localhost' | def           | DROP                    | YES          |
| 'root'@'localhost' | def           | RELOAD                  | YES          |
| 'root'@'localhost' | def           | SHUTDOWN                | YES          |
| 'root'@'localhost' | def           | PROCESS                 | YES          |
| 'root'@'localhost' | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | REFERENCES              | YES          |
| 'root'@'localhost' | def           | INDEX                   | YES          |
| 'root'@'localhost' | def           | ALTER                   | YES          |
| 'root'@'localhost' | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost' | def           | SUPER                   | YES          |
| 'root'@'localhost' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | LOCK TABLES             | YES          |
| 'root'@'localhost' | def           | EXECUTE                 | YES          |
| 'root'@'localhost' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost' | def           | CREATE VIEW             | YES          |
| 'root'@'localhost' | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost' | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost' | def           | CREATE USER             | YES          |
| 'root'@'localhost' | def           | EVENT                   | YES          |
| 'root'@'localhost' | def           | TRIGGER                 | YES          |
| 'root'@'localhost' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1' | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1' | def           | DELETE                  | YES          |
| 'root'@'127.0.0.1' | def           | CREATE                  | YES          |
| 'root'@'127.0.0.1' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1' | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1' | def           | PROCESS                 | YES          |
| 'root'@'127.0.0.1' | def           | FILE                    | YES          |
| 'root'@'127.0.0.1' | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1' | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1' | def           | ALTER                   | YES          |
| 'root'@'127.0.0.1' | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1' | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1' | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1' | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | SHOW VIEW               | YES          |
| 'root'@'127.0.0.1' | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1' | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1' | def           | TRIGGER                 | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'       | def           | SELECT                  | YES          |
| 'root'@'::1'       | def           | INSERT                  | YES          |
| 'root'@'::1'       | def           | UPDATE                  | YES          |
| 'root'@'::1'       | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | DROP                    | YES          |
| 'root'@'::1'       | def           | RELOAD                  | YES          |
| 'root'@'::1'       | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | FILE                    | YES          |
| 'root'@'::1'       | def           | REFERENCES              | YES          |
| 'root'@'::1'       | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | SHOW DATABASES          | YES          |
| 'root'@'::1'       | def           | SUPER                   | YES          |
| 'root'@'::1'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'::1'       | def           | LOCK TABLES             | YES          |
| 'root'@'::1'       | def           | EXECUTE                 | YES          |
| 'root'@'::1'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'::1'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'       | def           | CREATE VIEW             | YES          |
| 'root'@'::1'       | def           | SHOW VIEW               | YES          |
| 'root'@'::1'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'::1'       | def           | CREATE USER             | YES          |
| 'root'@'::1'       | def           | EVENT                   | YES          |
| 'root'@'::1'       | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | CREATE TABLESPACE       | YES          |
| ''@'localhost'     | def           | USAGE                   | NO           |
+--------------------+---------------+-------------------------+--------------+
85 rows in set (0.00 sec)

    order by PRIVILEGE_TYPE 或者可以给 order by 3 . 注意第三栏( PRIVILEGE_TYPE )按字母顺序排序。 

    mysql> select * from user_privileges order by PRIVILEGE_TYPE;
+--------------------+---------------+-------------------------+--------------+
| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'127.0.0.1' | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | ALTER                   | YES          |
| 'root'@'localhost' | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | CREATE                  | YES          |
| 'root'@'localhost' | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost' | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1' | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'       | def           | CREATE TABLESPACE       | YES          |
| 'root'@'localhost' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1' | def           | CREATE USER             | YES          |
| 'root'@'::1'       | def           | CREATE USER             | YES          |
| 'root'@'localhost' | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | CREATE VIEW             | YES          |
| 'root'@'::1'       | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | DELETE                  | YES          |
| 'root'@'localhost' | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | DROP                    | YES          |
| 'root'@'localhost' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | EVENT                   | YES          |
| 'root'@'::1'       | def           | EVENT                   | YES          |
| 'root'@'localhost' | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1' | def           | EXECUTE                 | YES          |
| 'root'@'::1'       | def           | EXECUTE                 | YES          |
| 'root'@'localhost' | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1' | def           | FILE                    | YES          |
| 'root'@'::1'       | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1' | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | INSERT                  | YES          |
| 'root'@'localhost' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | LOCK TABLES             | YES          |
| 'root'@'::1'       | def           | LOCK TABLES             | YES          |
| 'root'@'localhost' | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1' | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | PROCESS                 | YES          |
| 'root'@'localhost' | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | REFERENCES              | YES          |
| 'root'@'localhost' | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1' | def           | REFERENCES              | YES          |
| 'root'@'::1'       | def           | RELOAD                  | YES          |
| 'root'@'localhost' | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1' | def           | RELOAD                  | YES          |
| 'root'@'::1'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | SELECT                  | YES          |
| 'root'@'::1'       | def           | SELECT                  | YES          |
| 'root'@'localhost' | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1' | def           | SHOW DATABASES          |  YES          |
| 'root'@'::1'       | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost' | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1' | def           | SHOW VIEW               | YES          |
| 'root'@'::1'       | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1' | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | SUPER                   | YES          |
| 'root'@'localhost' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | TRIGGER                 | YES          |
| 'root'@'localhost' | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | UPDATE                  | YES          |
| 'root'@'localhost' | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1' | def           | UPDATE                  | YES          |
| ''@'localhost'     | def           | USAGE                   | NO           |     +--------------------+---------------+-------------------------+--------------+
85 rows in set (0.00 sec)

    最后,一个长长的答案和大量的滚动。 下面是如何做到这一点,而不使用恼人的 into outfile

    三通E:/sqllogfile.txt;

    完成后,停止记录-

    发球;

    希望它能增加更多的清晰度。

    推荐文章
    Johnny T  ·  基于当前值的SQL合并表[重复]
    1 年前
    Shukurullox Komiljonov  ·  从记录中获得相互和解。使用SQL
    1 年前
    John D  ·  需要为NULL或NOT NULL的WHERE子句
    1 年前
    ojek  ·  如何对SQL结果进行分组和编号?
    1 年前
    senek  ·  如何在PL/SQL中将选择结果(列)放入数组中
    1 年前
    Sax  ·  规范化Google表格(第一步)
    1 年前
    Barış Uşaklı  ·  如何在ON冲突更新中使用CTE中的值
    1 年前
    Jatin  ·  检索卷计数的动态sql抛出错误语法错误[关闭]
    1 年前
    Andrus  ·  如何在sql中查找第二个匹配项
    1 年前
    Nebula Tech  ·  在SQL Server中搜索字符串中单词的多次出现
    1 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号