精华内容
下载资源
问答
  • SELECT 后的字段与 GROUP BY 后的字段要保持一致,否则语法错误,无法执行查询。 SELECT ORGID, STUID, count(*) FROM STUDENT GROUP BY ORGID, STUID ORDER BY count(*) DESC

    SELECT 后的字段与 GROUP BY 后的字段要保持一致,否则语法错误,无法执行查询。
    在这里插入图片描述

    SELECT ORGID, STUID, count(*) FROM STUDENT
    GROUP BY ORGID, STUID
    ORDER BY count(*)
    DESC
    
    展开全文
  • 根据userId分组,取日期最新的(即每个UserId取日期最新的一条数据) <!--同一个用户日志只取createdate和createtime日期最新的一个--> SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY userId ORDER ...

    根据userId分组,取日期最新的(即每个UserId取日期最新的一条数据)

    <!--同一个用户日志只取createdate和createtime日期最新的一个-->
    SELECT *
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY userId ORDER BY createdate || createtime DESC) rn,
    t.*
    FROM (
    select *
    from ${@t_user_log} t1
    ) t)
    WHERE rn = 1
    
    展开全文
  • select t3.*,row_number() over(partition by t3.grid order by t3....根据t3 表的grid 字段分组,然后,按照year 字段 从小到大排序。 重命名一个字段名叫 row_number表示根据分组编号,即 每一组是 1,2,3...的序号
     select t3.*,row_number() over(partition by t3.grid order by t3.year asc) 
        row_number from 表名 t3

    根据t3 表的grid 字段分组,然后,按照year 字段 从小到大排序。

    重命名一个字段名叫

     row_number
    表示根据分组编号,即 每一组是 1,2,3...的序号

    展开全文
  • < select id = "selectRealTimeMonitorPaging" parameterType ..."realTimeMonitorEntity" ...partition by 以此字段分组 order by 以此字段排序 desc ) rn from 表 ) where rn = 1 -- 表示取第一个

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    <select id="selectRealTimeMonitorPaging" parameterType="realTimeMonitorEntity" resultType="realTimeMonitorEntity">
    		select
    		<include refid="Base_Column_List"/>,
    		rownum_ 
    		from (select
    		<include refid="Base_Column_List"/>,
    		row_number() over(order by RSM_ID desc) as rownum_
    		from(
    			select
    			 RSM_ID 
    			,RSM_FID
    			,(select EMI_NUMBER from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RSM_EM_FID
    			,RSM_FIRST_VALUE
    			,RSM_PRESENT
    			,to_char(RSM_COLLECTION_TIME,'yyyy-MM-dd') RSM_COLLECTION_TIME
    			,RSM_RELATIVE
    			,RSM_X
    			,RSM_Y
    			,RSM_H
    			,RSM_INITIAL_VALUE
    			,RSM_CURRENT_VALUE
    			,RSM_CURRENT_SOILRESSURE
    			,RSM_CUMULATIVE_RAINFALL
    			,RSM_TEMPERATURE
    			,RSM_HUMIDITY
    			,RSM_RAINFALL
    			,RSM_TYPE
    			,RSM_INITIAL_WATER
    			,RSM_CURRENT_WATER
    			,RSM_DEPTH
    			,RSM_ESTABLISH_PEOPLE
    			,to_char(RSM_ESTABLISH_TIME,'yyyy-MM-dd') RSM_ESTABLISH_TIME
    			,RSM_DELFLAG
    			,(select TMP_NAME from TERM_MEASUREPOINKS where TERM_MEASUREPOINKS.TMP_FID = (select EMI_TMP_FID from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID)) AS point
    			,(select EMI_MODEL from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS model
    			,(select EMI_NAME from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS type
    			,row_number() over(partition by RSM_EM_FID order by RSM_COLLECTION_TIME desc) as rn
    			from REAL_TIME_MONITOR
    			where 
    			1=1 and RSM_DELFLAG='0'
    			<include refid="where_conditions" />
    		)where rn=1
    		) row_ WHERE row_.rownum_ between #{startPage} and #{endPage}
    	</select>
    
    
    <select id="selectRealTimeMonitorCount" parameterType="realTimeMonitorEntity" resultType="Integer">
    		select count(1) from (
    			select
    		<include refid="Base_Column_List"/>,
    		row_number() over(order by RSM_ID desc) as rownum_
    		from(
    			select
    			 RSM_ID 
    			,RSM_FID
    			,(select EMI_NUMBER from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RSM_EM_FID
    			,RSM_FIRST_VALUE
    			,RSM_PRESENT
    			,to_char(RSM_COLLECTION_TIME,'yyyy-MM-dd') RSM_COLLECTION_TIME
    			,RSM_RELATIVE
    			,RSM_X
    			,RSM_Y
    			,RSM_H
    			,RSM_INITIAL_VALUE
    			,RSM_CURRENT_VALUE
    			,RSM_CURRENT_SOILRESSURE
    			,RSM_CUMULATIVE_RAINFALL
    			,RSM_TEMPERATURE
    			,RSM_HUMIDITY
    			,RSM_RAINFALL
    			,RSM_TYPE
    			,RSM_INITIAL_WATER
    			,RSM_CURRENT_WATER
    			,RSM_DEPTH
    			,RSM_ESTABLISH_PEOPLE
    			,to_char(RSM_ESTABLISH_TIME,'yyyy-MM-dd') RSM_ESTABLISH_TIME
    			,RSM_DELFLAG
    			,(select TMP_NAME from TERM_MEASUREPOINKS where TERM_MEASUREPOINKS.TMP_FID = (select EMI_TMP_FID from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID)) AS point
    			,(select EMI_MODEL from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS model
    			,(select EMI_NAME from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS type
    			,row_number() over(partition by RSM_EM_FID order by RSM_COLLECTION_TIME desc) as rn
    			from REAL_TIME_MONITOR
    			where 
    			1=1 and RSM_DELFLAG='0'
    			<include refid="where_conditions" />
    		)where rn=1
    			)
    	</select>
    

    参考sql

    select * from (select 表.*, row_number() over(partition by 以此字段为分组  order by 以此字段排序 desc) rn
    
    from) where rn=1   --表示取第一个
    
    展开全文
  •  select * from (select 表.*, row_number() over(partition by 以此字段分组 order by 以此字段排序 desc) rn from 表) where rn=1 --表示取第一个
  • oracle按单个字段分组

    千次阅读 2019-11-10 03:19:26
    按不同单个字段分组结果查询不分组字段 SELECT ID FROM ( SELECT ID, NAME, TASK_VERSION, COUNT(ID) OVER (PARTITION BY NAME)AS NUM, --单个字段分组 MIN(TASK_VERSION) OVER (PARTITION BY NAME) AS TASK_...
  • 字段有:listid recid 1 1 1 2 2 3 2 4 2 5 想得到的数据是 按照 listid 分组 然后取出每组中recid最大的那几条纪录。结果应该是这两条纪录: 字段:listid recid 1 2 ...
  • oracle 根据某个字段获取第一条数据 根据分组获取排序的第一条数据 select * from ( SELECT ROW_NUMBER()over(PARTITION by 分组字段 ORDER BY 排序字段 desc) as ro, xxx需要查询的字段 from 表名 ) dual where ...
  • 1.连接某个字段对于表A我们有B和C两个字段有时候,我们想B相同的,C字段相连接的值,可以用WM_CONCAT和LISTAGG两种方法。其中LISTAGG为11g中的新特性。两种用法如下:SELECT WM_CONCAT(V.USER_NAME) AS USER_NAME, ...
  • 需求:查询各个设备存在未消缺的缺陷,取缺陷等级最高的,作为这个设备当前的缺陷等级: ...by 分组字段   order   by  排序字段  desc 转载于:https://www.cnblogs.com/ryanchancrj/p/6437288.html
  • 我的数据库中有一个名为testgroup的表,如下所示:I J---------------------- ----------------------1 a1 a2 a1 b1 ...
  • 某个字段分组 取其第一条数据 select * from (select t.app_id, t.emp_no, t.device_id, t.device_type, t.last_connect_time, t.device_model, rank() over(partition by t.device_type or
  • oracle sql(按某个字段分组然后从每组取出最大的一条纪录)?  比如表 mo_partprg 字段有:listid recid  1 1  1 2  2 3  2 4  2 5 想得到的数据是 按照 listid 分组 然后取出每组...
  • 将表内 CLOSING_CASE 中的多个字段拆分出来拆分成多行,然后在进行分组统计数据 SELECT ROAD_PRODUCT_CASE_ID, REGEXP_SUBSTR (CLOSING_CASE, '[^、]+', 1, LEVEL) CLOSING_CASE FROM VIEW_ROAD_CASE CONNECT...
  • 1.连接某个字段 对于表A 我们有B  有时候,我们想
  • 说到去重,一般会想到使用distinct关键字。如果像下面这样使用,会报错。因为distinct关键字只能放在头部。 如果像下面这样使用,却只能仅仅得到去重后的Salary字段信息,其它字段却...2. 根据要去重的字段分组...
  • oracle sql(按某个字段分组然后从每组取出最大的一条纪录)? 比如表 mo_partprg 字段有:listid recid  1 1  1 2  2 3  2 4  2 5 想得到的数据是 按照 listid 分组 然后取出
  • SELECT ( CASE WHEN SOFTGATEWAY_POSLIST.POSSTATUS = 0 THEN '离线' WHEN SOFTGATEWAY_POSLIST.POSSTATUS = 1 THEN '在线' END ) status, COUNT( * ) rs, 100 * round( COUNT( * ) / SUM( COUNT( * )) OVER (), ...
  • Oracle三种去重的方法 distinct group by rowid 分组函数:作用于一组数据,并对一组数据返回一个值 常见的分组函数有: count 返回找到的记录数 min 返回一个数字列或计算列的最小值 max 返回一个数字列或计算列...
  • 问题:在oracle中使用group by...(根据部分字段分组) over()分析函数 原sql SELECT IM. ID mediaGroupId, IC. ID cardId, IC.CARD_CONTENT, IC.CARD_TITLE, IM.UPDATE_TIME mediaUpdateTime, I
  • oracle中: select a,but from (select a,but row_number over(partition by a order by b) entry from t) where entry = 1 mysql中: select a,substring_index(group_concat(b order by but asc), ‘,’ ,1) as ...
  • 当前有这样一个需求,根据外键对子表数据进行分组,取每组中的一条数据就行了,如图: 如:COMMANDID = 26的有两条,只取一条数据。 sql语句: select * from(select SYSTEMID,COMMANDID,SUBTYPE,LIST...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 27,716
精华内容 11,086
关键字:

oracle根据某个字段分组