======================oracle中实现boolean==================================
一直被Oracle中没有boolean类型困扰,网上有两种解决方案,一是用Number(1),二是用Char(1),各有所长,个人比较喜欢用Number方式解决
特定boolean类型情况下,Char(1)是比Number(1)更好的选择,
因为char所用的存储空间会比后者少,但这二者在查询时存储空间的节省会提供查效率,
但是要注意的是用Char(1)的时候不能让这个字段可以为空,必须有缺省,否则查询效率会降低
==============================oracle修改表:添加和删除列================================
oracle删除列:
alter table Merchant_Terminal_g_Log dropcolumn MOVESIGN
oracle添加列:
alter table Merchant_Terminal_g_Log add(MOVESIGN number default 0 not null);
alter table Merchant_Terminal_l_Log add(MOVESIGN number default 0 not null);
select
t.id,t.merchant_no as "商户",
t.terminal_no as "终端",
to_char(t.date_created,'yyyy-mm-dd hh24:mi:ss') as "签到时间",
c.business_address as "注册位置",
t.master_cell_info as "实际使用基站位置"
from MERCHANT_TERMINAL_G_LOG t,cm_merchant m,cm_corporation c
where t.merchant_no = m.merchant_no and m.id = c.id and to_char(t.date_created,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')
order by t.merchant_no, t.id
--------------------------------------------------很强的一个函数-----------------------------------------------
分组后查询每个组的第几条记录(rowid标示)
SELECT terminal_no,from_phone
FROM (
SELECT ROW_NUMBER() OVER(PARTITIONBY test1.terminal_no ORDER BY test1.date_created ASC)rn,test1.*
FROM MERCHANT_TERMINAL_L_LOG test1
)
WHERE rn = 2 ;--取每个分组的第2条,如果组里数据不足两条,则直接把该组忽略,
----------------------------minus函数:取得结果中的第几条到第几条---------------------------------------------------
如下:取得第4条
SELECT * FROM (select * from merchant_terminal_l_log b ) where rownum <= 4
minus
SELECT * FROM (select * from merchant_terminal_l_log b ) where rownum <= 3
如下:取得第3,4条
SELECT * FROM (select * from merchant_terminal_l_log b ) where rownum <= 4
minus
SELECT * FROM (select * from merchant_terminal_l_log b ) where rownum <= 2
============================================================================
oracle没有float类型,直接用float就可以了
建表后给id,添加自增sequence
创建这张表的sequence
create sequence CELL_LNG_LAT_SEQUECE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
一旦定义了CELL_LNG_LAT_SEQUECE ,你就可以用CURRVAL,NEXTVAL
CURRVAL=返回
sequence
的当前值
NEXTVAL=增加
sequence
的值,然后返回
sequence
值
CELL_LNG_LAT_SEQUECE .CURRVAL
CELL_LNG_LAT_SEQUECE .NEXTVAL
删除sequence
drop sequence CELL_LNG_LAT_SEQUECE