--有一张表A
----------------------
-- id ct dt
-- 1 aa 2008-8-12
-- 2 bb 2008-9-10
-- 3 cc 2009-4-22
-- 4 dd 2010-3-19
-- 5 ee 2010-5-18
-- 6 ff 2010-8-25--我想得到这样的效果,sql语句该如何写:
--------------------
-- id ct dtyear dtmonth
-- 1 aa 2008 8
-- 2 bb 2008 9
-- 2 cc 2009 4
-- 3 dd 2010 3
-- 4 ee 2010 5
-- 5 ff 2010 8
----------------------
-- id ct dt
-- 1 aa 2008-8-12
-- 2 bb 2008-9-10
-- 3 cc 2009-4-22
-- 4 dd 2010-3-19
-- 5 ee 2010-5-18
-- 6 ff 2010-8-25--我想得到这样的效果,sql语句该如何写:
--------------------
-- id ct dtyear dtmonth
-- 1 aa 2008 8
-- 2 bb 2008 9
-- 2 cc 2009 4
-- 3 dd 2010 3
-- 4 ee 2010 5
-- 5 ff 2010 8
select id,ct,year(dt) as dtyeare,month(dt) as dtmonth
from A
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,ct varchar(2),dt datetime)
insert into #tb
select 1,'aa','2008-8-12' union all
select 2,'bb','2008-9-10' union all
select 3,'cc','2009-4-22' union all
select 4,'dd','2010-3-19' union all
select 5,'ee','2010-5-18' union all
select 6,'ff','2010-8-25'select id, ct, dtyear=year(dt), dtmonth=month(dt) from #tb id ct dtyear dtmonth
----------- ---- ----------- -----------
1 aa 2008 8
2 bb 2008 9
3 cc 2009 4
4 dd 2010 3
5 ee 2010 5
6 ff 2010 8(6 行受影响)
SELECT id,ct,Year(dt) AS dtyear,Month(dt) AS dtmonth FROM tableA
-------------------------
id year
1 2008
2 2009
3 2010
同意三楼的看法。