共同探讨测试难题，共享测试技术，分享测试经验，愿与您一起克服难关，分享成功，共同进步！让我们携起手来从这里出发吧，Let's go.......
sql(一）
上一篇 / 下一篇 2014-05-05 18:57:24 / 个人分类：数据库
查询数据后根据值的不同显示翻译后的信息；
1、select o.organ_id,
o.ps_organ_id,
case o.organ_status
when '0' then
'有'
when '1' then
'封'
else
'其他'
end as 状态
from t_org_organ o
2、select o.organ_id,
o.ps_organ_id,
decode(o.organ_status, '0', '有效', '1', '封存', '其他') as 状态
from t_org_organ o
order by 状态 desc
o.ps_organ_id,
case o.organ_status
when '0' then
'有'
when '1' then
'封'
else
'其他'
end as 状态
from t_org_organ o
2、select o.organ_id,
o.ps_organ_id,
decode(o.organ_status, '0', '有效', '1', '封存', '其他') as 状态
from t_org_organ o
order by 状态 desc
3、查询不存在子表Z1中的但只在o表中存在的数据记录（2种方法）：
1) select *
from t_org_organ o
where o.organ_id not in (select z1.oid from z1)
order by o.ps_organ_id;
2) select *
from t_org_organ o
where not exists (select * from z1 where o.organ_id = z1.oid)
order by o.ps_organ_id;
from t_org_organ o
where o.organ_id not in (select z1.oid from z1)
order by o.ps_organ_id;
2) select *
from t_org_organ o
where not exists (select * from z1 where o.organ_id = z1.oid)
order by o.ps_organ_id;
4、like的用法：
select * from t_org_organ t
where t.organ_name like '%置地顾问%';
5、统计ps代码是否重复：
select t.ps_organ_id,count(t.organ_id) from t_org_organ t where t.ps_organ_id is not null
group by t.ps_organ_id, t.organ_id;
select t.ps_organ_id,count(t.organ_id) from t_org_organ t where t.ps_organ_id is not null
group by t.ps_organ_id, t.organ_id;
6、批量更新数据：
create table t_temp(organ_id varchar2(32),organ_code varchar2(50)); --创建临时表
select t.organ_code,t.organ_id,rowid from t_temp t;
(注意rowid，然后点击锁，打开编辑状态，复制，粘贴，注意来源的表格前空一列，然后更新就可以了）
7、利用查询出的数据创建临时表，然后查询，统计Ps代码是否重复；
select * from result1 where count1 > 1;
create table result1 as
(select t.ps_organ_id, count(distinctt.organ_id) as count1
from t_org_organ t
where t.ps_organ_id is not null
group by t.ps_organ_id, t.organ_id);
(select t.ps_organ_id, count(distinctt.organ_id) as count1
from t_org_organ t
where t.ps_organ_id is not null
group by t.ps_organ_id, t.organ_id);
select * from result1 where count1 > 1;
8、若7的临时表中没有group by，或者having等，则可以用with result1 as来创建CTE（common table expression）来进行查询；
9、其他with用法：
withc(orderyear,custid)as(2 selectYEAR(orderdate),custid fromsales.orders)3
select orderyear,COUNT(distinct(custid)) numCustsfromcgroupbyc.orderyear
;
10、having的用法：（不能用as后面的别名进行判断）
select t.ps_organ_id, count(distinct t.organ_id) as count1
from t_org_organ t
where t.ps_organ_id is not null
group by t.ps_organ_id, t.organ_id having count(distinct t.organ_id) > 1;
from t_org_organ t
where t.ps_organ_id is not null
group by t.ps_organ_id, t.organ_id having count(distinct t.organ_id) > 1;
11、日期格式的转换（若数据库中的存储的格式与sql中不一致时）
1) select * from t_org_organ o where o.establish_date> to_date('2013-05-01', 'yyyy-mm-dd') order by o.establish_date;
--此时数据库的存储格式为：2013/5/7 若：select * from t_org_organ o where o.establish_date> '2013/5/1' order by o.establish_date;则是错误的，因为‘2013/5/1’是字符型的；会报格式不一致的错误。
2) 下面这种与1）的效果是一样的：
select * from t_org_organ o where to_char(o.establish_date, 'yyyy-mm-dd') >'2013-05-01' order by o.establish_date;
TAG: