SQL学习总结

操作	sql
查询某个字段是否等于	SELECT * FROM dd_User where username = ‘张三’
查询某个字段包含某个字段的人	SELECT * FROM dd_User where username Like ‘张三’
查询在XX时间段内 部门名称是’经营部’	SELECT * FROM dd_fin_journal_detail where (record_date >= ‘2017-04-01 00:00:00’) and (record_date < ‘2017-05-01 00:00:00’) and (department_name = ‘经营部’)
对数据进行操作后需要setval	SELECT setval(‘dd_hr_check_in_id_seq’, (select max(id) from dd_hr_check_in))
Integer类型的字段set空值	UPDATE table SET create_expense=null WHERE del=0;
查询库中的Geometry字段并转成wkt	select st_astext(geom) as wkt from dd_prj_geometry;
to_number（text,text）函数，参数1是要转的数字字符串，参数2为模式参数	SELECT to_number(‘0.5’, ‘99.99’);//0.5

1.求和,分组,多表查询,左连接

    SELECT   
            sum(t.cost) as total_cost,           
            t.project_id ,      
            t.project_name, 
            t.reimber_id,   
            u.username as reimber_name,   
            t.prjmanager_id as leader_id,
            u1.username as leader_name,
            u1.departmentid as deparetment_id,
            u1.department as department

    FROM dd_fin_journal_detail t 
            left join dd_user u on t.reimber_id = u.id  
            left join dd_user u1 on t.prjmanager_id = u1.id

     where t.id  in (27106,27107,27108,27109) group by 
             t.project_id,      
             t.project_name, 
             t.reimber_id,  
             u.username,       
             t.prjmanager_id,
             u1.username,
             u1.departmentid,
             u1.department;

2.查询库中的Geometry字段返回list

@Override
public List<Object> getPrjGeometryByProjectId(String projectId) {
    // TODO Auto-generated method stub
    String hql = "select st_astext(geom) as getwkt from dd_prj_geometry where project_id ="+ "'"+projectId+"'";
    Query query = getSession().createSQLQuery(hql);
    List<Object> list = query.list();
    return list;
    }

3.截取projectId后四位并返回最大值

SELECT max(substring(project_id,LENGTH(project_id)-3,LENGTH(project_id))) 
FROM dd_prj_info WHERE project_id like '%JY%'

4.PG将String转换成number求和

//需要转换的类型要判断不能为'' 
SELECT SUM(to_number(reality_workload,'9999.99')) AS totalWorkload FROM dd_prj_logbook WHERE productProgamId = :productProgamId AND reality_workload != '' AND fieldLeader = 1

---

SQL学习笔记

关键字	说明	sql
DISTINCT	只返回不同的值	SELECT DISTINCT id FROM dd_user;
TOP	返回查询结果前X行	SELECT TOP5 id FROM dd_user;
ORDER BY	升序排序(必须是最后子句,可以按多个列排序)	SELECT id, name, age FROM dd_user ORDER BY id, age;
DESC	降序(id按降序排列)	SELECT id, name, age FROM dd_user ORDER BY id DESC, age;
LIMIT 15 OFFSET 10	从第十行开始返回15行	SELECT id, name, age FROM dd_user ORDER BY id DESC, LIMIT 15 OFFSET 10;