blocks|key|1643120|text|特定于T-SQL+:从存储过程返回的结果集中进行选择的一种很好的方法，它不需要将结果放入临时表或使用一些晦涩难懂的函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643121|SELECT+*+FROM+EXEC+[master].[dbo].[xp_readerrorlog]|code-block|syntax|javascript|1643122|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

T-SQL Specific: A decent way to select from a result set returned by a stored procedure that doesn't involve putting it into a temporary table or using some obscure function.

<pre><code>SELECT * FROM EXEC [master].[dbo].[xp_readerrorlog]
</code></pre>

blocks|key|1428881|text|我知道这是非常不现实的，但我希望他们能使插入和更新的语法保持一致。谈论无偿的非正交性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428882|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I know it's wildly unrealistic, but I wish they'd make the syntax of INSERT and UPDATE consistent. Talk about gratuitous non-orthogonality.

blocks|key|1428457|text|用于管理日期(或数字)范围的运算符：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428458|where+interval(date0,+date1)+intersects+interval(date3,+date4)+|code-block|syntax|javascript|1428459|编辑:日期或数字，当然是相同的。|1428460|编辑2:+Oracle似乎有事情要做，没有文档记录的重叠谓词。更多信息here。|offset|length|1428461|entityMap|0|LINK|mutability|MUTABLE|url|http://www.orafaq.com/node/2067^0|0|0|0|Z|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Operator to manage range of dates (or numbers):

<pre><code>where interval(date0, date1) intersects interval(date3, date4) 
</code></pre>

EDIT: Date or numbers, of course are the same.

EDIT 2: It seems Oracle have something to go, the undocumented OVERLAPS predicate. More info <a href="http://www.orafaq.com/node/2067" rel="nofollow noreferrer">here</a>.

blocks|key|1428170|text|使用分层数据遍历树的一种不错的方式。Oracle有CONNECT+BY，但是简单而常见的结构存储一个对象和一个自引用连接回'parent‘的表是很难自然地查询的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1428171|entityMap|0|LINK|mutability|MUTABLE|url|http://www.adp-gmbh.ch/ora/sql/connect_by.html^0|P|A|P|A|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

A decent way of walking a tree with hierarchical data. Oracle has <a href="http://www.adp-gmbh.ch/ora/sql/connect_by.html" rel="nofollow noreferrer"><code>CONNECT BY</code></a> but the simple and common structure of storing an object and a self-referential join back to the table for 'parent' is hard to query in a natural way.

blocks|key|1428220|text|SQL+Server比SQL更多，但与源代码管理的集成更好。最好是SVN而不是VSS。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428221|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

More SQL Server than SQL but better integration with Source Control. Preferably SVN rather than VSS.

blocks|key|1643659|text|我希望看到在字符串处理中使用正则表达式的能力。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643660|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I would like to see the ability to use Regular Expressions in string handling.

blocks|key|1643207|text|一种动态指定列/表的方法，而不必求助于在另一个上下文中执行的完全动态sql。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643208|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

A way of dynamically specifying columns/tables without having to resort to full dynamic sql that executes in another context.

blocks|key|1643447|text|能够定义列基于其他列和无限(包括消除歧义)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643448|这是一个人为的例子，而不是一个真实的案例，但我想你会明白我要说的：|1643449|SELECT+LTRIM(t1.a)+AS+[a.new]
++++,REPLICATE('+',+20+-+LEN([a.new]))+%2B+[a.new]+AS+[a.conformed]
++++,LEN([a.conformed])+as+[a.length]
FROM+t1
INNER+JOIN+TABLE+t2
++++ON+[a.new]+=+t2.a
ORDER+BY+[a.new]|code-block|syntax|javascript|1643450|而不是：|1643451|SELECT+LTRIM(t1.a)+AS+[a.new]
++++,REPLICATE('+',+20+-+LEN(LTRIM(t1.a)))+%2B+LTRIM(t1.a)+AS+[a.conformed]
++++,LEN(REPLICATE('+',+20+-+LEN(LTRIM(t1.a)))+%2B+LTRIM(t1.a))+as+[a.length]
FROM+t1
INNER+JOIN+TABLE+t2
++++ON+LTRIM(t1.a)+=+t2.a
ORDER+BY+LTRIM(t1.a)|1643452|现在，在SQL+Server+2005和更高版本中，我将使用CTE并在连续的层中构建。|1643453|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Ability to define columns based on other columns ad infinitum (including disambiguation).

