mysql特殊的一些情况

静听山水

已于 2023-03-20 23:19:30 修改

阅读量68

点赞数

分类专栏： Mysql 文章标签： sql 数据库 Powered by 金山文档

于 2023-02-12 12:53:04 首次发布

本文链接：https://blog.csdn.net/qq_41081716/article/details/128993788

版权

Mysql 专栏收录该内容

49 篇文章 9 订阅

订阅专栏

文章详细介绍了如何在MySQL中进行复杂的SQL查询，包括在SELECT和FROM之间放置表以进行子查询，计算不同条件下的计数，以及使用CASE语句根据特定条件确定新的列值。查询涉及到的任务状态检查、时间比较和数据百分比计算展示了数据库操作的高级用法。

摘要由CSDN通过智能技术生成

注意mysql中将表放在select和from之间的情况：

(SELECT count(id) FROM yx_task_customer c WHERE t.uid = c.task_uid) customerCount,
(SELECT count(id) FROM yx_task_customer c WHERE t.uid = c.task_uid AND c.is_visit = '0') noVisitCount,

SELECT uid,
       task_name                                                   as taskName,
       task_no                                                     as taskNo,
       CASE
           WHEN task_status = '3' THEN '3'
           WHEN noVisitCount = 0 THEN '2'
           WHEN noVisitCount > 0 and NOW() > date_add(end_date, interval 1 day) THEN '4'
           ELSE '1' END                                               taskStatus,
       end_date                                                    as endDate,
       create_by                                                   as createBy,
       create_time                                                 as createTime,
       taskSource,
       customerCount,
       noVisitCount,
       needAllotCount,
       IF(needAllotCount > 0, 1, 0)                                as isNeedAllot,
       floor((customerCount - noVisitCount) / customerCount * 100) as taskProgress
FROM (SELECT t.uid,
             t.task_name,
             t.task_no,
             t.task_status,
             t.end_date,
             t.create_by,
             t.create_time,
             t.is_sup_bank_create as taskSource,
             (SELECT count(id) FROM yx_task_customer c WHERE t.uid = c.task_uid)                      customerCount,
             (SELECT count(id) FROM yx_task_customer c WHERE t.uid = c.task_uid AND c.is_visit = '0') noVisitCount,
             0                    as                                                                  needAllotCount
      FROM yx_task t) tt
where customerCount > 0
  and task_status = '3'
  and taskSource = ?
order by create_time desc
LIMIT ?