sql创新语句.docx
- 文档编号:28486534
- 上传时间:2023-07-14
- 格式:DOCX
- 页数:33
- 大小:42.11KB
sql创新语句.docx
《sql创新语句.docx》由会员分享,可在线阅读,更多相关《sql创新语句.docx(33页珍藏版)》请在冰豆网上搜索。
sql创新语句
日志
上一篇:
一些有创意的SQL...
下一篇:
精妙SQL语句收集
|返回日志列表
创意SQL语句
∙分享
∙复制地址
日志地址:
请用Ctrl+C复制后贴给好友。
∙转播到微博
俺~2009年10月29日17:
40阅读
(1)评论(0)分类:
个人日记权限:
公开
∙字体:
大▼
o小
o中
o大
∙更多▼
o设置置顶
o权限设置
o推荐日志
o转为私密日志
∙删除
∙编辑
创意SQL语句
1.把某个字段重新生成序列(从1到n):
DECLARE@iint
Set@i=0
UpdateTable1Set@i=@i+1,Field1=@i
2.按成绩排名次
Update成绩表
Seta.名次=(
SelectCount(*)+1
From成绩表b
Wherea.总成绩 ) From成绩表a 3.查询外部数据库 Selecta.* FromOpenRowSet('Microsoft.Jet.OLEDB.4.0','c: \test.mdb';'admin';'',Table1)a 4.查询Excel文件 Select* FromOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c: \test.xls";UserID=Admin;Password=;Extendedproperties=Excel8.0')...Sheet1$ 5.在查询中指定排序规则 Select*FromTable1orderByField1COLLATEChinese_PRC_BIN 为什么要指定排序规则呢? 参见: 例,检查数据库中的Pub_Users表中是否存在指定的用户: SelectCount(*)FromPub_UsersWhere[UserName]='admin'And[PassWord]='aaa'COLLATEChinese_PRC_BIN 默认比较是不区分大小写的,如果不加COLLATEChinese_PRC_BIN,那么密码aaa与AAA是等效的,这当然与实际不符.注意的是,每个条件都要指定排序规则,上例中用户名就不区分大小写. 6.OrderBy的一个小技巧 orderBy可以指定列序而不用指定列名,在下面的例子里说明它的用处(注意,第三列未指定别名) Selecta.ID,a.Name,(SelectCount(*)FromTableBbWherea.ID=b.PID)FromTableAaorderBy3 2? MicrosoftSQLServer2005ProductGuide 2007-04-22T11: 13: 05+08: 00|rxl 推荐给学生阅读的文章,较为详细的说明了MicrosoftSQLServer2005方方面面,具有很好的学习价值,对于了解和学习MicrosoftSQLServer2005有很好的借鉴意义。 前期提供给盛浩峰和张飞进行阅读,希望他们拿出一份翻译稿件来,但是一直没有收到成效。 但是他们做了一些标注,还是直接借鉴的,尽管没有看完。 盛浩峰阅读批示后的文章 张飞阅读批示后的文章 3? sqlserver中【农历】计算方法 2007-02-02T08: 00: 00+08: 00|rxl 最近几天,一时兴起,开始研究农历,趁着元旦放假,把它给做出来了! 拿给大家看看,我自己测试觉得没有问题,请大家看看! 1、建一表,放初始化资料 因为农历的日期,是由天文学家推算出来的,到现在只有到2049年的,以后的有了还可以加入! CreateTABLESolarData ( yearIdintnotnull, datachar(7)notnull, dataIntintnotnull ) --插入数据 InsertINTO SolarDataSelect1900,'0x04bd8',19416UNIONALLSelect1901,'0x04ae0',19168 UNIONALLSelect1902,'0x0a570',42352UNIONALLSelect1903,'0x054d5',21717 UNIONALLSelect1904,'0x0d260',53856UNIONALLSelect1905,'0x0d950',55632 UNIONALLSelect1906,'0x16554',91476UNIONALLSelect1907,'0x056a0',22176 UNIONALLSelect1908,'0x09ad0',39632UNIONALLSelect1909,'0x055d2',21970 UNIONALLSelect1910,'0x04ae0',19168UNIONALLSelect1911,'0x0a5b6',42422 UNIONALLSelect1912,'0x0a4d0',42192UNIONALLSelect1913,'0x0d250',53840 UNIONALLSelect1914,'0x1d255',119381UNIONALLSelect1915,'0x0b540',46400 UNIONALLSelect1916,'0x0d6a0',54944UNIONALLSelect1917,'0x0ada2',44450 UNIONALLSelect1918,'0x095b0',38320UNIONALLSelect1919,'0x14977',84343 UNIONALLSelect1920,'0x04970',18800UNIONALLSelect1921,'0x0a4b0',42160 UNIONALLSelect1922,'0x0b4b5',46261UNIONALLSelect1923,'0x06a50',27216 UNIONALLSelect1924,'0x06d40',27968UNIONALLSelect1925,'0x1ab54',109396 UNIONALLSelect1926,'0x02b60',11104UNIONALLSelect1927,'0x09570',38256 UNIONALLSelect1928,'0x052f2',21234UNIONALLSelect1929,'0x04970',18800 UNIONALLSelect1930,'0x06566',25958UNIONALLSelect1931,'0x0d4a0',54432 UNIONALLSelect1932,'0x0ea50',59984UNIONALLSelect1933,'0x06e95',28309 UNIONALLSelect1934,'0x05ad0',23248UNIONALLSelect1935,'0x02b60',11104 UNIONALLSelect1936,'0x186e3',100067UNIONALLSelect1937,'0x092e0',37600 UNIONALLSelect1938,'0x1c8d7',116951UNIONALLSelect1939,'0x0c950',51536 UNIONALLSelect1940,'0x0d4a0',54432UNIONALLSelect1941,'0x1d8a6',120998 UNIONALLSelect1942,'0x0b550',46416UNIONALLSelect1943,'0x056a0',22176 UNIONALLSelect1944,'0x1a5b4',107956UNIONALLSelect1945,'0x025d0',9680 UNIONALLSelect1946,'0x092d0',37584UNIONALLSelect1947,'0x0d2b2',53938 UNIONALLSelect1948,'0x0a950',43344UNIONALLSelect1949,'0x0b557',46423 UNIONALLSelect1950,'0x06ca0',27808UNIONALLSelect1951,'0x0b550',46416 UNIONALLSelect1952,'0x15355',86869UNIONALLSelect1953,'0x04da0',19872 UNIONALLSelect1954,'0x0a5d0',42448UNIONALLSelect1955,'0x14573',83315 UNIONALLSelect1956,'0x052d0',21200UNIONALLSelect1957,'0x0a9a8',43432 UNIONALLSelect1958,'0x0e950',59728UNIONALLSelect1959,'0x06aa0',27296 UNIONALLSelect1960,'0x0aea6',44710UNIONALLSelect1961,'0x0ab50',43856 UNIONALLSelect1962,'0x04b60',19296UNIONALLSelect1963,'0x0aae4',43748 UNIONALLSelect1964,'0x0a570',42352UNIONALLSelect1965,'0x05260',21088 UNIONALLSelect1966,'0x0f263',62051UNIONALLSelect1967,'0x0d950',55632 UNIONALLSelect1968,'0x05b57',23383UNIONALLSelect1969,'0x056a0',22176 UNIONALLSelect1970,'0x096d0',38608UNIONALLSelect1971,'0x04dd5',19925 UNIONALLSelect1972,'0x04ad0',19152UNIONALLSelect1973,'0x0a4d0',42192 UNIONALLSelect1974,'0x0d4d4',54484UNIONALLSelect1975,'0x0d250',53840 UNIONALLSelect1976,'0x0d558',54616UNIONALLSelect1977,'0x0b540',46400 UNIONALLSelect1978,'0x0b5a0',46496UNIONALLSelect1979,'0x195a6',103846 UNIONALLSelect1980,'0x095b0',38320UNIONALLSelect1981,'0x049b0',18864 UNIONALLSelect1982,'0x0a974',43380UNIONALLSelect1983,'0x0a4b0',42160 UNIONALLSelect1984,'0x0b27a',45690UNIONALLSelect1985,'0x06a50',27216 UNIONALLSelect1986,'0x06d40',27968UNIONALLSelect1987,'0x0af46',44870 UNIONALLSelect1988,'0x0ab60',43872UNIONALLSelect1989,'0x09570',38256 UNIONALLSelect1990,'0x04af5',19189UNIONALLSelect1991,'0x04970',18800 UNIONALLSelect1992,'0x064b0',25776UNIONALLSelect1993,'0x074a3',29859 UNIONALLSelect1994,'0x0ea50',59984UNIONALLSelect1995,'0x06b58',27480 UNIONALLSelect1996,'0x055c0',21952UNIONALLSelect1997,'0x0ab60',43872 UNIONALLSelect1998,'0x096d5',38613UNIONALLSelect1999,'0x092e0',37600 UNIONALLSelect2000,'0x0c960',51552UNIONALLSelect2001,'0x0d954',55636 UNIONALLSelect2002,'0x0d4a0',54432UNIONALLSelect2003,'0x0da50',55888 UNIONALLSelect2004,'0x07552',30034UNIONALLSelect2005,'0x056a0',22176 UNIONALLSelect2006,'0x0abb7',43959UNIONALLSelect2007,'0x025d0',9680 UNIONALLSelect2008,'0x092d0',37584UNIONALLSelect2009,'0x0cab5',51893 UNIONALLSelect2010,'0x0a950',43344UNIONALLSelect2011,'0x0b4a0',46240 UNIONALLSelect2012,'0x0baa4',47780UNIONALLSelect2013,'0x0ad50',44368 UNIONALLSelect2014,'0x055d9',21977UNIONALLSelect2015,'0x04ba0',19360 UNIONALLSelect2016,'0x0a5b0',42416UNIONALLSelect2017,'0x15176',86390 UNIONALLSelect2018,'0x052b0',21168UNIONALLSelect2019,'0x0a930',43312 UNIONALLSelect2020,'0x07954',31060UNIONALLSelect2021,'0x06aa0',27296 UNIONALLSelect2022,'0x0ad50',44368UNIONALLSelect2023,'0x05b52',23378 UNIONALLSelect2024,'0x04b60',19296UNIONALLSelect2025,'0x0a6e6',42726 UNIONALLSelect2026,'0x0a4e0',42208UNIONALLSelect2027,'0x0d260',53856 UNIONALLSelect2028,'0x0ea65',60005UNIONALLSelect2029,'0x0d530',54576 UNIONALLSelect2030,'0x05aa0',23200UNIONALLSelect2031,'0x076a3',30371 UNIONALLSelect2032,'0x096d0',38608UNIONALLSelect2033,'0x04bd7',19415 UNIONALLSelect2034,'0x04ad0',19152UNIONALLSelect2035,'0x0a4d0',42192 UNIONALLSelect2036,'0x1d0b6',118966UNIONALLSelect2037,'0x0d250',53840 UNIONALLSelect2038,'0x0d520',54560UNIONALLSelect2039,'0x0dd45',56645 UNIONALLSelect2040,'0x0b5a0',46496UNIONALLSelect2041,'0x056d0',22224 UNIONALLSelect2042,'0x055b2',21938UNIONALLSelect2043,'0x049b0',18864 UNIONALLSelect2044,'0x0a577',42359UNIONALLSelect2045,'0x0a4b0',42160 UNIONALLSelect2046,'0x0aa50',43600UNIONALLSelect2047,'0x1b255',111189 UNIONALLSelect2048,'0x06d20',27936UNIONALLSelect2049,'0x0ada0',44448 CreateFUNCTIONfn_GetLunar(@solarDayDATETIME) RETURNSvarchar(10)--datetime AS BEGIN DECLARE@solDataint DECLARE@offsetint DECLARE@iLunarint DECLARE@iINT DECLARE@jINT DECLARE@yDaysint DECLARE@mDaysint DECLARE@mLeapint DECLARE@mLeapNumint DECLARE@bLeapsmallint DECLARE@tempint DECLARE@YEARINT DECLARE@MONTHINT DECLARE@DAYINT DECLARE@OUTPUTDATEvarchar(10)--DATETIME --保证传进来的日期是不带时间 SET@solarDay=cast(@solarDayASchar(10)) SET@offset=CAST(@solarDay-'1900-01-30'ASINT) --确定农历年开始 SET@i=1900 --SET@offset=@solData WHILE@i<2050AND@offset>0 BEGIN SET@yDays=348 SET@mLeapNum=0 Select@iLunar=dataIntFROMSolarDataWhereyearId=@i --传回农历年的总天数 SET@j=32768 WHILE@j>8 BEGIN IF@iLunar&@j>0 SET@yDays=@yDays+1 SET@j=@j/2 END --传回农历年闰哪个月1-12,没闰传回0 SET@mLeap=@iLunar&15 --传回农历年闰月的天数,加在年的总天数上 IF@mLeap>0 BEGIN IF@iLunar&65536>0 SET@mLeapNum=30 ELSE SET@mLeapNum=29 SET@yDays=@yDays+@mLeapNum END SET@offset=@offset-@yDays SET@i=@i+1 END IF@offset<=0 BEGIN SET@offset=@offset+@yDays SET@i=@i-1 END --确定农历年结束 SET@YEAR=@i --确定农历月开始 SET@i=1 Select@iLunar=dataIntFROMSolarDataWhereyearId=@YEAR --判断那个月是润月 SET@mLeap=@iLunar&15 SET@bLeap=0 WHILE@i<13AND@offset>0 BEGIN --判断润月 SET@mDays=0 IF(@mLeap>0AND@i=(@mLeap+1)AND@bLeap=0) BEGIN--是润月 SET@i=@i-1 SET@bLeap=1 --传回农历年闰月的天数 IF@iLunar&65536>0 SET@mDays=30 ELSE SET@mDays=29 END ELSE --不是润月 BEGIN SET@j=1 SET@temp=65536 WHILE@j<=@i BEGIN SET@temp=@temp/2
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 创新 语句