This is a contrived example and not a real world case, but I think you'll see where I'm going:

<pre><code>SELECT LTRIM(t1.a) AS [a.new]
 ,REPLICATE(' ', 20 - LEN([a.new])) + [a.new] AS [a.conformed]
 ,LEN([a.conformed]) as [a.length]
FROM t1
INNER JOIN TABLE t2
 ON [a.new] = t2.a
ORDER BY [a.new]
</code></pre>

instead of:

<pre><code>SELECT LTRIM(t1.a) AS [a.new]
 ,REPLICATE(' ', 20 - LEN(LTRIM(t1.a))) + LTRIM(t1.a) AS [a.conformed]
 ,LEN(REPLICATE(' ', 20 - LEN(LTRIM(t1.a))) + LTRIM(t1.a)) as [a.length]
FROM t1
INNER JOIN TABLE t2
 ON LTRIM(t1.a) = t2.a
ORDER BY LTRIM(t1.a)
</code></pre>

Right now, in SQL Server 2005 and up, I would use a CTE and build up in successive layers.

blocks|key|1429139|text|我希望供应商真正标准化他们的SQL。他们都对此感到内疚。来自MySQL和PostGresql的LIMIT/OFFSET子句是一个很好的解决方案，似乎没有人能做到这一点。Oracle有自己的显式JOIN语法，而其他所有人都使用ANSI92。MySQL应该弃用CONCAT()函数，像其他人一样使用%7C%7C。而且有许多条款和声明超出了标准，可以得到更广泛的传播。MySQL的REPLACE就是一个很好的例子。还有更多关于类型转换和比较的问题，列类型的怪癖，序列等等。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1429140|entityMap^0|1B|C|2O|4|3K|8|43|2|53|7|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|G|$]]

I'd like the vendors to actually standardise their SQL. They're all guilty of it. The <code>LIMIT/OFFSET</code> clause from MySQL and PostGresql is a good solution that no-one else appears to do. Oracle has it's own syntax for explicit <code>JOIN</code>s whilst everyone else uses ANSI-92. MySQL should deprecate the <code>CONCAT()</code> function and use <code>||</code> like everyone else. And there are numerous clauses and statements that are outside the standard that could be wider spread. MySQL's <code>REPLACE</code> is a good example. There's more, with issues about casting and comparing types, quirks of column types, sequences, etc etc etc.

blocks|key|1429177|text|参数化的order+by，如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429178|select+*+from+tableA+order+by+@columName|code-block|syntax|javascript|1429179|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

parameterized order by, as in:

<pre><code>
select * from tableA order by @columName
</code></pre>

blocks|key|1643175|text|SQL中的支持指定是希望优化查询计划以快速返回第一行，还是快速返回所有行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643176|Oracle有FIRST_ROWS提示的概念，但是该语言中的标准方法会很有用。|1643177|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Support in SQL to specify if you want your query plan to be optimized to return the first rows quickly, or all rows quickly.

Oracle has the concept of FIRST_ROWS hint, but a standard approach in the language would be useful.

blocks|key|1643232|text|自动反规范化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643233|但我可能是在做梦。|1643234|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Automatic denormalization.

But I may be dreaming.

blocks|key|1643280|text|改进的数据透视表。我想告诉它根据在数据中找到的键自动创建列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643281|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Improved pivot tables. I'd like to tell it to automatically create the columns based on the keys found in the data.

