SQL 综合运用（SQL Server 2005）(别名、时间处理、字段处理、联合查询……)

sl_project为表名，表中有字段：id、name、state、date。

--1、为列建立别名：关键字 as 后加别名

select id as 编号 from sl_project; 

 

--2、 据记录值显示不同名称:when后面为表中记录中，then后为查询结果显示值，最后的end不可掉，在要根据不同字段显示不同内容时候使用

select (case when state='JX' then '已结算'when p.project_state='QB' then '全部' whenp.project_state='SS' then '实施' when state='ST' then '停止' state='WH' then '维护' when state='YGB' then'已关闭' when state='ZT' then '暂停'end) as 状态 from sl_project;

 

--3、时间的处理：(--结果格式为：1、1900-01-01   2、1900-01-01 00:00:00.000 其他格式见附录：SQL时间转换函数)

select convert(varchar(10),date,120) as 日期from sl_project  

select convert(varchar,date,120) as 日期from sl_project ;

 

--4、字段处理：将张三[zhangsan] 转换为 张三

select substring(name,0,CHARINDEX('[',name)) as 名称from sl_project; 

 

--5、联合查询

（1）union 连接，将两个表的记录联合起来，前提：两个表中查询的记录字段名一样

例如：

select id,name from sl_project union all select id, namefrom sl_project_child; 

 -- 结果将两个表中的数据都查出来，表结构不变

 

（2）left join 左外连接

select p.id,p.name,u.addre from  sl_project p  left join  sl_user u on u.name=p.name;

--解释：根据name来连接两个表，查出project表中的id、name以及user表中的addre，已左边的表为主表，将右边表的字段加在左边表查询结果的后面，即：查询结果为3列，如果第二个user表的addre字段值为空或没有，则null填充

 

（3）right join右外连接（与左外连接相反）

select p.id,p.name,u.addre from sl_project  p right join sl_user  u on  u.name=p.name;

--以第二个表user为基准，第一个表project的字段加到结果左边，没有的记录用null来填充

 

 

临时表：暂时存在数据库中，会自动清除的表结构，关键字 #，分为全局临时表、部分临时表

使用：比如联合查询时，要查询的数据在几个表中，为了数据齐全又代码简单，选择建立临时表来将对应字段合并插入到临时表中，最后联合临时表来查，示例：

<pre name="code" class="sql">因为项目信息、子系统信息在不同的表中，联合查询项目流程时，建立临时表来将项目、子系统表合并

--项目&子系统基础信息

--建立项临时表

create table #temp_date(

  djh varchar(200),

  applydatevarchar(400)

)

 

insert into #temp_date  --将数据插入临时表

  select id,namefrom sl_project union all select id,name from sl_project_child order by id;

</pre><pre name="code"class="sql">--综合运用：（本人数据导出实例--项目信息 ,仅供参考）

select p.contcd as 项目所属合同编号,p.custna as 客户名称,p.project_id as 项目编号,p.project_name as 项目名称,

  u.username as 项目经理,kp.paranaas 项目状态,k.parana as 项目类型,p.prj_valueas 项目价值 ,p.reasonablemm as 合理人月,

  fc.grandadd as 累计追加人月,fc.promonth as 累计合理追加人月,convert(varchar(10),cf.applydate,120)as 项目立项日期,

 convert(varchar(10),p.s_date_start,120) as 项目启动日期,convert(varchar(10),ps.applydate,120) as 项目规划日期,

  p.deptna as 主办业务部,'' as 上线后责任人,'' as 项目情况说明

  fromsl_pm_project_main p

  left joinpcmc_knp_para k on k.paracd=p.prjttp

  left joinpcmc_knp_para kp on kp.paracd=p.project_state

  left joinpcmc_user u on u.userid=p.project_manager

  left join#temp_date cf on cf.djh=p.lx_djh

  left joinflow_sl_pm_supply ps on ps.prjtcd=p.project_id

  left joinflow_sl_pm_change fc on fc.project_id=p.project_id

  wherekp.subscd='OAMS' and kp.paratp='pmphase' and k.subscd='OAMS' andk.paratp='prjttp'

 

