select from where group by orde by having 执行顺序及where中不能使用别名

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/king2wang/article/details/99620630

sql 书写顺序为select * from  * where * group by * having *  order by *

其中select和from两个关键字是必选的,其他为可选的,

但sql执行和书写顺序是不同的,执行顺序为 from -- where -- group by -- having -- select -- order by --

 

注意:where字句可以使用任何限定条件,但不能使用组函数,想使用组函数可以使用having字句

并且where字句不能使用别名当做条件,想使用别名也是可以在having字句中进行使用

例如:

 

SELECT SUM(money) as all_money FROM orders WHERE user_id > 100 GROUP BY user_id HAVING all_money > 200 ORDER BY all_money;

或写成

SELECT SUM(money) FROM orders WHERE user_id > 100 GROUP BY user_id HAVING SUM(money) > 200 ORDER BY SUM(money);

 

展开阅读全文

关于sparksql使用where、group by,having的问题

11-21

[b]在spark1.6中,[/b]rn有如下数据:rndf=spark.createDataFrame([(1,2,3,4),(1,3,4,5),(4,5,6,7),(1,3,4,5),(2,3,4,5),(2,2,4,5)],["aa","bb","cc","dd"])rndf.registerTempTable ("record")rnrn aa bb cc ddrn0 1 2 3 4rn1 1 3 4 5rn2 4 5 6 7rn3 1 3 4 5rn4 2 3 4 5rn5 2 2 4 5rnrn为了把aa这一列,计数大于1的记录全部返回,返回结果应该如下所示:rnrn aa bb cc ddrn0 1 2 3 4rn1 1 3 4 5rn2 1 3 4 5rn3 2 3 4 5rn4 2 2 4 5rnrn在mysql中可以执行如下语句:rnselect * from record where aa in (select aa from record group by aa having COUNT(*)>1)rn并且能够返回所满足的需求。rnrn现在在spark中,执行的时候,报错:rnrn[b]Py4JJavaError: An error occurred while calling o30.sql.[/b]rn[b]: java.lang.RuntimeException: [1.42] failure: ``)'' expected but identifier aa found[/b]rnrn[b]select * from record where aa in (select aa from record group by aa having COUNT(*)>1)[/b]rn ^rn at scala.sys.package$.error(package.scala:27)rn at org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:36)rn at org.apache.spark.sql.catalyst.DefaultParserDialect.parse(ParserDialect.scala:67)rn at org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)rn at org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)rn at org.apache.spark.sql.execution.SparkSQLParser$$anonfun$org$apache$spark$sql$execution$SparkSQLParser$$others$1.apply(SparkSQLParser.scala:114)rn at org.apache.spark.sql.execution.SparkSQLParser$$anonfun$org$apache$spark$sql$execution$SparkSQLParser$$others$1.apply(SparkSQLParser.scala:113)rn at scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:136)rn at scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:135)rn at scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)rn at scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)rn at scala.util.parsing.combinator.Parsers$$anon$3.apply(Parsers.scala:222)rn at scala.util.parsing.combinator.Parsers$Parser$$anonfun$append$1$$anonfun$apply$2.apply(Parsers.scala:254)rn at scala.util.parsing.combinator.Parsers$Parser$$anonfun$append$1$$anonfun$apply$2.apply(Parsers.scala:254)rn at scala.util.parsing.combinator.Parsers$Failure.append(Parsers.scala:202)rn at scala.util.parsing.combinator.Parsers$Parser$$anonfun$append$1.apply(Parsers.scala:254)rn at scala.util.parsing.combinator.Parsers$Parser$$anonfun$append$1.apply(Parsers.scala:254)rn at scala.util.parsing.combinator.Parsers$$anon$3.apply(Parsers.scala:222)rn at scala.util.parsing.combinator.Parsers$$anon$2$$anonfun$apply$14.apply(Parsers.scala:891)rn at scala.util.parsing.combinator.Parsers$$anon$2$$anonfun$apply$14.apply(Parsers.scala:891)rn at scala.util.DynamicVariable.withValue(DynamicVariable.scala:57)rn at scala.util.parsing.combinator.Parsers$$anon$2.apply(Parsers.scala:890)rn at scala.util.parsing.combinator.PackratParsers$$anon$1.apply(PackratParsers.scala:110)rn at org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:34)rn at org.apache.spark.sql.SQLContext$$anonfun$1.apply(SQLContext.scala:208)rn at org.apache.spark.sql.SQLContext$$anonfun$1.apply(SQLContext.scala:208)rn at org.apache.spark.sql.execution.datasources.DDLParser.parse(DDLParser.scala:43)rn at org.apache.spark.sql.SQLContext.parseSql(SQLContext.scala:231)rn at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:817)rn at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)rn at java.lang.reflect.Method.invoke(Method.java:497)rn at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:231)rn at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:381)rn at py4j.Gateway.invoke(Gateway.java:259)rn at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:133)rn at py4j.commands.CallCommand.execute(CallCommand.java:79)rn at py4j.GatewayConnection.run(GatewayConnection.java:209)rn at java.lang.Thread.run(Thread.java:745)rnrn请问,如何解决? 论坛

没有更多推荐了,返回首页