<< Click to Display Table of Contents >> 常用查询语句 |
❖基础语句
语句 |
举例 |
---|---|
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 |