blocks|key|1428858|text|在我的愿望清单上是一个数据库，支持检查约束中的子查询，而不必依赖于materialized+view+tricks。以及支持SQL标准的“断言”的数据库，即可以跨越多个表的约束。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1428859|其他东西:一个与元数据相关的函数，如果一组可能的值很低，它将返回给定列的可能值。也就是说，如果一列有指向另一列的外键，它将返回被引用的列中的现有值。如果列有像"CHECK+foo+IN(+1,2,3+)“这样的检查约束，它将返回1，2，3。这将使基于表模式创建GUI元素变得更容易:如果函数返回两个值的列表，程序员可以决定单选按钮小部件是否相关-或者如果函数返回-+10个值，则应用程序将显示一个下拉小部件。等。|1428860|entityMap|0|LINK|mutability|MUTABLE|url|http://www.dbazine.com/oracle/or-articles/tropashko8^0|X|O|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

On my wish list is a database supporting sub-queries in CHECK-constraints, without having to rely on <a href="http://www.dbazine.com/oracle/or-articles/tropashko8" rel="nofollow noreferrer">materialized view tricks</a>. And a database which supports the SQL standard's "assertions", i.e. constraints which may span more than one table.

Something else: A metadata-related function which would return the possible values of a given column, if the set of possible values is low. I.e., if a column has a foreign key to another column, it would return the existing values in the column being referred to. Of if the column has a CHECK-constraint like "CHECK foo IN(1,2,3)", it would return 1,2,3. This would make it easier to create GUI elements based on a table schema: If the function returned a list of two values, the programmer could decide that a radio button widget would be relevant - or if the function returned - e.g. - 10 values, the application showed a dropdown-widget instead. Etc.

blocks|key|1643969|text|在PostgreSQL中执行UPSERT或MERGE。这是一个功能，它的缺失只会让我感到困惑。Postgres拥有其他一切；为什么他们不能一起行动并实现它，即使是有限的形式？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643970|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

UPSERT or MERGE in PostgreSQL. It's the one feature whose absence just boggles my mind. Postgres has everything else; why can't they get their act together and implement it, even in limited form?

blocks|key|1644303|text|对子查询检查约束，我的意思是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1644304|CHECK+(+1+>+(SELECT+COUNT(*)+FROM+TABLE+WHERE+A+=+COLUMN))|code-block|syntax|javascript|1644305|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Check constraints with subqueries, I mean something like:

<pre><code>CHECK ( 1 &gt; (SELECT COUNT(*) FROM TABLE WHERE A = COLUMN))
</code></pre>

blocks|key|1644416|text|WITH子句对于SELECT以外的其他语句，它表示用于UPDATE和DELETE。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1644417|例如：|1644418|WITH+table+as+(
++SELECT+...
)
DELETE+from+table2+where+not+exists+(SELECT+...)|code-block|syntax|javascript|1644419|entityMap^0|0|4|8|6|R|6|Y|6|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

<code>WITH</code> clause for other statements other than <code>SELECT</code>, it means for <code>UPDATE</code> and <code>DELETE</code>.

For instance:

<pre><code>WITH table as (
 SELECT ...
)
DELETE from table2 where not exists (SELECT ...)
</code></pre>

blocks|key|1429567|text|我称之为引用连接的东西。它通过在两个表之间隐式地使用外部KEY...REFERENCES约束将它们连接在一起。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429568|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Something which I call REFERENCE JOIN. It joins two tables together by implicitly using the FOREIGN KEY...REFERENCES constraint between them.

blocks|key|1643372|text|Group+by上的字符串聚合(在Oracle中可以使用此trick)：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1643373|SELECT+deptno,+string_agg(ename)+AS+employees
FROM+++emp
GROUP+BY+deptno;

