--标准函数Lpad 可以实现左补零,但是如果多于需要长度,则会截断字符串
SELECT LPAD ('1' , 3 , '0') FROM DUAL -- return 001
情况一:需要补零。
SELECT LPAD ('1' , 3 , '0') FROM DUAL
结果:001 正确
情况二:不需要补零。
SELECT LPAD ('001' , 3 , '0') FROM DUAL
结果:001 错误
select
to_char(wmsys.wm_concat(
queuesign
||(select LPAD(sortno,3,0)from dual)
||'('||
patname||')'
)) as WaitPatients
from
(--查询分组数据的前 3 条数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY cc.queuename ORDER BY cc.enroldate) rn,
cc.*
FROM
( --查询已登记数据,根据队列、登记日期、状态排序--结束至bb
select * from
(select a.patname,a.queuesign,a.queuename,a.sortno,a.enroldate,a.status
from qs_register a where a.status='已登记'
group by a.patname,a.queuesign,a.queuename,a.sortno,a.enroldate,a.status
order by a.queuename,a.enroldate,a.status
) bb
)cc ) WHERE rn in(1,2,3)
) b
group by queuename
实例二
create or replace view VIEW_GETCALLANDWAITPATIENT as
select *
--ok.queuename,ok.patname,ok.queuesign,ok.sortno,ok.enroldate,ok.status,ok.checkroom,ok.areapart,ok.ofdepart,ok.WaitPatients
from
(
SELECT ROW_NUMBER() OVER(PARTITION BY ok.queuename ORDER BY ok.enroldate) ook,
ok.*
from (select * from
(
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY cc.queuename ORDER BY cc.enroldate) rn,
cc.*
FROM
(select * from
(select a.patname,a.queuesign,a.queuename,a.sortno,a.status,a.checkroom,a.areapart,a.enroldate,a.ofdepart
from qs_register a where a.status='就诊中'
group by a.patname,a.queuesign,a.queuename,a.sortno,a.status,a.checkroom,a.areapart,a.enroldate,a.ofdepart
order by a.queuename,a.status,a.enroldate) bb)cc
)
WHERE rn = 1
) mm,
--查询已登记每组前3条数据并合并为(1003(张三丰),1003(张三丰),1003(张三丰))
(select
to_char(wmsys.wm_concat(
queuesign
||(select LPAD(sortno,3,0)from dual)
||'('||
patname||')'
)) as WaitPatients
from
(--查询分组数据的前 3 条数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY cc.queuename ORDER BY cc.enroldate) rn,
cc.*
FROM
( --查询已登记数据,根据队列、登记日期、状态排序--结束至bb
select * from
(select a.patname,a.queuesign,a.queuename,a.sortno,a.enroldate,a.status,a.ofdepart
from qs_register a where a.status='已登记'
group by a.patname,a.queuesign,a.queuename,a.sortno,a.enroldate,a.status,a.ofdepart
order by a.queuename,a.enroldate,a.status,a.ofdepart
) bb
)cc ) WHERE rn in(1,2,3)
) b
group by queuename
)nn) ok )
WHERE ook = 1
--group by ok.queuename,ok.patname,ok.queuesign,ok.sortno,ok.enroldate,ok.status,ok.checkroom,ok.areapart,ok.ofdepart,ok.WaitPatients