--子系统信息

select p.master_project_id as 所属项目编号,pm.project_name as 所属项目名称,p.cwxt_contract_idas 所属合同编号,p.custna as 客户名称,

  p.project_id as 子系统编号,p.project_name as 子系统名称,u.username as 子系统负责人,

  (case whenp.project_state='JX' then '已结算' when p.project_state='QB' then '全部' when p.project_state='SS' then '实施'

  whenp.project_state='ST' then '停止' when p.project_state='WH' then '已交接'

  whenp.project_state='WHDQ' then '维护到期' when p.project_state='YGB' then '已关闭' when p.project_state='ZT' then '暂停'

  whenp.project_state='ZZ' then '终止' end) as 子系统状态,

  (case whenp.prjttp='HT' then '合同项目' when p.prjttp='NB' then '内部项目' when p.prjttp='WB'

  then '外包项目'when p.prjttp='WH' then '维护项目' when p.prjttp='YF' then'研发项目' end) as 子系统类型 ,

  p.reasonablemm as合理人月,p.zhanbias 人月占比,p.prj_value as 子系统价值,

  fc.beginpro as 初始预算人月,fc.grandadd as 累计追加人月,fc.promonth as 累计合理追加人月,p.brchna as 考核部门,

 convert(varchar(10),cf.applydate,120) as 子系统立项日期,

 convert(varchar(10),p.s_date_start,120) as 子系统启动日期,convert(varchar(10),ps.applydate,120) as 子系统规划日期,

  (case whenp.ischeck='1' then '是' else '否' end)as 是否可报销,'' as 上线后责任人,'' as 项目情况说明

  fromsl_pm_project p

  left joinsl_pm_project_main pm on p.master_project_id=pm.project_id

  left joinpcmc_user u on u.userid=p.project_manager

  left join#temp_date cf on cf.djh=p.lx_djh

  left joinflow_sl_pm_supply ps on ps.prjtcd=p.project_id

  left join#temp_renyue fc on fc.project_id=p.project_id

  wherep.project_from='JS' and p.brchno is not null --此条件过滤子系统中多余历史数据 brchno主办部门，区分历史数据

  order byp.project_id

 

 

附： SQL时间转换函数

select CONVERT(varchar,getdate(),120)

--2009-03-15 15:10:02

 

select CONVERT(varchar(10),getdate(),120)

--2009-03-15

 

select replace(replace(replace(CONVERT(varchar,getdate(), 120 ),'-',''),' ',''),':','')

--20090315151201

 

select CONVERT(varchar(12) , getdate(), 111)

--2009/03/15

 

select CONVERT(varchar(12) , getdate(), 112)

--20090315

 

select CONVERT(varchar(12) , getdate(), 102)

--2009.03.15

 

select CONVERT(varchar(12) , getdate(), 108)

--15:13:26

 

--其它我不常用的日期格式转换方法:

 

select CONVERT(varchar(12) , getdate(), 101 )

--03/15/2009

 

select CONVERT(varchar(12) , getdate(), 103 )

--15/03/2009

 

select CONVERT(varchar(12) , getdate(), 104 )

--15.03.2009

 

select CONVERT(varchar(12) , getdate(), 105 )

--15-03-2009

 

select CONVERT(varchar(12) , getdate(), 106 )

--15 03 2009

 

select CONVERT(varchar(12) , getdate(), 107 )

-- 15, 2009

 

select CONVERT(varchar(12) , getdate(), 109 )

--03 15 2009  

 

select CONVERT(varchar(12) , getdate(), 110 )

--03-15-2009

 

select CONVERT(varchar(11) , getdate(), 113 )

--15 03 2009 

 

select CONVERT(varchar(12) , getdate(), 114)

--15:15:55:717

</span>

</pre><br /><br /><pre>