blocks|key|306479|text|SQL+GROUP+BY+-+CUBE,+ROLLUP+clauses|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|306480|分析(又称排名，又称窗口)函数+IE：|style|BOLD|306481|306482|ROW_NUMBER|unordered-list-item|306483|RANK|306484|DENSE_RANK|306485|NTILE|306486|OVER|306487|306488|视图：普通和物化|306489|如果不引用特定于供应商的语法，就很难说太多|306490|entityMap|0|LINK|mutability|MUTABLE|url|http://databases.about.com/od/sql/l/aacuberollup.htm^0|F|K|0|0|0|F|0|0|0|0|0|0|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@$A|17|B|18|1|19]]|C|$]]|$1|D|3|E|5|6|7|1A|8|@$A|1B|B|1C|F|G]]|9|@]|C|$]]|$1|H|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]|$1|I|3|J|5|K|7|1E|8|@]|9|@]|C|$]]|$1|L|3|M|5|K|7|1F|8|@]|9|@]|C|$]]|$1|N|3|O|5|K|7|1G|8|@]|9|@]|C|$]]|$1|P|3|Q|5|K|7|1H|8|@]|9|@]|C|$]]|$1|R|3|S|5|K|7|1I|8|@]|9|@]|C|$]]|$1|T|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]|$1|U|3|V|5|6|7|1K|8|@$A|1L|B|1M|F|G]]|9|@]|C|$]]|$1|W|3|X|5|6|7|1N|8|@]|9|@]|C|$]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|C|$]]]|Z|$10|$5|11|12|13|C|$14|15]]]]

SQL GROUP BY - <a href="http://databases.about.com/od/sql/l/aacuberollup.htm" rel="nofollow noreferrer">CUBE, ROLLUP clauses</a>

Analytic (AKA ranking, AKA windowing) functions IE: 

<ul>
<li>ROW_NUMBER</li>
<li>RANK</li>
<li>DENSE_RANK</li>
<li>NTILE</li>
<li>OVER</li>
</ul>

Views: Normal and Materialized

It's difficult to say much without referencing vendor specific syntax

blocks|key|3255811|text|OVER+Clause+(SQL+Server)也称为Window+functions+(PostgreSQL)或analytic+functions+(Oracle)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3255812|这对我来说是非常好的。你可以做各种方便的事情，比如计数，分区，排名等等。|3255813|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189461.aspx|1|http://www.postgresql.org/docs/current/static/tutorial-window.html|2|http://www.orafaq.com/node/55^0|0|B|0|R|G|1|1L|I|2|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]|$A|V|B|W|1|X]|$A|Y|B|Z|1|10]]|C|$]]|$1|D|3|E|5|6|7|11|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]|P|$5|I|J|K|C|$L|Q]]]]

<a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="nofollow noreferrer">OVER Clause</a> (SQL Server) a.k.a. <a href="http://www.postgresql.org/docs/current/static/tutorial-window.html" rel="nofollow noreferrer">Window functions</a> (PostgreSQL) or <a href="http://www.orafaq.com/node/55" rel="nofollow noreferrer">analytic functions</a> (Oracle)

This has been very nice to know for me. You can do all sorts of handy things like counting, partitioning, ranking, etc.

blocks|key|309514|text|EXISTS。我很惊讶有多少人在检查存在或IN+(SELECT...)时仍在使用COUNT(*)子句在存在时可以更快地完成这项工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|309515|您最常看到的可能是：|309516|SELECT+@MyVar+=+Count(*)+FROM+Table1+WHERE....
If+@MyVar+<>+0
BEGIN
+++--do+something
END|code-block|syntax|javascript|309517|什么时候|309518|IF+EXISTS(SELECT+1+FROM+Table1+WHERE...)
BEGIN
++++--don+something
END|309519|总是更好。|309520|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188336.aspx^0|0|6|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|15|8|@]|9|@]|C|$I|J]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

