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><span>本文来源gaodai#ma#com搞*代#码9网#</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>

喜欢 (0)赏

[搞代码]

分享 (0)