SELECT *, @rownum := @rownum + 1 AS tmp, @incrnum := CASE WHEN @rowtotal = ${field} THEN @incrnum WHEN @rowtotal := ${field} THEN @rownum END AS rownum FROM (SELECT * from (SELECT * from aqi_hour_site WHERE 1=1 <if test="cityCode != null and cityCode != ''"> AND city_code = #{cityCode} </if> <if test="arrayList!=null and arrayList.size()>0"> AND site_code in <foreach collection="arrayList" item="item" index="index" open="(" separator="," close=")">#{item}</foreach> </if> ORDER BY data_time desc LIMIT 2000) d GROUP BY site_code <if test="dataSort==1">ORDER BY ${field} asc</if> <if test="dataSort==2">ORDER BY ${field} desc</if> ) as a, (SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0) b
时间: 2024-02-15 08:27:01 浏览: 171
这段代码看起来像是一个SQL查询语句,其中包含了一些参数以及条件筛选。具体来说,它查询了一个叫做aqi_hour_site的表,并根据一些条件进行筛选,比如cityCode和site_code。查询结果按照data_time进行降序排列,并且只取前2000条数据。然后,再对结果按照site_code进行分组,并且使用MySQL的变量来计算每个分组的行号,以便于后续的排序。最后,根据参数dataSort的值,对结果进行升序或降序排序。
相关问题
Oracle 下SQL语句怎么写 SELECT @ROWNUM := @ROWNUM + 1 AS TEMPRANK, @INCRNUM := CASE WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM END AS FILERANK, OBJ.* FROM ( SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE, U.USER_NAME AS AUTHORNAME, CONCAT( U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END ) AS USERNAME, IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) AS YLCOUNT FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru WHERE DOCINFO.VALID_FLAG = '1' AND IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) != 0 AND U.USER_ID = DOCINFO.AUTHOR_ID AND sys_stru.STRU_ID = U.DEPARTMENT_ID AND O.ORGAN_ID = sys_stru.ORGAN_ID GROUP BY DOCINFO.DOC_ID, U.USER_NAME ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC ) OBJ, ( SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0 ) R
在 Oracle 中,可以将该 SQL 语句写成如下形式:
```
SELECT TEMPRANK, FILERANK, TITLE, DOCID, DOCTYPE, AUTHORNAME, USERNAME, YLCOUNT
FROM (
SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE,
U.USER_NAME AS AUTHORNAME, CONCAT(U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END) AS USERNAME,
IFNULL(DOCINFO.DOWNLOAD_NUM, 0) AS YLCOUNT,
@ROWNUM := @ROWNUM + 1 AS TEMPRANK,
@INCRNUM := CASE
WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM
WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM
END AS FILERANK
FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru
WHERE DOCINFO.VALID_FLAG = '1'
AND IFNULL(DOCINFO.DOWNLOAD_NUM, 0) != 0
AND U.USER_ID = DOCINFO.AUTHOR_ID
AND sys_stru.STRU_ID = U.DEPARTMENT_ID
AND O.ORGAN_ID = sys_stru.ORGAN_ID
GROUP BY DOCINFO.DOC_ID, U.USER_NAME
ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC
) OBJ, (SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0) R;
```
在 Oracle 中,变量需要在 SQL 语句外面进行声明和初始化。这里使用了 `@ROWNUM`、`@ROWTOTAL` 和 `@INCRNUM` 三个变量,它们都在 SQL 语句的最后一行进行了声明和初始化。
此外,由于 Oracle 对于 SQL 语句的执行顺序与 MySQL 略有不同,因此需要将临时变量的计算放在子查询中,并使用外层查询来获取这些变量的值。最终的结果集是从子查询中返回的。
神通数据库下 一下SQL怎么写 SELECT @ROWNUM := @ROWNUM + 1 AS TEMPRANK, @INCRNUM := CASE WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM END AS FILERANK, OBJ.* FROM ( SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE, U.USER_NAME AS AUTHORNAME, CONCAT( U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END ) AS USERNAME, IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) AS YLCOUNT FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru WHERE DOCINFO.VALID_FLAG = '1' AND IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) != 0 AND U.USER_ID = DOCINFO.AUTHOR_ID AND sys_stru.STRU_ID = U.DEPARTMENT_ID AND O.ORGAN_ID = sys_stru.ORGAN_ID GROUP BY DOCINFO.DOC_ID, U.USER_NAME ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC ) OBJ, ( SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0 ) R
这是一个比较复杂的 SQL 语句,主要是用于对文档信息表进行排序和排名操作,并且使用了 MySQL 中的用户变量。
下面是这个 SQL 语句的详细解释:
```sql
SELECT
@ROWNUM := @ROWNUM + 1 AS TEMPRANK, -- 对每一行进行排名,使用用户变量 @ROWNUM
@INCRNUM := CASE
WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM -- 如果当前行的下载数量和上一行一样,则排名也相同
WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM -- 否则,将 @ROWNUM 赋值给 @INCRNUM
END AS FILERANK, -- 最终排名结果
OBJ.* -- 选择所有表格列
FROM (
SELECT
DOCINFO.TITLE, -- 文档标题
DOCINFO.DOC_ID AS DOCID, -- 文档 ID
DOCINFO.DOC_TYPE AS DOCTYPE, -- 文档类型
U.USER_NAME AS AUTHORNAME, -- 作者姓名
CONCAT(U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END) AS USERNAME, -- 用户名
IFNULL(DOCINFO.DOWNLOAD_NUM, 0) AS YLCOUNT -- 下载数量
FROM
DOC_INFO DOCINFO, -- 文档信息表
SYS_USERS U, -- 用户表
SYS_ORGAN O, -- 组织表
sys_stru -- 组织结构表
WHERE
DOCINFO.VALID_FLAG = '1' -- 有效标志
AND IFNULL(DOCINFO.DOWNLOAD_NUM, 0) != 0 -- 下载数量不为零
AND U.USER_ID = DOCINFO.AUTHOR_ID -- 用户 ID 对应的作者 ID
AND sys_stru.STRU_ID = U.DEPARTMENT_ID -- 用户所在部门对应的组织结构 ID
AND O.ORGAN_ID = sys_stru.ORGAN_ID -- 组织表中的组织 ID 对应的组织结构 ID
GROUP BY
DOCINFO.DOC_ID, -- 文档 ID
U.USER_NAME -- 用户名
ORDER BY
DOCINFO.DOWNLOAD_NUM DESC, -- 下载数量降序排列
DOCINFO.TITLE DESC -- 标题降序排列
) OBJ, (SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0) R -- 初始化用户变量
```
这个 SQL 语句的作用是将文档信息表中的文档按照下载数量和标题进行降序排列,并且对每个文档进行排名,最终返回排序后的结果集。
阅读全文
相关推荐
















