SELECT count(*) ,a.SID,
a.CPMC,
F_GET_EQUIP_DICT(a.SSXQ, 'SSXQ'),
a.GZDD,
a.gzsbsj,
F_GET_EQUIP_DICT(a.ALARMTYPE, 'alarmtype'),
a.gzsm,
a.jcgzsm
FROM ATMS_EQUIPMENT_LSJL a, ATMS_EQUIPMENT_ZB b
WHERE (A.ISDELETE IS NULL OR A.ISDELETE <> '1')
AND B.SID = A.SID
AND a.sblx = 'PMS'
AND substr(gzsbsj, 1, 10) >= '2012-01-01'
and substr(gzsbsj, 1, 10) <= '2012-12-31'
group by a.SIDselect
a.CPMC,
F_GET_EQUIP_DICT(a.SSXQ, 'SSXQ'),
a.GZDD,
a.gzsbsj,
F_GET_EQUIP_DICT(a.ALARMTYPE, 'alarmtype'),
a.gzsm,
a.jcgzsm
FROM ATMS_EQUIPMENT_LSJL a, ATMS_EQUIPMENT_ZB b
WHERE (A.ISDELETE IS NULL OR A.ISDELETE <> '1')
AND B.SID = A.SID
AND a.sblx = 'PMS'
AND substr(gzsbsj, 1, 10) >= '2012-01-01'
and substr(gzsbsj, 1, 10) <= '2012-12-31'
group by a.SIDselect
a.CPMC,
F_GET_EQUIP_DICT(a.SSXQ, 'SSXQ'),
a.GZDD,
a.gzsbsj,
F_GET_EQUIP_DICT(a.ALARMTYPE, 'alarmtype'),
a.gzsm,
a.jcgzsm
FROM ATMS_EQUIPMENT_LSJL a, ATMS_EQUIPMENT_ZB b
WHERE (A.ISDELETE IS NULL OR A.ISDELETE <> '1')
AND a.SID(+) = b.SID
AND a.sblx = 'PMS'
AND substr(gzsbsj, 1, 10) >= '2012-01-01'
and substr(gzsbsj, 1, 10) <= '2012-12-31'
group by a.SID,
a.CPMC,
F_GET_EQUIP_DICT(a.SSXQ, 'SSXQ'),
a.GZDD,
a.gzsbsj,
F_GET_EQUIP_DICT(a.ALARMTYPE, 'alarmtype'),
a.gzsm,
a.jcgzsm
group by a.SID,
a.CPMC,
F_GET_EQUIP_DICT(a.SSXQ, 'SSXQ'),
a.GZDD,
a.gzsbsj,
F_GET_EQUIP_DICT(a.ALARMTYPE, 'alarmtype'),
a.gzsm,
a.jcgzsm
在GROUP BY必须,是结合相关count,sum等函数,要不只有后面跟group by select 查询部分的字段了.
用GROUP BY时,单独出现在SELECT 子句中的字段必须出现在 GROUP BY 子句中,要么对字段做聚合函数处理。
SELECT z.no, z.date,ROW_NUMBER()
OVER(PARTITION BY z.no ORDER BY z.date) AS code_id
FROM record z
);
这里就根据no进行了group,不知道能不能够满足你的需求