表變量的使用:
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)
set @sql1='select '
Set @sql2='select '
set @sql3='select '
select @[email protected]+ '''' +convert(varchar(50), 字段1)+ ''' as ['+ convert(varchar(50), 字段1) +'],',
@[email protected]+ '''' +convert(varchar(50),×字段2)+''' as['+ convert(varchar(50), 字段1) +'],',
@[email protected]+ '''' +convert(varchar(50), 字段3)+''' as ['+ convert(varchar(50), 字段1) +'],'
from table
set @sql1=left(@sql1,len(@sql1)-1)
set @sql2=left(@sql2,len(@sql2)-1)
set @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 | 05/08/2006 |
102 | 2006.05.08 |
103 | 08/05/2006 |
104 | 08.05.2006 |
105 | 08-05-2006 |
106 | 08 05 2006 |
107 | 05 08, 2006 |
108 | 21:30:51 |
109 | 05 8 2006 9:31:11 |
110 | 05-08-2006 |
111 | 2006/05/08 |
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 on c.User_ID = u.User_ID where u.UserType=0