SELECT * FROM ( SELECT ci.*, CASE WHEN ci.stop_status = 1 THEN 3 WHEN ci.stop_status = 2 AND ci.is_class_time = 0 THEN 2 WHEN ci.stop_status = 2 AND ci.is_class_time = 1 AND CONVERT(VARCHAR (10), ci.class_start_time, 20) <![CDATA[ <= ]]> CONVERT(VARCHAR (10), getdate(), 20) THEN 2 ELSE 1 END AS classStatus, COALESCE(classUser.userNum,0) AS userNum, COALESCE(classCourse.courseNum,0) AS courseNum FROM class_info ci LEFT JOIN ( SELECT cu.class_id, COUNT(1) AS userNum FROM class_user cu INNER JOIN customer c ON c.id = cu.user_id WHERE cu.is_valid = 1 AND c.is_valid = 1 AND cu.is_clearance = 0 GROUP BY cu.class_id ) AS classUser ON classUser.class_id = ci.id LEFT JOIN ( SELECT class_id, COUNT(1) AS courseNum FROM class_course GROUP BY class_id ) AS classCourse ON classCourse.class_id = ci.id
时间: 2024-02-14 17:27:27 浏览: 26
这是一个复杂的查询语句,它从class_info表中选择所有列,并通过左连接查询得到了classUser和classCourse的结果。我会将查询语句进行格式化,以便更好地理解它:
```sql
SELECT *
FROM (
SELECT ci.*,
CASE
WHEN ci.stop_status = 1 THEN 3
WHEN ci.stop_status = 2 AND ci.is_class_time = 0 THEN 2
WHEN ci.stop_status = 2 AND ci.is_class_time = 1 AND CONVERT(VARCHAR(10), ci.class_start_time, 20) <= CONVERT(VARCHAR(10), getdate(), 20) THEN 2
ELSE 1
END AS classStatus,
COALESCE(classUser.userNum, 0) AS userNum,
COALESCE(classCourse.courseNum, 0) AS courseNum
FROM class_info ci
LEFT JOIN (
SELECT cu.class_id, COUNT(1) AS userNum
FROM class_user cu
INNER JOIN customer c ON c.id = cu.user_id
WHERE cu.is_valid = 1
AND c.is_valid = 1
AND cu.is_clearance = 0
GROUP BY cu.class_id
) AS classUser ON classUser.class_id = ci.id
LEFT JOIN (
SELECT class_id, COUNT(1) AS courseNum
FROM class_course
GROUP BY class_id
) AS classCourse ON classCourse.class_id = ci.id
)
```
这个查询语句的目的是获取class_info表中的所有信息,并根据一些条件计算出classStatus、userNum和courseNum字段的值。classStatus字段根据stop_status和is_class_time的值进行计算,userNum字段是根据classUser子查询中的用户数量计算得出的,courseNum字段是根据classCourse子查询中的课程数量计算得出的。整个查询使用了多个子查询和左连接来获取所需的数据。