统计MYSQL 字段数量 、查询全部字段定义
SELECT
COUNT(*)
FROM
information_schema. COLUMNS
WHERE
table_schema = 'CLConsumptionDetailGatherDB'
AND table_name = 'PersonConsumptionDetailTab';
SELECT
DISTINCT
column_name,column_comment,
DATA_TYPE
FROM
information_schema. COLUMNS
WHERE
TABLE_SCHEMA = 'CLConsumptionDetailGatherDB' and column_comment !=''
SELECT data_type,column_type, column_name,column_COMMENT,character_maximum_length
FROM
information_schema. COLUMNS
WHERE
TABLE_SCHEMA = 'CLConsumptionDetailGatherDB' AND table_name = ' Tab' and data_type in ('char','varchar') and column_name not like '%id'
统计数据库一共多少张表
SELECT COUNT(*) TABLES, table_schema FROM information_schema. TABLES WHERE table_schema = 'test' GROUP BY table_schema;
统计MYSQL 数据类型对应的字段数量
SELECT
TABLE_NAME,
DATA_TYPE,
count(1)
FROM
information_schema. COLUMNS
WHERE
table_schema = 'CLConsumptionDetailGatherDB'
AND table_name = 'PersonConsumptionDetailTab'
GROUP BY DATA_TYPE
查询一个数据库中的所有表和所有字段、字段类型及注释等信息
SELECT
TABLE_NAME,
column_name,
DATA_TYPE,
column_comment
FROM
information_schema. COLUMNS
WHERE
TABLE_SCHEMA = 'test';
查询所有库表名称-mysql
select table_name tablename from information_schema.tables where table_schema=(select database()) order by table_name;
mysql 分组： 取最小值、最大值 到固定字段
SELECT OrderId, CONCAT(IF(MIN(TradeTime) = TradeTime, Travel, ''),IF(MAX(TradeTime) = TradeTime, Travel, ''))
FROM PersonConsumptionDetailTab WHERE TradeTime >= '2023-02' GROUP BY OrderId
mysql 取一堆交易时间数组中的第一个和最后一个
SELECT
KeyId, OrderId, tradetime,Travel,
MIN(CONCAT(TradeTime,TRAVEL)),MAX(CONCAT(TradeTime,TRAVEL)),
IF(MIN(TradeTime) = TradeTime, Travel, ''),
IF(MAX(TradeTime) = TradeTime, Travel, '')
,CONCAT(IF(MIN(TradeTime) = TradeTime, Travel, ''),IF(MAX(TradeTime) = TradeTime, Travel, ''))
FROM
PersonConsumptionDetailTab, (select @rankno:=0) s
WHERE
orderId ='230213175416240303'
order BY TradeTime
查询灰名单数据
SELECT a.KeyID from GreySetting19 a LEFT JOIN PersonConsumptionDetailGreyReference19 b on (a.`KeyID`=b.RelationGreySettingKeyId) where a.IsEnable=1 and a.`KeyID` IN (
SELECT DISTINCT( `RelationGreySettingKeyId`) FROM `PersonConsumptionDetailGreyReference19`
where `IsDelete` =0 and left(PersonConsumptionDetailKeyId,6) >='230201' and `AddTime` >'2023-02-01' -- and `StaffNo` ='20220512114602876604'
)
GROUP BY b.StaffNo
HAVING SUM(IF(b.LatestStatisticalStatusType=0,1,0)) =0
不用@Rank 实现 累计、累加一个值（通过自关联+子查询实现）
SELECT
LEFT(f.ym1,7) AS 账期月份, f.高管账户金额, f.备用金支付金额 as 备用金支付金额,f.中泰证券服务费加价金额,f.鲁证期货服务费加价金额,f.中泰国际服务费加价金额,IF( LEFT(f.ym1,7) = '2023-01' , '102587.6',102587.6 +sum(g.本期服务费)) as 上期服务费余额,
(102587.6 + f.中泰证券服务费加价金额 + f.鲁证期货服务费加价金额 + f.中泰国际服务费加价金额 - f.高管账户金额 - 0) +sum(ifnull(g.本期服务费,0)) as 本期服务费余额
FROM
(
SELECT
a.*,ax.*, b.*, c.*, d.*, (中泰证券服务费加价金额 + 鲁证期货服务费加价金额 + 中泰国际服务费加价金额 - 高管账户金额 - 0) AS 本期服务费
FROM
(
SELECT
LEFT (`TradeTime`, 7) AS ym,
SUM(
(
OrderMoney - ServiceFee - ProductRealClearingCheckMoney
) *
IF (
PayType = 1
OR PayType = 6
OR PayType = 2
OR PayType = 7,
1,
0
) + ServiceFee *
IF (
ServeFeePayType = 1
OR ServeFeePayType = 6
OR ServeFeePayType = 2
OR ServeFeePayType = 7,
1,
0
)
) AS 高管账户金额,
SUM(
ProductRealClearingCheckMoney
) AS 超标个人支付金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20210727144822488106'
AND `TradeTime` >= '2023-01-01'
GROUP BY
LEFT (`TradeTime`, 7)
) a left join
( SELECT LEFT (`TradeTime`, 7) AS ym11,
SUM(if( ThridPayAccount= '62226295******9378',ProductRealClearingCheckMoney,0)) as 备用金支付金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20200507105854675474'
AND `TradeTime` >= '2023-01-01'
GROUP BY
LEFT (`TradeTime`, 7)
) ax ON (a.ym = ax.ym11)
LEFT JOIN (
SELECT
LEFT (ActualStmperiod, 7) ym1,
SUM(
IF (
`ProductType` = 1
AND `ProductSubTypeName` = '出票',
3,
0
) +
IF (
`ProductType` = 3
AND `ProductSubTypeName` = '出票'
AND `ServiceFee` > 0,
Count * 2,
0
) +
IF (
`ProductType` = 5
AND `ProductSubTypeName` = '出票',
3,
0
)
) AS 中泰证券服务费加价金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20200507105854675474'
AND `ActualStmperiod` > '2022-12-31'
AND `PayType` IN ('1', '2')
AND `BusinessBillNo` <> ''
GROUP BY
LEFT (ActualStmperiod, 7)
) b ON (a.ym = b.ym1)
LEFT JOIN (
SELECT
LEFT (ActualStmperiod, 7) ym2,
SUM(
IF (
`ProductType` = 1
AND `ProductSubTypeName` = '出票',
3,
0
) +
IF (
`ProductType` = 3
AND `ProductSubTypeName` = '出票'
AND `ServiceFee` > 0,
Count * 2,
0
) +
IF (
`ProductType` = 5
AND `ProductSubTypeName` = '出票',
3,
0
)
) AS 鲁证期货服务费加价金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20210312102134296162'
AND `ActualStmperiod` > '2022-12-31'
AND `PayType` IN ('1', '2')
AND `BusinessBillNo` <> ''
GROUP BY
LEFT (ActualStmperiod, 7)
) c ON (a.ym = c.ym2)
LEFT JOIN (
SELECT
LEFT (ActualStmperiod, 7) ym3,
SUM(
IF (
`ProductType` = 1
AND `ProductSubTypeName` = '出票',
2,
0
) +
IF (
`ProductType` = 3
AND `ProductSubTypeName` = '出票'
AND `ServiceFee` > 0,
Count * 2,
0
) +
IF (
`ProductType` = 5
AND `ProductSubTypeName` = '出票',
3,
0
)
) AS 中泰国际服务费加价金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20221009174515269604'
AND `ActualStmperiod` > '2022-12-31'
AND `PayType` IN ('1', '2')
AND `BusinessBillNo` <> ''
GROUP BY
LEFT (ActualStmperiod, 7)
) d ON (a.ym = d.ym3)
) f
left join
(
SELECT
a.*, Date_add(
concat(b.ym1, '-01'),
INTERVAL 1 MONTH
) AS ym1,
b.中泰证券服务费加价金额,
c.*, d.*,
(中泰证券服务费加价金额 + 鲁证期货服务费加价金额 + 中泰国际服务费加价金额 - 高管账户金额 - 0) AS 本期服务费
FROM
(
SELECT
LEFT (`TradeTime`, 7) AS ym,
SUM(
(
OrderMoney - ServiceFee - ProductRealClearingCheckMoney
) *
IF (
PayType = 1
OR PayType = 6
OR PayType = 2
OR PayType = 7,
1,
0
) + ServiceFee *
IF (
ServeFeePayType = 1
OR ServeFeePayType = 6
OR ServeFeePayType = 2
OR ServeFeePayType = 7,
1,
0
)
) AS 高管账户金额,
SUM(
ProductRealClearingCheckMoney
) AS 超标个人支付金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20210727144822488106'
AND `TradeTime` >= '2023-01-01'
GROUP BY
LEFT (`TradeTime`, 7)
) a
LEFT JOIN (
SELECT
LEFT (ActualStmperiod, 7) ym1,
SUM(
IF (
`ProductType` = 1
AND `ProductSubTypeName` = '出票',
3,
0
) +
IF (
`ProductType` = 3
AND `ProductSubTypeName` = '出票'
AND `ServiceFee` > 0,
Count * 2,
0
) +
IF (
`ProductType` = 5
AND `ProductSubTypeName` = '出票',
3,
0
)
) AS 中泰证券服务费加价金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20200507105854675474'
AND `ActualStmperiod` > '2022-12-31'
AND `PayType` IN ('1', '2')
AND `BusinessBillNo` <> ''
GROUP BY
LEFT (ActualStmperiod, 7)
) b ON (a.ym = b.ym1)
LEFT JOIN (
SELECT
LEFT (ActualStmperiod, 7) ym2,
SUM(
IF (
`ProductType` = 1
AND `ProductSubTypeName` = '出票',
3,
0
) +
IF (
`ProductType` = 3
AND `ProductSubTypeName` = '出票'
AND `ServiceFee` > 0,
Count * 2,
0
) +
IF (
`ProductType` = 5
AND `ProductSubTypeName` = '出票',
3,
0
)
) AS 鲁证期货服务费加价金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20210312102134296162'
AND `ActualStmperiod` > '2022-12-31'
AND `PayType` IN ('1', '2')
AND `BusinessBillNo` <> ''
GROUP BY
LEFT (ActualStmperiod, 7)
) c ON (a.ym = c.ym2)
LEFT JOIN (
SELECT
LEFT (ActualStmperiod, 7) ym3,
SUM(
IF (
`ProductType` = 1
AND `ProductSubTypeName` = '出票',
2,
0
) +
IF (
`ProductType` = 3
AND `ProductSubTypeName` = '出票'
AND `ServiceFee` > 0,
Count * 2,
0
) +
IF (
`ProductType` = 5
AND `ProductSubTypeName` = '出票',
3,
0
)
) AS 中泰国际服务费加价金额
FROM
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
AND `CorpNo` = '20221009174515269604'
AND `ActualStmperiod` > '2022-12-31'
AND `PayType` IN ('1', '2')
AND `BusinessBillNo` <> ''
GROUP BY
LEFT (ActualStmperiod, 7)
) d ON (a.ym = d.ym3)
) g ON (f.ym1 >= left(g.ym1,7))
-- where
-- LEFT(f.ym1,7)='2023-03'
group by LEFT(f.ym1,7)
order by LEFT(f.ym1,7)
@Rank 的替代方案 ，左连接, 子查询
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。
TIMESTAMPDIFF(day,substr(DelayEffectTime,1,10),substr('2023-04-21',1,10))
@Rank 的代替方案。通过自关联实现，mysql 单表序号。
SELECT
p.Id,
pp.ProductSonTypeName,
count( pp.KeyId) AS rank
FROM
Tab p
LEFT JOIN Tab pp ON ( p.Id >= pp.Id AND pp.isdelete = 0)
WHERE
p.IsDelete = 0
AND p.Id >= ''
AND p.Id <= ''
AND pp.Id >= ''
AND pp.Id <= ''
AND p.ProductType = 3
AND pp.ProductType = 3
GROUP BY
p.`KeyId`
mysql GROUP_CONCAT 顺序排序 分割换行 separator '\n'
GROUP_CONCAT((CONCAT(StartAddress,"-",ArriveAddress)) separator '\n') as 出差起止城市,
select
GROUP_CONCAT( DISTINCT `ProductTypeName` order by `ProductType` )
from
`PersonConsumptionDetailTab`
WHERE
`IsDelete` = 0
and `ApplicationInfoID` = '23031919104184555401sq4559'
ORDER BY `ApplicationInfoID` , `ProductType`
SELECT count(1) FROM SellerInvoiceInfo s JOIN ( SELECT * FROM InvoiceRelateInfo WHERE IsDelete = 0 AND SourceAmount LIKE concat('%"billNo":"','155', '"%') ) i ON s.KeyId = i.RelateSellerInvoiceInfoID WHERE s.IsDelete = 0
mysql group_concat 去重，过滤 重复分隔符。 distinct , null, ORDER BY TravelTime ASC separator
select ApplicationInfoID,
GROUP_CONCAT( IF(ProductTypeName in ('国内酒店' , '用车') ,null,(CONCAT(StartAddress,"-",ArriveAddress))) ORDER BY TravelTime ASC separator '|') as 出差起止城市,
GROUP_CONCAT( IF(ProductTypeName in ('国内酒店' , '用车') ,'',(CONCAT(StartAddress,"-",ArriveAddress))) ORDER BY TravelTime ASC separator '|') as 出差起止城市
from
PersonConsumptionDetailTab
where
isDelete= 0
and CorpNo='20210816170609458160'
and BusinessBillNo='2305010312204501209' and `ApplicationInfoID` ='23040121525795055401sq0576'
GROUP BY if(ApplicationInfoID='',keyid,ApplicationInfoID) ,PassengersName
MYSQL group_concat()函数默认忽略空列但不忽略空字符串列.我有一个中等文字类型的字段,而不是null. 使用group_concat函数时,查询生成了不需要的',,,'这种类型的值. 通过if 函数，将拼接分隔符 换成null
