sql怎么做数据透视表
举例说明怎么用SQL做数据透视表： [表一 学生信息表] [表二 课程信息表] [表三 成绩信息表] 生成基础数据的代码 CREATE TABLE [dbo].[StuInfo] ( [StuID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL , [StuName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE TABLE [dbo].[CourseInfo] ( [CourseID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL , [CourseName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE TABLE [dbo].[ScoreInfo] ( [StuID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL , [CourseID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL , [Score] [float] NULL ) ON [PRIMARY]GO insert stuInfo(StuID,StuName) values ('090301','张三')insert stuInfo(StuID,StuName) values ('090302','李四')insert stuInfo(StuID,StuName) values ('090303','王五') insert CourseInfo(CourseID,CourseName) values ('201001','数学')insert CourseInfo(CourseID,CourseName) values ('201002','C语言') insert ScoreInfo(StuID,CourseID,Score) values ('090301','201001',90)insert ScoreInfo(StuID,CourseID,Score) values ('090301','201002',100)insert ScoreInfo(StuID,CourseID,Score) values ('090302','201001',95)insert ScoreInfo(StuID,CourseID,Score) values ('090302','201002',98)insert ScoreInfo(StuID,CourseID,Score) values ('090303','201001',88) 生成数据透视表 (1)单表 ScoreInfo declare @sql nvarchar(500)set @sql = 'SELECT [StuID], 'select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename(CourseID)+',' from ScoreInfo group by CourseIDselect @sql = left(@sql,len(@sql)-1)select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'select @sqlexec(@sql) 执行效果图： (2)双表 StuInfo,ScoreInfodeclare @sql nvarchar(500)set @sql = 'SELECT (select stuName from StuInfo where StuInfo.stuID=scoreInfo.stuID), 'select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename(CourseID)+',' from ScoreInfo group by CourseIDselect @sql = left(@sql,len(@sql)-1)select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'select @sqlexec(@sql)执行效果图： (3)多表 StuInfo,ScoreInfo,ScoreInfo declare @sql nvarchar(500)set @sql = 'SELECT (select stuName from StuInfo where StuInfo.stuID=scoreInfo.stuID) as stuName, 'select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename((select courseName from courseInfo where courseInfo.CourseID=ScoreInfo.CourseID),'''')+',' from ScoreInfo group by CourseIDselect @sql = left(@sql,len(@sql)-1)select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'select @sqlexec(@sql) 执行效果图：
我的网站老是被sql注入攻击怎么办？
<%
'----实现get请求的注入的拦截-----
dim sql_injdata,Sql_Inj,SQL_Get,Sql_DATA
SQL_injdata = "'|and|exec|insert|select|delete|update|count|*|%|chr|mid|master|truncate|char|declare"
SQL_inj = split(SQL_Injdata,"|")
If Request.QueryString"" Then
For Each SQL_Get In Request.QueryString
For SQL_Data=0 To Ubound(SQL_inj)
if instr(Request.QueryString(SQL_Get),Sql_Inj(Sql_DATA))>0 Then
Response.Write "alert('安全警告：请勿非法注入，你的IP已被记录，已发送管理员！');history.back(-1)"
Response.end
end if
next
Next
End If
'----我们还要过滤post请求，所以我们还得继续考虑request.form,这个也是以数组形式存在的，我们只需要再进一次循环判断即可-----
If Request.Form"" Then
For Each Sql_Post In Request.Form
For SQL_Data=0 To Ubound(SQL_inj)
if instr(Request.Form(Sql_Post),Sql_Inj(Sql_DATA))>0 Then
Response.Write "alert('安全警告：请勿非法注入，你的IP已被记录，已发送管理员！');history.back(-1)"
Response.end
end if
next
next
end if
%>
把这段代码，加在你的连接数据库文件时太OK啦，
还有你看看你网站的服务器安全吗？如果服务器别的网站有漏洞还是一样可以加，我也就知道这么多啦，希望能帮上你的忙！！