<a href="http://msdn.microsoft.com/en-us/library/ms188336.aspx" rel="nofollow noreferrer">EXISTS</a>. I'm amazed how many people still use COUNT(*) when checking existence or IN (SELECT...) clauses when EXISTS can do the job much quicker.

Most frequently you might see :

<pre><code>SELECT @MyVar = Count(*) FROM Table1 WHERE....
If @MyVar &lt;&gt; 0
BEGIN
 --do something
END
</code></pre>

when

<pre><code>IF EXISTS(SELECT 1 FROM Table1 WHERE...)
BEGIN
 --don something
END
</code></pre>

is always better.

blocks|key|304437|text|SELECT...+EXCEPT+SELECT...|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|304438|和|unstyled|304439|SELECT...+INTERSECT+SELECT...|304440|在集合之间挑选不同的或共同的行--这适用于行中的所有列--可能是有用的(而且是令人不安的高效)。当你有很多列时，这是非常有用的。|304441|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT... EXCEPT SELECT...
</code></pre>

and 

<pre><code>SELECT... INTERSECT SELECT...
</code></pre>

can be useful (and disturbingly efficient) at pickout out differing or common rows--and that's for all columns in the row--between sets. This is extremely useful when you have lots of columns.

blocks|key|3255797|text|人们使用内置函数的次数不够，喜欢重新发明轮子，下面是Ten+SQL+Server+Functions+That+You+Have+Ignored+Until+Now|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3255798|在集群唯一标识符列上使用NEWSEQUENTIALID()而不是NEWID()会执行得更好，因为它不会导致页面拆分和碎片|3255799|使用辅助的数字表格，以便您可以快速执行一些基于集合的逻辑|3255800|例如|3255801|select+DATEADD(m,number,'20010101')
from+master..spt_values
where+type+=+'P'
order+by+1|code-block|syntax|javascript|3255802|ANY、ALL和SOME|3255803|entityMap|0|LINK|mutability|MUTABLE|url|http://wiki.lessthandot.com/index.php/Ten_SQL_Server_Functions_That_You_Have_Ignored_Until_Now^0|Q|1K|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|15|8|@]|9|@]|C|$M|N]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

People don't use built in functions enough and like to reinvent the wheel, here are <a href="http://wiki.lessthandot.com/index.php/Ten_SQL_Server_Functions_That_You_Have_Ignored_Until_Now" rel="nofollow noreferrer">Ten SQL Server Functions That You Have Ignored Until Now</a>

Using NEWSEQUENTIALID() instead of NEWID() on a clustered uniqueidentifier column will perform much better since it won't cause page splits and thus fragmentation

Using an auxilarry table of numbers so that you can quickly do some set based logic

for example

<pre><code>select DATEADD(m,number,'20010101')
from master..spt_values
where type = 'P'
order by 1
</code></pre>

ANY, ALL and SOME

blocks|key|309573|text|两个来自Postgresql：+(见示例)和新的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|309574|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|$]]

Two from Postgresql: <a href="http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-DISTINCT" rel="nofollow noreferrer">DISTINCT ON</a> (see example) and the new <a href="http://www.postgresql.org/docs/8.4/static/queries-with.html" rel="nofollow noreferrer">WITH</a>.

blocks|key|3255817|text|最近我一直在使用CROSS+APPLY。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3255818|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms175156.aspx^0|8|B|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Lately I have been using <a href="http://msdn.microsoft.com/en-us/library/ms175156.aspx" rel="nofollow noreferrer">CROSS APPLY</a> a lot.

blocks|key|306519|text|Pivot|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|306520|这是2005年的新东西(我知道那是很久以前的事了，但现在仍然有很多人在使用2000)。省去了这个周末做一堆"case+when+name+=+'tim‘then+value+else+0+end“来构建聚合。|306521|entityMap|0|LINK|mutability|MUTABLE|url|http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx^0|0|5|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]]]]

<a href="http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx" rel="nofollow noreferrer">Pivot</a>

