大数据相关SQL查询

1.查询多条数据中当下最新时间段的某一条：2种方式

select * from bootdo.company_shares where date_1 = (
select date_1 from bootdo.company_shares group by date_1 order by date_1 desc limit 1
) order by time_1 desc limit 1

select * from bootdo.company_shares order by date_1 desc,time_1 desc limit 1

2.多个表通过·相同日期·查询，组合成一个表，并且过滤不需要的字段。

SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1

3.行转列，这是原始数据（2种方法）

select date_1,'currLiabili' COURSE,currLiabili as SCORE  from (select * from(SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1)C)D 
union select date_1, 'noLiabili' COURSE, noLiabili as SCORE from (select * from(SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1)C)D 
 union select date_1, 'totalLiabili' COURSE, totalLiabili as SCORE from (select * from(SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1)C)D 
union select date_1, 'currAsset' COURSE, currAsset as SCORE from (select * from(SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1)C)D 
 union select date_1, 'noAsset' COURSE, noAsset as SCORE from (select * from(SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1)C)D 
 union select date_1, 'totalAsset' COURSE, totalAsset as SCORE from (select * from(SELECT A.currLiabili,A.noLiabili,totalLiabili,B.currAsset,B.noAsset,B.totalAsset,B.date_1 FROM `company_liabili` A,`company_asset` B where A.date_1 = B.date_1)C)D 
order by date_1,COURSE;

 SELECT id,date_1,'经营活动现金流入小计' as 分类,cashInflowAct as 数值 from `company_cashFlow` UNION select
 id,date_1,'经营活动现金流出小计' as 分类,cashOutAct as 数值 from `company_cashFlow` UNION SELECT
 id,date_1,'投资活动现金流出小计' as 分类,cashOutInvest as 数值 FROM `company_cashFlow` UNION SELECT 
 id,date_1,'投资活动现金流入小计' as 分类,cashInflowInvest as 数值 FROM `company_cashFlow` UNION SELECT
 id,date_1,'筹资活动现金流入小计' as 分类,cashInflowFinanc as 数值 FROM `company_cashFlow` UNION select
 id,date_1,'筹资活动现金流出小计' as 分类, cashOutFinanc as 数值 from `company_cashFlow` ORDER BY id

4.通过上方数据来，查询数据库最新2020年时间段的数据。（首先列转行，然后条件查询最新的日期数据）

select * from
 (
 SELECT id,date_1,'经营活动现金流入小计' as 分类,cashInflowAct as 数值 from `company_cashFlow` UNION select
 id,date_1,'经营活动现金流出小计' as 分类,cashOutAct as 数值 from `company_cashFlow` UNION SELECT
 id,date_1,'投资活动现金流出小计' as 分类,cashOutInvest as 数值 FROM `company_cashFlow` UNION SELECT 
 id,date_1,'投资活动现金流入小计' as 分类,cashInflowInvest as 数值 FROM `company_cashFlow` UNION SELECT
 id,date_1,'筹资活动现金流入小计' as 分类,cashInflowFinanc as 数值 FROM `company_cashFlow` UNION select
 id,date_1,'筹资活动现金流出小计' as 分类, cashOutFinanc as 数值 from `company_cashFlow`
) t  WHERE t.date_1=(
	select date_1 from `company_cashFlow` group by date_1 order by date_1 desc limit 1
 ) 

5.查询数据过滤掉某字段相同的数据（distinct）

select distinct name1 from bootdo.dwb_sap_customer 

 

 

6.两个表关联，查询某一个字段的总和并且获取前面6个排名

SELECT company,SUM(total_area) AS areasqm
  FROM
    (SELECT t1.company,t2.total_area FROM mapdata t1 LEFT JOIN maptabledata t2 ON t1.id=t2.mapdata_id
      WHERE t1.city='成都'
    ) t3
GROUP BY t3.company
ORDER BY areasqm DESC LIMIT 6