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