DEPTNO+EMPLOYEES
----------+--------------------------------------------------
++++10+CLARK,KING,MILLER
++++20+SMITH,FORD,ADAMS,SCOTT,JONES
++++30+ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD|code-block|syntax|javascript|1643374|entityMap|0|LINK|mutability|MUTABLE|url|http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php^0|T|5|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

String Agregation on Group by (In Oracle is possible with this <a href="http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php" rel="nofollow noreferrer">trick</a>):

<pre><code>SELECT deptno, string_agg(ename) AS employees
FROM emp
GROUP BY deptno;

DEPTNO EMPLOYEES
---------- --------------------------------------------------
 10 CLARK,KING,MILLER
 20 SMITH,FORD,ADAMS,SCOTT,JONES
 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
</code></pre>

blocks|key|1428947|text|抽象表和子类化|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428948|create+abstract+table+person
++(
++id+primary+key,
+++name+varchar(50)
++);

create+table+concretePerson+extends+person
++(
++birth+date,
++death+date
++);

create+table+fictionalCharacter++extends+person
++(
++creator+int+references+concretePerson.id++++++
++);|code-block|syntax|javascript|1428949|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Abstract tables and sub-classing

<pre><code>create abstract table person
 (
 id primary key,
 name varchar(50)
 );

create table concretePerson extends person
 (
 birth date,
 death date
 );

create table fictionalCharacter extends person
 (
 creator int references concretePerson.id 
 );
</code></pre>

blocks|key|1429270|text|在Sql+Server中增加了对时态数据库的支持。间隔、重叠等。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429271|增加了对Sql+Server的支持，包括LAG、LEAD和TOP。|1429272|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Increased temporal database support in Sql Server. Intervals, overlaps, etc.

Increased OVER support in Sql Server, including LAG, LEAD, and TOP.

blocks|key|1429464|text|检查约束的注释。使用此功能，应用程序(或引发错误时的数据库本身)可以查询元数据并检索该注释以将其显示给用户。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429465|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Comments for check constraints. With this feature, an application (or the database itself when raising an error) can query the metadata and retrieve that comment to show it to the user.

blocks|key|1429795|text|如果优化器生成的计划与测试查询时使用的计划不同，则会自动发出dba通知。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429796|换句话说，每个查询都可以注册。此时，计划将被保存。稍后，当执行查询时，如果计划发生更改，则dba会收到发生意外情况的通知。|1429797|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Automated dba notification in the case where the optimizer generates a plan different that the plan that that the query was tested with.

In other words, every query can be registered. At that time, the plan is saved. Later when the query is executed, if there is a change to the plan, the dba receives a notice, that something unexpected occurred.

blocks|key|1644777|text|1)+LEFT+%7C+RIGHT+SEMI+JOIN和LEFT+%7C+RIGHT+and+and+JOIN这些将允许我编写如下内容|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1644778|--+return+customers+who+have+placed+at+least+one+order
SELECT+c.*
++FROM+Customers+c
++LEFT+SEMI+JOIN+o+ON+o.CustomerId+=+c.Id

