Oracle数据库SQL常用语句

1.选出表中字段名出现过两次及以上的记录

select * from table_name where column_name in (select column_name from table_name where colunm1_name = '123' and column2_name = '456' group by column_name having count(1)>=2)

2.两表联合查询：表1和表2有相同的字段名id，表1有字段名city，表2有字段名money，统计每个city的sum(money)

select city, sum(money) from (select table1.city, table2.money from table1,table2 where table1.id = table2.id) group by city

3.选出表中一个字段每个值出现的次数

select column_name, count(*) from table_name group by column_name

4.group by后再order by排序

select colunm_name, count(*) as num from table_name group by colunm_name order by num

5.复制一个表的结构到新表中

create table B as select * from A where 1=2;

6.将表1的数据复制到表2中

insert into table2 select * from table1;

7.varchar转decimal

select name,cast(age/(select sum(age) from boss) as decimal(32,2)) as age from boss;

未完待续。。。