81,092
社区成员
发帖
与我相关
我的任务
分享
SELECT 1 天数, COUNT(1) 任务 ,COUNT(DISTINCT UserId) 人员,COUNT(DISTINCT DepartId) 部门
FROM Task
WHERE CompanyId ='18699d7c-aa26-11ea-b164-00163e069cdf'
AND State = 0
AND CreatedAt BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 1 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 0 DAY)
UNION ALL
SELECT 2 天数, COUNT(1) 任务 ,COUNT(DISTINCT UserId) 人员,COUNT(DISTINCT DepartId) 部门
FROM Task
WHERE CompanyId ='18699d7c-aa26-11ea-b164-00163e069cdf'
AND State = 0
AND CreatedAt BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 2 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
UNION ALL
SELECT 3 天数, COUNT(1) 任务 ,COUNT(DISTINCT UserId) 人员,COUNT(DISTINCT DepartId) 部门
FROM Task
WHERE CompanyId ='18699d7c-aa26-11ea-b164-00163e069cdf'
AND State = 0
AND CreatedAt BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 3 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 2 DAY)
UNION ALL
SELECT 4 天数, COUNT(1) 任务 ,COUNT(DISTINCT UserId) 人员,COUNT(DISTINCT DepartId) 部门
FROM Task
WHERE CompanyId ='18699d7c-aa26-11ea-b164-00163e069cdf'
AND State = 0
AND CreatedAt BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 10 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 3 DAY)
UNION ALL
SELECT 10 天数, COUNT(1) 任务 ,COUNT(DISTINCT UserId) 人员,COUNT(DISTINCT DepartId) 部门
FROM Task
WHERE CompanyId ='18699d7c-aa26-11ea-b164-00163e069cdf'
AND State = 0
AND CreatedAt<DATE_SUB(DATE(NOW()),INTERVAL 10 DAY)
执行语句是这样的。表是有按WHERE来建索引的KEY `Task_Ix_Main` (`CompanyId`,`State`,`CreatedAt`)
想从语句和索引上来优化,我觉得已经没可能了。