SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数 先来创建一个测试表 1 USE [ tempdb ] 2 GO 3 4 CREATE TABLE #temptb(id INT ,NAME VARCHAR ( 200 )) 5 GO 6 7 INSERT INTO [ #temptb ] ( [ id ] , [ NAME ] ) 8 SELECT 1 , ' 中国 '
SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数
先来创建一个测试表
<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span> 4</span> <span>CREATE</span> <span>TABLE</span> #temptb(id <span>INT</span> ,NAME <span>VARCHAR</span>(<span>200</span><span>)) </span><span> 5</span> <span>GO</span> <span> 6</span> <span> 7</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>#temptb</span><span>]</span> ( <span>[</span><span>id</span><span>]</span>, <span>[</span><span>NAME</span><span>]</span><span> ) </span><span> 8</span> <span>SELECT</span> <span>1</span>,<span>'</span><span>中国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span> 9</span> <span>SELECT</span> <span>2</span>,<span>'</span><span>中国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>10</span> <span>SELECT</span> <span>3</span>,<span>'</span><span>英国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>11</span> <span>SELECT</span> <span>4</span>,<span>'</span><span>英国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>12</span> <span>SELECT</span> <span>5</span>,<span>'</span><span>美国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>13</span> <span>SELECT</span> <span>6</span>,<span>'</span><span>美国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>14</span> <span>SELECT</span> <span>null</span>, <span>'</span><span>法国</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>15</span> <span>SELECT</span> <span>8</span>,<span>'</span><span>法国</span><span>'</span> <span>16</span> <span>GO</span> <span>17</span> <span>18</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>19</span> <span>GO</span>
先来看一下SELECT语句的语法：
<span>1</span> <span>SELECT</span> <span>[</span><span> ALL | DISTINCT </span><span>]</span> <span>[</span><span> topSubclause </span><span>]</span><span> aliasedExpr
</span><span>2</span> <span>[</span><span>{ , aliasedExpr }</span><span>]</span> <span>FROM</span> fromClause <span>[</span><span> WHERE whereClause </span><span>]</span> <span>[</span><span> GROUP BY groupByClause [ HAVING havingClause </span><span>]</span> ] <span>[</span><span> ORDER BY orderByClause </span><span>]</span>
<span>3</span> <span>or</span>
<span>4</span> <span>SELECT</span> VALUE <span>[</span><span> ALL | DISTINCT </span><span>]</span> <span>[</span><span> topSubclause </span><span>]</span> expr <span>FROM</span> fromClause <span>[</span><span> WHERE whereClause </span><span>]</span> <span>[</span><span> GROUP BY groupByClause [ HAVING havingClause </span><span>]</span> ] <span>[</span><span> ORDER BY orderByClause</span>
ALL关键字：指定在结果集中可以显示重复的行，这是默认的关键字，也就是说，当您在查询中不使用ALL关键字，默认都已经附加上了ALL这个关键字
例如下面两个SQL语句，实际上是等价的，都会把重复的记录select出来
<span>1</span> <span>--</span><span>这两个语句是等价的</span> <span>2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>3</span> <span>GO</span> <span>4</span> <span>--</span><span>-----------------------------------------</span> <span>5</span> <span>SELECT</span> <span>ALL</span> <span>*</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>6</span> <span>GO</span>
如果您需要把唯一值select出来，过滤掉那些重复值需要使用DISTINCT关键字
<span>1</span> <span>SELECT</span> <span>DISTINCT</span>(<span>[</span><span>NAME</span><span>]</span>) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>
而当您把SQL语句，字段放在ALL括号中，这时候就会变成一个表达式，例如下面SQL语句
<span>1</span> <span>SELECT</span> <span>ALL</span>(<span>[</span><span>NAME</span><span>]</span><span>+</span><span>'</span><span>您好</span><span>'</span>) <span>AS</span> <span>'</span><span>国别</span><span>'</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span>
处理表重复记录(查询和删除)
在Name相同ID最大的记录，其中有一个SQL语句
<span>1</span> <span>SELECT</span> <span>*</span> <span>2</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span><span> a </span><span>3</span> <span>WHERE</span> ID<span>!<all> ( <span>SELECT</span><span> ID </span><span>4</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>5</span> <span>WHERE</span> Name <span>=</span> a.Name )</all></span>
如果去掉ALL关键字会怎样呢？
因为子查询需要的是一个表达式，所以需要使用ALL关键字把他变为一个表达式，所以要用ALL
ALL关键字还可以放在GROUP BY 之后
这里要分两种情况，一种是SQL语句中有where子句的的，另一种是SQL语句中没有where子句的
情况一：
<span>1</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span> NAME<span>=</span><span>'</span><span>法国</span><span>'</span> <span>GROUP</span> <span>BY</span> <span>ALL</span><span> NAME </span><span>2</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span> NAME<span>=</span><span>'</span><span>法国</span><span>'</span> <span>GROUP</span> <span>BY</span> NAME
对于没有符合条件的行的组，这里是没有符合name='法国'，作为聚合值的列值为NULL
如果没有ALL关键字，GROUP BY子句将不显示没有符合条件的行的组
情况二：
<span>1</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>GROUP</span> <span>BY</span> <span>ALL</span><span> NAME </span><span>2</span> <span>SELECT</span> <span>AVG</span>(id) <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>GROUP</span> <span>BY</span> NAME
当SQL语句中没有where子句的时候，查询出来的结果都是一样的
ALL关键字还可以放在UNION之后
<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span>2</span> <span>GO</span> <span>3</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SystemPara</span><span>]</span> ( <span>[</span><span>ParaValue</span><span>]</span>, <span>[</span><span>Name</span><span>]</span>, <span>[</span><span>Description</span><span>]</span><span> ) </span><span>4</span> <span>SELECT</span> <span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span> <span>UNION</span> <span>ALL</span> <span>5</span> <span>SELECT</span> <span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span>,<span>'</span><span>nihao</span><span>'</span>
PERCENT关键字
PERCENT关键字需要跟TOP 关键字一起使用
从结果集中输出百分之N行，n必须是介于0~100之间的整数
<span>1</span> <span>SELECT</span> <span>TOP</span> <span>10</span> <span>PERCENT</span> <span>*</span> <span>from</span> <span>[</span><span>#temptb</span><span>]</span> <span>2</span> <span>GO</span>
上面的SQL语句意思是：从[#temptb]表中输出10%的记录数，因为没有使用order by子句，所以这条记录是随机的
因为[#temptb]表有8条记录，8*10%=0.8 四舍五入之后相当于一条记录
<span>1</span> <span>SELECT</span> <span>TOP</span> <span>30</span> <span>PERCENT</span> <span>*</span> <span>from</span> <span>[</span><span>#temptb</span><span>]</span> <span>2</span> <span>GO</span>
8*30%=2.4 四舍五入之后相当于三条记录，SQLSERVER在这里就算四舍五入不足三条记录，他也会输出偏大的数，也就是三条记录
CUBE关键字
CUBE关键字:如果需要在结果集内不仅包含由GROUP BY提供的正常行，还包含汇总行，可以用CUBE关键字。CUBE关键字与GROUP BY一起使用
当使用CUBE关键字的时候，可以使用GROUPING函数来输出一个额外的列，当结果行是正常的行时，返回0；当结果行是汇总行时，返回1。
<span>1</span> <span>SELECT</span> <span>AVG</span>(id) <span>AS</span> <span>'</span><span>平均值</span><span>'</span>, <span>GROUPING</span>(NAME) <span>AS</span> <span>'</span><span>是否已汇总</span><span>'</span> <span>2</span> <span>FROM</span> <span>[</span><span>#temptb</span><span>]</span> <span>3</span> <span>GROUP</span> <span>BY</span><span> NAME </span><span>4</span> <span>WITH</span> CUBE
最后一行显示了GROUP BY的记录有多少行，一共有4行记录，而在汇总行（即最后一行）是否已汇总那列显示1，表示是汇总行
Grouping关键字
指示是否聚合 GROUP BY 列表中的指定列表达式。
在结果集中，如果 GROUPING 返回 1 则指示聚合；返回 0 则指示不聚合。
如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT
http://msdn.microsoft.com/zh-cn/library/ms178544(v=sql.105).aspx
GROUPING 用于区分标准空值和由 ROLLUP、CUBE 或 GROUPING SETS 返回的空值。
作为 ROLLUP、CUBE 或 GROUPING SETS 操作结果返回的 NULL 是 NULL 的特殊应用。
它在结果集内作为列的占位符，表示全体。
以下示例将分组 SalesQuota 并聚合 SaleYTD 数量。GROUPING 函数应用于 SalesQuota 列。
<span>1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span>; </span><span>2</span> <span>GO</span> <span>3</span> <span>SELECT</span> SalesQuota, <span>SUM</span>(SalesYTD) <span>'</span><span>TotalSalesYTD</span><span>'</span><span>, </span><span>4</span> <span>GROUPING</span>(SalesQuota) <span>AS</span> <span>'</span><span>Grouping</span><span>'</span> <span>5</span> <span>FROM</span><span> Sales.SalesPerson </span><span>6</span> <span>GROUP</span> <span>BY</span><span> SalesQuota </span><span>7</span> <span>WITH</span><span> ROLLUP; </span><span>8</span> <span>GO</span>
结果集在 SalesQuota 下面显示两个空值。
第一个 NULL 代表从表中的这一列得到的空值组。
第二个 NULL 位于 ROLLUP 操作所添加的汇总行之中。
汇总行显示所有 SalesQuota 组的 TotalSalesYTD 数量，并以 Grouping 列中的 1 进行指示。
http://msdn.microsoft.com/zh-cn/library/ms191500(v=sql.100).aspx
对简单汇总报表使用 Transact-SQL
生成简单汇总报表的应用程序可使用下列 Transact-SQL 元素：
ROLLUP、CUBE 或 GROUPING SETS 运算符。这些是 SELECT 语句的 GROUP BY 子句的扩展。
COMPUTE 或 COMPUTE BY 运算符。这两种运算符也与 GROUP BY 相关联。
这些运算符生成的结果集中，既包含每个项目的明细行，也包含每个组的汇总行，汇总行显示了该组的聚合合计。
GROUP BY 子句可用于生成只包含各组的聚合而不包含其明细行的结果。
应用程序应使用 Analysis Services，而不是 CUBE、ROLLUP、COMPUTE 或 COMPUTE BY。
特别要注意的是，CUBE 和 ROLLUP 应当只用在无法访问 OLE DB 或 ADO 的环境中，例如脚本或存储过程中。
支持 COMPUTE 和 COMPUTE BY 是为了向后兼容。
应当优先选用 ROLLUP 运算符而非 COMPUTE 或 COMPUTE BY。由 COMPUTE 或 COMPUTE BY 生成的汇总值将作为多个单独的结果集返回，
这些结果集之间还插入了包含各组明细行的结果集；或者作为包含合计的结果集返回，附加在主结果集之后。
处理这些多个结果集将增加应用程序代码的复杂性。服务器游标既不支持 COMPUTE，也不支持 COMPUTE BY。
但 ROLLUP 支持服务器游标。CUBE 和 ROLLUP 将生成单个结果集，其中包含嵌入的小计和合计行。
此外，查询优化器有时还可以为 ROLLUP 生成比为 COMPUTE 和 COMPUTE BY 生成的执行计划更高效的执行计划。
如果使用不带这些运算符的 GROUP BY，将返回单个结果集，其中每组对应一行，行中包含该组的聚合小计。结果集中没有明细行。
SQLSERVER中Cube 、RollUp的用法
Cube 、RollUp可以对查询的数据进行汇总，在数据统计中经常用到，尤其是做报表时，用在Select语句中
下面就对两种统计方式进行对比
SQL脚本如下：
<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>CREATE</span> <span>TABLE</span><span> t_test </span><span> 4</span> <span>( </span><span> 5</span> id <span>INT</span><span> , </span><span> 6</span> productName <span>VARCHAR</span>(<span>200</span><span>) , </span><span> 7</span> price <span>MONEY</span><span> , </span><span> 8</span> num <span>INT</span><span> , </span><span> 9</span> amount <span>INT</span><span> , </span><span>10</span> operatedate <span>DATETIME</span> <span>11</span> <span>) </span><span>12</span> <span>GO</span> <span>13</span> <span>14</span> <span>--</span><span>插入随机数据</span> <span>15</span> <span>DECLARE</span> <span>@i</span> <span>INT</span> <span>16</span> <span>DECLARE</span> <span>@rand</span> <span>MONEY</span> <span>17</span> <span>DECLARE</span> <span>@date</span> <span>DATETIME</span> <span>18</span> <span>DECLARE</span> <span>@index</span> <span>INT</span> <span>19</span> <span>DECLARE</span> <span>@DateBase</span> <span>INT</span> <span>20</span> <span>SET</span> <span>@date</span> <span>=</span> <span>'</span><span>2012-10-23</span><span>'</span> <span>21</span> <span>SET</span> <span>@i</span> <span>=</span> <span>1</span> <span>22</span> <span>WHILE</span> ( <span>@i</span> <span> <span>18</span><span> ) </span><span>23</span> <span>BEGIN</span> <span>24</span> <span>SET</span> <span>@rand</span> <span>=</span> <span>RAND</span>() <span>*</span> <span>20</span> <span>25</span> <span>SET</span> <span>@index</span> <span>=</span> <span>CAST</span>(<span>RAND</span>() <span>*</span> <span>3</span> <span>AS</span> <span>INT</span><span>) </span><span>26</span> <span>SET</span> <span>@DateBase</span> <span>=</span> <span>CAST</span>(<span>RAND</span>() <span>*</span> <span>10</span> <span>AS</span> <span>INT</span><span>) </span><span>27</span> <span>28</span> <span>INSERT</span> <span>INTO</span><span> t_test ( id, productName, price, num, amount, operatedate ) </span><span>29</span> <span>VALUES</span> ( <span>@i</span>, <span>'</span><span>product</span><span>'</span> <span>+</span> <span>CAST</span> (<span>@index</span> <span>AS</span> <span>VARCHAR</span>(<span>10</span>)), <span>@rand</span>, <span>100</span><span>, </span><span>30</span> <span>@rand</span> <span>*</span> <span>100</span>, <span>@date</span> <span>+</span> <span>@DateBase</span><span> ) </span><span>31</span> <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span> <span>32</span> <span>END</span> <span>33</span> <span>34</span> <span>35</span> <span>SELECT</span> <span>*</span> <span>FROM</span> t_test</span>
分别用两种方式统计：
<span> 1</span> <span>--</span><span>分别用两种方式统计：</span> <span> 2</span> <span> 3</span> <span>SELECT</span> <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(operatedate) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span> <span> 4</span> <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>) </span><span> 5</span> <span>END</span> <span>AS</span> 日期, <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(productName) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span> <span> 6</span> <span>ELSE</span><span> productName </span><span> 7</span> <span>END</span> <span>AS</span> 产品名称, <span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span> 平均价格, <span>SUM</span>(num) <span>AS</span><span> 数量, </span><span> 8</span> <span>SUM</span>(amount) <span>AS</span><span> 金额 </span><span> 9</span> <span>FROM</span><span> t_test </span><span>10</span> <span>GROUP</span> <span>BY</span> operatedate, productName <span>WITH</span><span> ROLLUP; </span><span>11</span> <span>--</span><span>-----------------------------------------------------------------</span> <span>12</span> <span>SELECT</span> <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(operatedate) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span> <span>13</span> <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>) </span><span>14</span> <span>END</span> <span>AS</span> 日期, <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(productName) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span> <span>15</span> <span>ELSE</span><span> productName </span><span>16</span> <span>END</span> <span>AS</span> 产品名称, <span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span> 平均价格, <span>SUM</span>(num) <span>AS</span><span> 数量, </span><span>17</span> <span>SUM</span>(amount) <span>AS</span><span> 金额 </span><span>18</span> <span>FROM</span><span> t_test </span><span>19</span> <span>GROUP</span> <span>BY</span> operatedate, productName <span>WITH</span> CUBE;
ROLLUP 按照分组顺序，先对第一个字段operatedate分组，在组内进行统计，最后给出合计
<span>1</span> <span>SELECT</span> <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(operatedate) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span> <span>--</span><span>用GROUPING得出是否是汇总行，这个例子里最后一行是汇总行</span> <span>2</span> <span>ELSE</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), operatedate, <span>120</span><span>) </span><span>3</span> <span>END</span> <span>AS</span> 日期, <span>CASE</span> <span>WHEN</span> <span>GROUPING</span>(productName) <span>=</span> <span>1</span> <span>THEN</span> <span>'</span><span>小计</span><span>'</span> <span>4</span> <span>ELSE</span><span> productName </span><span>5</span> <span>END</span> <span>AS</span> 产品名称, <span>SUM</span>(amount) <span>/</span> <span>SUM</span>(num) <span>AS</span> 平均价格, <span>SUM</span>(num) <span>AS</span><span> 数量, </span><span>6</span> <span>SUM</span>(amount) <span>AS</span><span> 金额 </span><span>7</span> <span>FROM</span><span> t_test </span><span>8</span> <span>GROUP</span> <span>BY</span> operatedate, productName <span>WITH</span> ROLLUP; <span>--</span><span>因为operatedate和productName字段都在GROUPING函数里统计是否汇总，所以GROUP BY后面就需要加operatedate和productName这两个字段</span>
CUBE 会对所有的分组字段进行统计，如上例，先对日期求小计，也就是统计每天的产品总金额，然后统计每个产品的总金额，最后给出总的合计。
ROLLUP和CUBE的区别就是： ROLLUP 只会去统计group by 后面的第一个字段每个分组的小计和第一个字段的总计
Grouping(字段名) 用来区分当前行是不是小计产生的行， Grouping(字段名)=1 说明是统计行，Grouping(字段名)=0 说明是表中行
可以用在case,where 后面
http://www.2cto.com/database/201210/163455.html
另外一个例子
SQL脚本如下：
<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>CREATE</span> <span>TABLE</span> Sales (EmpId <span>INT</span>, Yr <span>INT</span>, Sales <span>MONEY</span><span>) </span><span> 4</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>, <span>2005</span>, <span>12000</span><span>) </span><span> 5</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>, <span>2006</span>, <span>18000</span><span>) </span><span> 6</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>, <span>2007</span>, <span>25000</span><span>) </span><span> 7</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>, <span>2005</span>, <span>15000</span><span>) </span><span> 8</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>, <span>2006</span>, <span>6000</span><span>) </span><span> 9</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>, <span>2006</span>, <span>20000</span><span>) </span><span>10</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>, <span>2007</span>, <span>24000</span><span>) </span><span>11</span> <span>12</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sales</span><span>]</span>View Code
ROLLUP
<span>1</span> <span>SELECT</span> EmpId, Yr, <span>SUM</span>(Sales) <span>AS</span><span> Sales </span><span>2</span> <span>FROM</span><span> Sales </span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr <span>WITH</span> ROLLUP
CUBE
<span>1</span> <span>SELECT</span> EmpId, Yr, <span>SUM</span>(Sales) <span>AS</span><span> Sales </span><span>2</span> <span>FROM</span><span> Sales </span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr <span>WITH</span> CUBE
CUBE比ROLLUP多了年份的统计，统计了2005、2006、2007年的销售额
可以用下图来表示
ROLLUP
CUBE
http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
验证CUBE和ROLLUP 的区别
ROLLUP和CUBE的区别就是： ROLLUP 只会去统计group by 后面的第一个字段每个分组的小计和第一个字段的总计
我们修改一下上面那个实验
<span> 1</span> <span>USE</span> <span>[</span><span>tempdb</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>CREATE</span> <span>TABLE</span> Sales (EmpId <span>INT</span>,productName <span>VARCHAR</span>(<span>200</span>), Yr <span>INT</span>, Sales <span>MONEY</span><span>) </span><span> 4</span> <span>GO</span> <span> 5</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product2</span><span>'</span>, <span>2005</span>, <span>12000</span><span>) </span><span> 6</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product1</span><span>'</span>, <span>2005</span>, <span>18000</span><span>) </span><span> 7</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product0</span><span>'</span>, <span>2006</span>, <span>25000</span><span>) </span><span> 8</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>1</span>,<span>'</span><span>product2</span><span>'</span>, <span>2007</span>, <span>15000</span><span>) </span><span> 9</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>,<span>'</span><span>product1</span><span>'</span>, <span>2005</span>, <span>60000</span><span>) </span><span>10</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>,<span>'</span><span>product1</span><span>'</span>, <span>2006</span>, <span>22000</span><span>) </span><span>11</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>2</span>,<span>'</span><span>product0</span><span>'</span>, <span>2007</span>, <span>24000</span><span>) </span><span>12</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>,<span>'</span><span>product0</span><span>'</span>, <span>2005</span>, <span>32000</span><span>) </span><span>13</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>,<span>'</span><span>product2</span><span>'</span>, <span>2006</span>, <span>42000</span><span>) </span><span>14</span> <span>INSERT</span> Sales <span>VALUES</span>(<span>3</span>,<span>'</span><span>product0</span><span>'</span>, <span>2007</span>, <span>24000</span><span>) </span><span>15</span> <span>GO</span> <span>16</span> <span>17</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sales</span><span>]</span>View Code
ROLLUP
<span>1</span> <span>SELECT</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span>, <span>SUM</span>(Sales) <span>AS</span><span> Sales </span><span>2</span> <span>FROM</span><span> Sales </span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span> <span>WITH</span> ROLLUP
CUBE
<span>1</span> <span>SELECT</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span>, <span>SUM</span>(Sales) <span>AS</span><span> Sales </span><span>2</span> <span>FROM</span><span> Sales </span><span>3</span> <span>GROUP</span> <span>BY</span> EmpId, Yr,<span>[</span><span>productName</span><span>]</span> <span>WITH</span> CUBE
可以看到CUBE除了统计EmpId字段之外，还统计了GROUP BY后面的Yr和productName这两个字段
而ROLLUP只统计了EmpId这个字段
总结
这些关键字和函数对平时用于统计的应用程序都非常有用，如果大家对这些函数功能都很熟悉的话，在开发当中一定能够得心应手
另外，个人觉得PERCENT关键字可以应用在分页上
如有不对的地方，欢迎大家拍砖哦o(∩_∩)o
MySQL的位置：數據庫和編程Apr 13, 2025 am 12:18 AMMySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen
MySQL：從小型企業到大型企業Apr 13, 2025 am 12:17 AMMySQL適合小型和大型企業。 1)小型企業可使用MySQL進行基本數據管理，如存儲客戶信息。 2)大型企業可利用MySQL處理海量數據和復雜業務邏輯，優化查詢性能和事務處理。
幻影是什麼讀取的，InnoDB如何阻止它們（下一個鍵鎖定）？Apr 13, 2025 am 12:16 AMInnoDB通過Next-KeyLocking機制有效防止幻讀。 1）Next-KeyLocking結合行鎖和間隙鎖，鎖定記錄及其間隙，防止新記錄插入。 2）在實際應用中，通過優化查詢和調整隔離級別，可以減少鎖競爭，提高並發性能。
mysql：不是編程語言，而是...Apr 13, 2025 am 12:03 AMMySQL不是一門編程語言，但其查詢語言SQL具備編程語言的特性：1.SQL支持條件判斷、循環和變量操作；2.通過存儲過程、觸發器和函數，用戶可以在數據庫中執行複雜邏輯操作。
MySQL：世界上最受歡迎的數據庫的簡介Apr 12, 2025 am 12:18 AMMySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。
MySQL的重要性：數據存儲和管理Apr 12, 2025 am 12:18 AMMySQL是一個開源的關係型數據庫管理系統，適用於數據存儲、管理、查詢和安全。 1.它支持多種操作系統，廣泛應用於Web應用等領域。 2.通過客戶端-服務器架構和不同存儲引擎，MySQL高效處理數據。 3.基本用法包括創建數據庫和表，插入、查詢和更新數據。 4.高級用法涉及復雜查詢和存儲過程。 5.常見錯誤可通過EXPLAIN語句調試。 6.性能優化包括合理使用索引和優化查詢語句。
為什麼要使用mysql？利益和優勢Apr 12, 2025 am 12:17 AM選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。
描述InnoDB鎖定機制（共享鎖，獨家鎖，意向鎖，記錄鎖，間隙鎖，下一鍵鎖）。Apr 12, 2025 am 12:16 AMInnoDB的鎖機制包括共享鎖、排他鎖、意向鎖、記錄鎖、間隙鎖和下一個鍵鎖。 1.共享鎖允許事務讀取數據而不阻止其他事務讀取。 2.排他鎖阻止其他事務讀取和修改數據。 3.意向鎖優化鎖效率。 4.記錄鎖鎖定索引記錄。 5.間隙鎖鎖定索引記錄間隙。 6.下一個鍵鎖是記錄鎖和間隙鎖的組合，確保數據一致性。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
mPDF
mPDF是一個PHP庫，可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件，並處理不同的語言。與原始腳本如HTML2FPDF相比，它的速度較慢，並且在使用Unicode字體時產生的檔案較大，但支援CSS樣式等，並進行了大量增強。支援幾乎所有語言，包括RTL（阿拉伯語和希伯來語）和CJK（中日韓）。支援嵌套的區塊級元素（如P、DIV），
SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合，這些清單在安全評估過程中經常使用，而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表，幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上，他就可以存取所需的每種類型的清單。
EditPlus 中文破解版
體積小，語法高亮，不支援程式碼提示功能
SublimeText3 Linux新版
SublimeText3 Linux最新版
Dreamweaver Mac版
視覺化網頁開發工具
