个人目前最长sql语句（美化之后）

最新推荐文章于 2022-06-09 12:09:51 发布

王黎明--成都

最新推荐文章于 2022-06-09 12:09:51 发布

阅读量1.6k

点赞数

分类专栏： sql语句文章标签： sql语句

本文链接：https://blog.csdn.net/m0_37997811/article/details/89307387

版权

sql语句专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SELECT
a.id AS id,
a.departmentId AS departmentId,
a.rank AS rank,
(
SELECT
SUM(d.studyTime)
FROM
tbl_User_Learn_Record AS d
WHERE
d.userId = a.userId
AND d.startTime >= : startTime
AND d.startTime <= : endTime
) AS totalStudyTime,
a.userId AS userId,
a.departmentName AS departmentName,
(
SELECT
COUNT(c.userId)
FROM
tbl_User_Learn_Record AS c
WHERE
c.userId = a.userId
AND c.startTime >= : startTime
AND c.startTime <= : endTime
) AS courseNum,
a.cnName AS cnName,
a.fullDepartmentName AS fullDepartmentName,
(
SELECT
sum(i.integralValue)
FROM
tbl_UserIntegral AS i
WHERE
i.userName = a.userId
AND i.beginTime >= : startTime
AND i.beginTime <= : endTime
) AS totalIntegral
FROM
tbl_User_Learn_Summary AS a
INNER JOIN tbl_User_Learn_Record AS r ON r.userId = a.userId
WHERE
a.departmentId IN (
‘1’,
‘179’,
‘16’,
‘49’,
‘101’,
‘102’,
‘103’,
‘104’,
‘105’,
‘106’,
‘166’,
‘366’,
‘367’
)
AND a.cnName LIKE ‘%l%’
AND a.departmentName LIKE ‘%zu%’
GROUP BY
a.userId
ORDER BY
a.totalStudyTime DESC;