(1)如此update
drop table #t
create table #t(whid varchar(10),itemcode varchar(32),TotalAmount money,StockDate datetime)
go
insert into #t values('01','40010401050075',100,'2007-09-01')
insert into #t values('01','40010401050075',null,'2007-09-02')
insert into #t values('01','40010401050075',null,'2007-09-03')
insert into #t values('01','40010401050075',200,'2007-09-04')
insert into #t values('01','40010401050075',null,'2007-09-05')
insert into #t values('01','40010401050075',null,'2007-09-06')
insert into #t values('01','40010401050075',null,'2007-09-07')
insert into #t values('01','40010401050075',null,'2007-09-08')
insert into #t values('01','40010401050075',300,'2007-09-09')
go
update #t
set TotalAmount=(select top 1 TotalAmount from #t where
StockDate<a.StockDate and TotalAmount is not null order by StockDate desc)
from #t a where a.TotalAmount is null
select * from #t
-----------------------------------------------------------------
select top 1 TotalAmount from #t where
StockDate<a.StockDate and TotalAmount is not null order by StockDate desc
找出日期比我小的,而且离我最近的日期的金额进行更新那些金额为空的
(2)两段行转列
drop
table
tb
create
table
tb
(
name
varchar
(
20
),
kind
varchar
(
20
),
address
varchar
(
20
),
sex
varchar
(
20
)
)
insert
into
tb
select
'
王大
'
,
'
员工
'
,
'
北京市
'
,
'
男
'
union
all
select
'
李小名
'
,
'
员工
'
,
'
天津市
'
,
'
男
'
union
all
select
'
周露
'
,
'
管理员
'
,
'
陕西省
'
,
'
女
'
union
all
select
'
吴林
'
,
'
管理员
'
,
'
北京市
'
,
'
女
'
![](/Images/OutliningIndicators/None.gif)
select
*
from
tb
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
declare
@s
varchar
(
8000
)
set
@s
=
'
select address
'
select
@s
=
@s
+
'
,
'
+
kind
+
'
=sum(case kind when
'''
+
kind
+
'''
then 1 else 0 end)
'
+
'
,
'
+
sex
+
'
=sum(case sex when
'''
+
sex
+
'''
then 1 else 0 end)
'
from
tb
group
by
kind,sex
select
@s
=
@s
+
'
,sum(1) 合计 from tb group by address
'
exec
(
@s
)
-----------------------
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
北京市
11112
陕西省11001
天津市00111
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
drop
table
tb
create
table
tb
(
部門名稱
varchar
(
100
),
員工編號
varchar
(
100
),
员工姓名
varchar
(
100
),
請假日期
varchar
(
100
),
假别名称
varchar
(
100
),
請假時數
float
,
時數單位
varchar
(
100
)
)
insert
into
tb
select
'
(K1)
'
,
'
Q-0004
'
,
'
姚力
'
,
'
20070601
'
,
'
年休假
'
,
'
1
'
,
'
天
'
union
all
select
'
(K1)
'
,
'
Q-0004
'
,
'
姚力
'
,
'
20070608
'
,
'
陪產假
'
,
'
1
'
,
'
天
'
union
all
select
'
(K1)
'
,
'
Q-0004
'
,
'
姚力
'
,
'
20070612
'
,
'
年休假
'
,
'
2
'
,
'
天
'
union
all
select
'
(K1)
'
,
'
Q-0005
'
,
'
顧忠
'
,
'
20070601
'
,
'
年休假
'
,
0.5
,
'
天
'
union
all
select
'
(K2)
'
,
'
Q-0031
'
,
'
趙丽
'
,
'
20070601
'
,
'
半薪病假
'
,
8
,
'
小时
'
![](/Images/OutliningIndicators/None.gif)
declare
@s
varchar
(
8000
)
set
@s
=
'
select 部門名稱,員工編號,员工姓名
'
select
@s
=
@s
+
'
,
'
+
假别名称
+
'
=sum(case 假别名称 when
'''
+
假别名称
+
'''
then 請假時數*(case 時數單位 when
''
天
''
then 8 else 1 end) else 0 end)
'
from
tb
group
by
假别名称
print
(
@s
)
exec
(
@s
+
'
from tb group by 部門名稱,員工編號,员工姓名
'
)
(3)
数据多行转数据一列 类似的构造'union all select'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select '+name+' as name from 表名 union all ' from syscolumns where id=object_id('表名')
and colid!>2 --这是列名的序号
set @sql=left(@sql,len(@sql)-len('union all'))
declare @sql1 varchar(8000)
set @sql1=''
select @sql1=@sql1+'select '+name+' as name from 表名 union all ' from syscolumns where id=object_id('表名')
and colid>2 --这是列名的序号
set @sql1=left(@sql1,len(@sql1)-len('union all'))