--+return+customers+who+have+NOT+placed+any+order
SELECT+c.*
++FROM+Customers+c
++LEFT+ANTI+JOIN+o+ON+o.CustomerId+=+c.Id|code-block|syntax|javascript|1644779|这将产生完全相同的结果|1644780|SELECT+c.*
++FROM+Customers+c
+WHERE+c.Id+IN(SELECT+CustomerId+FROM+Orders)|1644781|和|1644782|SELECT+c.*
++FROM+Customers+c
+WHERE+c.Id+NOT+IN(SELECT+CustomerId+FROM+Orders)|1644783|，分别。然而，IN+(或几乎等效的存在)语法比我提出的语法混乱得多，特别是在更复杂的情况下。|1644784|当然，半/反连接表不能被引用，因此这将是非法的：|1644785|--+Error,+can't+reference+semi+joined+table.
SELECT+c.*,+o.OrderNumber
++FROM+Customers+c
++LEFT+SEMI+JOIN+o+ON+o.CustomerId+=+c.Id|1644786|2)如果能有一个好的解决方案就好了|1644787|WHERE+Column+IN('a',+'b',+'c')|1644788|当您不知道要搜索的值数时会出现问题。也许有可能允许|1644789|WHERE+Column+IN(ARRAY+@array)|1644790|调用代码将@array绑定到一个数组。|1644791|编辑:我刚刚又想到了一个|1644792|3)约束系统的某种可扩展性，它允许对在并发环境中完美工作的表之间的约束进行编码，并允许我手动执行任何锁定和验证，以确保约束始终得到满足。触发器目前可以使用，但考虑到并发性，它们很难正确使用。|1644793|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1E|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1H|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1I|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|D|7|1K|8|@]|9|@]|A|$E|F]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$]]|$1|10|3|11|5|D|7|1M|8|@]|9|@]|A|$E|F]]|$1|12|3|13|5|6|7|1N|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|1O|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|1P|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|19|$]]

1) [LEFT | RIGHT] SEMI JOIN and [LEFT | RIGHT] ANTI JOIN
These would allow me to write something like

<pre><code>-- return customers who have placed at least one order
SELECT c.*
 FROM Customers c
 LEFT SEMI JOIN o ON o.CustomerId = c.Id

-- return customers who have NOT placed any order
SELECT c.*
 FROM Customers c
 LEFT ANTI JOIN o ON o.CustomerId = c.Id
</code></pre>

This would have exactly the same result as

<pre><code>SELECT c.*
 FROM Customers c
 WHERE c.Id IN(SELECT CustomerId FROM Orders)
</code></pre>

and 

<pre><code>SELECT c.*
 FROM Customers c
 WHERE c.Id NOT IN(SELECT CustomerId FROM Orders)
</code></pre>

, respectively. However the IN (or the pretty much equivalent EXISTS) syntax is much messier than my proposed syntax, especially in more complicated cases.

Of course, the semi/anti-joined table can not be referenced so this would be ILLEGAL:

<pre><code>-- Error, can't reference semi joined table.
SELECT c.*, o.OrderNumber
 FROM Customers c
 LEFT SEMI JOIN o ON o.CustomerId = c.Id
</code></pre>

2) It woud be nice to have a good solution to the

<pre><code>WHERE Column IN('a', 'b', 'c')
</code></pre>

problem when you don't know the number of values to search for. Perhaps it could be possible to allow

<pre><code>WHERE Column IN(ARRAY @array)
</code></pre>

and the calling code would bind @array to an array.

Edit: I just thought of one more

3) Some kind of extensibility to the constraint system, which allows coding of constraints between tables which work perfectly in concurrent environments and which lets me manually do any locking and validation to ensure that the constraint is always satisfied. Triggers can be used currently, but they are very hard to get right considering concurrency.

blocks|key|1428619|text|管理其他表上的间隔日期的前置键|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428620|例如，对于表：|1428621|TABLE_A(begin+DATE,+end+DATE)
TABLE_B(day+DATE)|code-block|syntax|javascript|1428622|在TABLE_B上定义一些方法|offset|length|style|CODE|1428623|FOREIGN+KEY+(day)
++REFERENCES+INTERVAL+ON+TABLE_A+(begin,+endd)|1428624|entityMap^0|0|0|0|1|7|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Foreing keys that manage interval dates on other tables

For instance, for tables:

<pre><code>TABLE_A(begin DATE, end DATE)
TABLE_B(day DATE)
</code></pre>

Some way to define On <code>TABLE_B</code>:

<pre><code>FOREIGN KEY (day)
 REFERENCES INTERVAL ON TABLE_A (begin, endd)
</code></pre>

blocks|key|1428680|text|为那些不支持SQL标准的平台高效地实现SQL标准，例如域。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428681|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Efficient implementation of SQL standards, such as DOMAINs, for those platforms that don't support them.

