cube、rollup及exec的用法实例
- select sdept 系部,sno 学号,max(sage) 最大年龄,
- grouping(sno) s1/**goruping是一个聚合函数,其产生一个附加的列,当用cube或rollup运算符添加行时,附加的列输出值为1,
- 当所添加的行不是由cube或rollup产生时,附加列值为0*/
- from student
- group by sno,sdept
- with cube
- select sdept 系部,sno 学号,max(sage) 最大年龄,
- grouping(sno) s1
- from student
- group by sno,sdept
- with rollup
- select sno 学号,sdept 系部,avg(sage) 平均年龄,
- grouping(sno) s1
- from student
- where sgentle='男'
- group by sdept,sno
- with cube
- select sno 学号,sdept 系部,avg(sage) 平均年龄,
- grouping(sno) s1
- from student
- where sgentle='男'
- group by sdept,sno
- with rollup
- select sdept,avg(sage) from student where sgentle='男' group by sdept having avg(sage)>20
- order by avg(sage) desc
- select sno 学号,sname 姓名,sage 年龄,sgentle 性别,sdept 所属系部 from student where sage>=19 and((sdept='计算机' and sgentle='男') or (sdept='外语系' and sgentle='女')) order by sdept,sage desc
- select sdept 系部名称,avg(sage) 平均年龄 into studen from student where sgentle='男' group by sdept having avg(sage)>=20
- select * from class where ms like '%0!%' escape '!'
- EXEC xp_cmdshell 'bcp stored.dbo.student out c:\test1.txt -c -T'--这个是直接导出一张表
- exec xp_cmdshell 'bcp "select * from stored..student" queryout c:\data.txt -c -T'--这个是将查询到的结果导出
- alter table studenttest add constraint stu_snoa primary key clustered(sno)--这是创建主键的方法
- alter table studenttest drop constraint stu_snoa--这是删除主键的方法，这里其实就只是删除了一个约束，约束一删除主键就没有了
本文转自sucre03 51CTO博客，原文链接：http://blog.51cto.com/sucre/420281，如需转载请自行联系原作者
