为什么不作一个view(视图),然后从view里面查询,然后填充到dataset里
解决方案 »
- GridView列长~
- DreamWear CS5中使用鼠标经过图像工具!
- ***************一个疑问***************
- ASP.NET 页面传参问题
- 怎么ASP.NET的Table很多属性都用不了的?
- 一个类似日志功能的设计问题?
- 在vs.net中设计类型化数据集,如何定义一个字段为Guid类型?
- 怎么学习ASP.NET呢?
- 如何从showModalDialog的方式中传值?
- c#TreeView关于节点带有链接地址的,使用TreeView1_TreeNodeExpande,点击节点文字不能实现展开当前子节点和收缩其兄弟节点的问题
- 继续提问: .net中控件为什么不能拖动?
- 怎样得到postback是由哪个控件的哪个事件引起的?
select a.c1 a.c2 b.c2 b.c1 from table1 a inner join table2 b on ..where...
drop table [dbo].[Course]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mark]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Mark]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student]
GO
CREATE TABLE [dbo].[Student] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StudentID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[StudentName] [char] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into Student values('1','张三')
insert into Student values('2','李四')
insert into Student values('3','王五')CREATE TABLE [dbo].[Course] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CourseID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CourseName] [char] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into Course values('1','语文')
insert into Course values('2','数学')
insert into Course values('3','英语')GOCREATE TABLE [dbo].[Mark] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StudentID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CourseID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Mark] [int] NULL
) ON [PRIMARY]
GO
insert into Mark values('1','1',10)
insert into Mark values('1','2',20)
insert into Mark values('1','3',30)
insert into Mark values('2','1',40)
insert into Mark values('2','2',50)
insert into Mark values('2','3',60)
insert into Mark values('3','1',70)
insert into Mark values('3','2',80)
insert into Mark values('3','3',90)goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[viewMark]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[viewMark]
GOcreate view viewMark as
select a.StudentName,c.CourseName,b.Mark from student a
left join b on a.StudentID=b.StudentID
left join Course c on b.CourseID=c.CourseIDgo
declare @sql varchar(8000)
set @sql = 'select StudentName'
select @sql = @sql + ',sum(case CourseName when '''+CourseName+''' then ISNULL(Mark,0) else 0 end) ['+CourseName+']'
from (select distinct CourseName from viewMark) as a
select @sql = @sql+' from viewMark group by StudentName'
exec(@sql)