It's new in 2005 (which i know was a long time ago, but there's loads of people still using 2000).
Saves doing a bunch of "case when name = 'tim' then value else 0 end" to build your aggregates this weekend.

blocks|key|3042102|text|公用表表达式(SQL+Server+2005%2B)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3042103|WITH+x+AS+(
++++SELECT+1+as+A,+2+as+B,+3+as+C
),
WITH+y+AS+(
++++SELECT+4+as+A,+5+as+B,+6+as+C
++++UNION
++++SELECT+7+as+A,+8+as+B,+9+as+C
)
SELECT+A,+B,+C+FROM+x
UNION
SELECT+A,+B,+C+FROM+y|code-block|syntax|javascript|3042104|它们非常适合将查询分解为多个步骤|3042105|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Common Table Expressions (SQL Server 2005+)

<pre><code>WITH x AS (
 SELECT 1 as A, 2 as B, 3 as C
),
WITH y AS (
 SELECT 4 as A, 5 as B, 6 as C
 UNION
 SELECT 7 as A, 8 as B, 9 as C
)
SELECT A, B, C FROM x
UNION
SELECT A, B, C FROM y
</code></pre>

They are really nice for breaking your queries into steps

blocks|key|3255930|text|在MySQL下，使用关键字"STRAIGHT_JOIN“。如果您知道要连接到的数据和查找表的关系，有时优化器会将较小的表视为连接的基础，并尝试查询“较大”表的“较少记录”计数，从而花费更多的时间。如果您的主表是"from“中的第一个表，并且它的”条件“在前面，那么直接连接将首先命中该表，然后连接到其他表，并且很快就会完成。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3255931|我必须处理连接到关于15%2B查找表的10%2B百万条记录的管理数据。如果没有直接连接，系统在20%2B小时后就会阻塞。加上直连接，它在大约2小时内完成了。|3255932|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|$]]

under MySQL, using the keyword "STRAIGHT_JOIN". If you know your data, and the relationships of lookup tables that you are joining to, sometimes the optimizer looks at the smaller tables as a basis of a join and tries to query the "less record" count to your "bigger" table thus taking significantly more time. If your primary table is first in the "from", and its "criteria" up front, the straight join will hit that first, join to the rest of the tables and be done in no time.

I've had to do this dealing with gov't data of 10+ million records joined to about 15+ lookup tables. Without straight-join, the system choked after 20+ hours. Adding Straight-join, it was done in about 2 hrs.

blocks|key|3255941|text|Sql+Server中的HAVING子句。特别是，让(COUNT+DISTINCT+FOO)>+@SomeNumber为给定分组快速查找具有多个不同值的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3255942|来自MSDN|offset|length|3255943|USE+AdventureWorks2008R2+;
GO
SELECT+SalesOrderID,+SUM(LineTotal)+AS+SubTotal
FROM+Sales.SalesOrderDetail
GROUP+BY+SalesOrderID
HAVING+SUM(LineTotal)+>+100000.00
ORDER+BY+SalesOrderID+;|code-block|syntax|javascript|3255944|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms180199.aspx^0|0|2|4|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

In Sql Server, the HAVING clause. Particularly, HAVING(COUNT DISTINCT FOO)> @SomeNumber to quickly find rows with more than one distinct value for a given grouping.

From <a href="http://msdn.microsoft.com/en-us/library/ms180199.aspx" rel="nofollow noreferrer">MSDN</a>:

<pre><code>USE AdventureWorks2008R2 ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) &gt; 100000.00
ORDER BY SalesOrderID ;
</code></pre>