blocks|key|1428726|text|部分外键约束。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428727|如果有这些表：|1428728|create+table+prova_a+(a+number,+b+number);
alter+table+prova_a+add+primary+key+(a,b);
create+table+prova_b+(a+number,+b+number);
alter+table+prova_b+add+foreign+key+(a,b)+references+prova_a(a,b)+;
insert+into+prova_a++values+(1,2);|code-block|syntax|javascript|1428729|您可以毫无错误地插入以下内容：|1428730|insert+into+prova_b++values+(123,null);
insert+into+prova_b++values+(null,null);
insert+into+prova_b++values+(null,123);|1428731|我希望在不使用check约束的情况下避免这种情况。|1428732|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Partial Foreing key constraint.

If have those tables:

<pre><code>create table prova_a (a number, b number);
alter table prova_a add primary key (a,b);
create table prova_b (a number, b number);
alter table prova_b add foreign key (a,b) references prova_a(a,b) ;
insert into prova_a values (1,2);
</code></pre>

You can insert this without error:

<pre><code>insert into prova_b values (123,null);
insert into prova_b values (null,null);
insert into prova_b values (null,123);
</code></pre>

I wish something to avoid this without using check constraints.

blocks|key|1428941|text|LINQ-like功能集成到SQL+:-)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428942|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

LINQ-like functionalities integration to SQL :-)

blocks|key|1643867|text|在递归表上选择：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643868|+++select+*+from+rdfClass+where+rdfClass.uri+is+instance+of+"foaf:Person";|code-block|syntax|javascript|1643869|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

select on recursive tables:

<pre><code> select * from rdfClass where rdfClass.uri is instance of "foaf:Person";
</code></pre>

blocks|key|1643902|text|不再有记录大小/命令长度限制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643903|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

No more record size/command length limits.

blocks|key|1429219|text|凯文比我早了几秒钟。但更普遍的是，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429220|select+@columnName+from+@tableName
order+by+@otherColumnName|code-block|syntax|javascript|1429221|等|1429222|它将呈现大量令人讨厌的字符串连接，然后是|1429223|exec+(@sql)+|1429224|即刻没有必要。|1429225|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Kevin beat me to it by a couple of seconds... but more generally, 

<pre><code>select @columnName from @tableName
order by @otherColumnName
</code></pre>

etc

It would render swathes of nasty string concatenation followed by

<pre><code>exec (@sql) 
</code></pre>

instantly unnecessary.

blocks|key|1429314|text|特定于SQL+Server：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429315|一些不错的日期函数，比如TRUNC。对全文搜索的改进(更好地控制匹配逻辑)|1429316|如果SQL+server可以在同一数据库和日志文件(共享文件)中存储不同的数据库，我会很高兴，这样我就可以一次性备份整个服务器。|1429317|当我编译存储过程时(不仅仅是当我运行它时)，它的完整语法和错误检查|1429318|是的，这开始听起来像是“我想要SQL+Server中的所有Oracle功能，而不是所有的复杂性(和成本)！”|1429319|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

SQL Server specific: 

Some decent date functions, like TRUNC.
Improvements to full text searching (better control over matching logic)

I would LOVE it if SQL server could store different databases within the same database and log files (shared FILEGROUPS) so I can backup the WHOLE server in one go.

Full syntax and error checking of a stored procedure when I compile it (not only when I run it)

Yes, this is starting to sound like "I want all the Oracle features in SQL Server without all the complexity (and cost!)"

