常用场景SQL

求所在月份的数据都处理完成

• 按月份查出该月的总数量 num1
• 按月份查出该月都处理完成的数量 num2
• 比较 num1 = num2 代表处理完成

select * from
(
	select count(1) as num, month from table1 group by month
) A
left join
(
	select count(1) as num, month from table1 WHERE STATUS = 1 group by month
) B ON A.month = B.month
where a.num = b.num

查询表结构

-- 查询表
select distinct t.TABLE_SCHEMA ,t.TABLE_NAME as 表名, t.TABLE_COMMENT as 表描述, 
t.TABLE_ROWS 数据条数, concat(round(t.DATA_LENGTH/1024/1024,2),'M') as 数据大小
from information_schema.tables t
where t.TABLE_SCHEMA = 'dhcs'
-- AND t.TABLE_NAME like '%log%' 
-- AND T.TABLE_COMMENT LIKE '%日志表%'
ORDER BY t.TABLE_NAME,t.TABLE_ROWS desc, t.CREATE_TIME desc;

-- 查询字段
select distinct t.TABLE_SCHEMA,t.TABLE_NAME,t.IS_NULLABLE,LOWER(t.COLUMN_NAME), t.COLUMN_NAME,t.COLUMN_TYPE,t.COLUMN_COMMENT 
from information_schema.columns t 
where t.TABLE_SCHEMA = 'dhcs'
-- and table_name like '%order%'
-- and COLUMN_COMMENT like '%订单表%'
-- and LOWER(t.COLUMN_NAME) like '%orderno%'
ORDER BY TABLE_NAME;