blocks|key|3042071|text|来自PostgreSQL文档：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3042072|Table+partitioning|offset|length|3042073|分区指的是将逻辑上属于一个大表的内容拆分成较小的物理部分。分区可以提供几个好处：|3042074|对于某些类型的queries.|3042075|Update，可以显著提高|unordered-list-item|3042076|查询性能，因为表的每个部分的索引都比整个数据集上的索引小。当索引不再容易地放入内存时，对索引的读取和写入操作都会占用越来越多的磁盘访问。如果在分区设计中已考虑到这一要求，则只需删除其中一个分区即可完成|3042077|批量删除。DROP+TABLE比批量删除快得多，更不用说随后的VACUUM+overhead.|3042078|Seldom-used数据可以迁移到更便宜、更慢的存储介质上了。|3042079|3042080|3042081|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html^0|0|0|I|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|F|3|G|5|6|7|17|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|19|8|@]|9|@]|A|$]]|$1|M|3|N|5|L|7|1A|8|@]|9|@]|A|$]]|$1|O|3|P|5|L|7|1B|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1C|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

From PostgreSQL Docs:

<a href="http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html" rel="nofollow noreferrer">Table partitioning</a>

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:

<ul>
<li>Query performance can be improved dramatically for certain kinds of queries.</li>
<li>Update performance can be improved too, since each piece of the table has indexes smaller than an index on the entire data set would be. When an index no longer fits easily in memory, both read and write operations on the index take progressively more disk accesses.</li>
<li>Bulk deletes may be accomplished by simply removing one of the partitions, if that requirement is planned into the partitioning design. DROP TABLE is far faster than a bulk DELETE, to say nothing of the ensuing VACUUM overhead.</li>
<li>Seldom-used data can be migrated to cheaper and slower storage media. </li>
</ul>

blocks|key|306578|text|派生表，创建“变量”，减少重复代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|306579|像这样的东西，但可以扩展。显然，“平均值”可以是一个复杂得多的计算，如果你有几个，它有助于清理代码。|306580|Select+*,+case+when+AverageValue+>+50+then+'Pass'+Else+'Fail'+end
From
(
+Select+ColA,+ColB,+AverageValue+=+(ColA%2BColB)/2
+From+InnerMostTable
)+AverageValues
Order+By+AverageValue+Desc|code-block|syntax|javascript|306581|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Derived tables to create "variables" and reduce repeated code.

Something like this but can be expanded upon. Obviously "Average Value" can be a much more complex calculation, and if you have several it helps clean up code.

<pre><code>Select *, case when AverageValue &gt; 50 then 'Pass' Else 'Fail' end
From
(
 Select ColA, ColB, AverageValue = (ColA+ColB)/2
 From InnerMostTable
) AverageValues
Order By AverageValue Desc
</code></pre>

blocks|key|3042111|text|在SQL+Server中，使用Convert()函数来获取mm/dd/yyyy格式的日期，而不是使用Cast()函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3042112|SELECT+convert(datetime,++'1/1/2010',+101)|code-block|syntax|javascript|3042113|我一直在用这个|3042114|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

In SQL Server using the Convert() function to get dates in the format mm/dd/yyyy instead of Cast() function

<pre><code>SELECT convert(datetime, '1/1/2010', 101)
</code></pre>

I use this all the time

<blockquote>
 Possible Duplicate: 
 <a href="https://stackoverflow.com/questions/121243/hidden-features-of-sql-server">Hidden Features of SQL Server</a> 
</blockquote>



What are those pro/subtle techniques that SQL provides and not many know about which also cut code and improve performance? 

eg: I have just learned how to use CASE statements inside aggregate functions and it totally changed my approach on things.

Are there others?

UPDATE: Basically any vendor. But PostgreSQL if you want to focus only on one :D

SQL hidden techniques?

SQL Server

PostgreSQL

MySQL

 可能重复：SQL提供的那些亲/微妙的技术是什么，而且不是很多人知道的，它们还可以减少代码和提高性能？我刚刚学习了如何在聚合函数中使用CASE语句，它完全改变了我处理事情的方法。还有其他的吗？更新：基本上是任何供应商。但是PostgreSQL如果你只想关注一个:D

问SQL隐藏技术？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL隐藏技术？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL隐藏技术？
EN