我经常发现自己在测试时需要非常复杂的 SQL 示例 abstraction concepts或者只是在使用 PostgreSQL、MySQL 甚至 SQLite 时比较数据库样式和结构。

我认为这意味着其他人需要疯狂的查询来打开我们的眼界，看看什么是可能的，并确保我们的数据库层能够处理我们抛给他们的任何东西。

那么，任何人都可以分享一些查询，即使是最顽固的 ORM-all-the-way 人也可以循环吗？

PostgreSQL

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

MySQL

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

最佳答案

查看 2009 年 11 月 OpenSQLCamp 期间的演讲。

标题:SQL For the Insane

还有 Recursion with SQL

问题

假设您正在跟踪用品并有一个名为 si_item 的字段和另一个名为 si_parentid 的字段。父级跟踪供应项目属于哪个子类。例如。您的纸质父级具有回收、非回收等子类。当有人拿走补给品时，您想返回完全限定名称，例如纸->回收->20 磅

解决方案

CREATE TABLE supplyitem(si_id integer PRIMARY KEY, si_parentid integer, si_item varchar(100));

--load up the table (multirow constructor introduced in 8.2)
INSERT INTO supplyitem(si_id,si_parentid, si_item)
VALUES (1, NULL, 'Paper'),
(2,1, 'Recycled'),
(3,2, '20 lb'),
(4,2, '40 lb'),
(5,1, 'Non-Recycled'),
(6,5, '20 lb'),
(7,5, '40 lb'),
(8,5, 'Scraps');

--Recursive query (introduced in 8.4 returns fully qualified name)
WITH RECURSIVE supplytree AS
(SELECT si_id, si_item, si_parentid, CAST(si_item As varchar(1000)) As si_item_fullname
FROM supplyitem
WHERE si_parentid IS NULL
UNION ALL
SELECT si.si_id,si.si_item,
    si.si_parentid,
    CAST(sp.si_item_fullname || '->' || si.si_item As varchar(1000)) As si_item_fullname
FROM supplyitem As si
    INNER JOIN supplytree AS sp
    ON (si.si_parentid = sp.si_id)
)
SELECT si_id, si_item_fullname
FROM supplytree
ORDER BY si_item_fullname;

结果看起来像

si_id |      si_item_fullname
------+-----------------------------
 1    | Paper
 5    | Paper->Non-Recycled
 6    | Paper->Non-Recycled->20 lb
 7    | Paper->Non-Recycled->40 lb
 8    | Paper->Non-Recycled->Scraps
 2    | Paper->Recycled
 3    | Paper->Recycled->20 lb
 4    | Paper->Recycled->40 lb

关于sql - 非常复杂的 (Postgre/My)SQL 示例？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1925879/