常用查询语句

<< Click to Display Table of Contents >>

当前位置:  附录 

常用查询语句

Previous pageReturn to chapter overviewNext page

基础语句

语句

举例

All

SELECT * from test_cloud.clqry

Where

SELECT Market_Size, Sales from test_cloud.clqry where Market_Size ="Major Market"

Group by

SELECT Market, Sum(Sales) as Sum_Sales from test_cloud.clqry group by Market

Order by

SELECT Market, Sales from test_cloud.clqry order by Sales DESC

SubQuery

SELECT Id_O as M_ID FROM Orders.eqry WHERE M_ID = (SELECT domestic_price FROM CustomOilQry.cqry WHERE domestic_price=3

SELECT ID FROM DerbyCoffeeQryScript.scqry where ID > (SELECT ID as S_ID FROM DerbyCoffeeQrySQL.sqry WHERE S_ID = 4245

Alias

SELECT Market as "Market1", Sum(Sales) as Sum_Sales from test_cloud.clqry group by Market SELECT Market as shichang from test_cloud.clqry

 

过滤条件

函数

举例

Like

SELECT Market from test_cloud.clqry where Market like '%a%'

Sub Query not in

SELECT domestic_price as M_ID ,global_price FROM CustomOilQry.cqry where M_ID NOT IN (SELECT ID as S_ID FROM DerbyCoffeeQrySQL.sqry WHERE S_ID>3

Between

SELECT ID from test_cloud.clqry where ID between 2 and 10

ContainsIn

“=”, "!=", "<>", ">", ">=", "<", "<="

Join

SELECT * from "a.eqry" A inner join "b.eqry" B on A.key=B.key

SELECT * from "a.eqry" A full join "b.eqry" B on A.key=B.key

SELECT * from "a.eqry" A right join "b.eqry" B on A.key=B.key

SELECT * from "a.eqry" A left join "b.eqry" B on A.key=B.key

Union

SELECT ID1 from "a.eqry" A union select ID2 from "b.eqry"

 

算数计算

举例

SELECT State as state_a, Product, sum(ID) + 2 as tp from coffee.sqry group by state_a, Product order by tp

SELECT Sales * 2 + 3 from test_cloud.clqry

 

日期函数

函数

举例

year, quarter, month, week, day, hour, minute, second

SELECT "Date", year("Date"), quarter("Date"), month("Date"), week("Date"), day("Date"), hour("Date"), minute("Date"), second("Date") from test_cloud.clqry

Datepart: quarterpart, monthpart, weekpart, daypart, hourpart, minutepart, secondpart

SELECT "Date", quarterpart("Date"), monthpart("Date"), weekpart("Date"), daypart("Date"), hourpart("Date"), minutepart("Date"), secondpart("Date") from test_cloud.clqry

 

GIS 函数

InCircle 用来返回指定经纬度,半径(单位是米)以内满足条件的点;InRect 用来返回指定中心点,宽和高(单位是米)以内满足条件的点。

函数

举例

 incircle

SELECT longitude,latitude from "gis/gis_cloud.clqry" where pos1 incircle(6.0, 5.1, 1000000) order by longitude, latitude asc

inrect

SELECT longitude,latitude from "gis/gis_cloud.clqry" where pos1 inrect(-15, 4.1, 2226388, 2226388) order by longitude, latitude asc