常用查询语句

最近更新时间:2018-09-14 19:19:28

基础语句

语句 举例
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=3SELECT ID FROM CoffeeQryScript.scqry where ID > (SELECT ID as S_ID FROM CoffeeQrySQL.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 CoffeeQrySQL.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.keySELECT from "a.eqry" A full join "b.eqry" B on A.key=B.keySELECT from "a.eqry" A right join "b.eqry" B on A.key=B.keySELECT 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 tpSELECT 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