周一新干劲 - 6/9/14 - Sharepoint Report Creation - Stuff(), Join, Case in SQL
来源:互联网 发布:淘宝客教网站程全集 编辑:程序博客网 时间:2024/05/21 11:12
新的一周,这周末精神保持的还很好,好好干吧!也就三周了!
上午活还挺多,要create一个新的report给graduation application,这个很好啊,造福于同学们,主要工作在SQL语句设计上,我要join几个table调用不同信息,最后把每个学生申请的专业,小专业和具体方向放到record的主表之中。有一步要把多个cell的内容连接放到一个里面,于是就得用到了for xml 这样的语句,还有stuff()string insertion。代码如下:
SELECT [id] as [ID], [studentID] as [Student ID], [firstName] as [First Name],[lastName] as [Last Name], [reqDate] as [Request Date], [email] as [Email],[gradTerm] as [Graduation Term],[honorsCollege] as [Honors College],(STUFF ((SELECT majorName+', ' FROM [GradApp_Degrees] where [GradApp_Degrees].studentID=Records.studentID for xml path ('')), 1, 0,' ') + STUFF ((SELECT minor+', ' FROM [GradApp_Minors] where [GradApp_Minors].studentID=Records.studentID for xml path ('')), 1, 0,' ')) as [Applied Minor] , STUFF ((SELECT specialization+', ' FROM [GradApp_Degrees] where [GradApp_Degrees].studentID=Records.studentID for xml path ('')), 1, 0,' ') as [Specialization] FROM [MyState_CLG].[dbo].[GradApp_Records] as Records
下午明白了不是要简单的把两个cell内容连起,是要有小专业的attach到大专业去,这样我就傻逼了,于是就琢磨了一个小时,开始想用case when then end来判断,后来发现stuff()method不能嵌套使用,于是就想到了另外加一个column,把三个table join之后select得到的内容,然后join来join去又花了半个小时,最后得出正确结果。
SELECT [id] as [ID], [studentID] as [Student ID], [firstName] as [First Name],[lastName] as [Last Name], [reqDate] as [Request Date], [email] as [Email],[gradTerm] as [Graduation Term],[honorsCollege] as [Honors College],STUFF ( (SELECT distinct Degree.majorName + '; 'FROM [MyState_CLG].[dbo].[GradApp_Degrees] Degree join [MyState_CLG].[dbo].[GradApp_Records] Recordson Degree.studentID=Records.studentID for xml path ('')), 1, 0,' ') as [Applied Major] , STUFF ( (SELECT distinct Degree.majorName + '('+Minors.minor+'); 'FROM [MyState_CLG].[dbo].[GradApp_Degrees] Degree join [MyState_CLG].[dbo].[GradApp_Minors] Minorson Degree.majorName= Minors.majorName and Minors.studentID = Degree.studentIDjoin [MyState_CLG].[dbo].[GradApp_Records] Recordson Degree.studentID=Records.studentID for xml path ('')), 1, 0,' ') as[Applied Minor] , STUFF ((SELECT distinct specialization+'; ' FROM [MyState_CLG].[dbo].[GradApp_Degrees] where [MyState_CLG].[dbo].[GradApp_Degrees].studentID=Records.studentID for xml path ('')), 1, 0,' ') as [Specialization] FROM [MyState_CLG].[dbo].[GradApp_Records] as Records
今下午insidestate的server看来是正在升级,于是干不了report的事了,从今天开始学校就开始用sharepoint 2013了!
SQL里面有时候报错说找不到object,是因为没有put full DB名字,可以两种方法,在一组服务器上邮件new query,或者在new query里面加上use webforms就可以了。
最后把一个mockup做了,一个印第安orientation的申请表,挺有意思。
0 0
- 周一新干劲 - 6/9/14 - Sharepoint Report Creation - Stuff(), Join, Case in SQL
- 周一,关于sql几个join的理解
- SQL practice :"Case when" & "join"
- sql STUFF
- sql STUFF
- sql stuff
- join operations in SQL
- Understanding basic of Report creation
- COALESCE and CASE in SQL
- sql join and in statement
- SharePoint Usage Report
- SQL中的Stuff()函数
- SQL中的Stuff()函数
- sql STUFF用法
- SQL 中的Stuff函数
- sql STUFF用法
- sql STUFF用法
- sql STUFF用法
- pdo
- html gb2312解析,求助
- 求百度收录
- NYOJ847 S + T 【预处理】
- FtpRequest
- 周一新干劲 - 6/9/14 - Sharepoint Report Creation - Stuff(), Join, Case in SQL
- ACM怀化学院交流赛总结之只有AC才有MM
- 第二周__随机生成一个n bit位的长整数
- 从CM刷机过程和原理分析Android系统结构
- Android中滑屏实现----手把手教你如何实现触摸滑屏以及Scroller类详解
- ARM-2440-Led
- sso
- WebRequest.Create
- 【leetcode】Linked List Cycle