blocks|key|1644264|text|我希望能够在创建索引时添加WHERE子句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1644265|CREATE+INDEX+BAR+ON+FOO+(FooName,+FooId)+WHERE+FooEnabled+=+1;|code-block|syntax|javascript|1644266|然后，优化器可以在像这样处理SQL时使用它：|1644267|SELECT+FooId,+FooName
FROM+Foo
WHERE+FooEnabled+=+1
ORDER+BY+FooName;|1644268|从索引创建和优化器的角度来看，这似乎是一个可以解决的问题。实际上，您可以使用基于函数的索引和/或物化视图来模拟相同的事情。这很混乱，可能涉及更改应用程序发出的SQL。|1644269|如果能有一个以上的语法糖就好了。|1644270|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I would love to see the ability to put a WHERE clause on at index creation time:

<pre><code>CREATE INDEX BAR ON FOO (FooName, FooId) WHERE FooEnabled = 1;
</code></pre>

Then, the optimizer could use this when processing SQL like this:

<pre><code>SELECT FooId, FooName
FROM Foo
WHERE FooEnabled = 1
ORDER BY FooName;
</code></pre>

This seems like a solvable problem from both the index creation and optimizer perspectives. Indeed, you can simulate the same thing using function based indexes and/or materialized views. This is messy and can involve changing the SQL issued by an application.

It would be nice to have a syntactic sugar above.

blocks|key|1429582|text|嵌套聚合函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429583|PostgreSQL不允许像SELECT+MAX(COUNT(*))这样的东西...|1429584|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Nested aggregate functions.

PostgreSQL doesn't allow something like SELECT MAX(COUNT(*))...

blocks|key|1429714|text|CreateOrAlter命令。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429715|我厌倦了在"Create+Proc“和"Alter+Proc”之间来回切换。我不关心它是create还是alter，我只希望最终结果是我放在过程主体中的内容。|1429716|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

A CreateOrAlter command.

I am sick and tired of changing back and forth between "Create Proc" and "Alter Proc". I don't care if it is create or alter, I just want the end result to be what I put in the body of the procedure.

blocks|key|1429922|text|计算列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429923|能够基于另一个表达式在表中添加列，而不需要创建视图。|1429924|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Computed columns. 

Hability to add columns on table based on an expression for another one without need to create a view.

blocks|key|1644938|text|宏SQL表达式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1644939|例如：|1644940|SELECT+*
++FROM+X
+WHERE+MACRO_1(A,+B,+C)|code-block|syntax|javascript|1644941|其中，MACRO_1可以是这样的：|1644942|A*2/B%2BC|1644943|或者类似这样的东西：|1644944|A=2+OR+B<C|1644945|MACRO_1不是一个函数，它只是一个宏，需要在最终解析之前进行预处理和替换。|1644946|它们类似于函数，但因为被替换，优化器可以利用索引，而不像函数那样优化器不知道发生了什么。|1644947|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

MACRO SQL EXPRESSIONS

For instance:

<pre><code>SELECT *
 FROM X
 WHERE MACRO_1(A, B, C)
</code></pre>

Where MACRO_1 can be something like:

<pre><code>A*2/B+C
</code></pre>

or something like:

<pre><code>A=2 OR B&lt;C
</code></pre>

MACRO_1 is not a function, it's just a macro to be preprocessed and substituted before the final parsing.

They are like functions but because are substituted the optimizer can take advantage of indexes, not like funcions that the optimizer does not know what happens.

Dealing with SQL shows us some limitations and gives us an opportunity to imagine what could be. 

Which improvements to SQL are you waiting for? Which would you put on top of the wish list?

I think it can be nice if you post in your answer the database your feature request lacks.

Which are the SQL improvements you are waiting for?

日志文件

SQL Server

PostgreSQL

数据库

服务器

MySQL

处理SQL向我们展示了一些限制，并让我们有机会想象可能发生的事情。您还在等待SQL的哪些改进？你会把哪一个放在愿望清单的首位？我认为如果你在你的答案中张贴你的功能请求所缺少的数据库，那会很好。

问您在等待哪些SQL改进？
EN

回答 37

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问您在等待哪些SQL改进？EN

回答 37

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问您在等待哪些SQL改进？
EN