表变量
使用:DECLARE @T_ChartsIdeaItem1 Table(XData char(20) null,YData char(20) null)
INSERT INTO @T_ChartsIdeaItem1
(XData,YData)
VALUES ('BU9876', 'Creating')
select * from @T_ChartsIdeaItem1
查询列转行显示:
begin
declare @sql1 varchar(8000),
@sql2 varchar(8000),
@sql3 varchar(8000)
@sql1='select 'Set @sql2='select '
@sql3='select 'select @sql1=@sql1+ '''' +convert(varchar(50), 字段1)+ ''' as ['+ convert(varchar(50), 字段1) +'],',
@sql2=@sql2+ '''' +convert(varchar(50),×字段2)+''' as['+ convert(varchar(50), 字段1) +'],',
@sql3=@sql3+ '''' +convert(varchar(50), 字段3)+''' as ['+ convert(varchar(50), 字段1) +'],'
from table
@sql1=left(@sql1,len(@sql1)-1) @sql2=left(@sql2,len(@sql2)-1) @sql3=left(@sql3,len(@sql3)-1)exec(@sql1 + ' union ' + @sql2 + ' union ' + @sql3)
end
日期:
Convert(varchar(10),getdate
,120)这样
思路方法来实现
其中varchar(10)定义是你要字段长度当然长度区别返回也会不如果我们只要日期部分设成10正好为日期长度如果设成19则正好可以读到时间部分
现在介绍最后面代
参数分别以代参数及例子效果介绍说明 参数
结果
100
05 8 2006 9:27PM
101
102
2006.05.08
103
104
08.05.2006
105
106
08 05 2006
107
05 08, 2006
108
21:30:51
109
05 8 2006 9:31:11
110
111
112
20060508
113
08 05 2006 21:31:59
114
21:33:06:503
120
2006-05-08 21:33:38
另类子查询:
语句:
select *,
(select top 1 grades from T_Assessment where companyid=c.companyid order by AssessmentID desc) as pgrades,
(select (sum(Score) + 60) from T_Honesty where AuditDate>=(select top 1 AssessmentDate from T_Assessment where companyid=c.companyid order by AssessmentID desc) and AuditDate<=getdate
and companyid=c.companyid and Status=2) as Score from t_company c inner join T_USER u _disibledevent=>
最新评论