MS SQL server 2005(SQL2005)在查询分析器里面备份 数据库的语句2008年05月12日 星期一 16:33(转)

--完整备份
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Full_20070908.bak'

--差异备份
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Diff_20070908.bak'
With Differential

--日志备份，默认截断日志
Backup Log NorthwindCS
To disk='G:\Backup\NorthwindCS_Log_20070908.bak'

--日志备份，不截断日志
Backup Log NorthwindCS
To disk='G:\Backup\NorthwindCS_Log_20070908.bak'
With No_Truncate

--截断日志不保留
Backup Log NorthwindCS
With No_Log

--或者
Backup Log NorthwindCS
With Truncate_Only
--截断之后日志文件不会变小
--有必要可以进行收缩

--文件备份
Exec Sp_Helpdb NorthwindCS --查看数据文件
Backup Database NorthwindCS
File='NorthwindCS'   --数据文件的逻辑名
To disk='G:\Backup\NorthwindCS_File_20070908.bak'

--文件组备份
Exec Sp_Helpdb NorthwindCS --查看数据文件
Backup Database NorthwindCS
FileGroup='Primary'   --数据文件的逻辑名
To disk='G:\Backup\NorthwindCS_FileGroup_20070908.bak'
With init

--分割备份到多个目标
--恢复的时候不允许丢失任何一个目标
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Full_1.bak'
     ,disk='G:\Backup\NorthwindCS_Full_2.bak'

--镜像备份
--每个目标都是相同的
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
Mirror
To disk='G:\Backup\NorthwindCS_Mirror_2.bak'
With Format --第一次做镜像备份的时候格式化目标

--镜像备份到本地和远程
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
Mirror
To disk='\\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak'
With Format

--每天生成一个备份文件
Declare @Path Nvarchar(2000)
Set @Path ='G:\Backup\NorthwindCS_Full_'
+Convert(Nvarchar,Getdate(),112)+'.bak'

Backup Database NorthwindCS
To disk=@Path

--从NoRecovery或者
--Standby模式恢复数据库为可用
Restore Database NorthwindCS_Bak
With Recovery

--查看目标备份中的备份集
Restore HeaderOnly
From Disk ='G:\Backup\NorthwindCS_Full_20070908.bak'

--查看目标备份的第一个备份集的信息
Restore FileListOnly
From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'
With File=1

--查看目标备份的卷标
Restore LabelOnly
From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'

--备份设置密码保护备份
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Full_20070908.bak'
With Password = '123',init

Restore Database NorthwindCS
From disk='G:\Backup\NorthwindCS_Full_20070908.bak'
With Password = '123'
 
-----------------------数据库自动备份----------------------

DECLARE @strPath NVARCHAR(200)

set @strPath = convert(NVARCHAR(19),getdate(),120)

set @strPath = REPLACE(@strPath, ':' , '.')

set @strPath = 'D:\temp\' + 'ehr_data'+@strPath + '.bak'

BACKUP DATABASE [ehr_data] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

以下是一些TSQL语句的介绍：
　　SELECT --从数据库表中检索数据行和列
　　INSERT --向数据库表添加新数据行
　　DELETE --从数据库表中删除数据行
　　UPDATE --更新数据库表中的数据
　　--数据定义
　　CREATE TABLE --创建一个数据库表
　　DROP TABLE --从数据库中删除表
　　ALTER TABLE --修改数据库表结构
　　CREATE VIEW --创建一个视图
　　DROP VIEW --从数据库中删除视图
　　CREATE INDEX --为数据库表创建一个索引
　　DROP INDEX --从数据库中删除索引
　　CREATE PROCEDURE --创建一个存储过程
　　DROP PROCEDURE --从数据库中删除存储过程
　　CREATE TRIGGER --创建一个触发器
　　DROP TRIGGER --从数据库中删除触发器
　　CREATE SCHEMA --向数据库添加一个新模式
　　DROP SCHEMA --从数据库中删除一个模式
　　CREATE DOMAIN --创建一个数据值域
　　ALTER DOMAIN --改变域定义
　　DROP DOMAIN --从数据库中删除一个域
　　--数据控制
　　GRANT --授予用户访问权限
　　DENY --拒绝用户访问
　　REVOKE --解除用户访问权限
　　--事务控制
　　COMMIT --结束当前事务
　　ROLLBACK --中止当前事务
　　SET TRANSACTION --定义当前事务数据访问特征
　　--程序化SQL
　　DECLARE --为查询设定游标
　　EXPLAN --为查询描述数据访问计划
　　OPEN --检索查询结果打开一个游标
　　FETCH --检索一行查询结果
　　CLOSE --关闭游标
　　PREPARE --为动态执行准备SQL 语句
　　EXECUTE --动态地执行SQL 语句
　　DESCRIBE --描述准备好的查询
　　---局部变量
　　declare @id char(10)
　　--set @id = '10010001'
　　select @id = '10010001'
　　---全局变量
　　---必须以@@开头
　　--IF ELSE
　　declare @x int @y int @z int
　　select @x = 1 @y = 2 @z=3
　　if @x > @y
　　print 'x > y' --打印字符串'x > y'
　　else if @y > @z
　　print 'y > z'
　　else print 'z > y'
　　--CASE
　　use pangu
　　update employee
　　set e_wage =
　　case
　　when job_level = ’1’ then e_wage*1.08
　　when job_level = ’2’ then e_wage*1.07
　　when job_level = ’3’ then e_wage*1.06
　　else e_wage*1.05
　　end
　　--WHILE CONTINUE BREAK
　　declare @x int @y int @c int
　　select @x = 1 @y=1
　　while @x < 3
　　begin
　　print @x --打印变量x 的值
　　while @y < 3
　　begin
　　select @c = 100*@x + @y
　　print @c --打印变量c 的值
　　select @y = @y + 1
　　end
　　select @x = @x + 1
　　select @y = 1
　　end
　　--WAITFOR
　　--例 等待1 小时2 分零3 秒后才执行SELECT 语句
　　waitfor delay ’01:02:03’
　　select * from employee
　　--例 等到晚上11 点零8 分后才执行SELECT 语句
　　waitfor time ’23:08:00’
　　select * from employee
　　***SELECT***
　　select *(列名) from table_name(表名) where column_name operator value
　　ex:(宿主)
　　select * from stock_information where stockid = str(nid)
　　stockname = 'str_name'
　　stockname like '% find this %'
　　stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
　　stockname like '[^F-M]%' --------- (^排除指定范围)
　　--------- 只能在使用like关键字的where子句中使用通配符)
　　or stockpath = 'stock_path'
　　or stocknumber < 1000
　　and stockindex = 24
　　not stock*** = 'man'
　　stocknumber between 20 and 100
　　stocknumber in(10,20,30)
　　order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
　　order by 1,2 --------- by列号
　　stockname = (select stockname from stock_information where stockid = 4)
　　--------- 子查询
　　--------- 除非能确保内层select只返回一个行的值，
　　--------- 否则应在外层where子句中用一个in限定符
　　select distinct column_name form. table_name --------- distinct指定检索独有的列值，不重复
　　select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
　　select stockname , "stocknumber" = count(*) from table_name group by stockname
　　--------- group by 将表按行分组,指定列中有相同的值
　　having count(*) = 2 --------- having选定指定的组
　　select *
　　from table1, table2
　　where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
　　table1.id =* table2.id -------- 右外部连接
　　select stockname from table1
　　union [all] ----- union合并查询结果集，all-保留重复行
　　select stockname from table2
　　***insert***
　　insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
　　value (select Stockname , Stocknumber from Stock_table2)---value为select语句
　　***update***
　　update table_name set Stockname = "xxx" [where Stockid = 3]
　　Stockname = default
　　Stockname = null
　　Stocknumber = Stockname + 4
　　***delete***
　　delete from table_name where Stockid = 3
　　truncate table_name ----------- 删除表中所有行，仍保持表的完整性
　　drop table table_name --------------- 完全删除表
　　***alter table*** --- 修改数据库表结构
　　alter table database.owner.table_name add column_name char(2) null .....
　　sp_help table_name ---- 显示表已有特征
　　create table table_name (name char(20), age smallint, lname varchar(30))
　　insert into table_name select ......... ----- 实现删除列的方法（创建新表）
　　alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
　　***function(/*常用函数*/)***
　　----统计函数----
　　AVG --求平均值
　　COUNT --统计数目
　　MAX --求最大值
　　MIN --求最小值
　　SUM --求和
　　--AVG
　　use pangu
　　select avg(e_wage) as dept_avgWage
　　from employee
　　group by dept_id
　　--MAX
　　--求工资最高的员工姓名
　　use pangu
　　select e_name
　　from employee
　　where e_wage =
　　(select max(e_wage)
　　from employee)
　　--STDEV()
　　--STDEV()函数返回表达式中所有数据的标准差
　　--STDEVP()
　　--STDEVP()函数返回总体标准差
　　--VAR()
　　--VAR()函数返回表达式中所有值的统计变异数
　　--VARP()
　　--VARP()函数返回总体变异数
　　----算术函数----
　　/***三角函数***/
　　SIN(float_expression) --返回以弧度表示的角的正弦
　　COS(float_expression) --返回以弧度表示的角的余弦
　　TAN(float_expression) --返回以弧度表示的角的正切
　　COT(float_expression) --返回以弧度表示的角的余切
　　/***反三角函数***/
　　ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
　　ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
　　ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
　　ATAN2(float_expression1,float_expression2)
　　--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
　　DEGREES(numeric_expression)
　　--把弧度转换为角度返回与表达式相同的数据类型可为
　　--INTEGER/MONEY/REAL/FLOAT 类型
　　RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
　　--INTEGER/MONEY/REAL/FLOAT 类型
　　EXP(float_expression) --返回表达式的指数值
　　LOG(float_expression) --返回表达式的自然对数值
　　LOG10(float_expression)--返回表达式的以10 为底的对数值
　　SQRT(float_expression) --返回表达式的平方根
　　/***取近似值函数***/
　　CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为
　　--INTEGER/MONEY/REAL/FLOAT 类型
　　FLOOR(numeric_expression) --返回<=表达式的最小整数返回的数据类型与表达式相同可为
　　--INTEGER/MONEY/REAL/FLOAT 类型
　　ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据
　　--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
　　ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为
　　--INTEGER/MONEY/REAL/FLOAT 类型
　　SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
　　--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
　　PI() --返回值为π 即3.1415926535897936
　　RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数
　　----字符串函数----
　　ASCII() --函数返回字符表达式最左端字符的ASCII 码值
　　CHAR() --函数用于将ASCII 码转换为字符
　　--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
　　LOWER() --函数把字符串全部转换为小写
　　UPPER() --函数把字符串全部转换为大写
　　STR() --函数把数值型数据转换为字符型数据
　　LTRIM() --函数把字符串头部的空格去掉
　　RTRIM() --函数把字符串尾部的空格去掉
　　LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
　　CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
　　SOUNDEX() --函数返回一个四位字符码
　　--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
　　DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
　　--0 两个SOUNDEX 函数返回值的第一个字符不同
　　--1 两个SOUNDEX 函数返回值的第一个字符相同
　　--2 两个SOUNDEX 函数返回值的第一二个字符相同
　　--3 两个SOUNDEX 函数返回值的第一二三个字符相同
　　--4 两个SOUNDEX 函数返回值完全相同
　　QUOTENAME() --函数返回被特定字符括起来的字符串
　　/*
　　例如:quotename('aaa','{')返回的结果就是 {aaa};
　　如果用户没有自定义字符,则sqlserver默认的字符是'[',例如quotename('aaa'),返回的结果就是[aaa]。
　　*/
　　REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
　　/*select replicate('abc', 3) replicate( 'abc', -2)
　　运行结果如下
　　----------- -----------
　　abcabcabc NULL*/
　　REVERSE() --函数将指定的字符串的字符排列顺序颠倒
　　REPLACE() --函数返回被替换了指定子串的字符串
　　/*select replace('abc123g', '123', 'def')
　　运行结果如下
　　----------- -----------
　　abcdefg*/
　　SPACE() --函数返回一个有指定长度的空白字符串
　　STUFF() --函数用另一子串替换字符串指定位置长度的子串
　　----数据类型转换函数----
　　CAST() 函数语法如下
　　CAST() (<expression> AS <data_ type>[ length ])
　　CONVERT() 函数语法如下
　　CONVERT() (<data_ type>[ length ], <expression> [, style])
　　select cast(100+99 as char) convert(varchar(12), getdate())
　　运行结果如下
　　------------------------------ ------------
　　199 Jan 15 2000
　　----日期函数----
　　DAY() --函数返回date_expression 中的日期值
　　MONTH() --函数返回date_expression 中的月份值
　　YEAR() --函数返回date_expression 中的年份值
　　DATEADD(<datepart> ,<number> ,<date>)
　　--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
　　DATEDIFF(<datepart> ,<number> ,<date>)
　　--函数返回两个指定日期在datepart 方面的不同之处
　　DATENAME(<datepart> , <date>) --函数以字符串的形式返回日期的指定部分
　　DATEPART(<datepart> , <date>) --函数以整数值的形式返回日期的指定部分
　　GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间
　　----系统函数----
　　APP_NAME() --函数返回当前执行的应用程序的名称
　　COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
　　COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
　　COL_NAME(
　　, <column_id>) --函数返回表中指定字段的名称即列名
　　DATALENGTH() --函数返回数据表达式的数据的实际长度
　　DB_ID(['database_name']) --函数返回数据库的编号
　　DB_NAME(database_id) --函数返回数据库的名称
　　HOST_ID() --函数返回服务器端计算机的名称
　　HOST_NAME() --函数返回服务器端计算机的名称
　　IDENTITY(<data_type>[, seed increment]) [AS column_name])
　　--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
　　/*select identity(int, 1, 1) as column_name
　　into newtable
　　from oldtable*/
　　ISDATE() --函数判断所给定的表达式是否为合理日期
　　ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换
　　ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
　　NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
　　NULLIF(<expression1>, <expression2>)
　　--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
　　sql中的保留字
　　action add aggregate all
　　alter after and as
　　asc avg avg_row_length auto_increment
　　between bigint bit binary
　　blob bool both by
　　cascade case char character
　　change check checksum column
　　columns comment constraint create
　　cross current_date current_time current_timestamp
　　data database databases date
　　datetime day day_hour day_minute
　　day_second dayofmonth dayofweek dayofyear
　　dec decimal default delayed
　　delay_key_write delete desc describe
　　distinct distinctrow double drop
　　end else escape escaped
　　enclosed enum explain exists
　　fields file first float
　　float4 float8 flush foreign
　　from for full function
　　global grant grants group
　　having heap high_priority hour
　　hour_minute hour_second hosts identified
　　ignore in index infile
　　inner insert insert_id int
　　integer interval int1 int2
　　int3 int4 int8 into
　　if is isam join
　　key keys kill last_insert_id
　　leading left length like
　　lines limit load local
　　lock logs long longblob
　　longtext low_priority max max_rows
　　match mediumblob mediumtext mediumint
　　middleint min_rows minute minute_second
　　modify month monthname myisam
　　natural numeric no not
　　null on optimize option
　　optionally or order outer
　　outfile pack_keys partial password
　　precision primary procedure process
　　processlist privileges read real
　　references reload regexp rename
　　replace restrict returns revoke
　　rlike row rows second
　　select set show shutdown
　　smallint soname sql_big_tables sql_big_selects
　　sql_low_priority_updates sql_log_off sql_log_update sql_select_limit
　　sql_small_result sql_big_result sql_warnings straight_join
　　starting status string table
　　tables temporary terminated text
　　then time timestamp tinyblob
　　tinytext tinyint trailing to
　　type use using unique
　　unlock unsigned update usage
　　values varchar variables varying
　　varbinary with write when
　　where year year_month zerofill 

设计一个存储过程，学到关于数据库的一些知识

IF Exists(Select * From sysobjects Where Name='SP_SumAgileAtt' And xType='P')
  Drop Proc SP_SumAgileAtt
go

Create Proc SP_SumAgileAtt (@Month Varchar(7),@DepIds Varchar(500)='',@EmpNo Varchar(30)='',@UserID Int)
  WITH ENCRYPTION
/*

*/
As
Begin
--SET NOCOUNT ON
Declare @EmpId Int,@i Int,@SelStr Varchar(8000),@BDay Varchar(5),@EDay Varchar(5),@SignOffSet Varchar(5),@BDate Varchar(10),@EDate Varchar(10),@aDate DateTime,@ResName Varchar(60),@FromStr Varchar(8000),@WhereStr Varchar(8000),@AndStr Varchar(200),@ExtStr Varchar(8000)

Create Table #EmpList(EmpID Int)
IF @UserID=1
  Insert #EmpList Select Employee_ID From Employee
ELSE
  Insert #EmpList Select a.Employee_ID From Employee As a,Position As b,Department As c,Users_Permission As d
    Where a.Position_ID=b.Position_ID And b.Department_ID=c.Department_ID And d.Department_Id=c.Department_ID And d.User_ID=@UserID
Create Table #Attendance  
(iID Int Identity(1,1),
 EmpId Int, 
 dDate datetime,
 Hours Decimal(7,2)
)
Select @SignOffSet=CHARINDEX(',',Option_Value) From Setoption Where Option_ename='calctimezone'
Select @BDay=Substring(Option_Value,1,@SignOffSet-1),@EDay=Substring(Option_Value,@SignOffSet+1,Len(Option_Value)-@SignOffSet) From Setoption Where Option_ename='calctimezone'
IF @BDay>@EDay
  Set @BDate=Convert(Varchar(10),Dateadd(mm,-1,cast(@Month+'-'+@BDay as datetime)),121)
ELSE
  Set @BDate=@Month+'-'+Replicate('0',2-Len(@BDay))+@BDay
IF @EDay<>'31'
  Set @EDate=@Month+'-'+@EDay
ELSE
  Set @EDate=Convert(Varchar(10),DateAdd(d,-1,DateAdd(m,1,@Month+'-01')),121)
 

--Select @BDay,@EDay,@BDate,@EDate
Delete a From #EmpList As a,Demission As b Where a.EmpId=b.Employee_Id And b.Date_Demission<@BDate And Employee_Status='L'
Insert #Attendance(EmpID,dDate,Hours) Select a.Employee_ID,a.dDate,a.Hours
  From Attendance_Agile_Result As a,#EmpList as b Where a.dDate>=@BDate And a.dDate<=@EDate And a.Employee_ID=b.EmpID
--- 部门 工号 姓名 上班时数
--  iID,Employee_ID,dDate,Hours,Attendance_Agile_Result
Set @SelStr='select r.EmpID,a.employee_no as [工号],a.name as [姓名],b.department_code as [部门代码],b.name as [部门名称],r.上班时数 as [上班时数]'
Set @FromStr='from employee as a,department as b,position as c'
Set @WhereStr='where a.position_id=c.position_id and b.department_id=c.department_id'

Set @SelStr=@SelStr
Set @FromStr=@FromStr+',(Select EmpID,Sum(Hours) As [上班时数] From #Attendance  Group By EmpID) As r'
Set @WhereStr=@WhereStr+' And a.Employee_Id*=r'+'.EmpID'

 

-- Print @SelStr
-- Print @FromStr
-- Print @WhereStr

Set @ExtStr=''
IF @DepIds<>''
  Set @ExtStr=' And b.department_id in('+@DepIds+')'
IF @EmpNo<>''
  Set @ExtStr=@ExtStr+' And (a.name like '''+@EmpNo+'%'' or a.employee_no='''+@EmpNo+''')'

Exec(@SelStr+' '+@FromStr+' '+@WhereStr+@ExtStr+' Order By b.Department_Code,a.Employee_No ')

Drop Table #EmpList
Drop Table #Attendance
End  --EndProc
go

--SP_SumAgileAtt '2009-03','','',1
--go

CHARINDEX
　　返回字符串中指定表达式的起始位置。

例一:
　　CustomName包含客户的First Name和Last Name，它们之间被一个空格隔开。我们用CHARINDX函数确定两个名字中间空格的位置。通过这个方法，我们可以分析ContactName列的空格位置，这样可以只显示这个列的last name部分。
　　select top 5 substring(ContactName,charindex(' ',ContactName)+1,len(ContactName)) as [Last Name] from customers
　　CHARINDEX函数找到First Name和Last Name之间的空格，所以SUBSTRING函数可以分开ContactName列，这样就只有Last Name被选出。在CHARINDEX函数返回的整数上加1，这样Last Name不是从空格开始。
　　例二:
　　计算Northwind.dbo.Customer表中Addresses字段中包含单词Road或者它的缩写Rd的记录数，选择语句类似这样：
　　Select count(*) from Northwind.dbo.Customers
　　Where CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address)> 0

--------------------------------------------------------------

DateAdd函数 返回
　　返回包含一个日期的 Variant (Date)，这一日期还加上了一段时间间隔。
　　语法
　　DateAdd(interval, number, date)
　　DateAdd 函数语法中有下列命名参数：
　　部分 描述
　　interval 必要。字符串表达式，是所要加上去的时间间隔。
　　number 必要。数值表达式，是要加上的时间间隔的数目。其数值可以为正数（得到未来的日期），也可以为负数（得到过去的日期）。
　　date 必要。Variant (Date) 或表示日期的文字，这一日期还加上了时间间隔。
　　设置
　　interval 参数具有以下设定值：
　　设置 描述
　　yyyy 年
　　q 季
　　m 月
　　y 一年的日数
　　d 日
　　w 一周的日数
　　ww 周
　　hh 时
　　n 分钟
　　s 秒
　　说明
　　可以使用 DateAdd 函数对日期加上或减去指定的时间间隔。例如，可以用 DateAdd 来计算距今天为三十天的日期；或者计算距现在为 45 分钟的时间。
　　为了对 date 加上“日”，可以使用“一年的日数” (“y”)，“日” (”d”) 或“一周的日数” (”w”)。
　　DateAdd 函数将不返回有效日期。在以下实例中将 1 月31 日加上一个月：
　　DateAdd(m, 1, 31-Jan-95)
　　上例中，DateAdd 返回 1995 年 2 月 28 日，而不是 1995 年 2 月 31 日。如果 date 是 1996 年 1 月 31 日，则由于 1996 年是闰年，返回值是 1996 年 2 月 29 日。

-------------------------

cast 函数 用法2008年04月17日 星期四 16:23示例
A. 同时使用 CAST 和 CONVERT
每个示例都将检索书名（这些图书的截止当前销售额的第一位数字为 3），并将这些图书的 ytd_sales 转换为 char(20)。

-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO

-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO

下面是任一查询的结果集：

Title ytd_sales
------------------------------ -----------
Cooking with Computers: Surrep 3876
Computer Phobic AND Non-Phobic 375
Emotional Security: A New Algo 3336
Onions, Leeks, and Garlic: Coo 375

(4 row(s) affected)

B. 使用带有算术运算符的 CAST
下面的示例通过将总的截止当前销售额 (ytd_sales) 与每本图书的价格 (price) 相除，进行单独列计算 (Copies)。在四舍五入到最接近的整数后，此结果将转换为 int 数据类型。

USE pubs
GO
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles
GO

下面是结果集：

Copies
------
205
324
6262
205
102
7440
NULL
383
205
NULL
17
187
16
204
418
18
1263
273

(18 row(s) affected)

C. 使用 CAST 进行串联
下面的示例使用 CAST 数据类型转换函数来串联非字符、非二进制表达式。

USE pubs
GO
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 10.00
GO

下面是结果集：

------------------
The price is 19.99
The price is 11.95
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99

(12 row(s) affected)

D. 使用 CAST 获得更多易读文本
下面的示例在选择列表中使用 CAST 将 title 列转换为 char(50) 列，这样结果将更加易读。

USE pubs
GO
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
GO

下面是结果集：

ytd_sales
-------------------------------------------------- ---------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095

(3 row(s) affected)

E. 使用带有 LIKE 子句的 CAST
下面的示例将 int 列（ytd_sales 列）转换为 char(20) 列，以便使用 LIKE 子句。

USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
GO

下面是结果集：

title ytd_sales
------------------------------------------------------------ -----------
Fifty Years in Buckingham Palace Kitchens 15096

(1 row(s) affected)

 

--------------------------

REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
　　/*select replicate('abc', 3) replicate( 'abc', -2)

-------------------------------------------------

 

查MYSQL 进程。

show  processlist

130 ：文件格式不正确。（还不是很清楚错误的状况）

         145 ：文件无法打开。

          1005：MYSQL创建表失败

1006：MYSQL创建数据库失败

     1007：MYSQL数据库已存在，创建数据库失败

1008：MYSQL数据库不存在，删除数据库失败

1009：MYSQL不能删除数据库文件导致删除数据库失败

1010：MYSQL不能删除数据目录导致删除数据库失败

          1011：MYSQL删除数据库文件失败

1012：MYSQL不能读取系统表中的记录

　　1016：文件无法打开，使用后台修复或者使用 phpmyadmin 进行修复。
                      Quote:
                   开始=>所有程序=>附件=>命令提示符
                       输入 mysql 所在硬盘盘符
                    cd mysql 所在目录
                       cd bin
                      输入 myisamchk -f D:usr/local/mysql/data/bbs/PW_members.MYI
                      ps : D:usr/local/mysql/data/bbs 是你论坛数据库的路径
                     -f 根据具体情况选择，一般也可以选择 -r
                    注意你的 系统C盘或放数据库的硬盘空间是否足够，一般小于 1G 很容易出现错误。
                      或用mysqlcheck命令进行修复。具体的方法：利用命令行进入mysql/bin目录，执行
                      mysqlcheck -o -r phpwind -uroot -p                                                     
                      其中phpwind是你数据库的名称，root是你的数据库用户名，然后会提示你输入密码。然后就会修
                      复你的数据库。

          1017：服务器非法关机，导致该文件损坏。

　　1020：MYSQL记录已被其他用户修改

1021：硬盘剩余空间不足，请加大硬盘可用空间

1022：MYSQL关键字重复，更改记录失败

1023：MYSQL关闭时发生错误

1024：MYSQL读文件错误

1025：MYSQL更改名字时发生错误

1026：MYSQL写文件错误

1030：可能是服务器不稳定。（具体原因不是很清楚）

          1032：MYSQL记录不存在

1036：MYSQL数据表是只读的，不能对它进行修改

1037：系统内存不足，请重启数据库或重启服务器

1038：MYSQL用于排序的内存不足，请增大排序缓冲区

1040：MYSQL已到达数据库的最大连接数，请加大数据库可用连接数
                    Quote:
                      在my.ini 修改max_connections=100为max_connections=1000或更大,重启mysql

1041：系统内存不足

1042：无效的主机名

1043：无效连接

1044：MYSQL当前用户没有访问数据库的权限

1045：MYSQL不能连接数据库，服务器、数据库名、用户名或密码错误
              　Quote:
                      方法:确保论坛data目录下的sql_config.php用户名与密码都正确.如果用户忘记了数据库的密码,
                      可以按如下方式进行密码的修改:
                       如果 MySQL 正在运行，首先停止。
                      启动 MySQL ：bin/safe_mysqld –skip-grant-tables &
                       就可以不需要密码就进入 MySQL 了。
                       然后就是
                    >use mysql
                    >update user set password=password(”new_pass”) where user=”root”;
                      >flush privileges;

          1046：没有选择数据库。

　　1048：MYSQL字段不能为空

1049：MYSQL数据库不存在

1050：MYSQL数据表已存在

1051：MYSQL数据表不存在

1054：MYSQL字段不存在，自行建立字段

　　1060：字段重复，导致无法插入这个字段。

       1062：字段值重复，入库失败
                      Quote:
                       1.如果出类似主码为”65535″的错误,可以查看相关表的自增字段,将字段值改在就可以
                       2.确保相关数据表中主码重复的字段是否存在,如果存在删除这条记录
                       3.备份数据库,修复相关表(注:这种情况比较常见,如pw_posts表,对表进行修复的时候不要忘记备份).

           1064：MySQL 不支持错误提示中的编码。

　　1065：MYSQL无效的SQL语句，SQL语句为空

　　1067：MySQL 版本为 5，不支持空的默认值。

　　1081：MYSQL不能建立Socket连接

1114：MYSQL数据表已满，不能容纳任何记录

1116：MYSQL打开的数据表太多

1129：MYSQL数据库出现异常，请重启数据库

1130：MYSQL连接数据库失败，没有连接数据库的权限

1133：MYSQL数据库用户不存在

　　1135：可能是内存不足够，请联系空间商解决。

　　1141：MYSQL当前用户无权访问数据库

1142：MYSQL当前用户无权访问数据表

1143：MYSQL当前用户无权访问数据表中的字段

1146：MYSQL数据表不存在或数据表缺失，请恢复备份数据

1147：MYSQL未定义用户对数据表的访问权限

1149：MYSQL语句语法错误

1158：网络错误，出现读错误，请检查网络连接状况

1159：网络错误，读超时，请检查网络连接状况

1160：网络错误，出现写错误，请检查网络连接状况

1161：网络错误，写超时，请检查网络连接状况

1062：MYSQL字段值重复，入库失败

1169：MYSQL字段值重复，更新记录失败

1177：MYSQL打开数据表失败

1180：MYSQL提交事务失败

1181：MYSQL回滚事务失败

1203：MYSQL当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或
                       重启数据库

1205：MYSQL加锁超时

1211：MYSQL当前用户没有创建用户的权限

1216：MYSQL外键约束检查失败，更新子表记录失败

1217：MYSQL外键约束检查失败，删除或修改主表记录失败

1226：MYSQL当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器

1227：MYSQL权限不足，您无权进行此操作

1235：MySQL版本过低，不具有本功能

        1250：客户端不支持服务器要求的认证协议，请考虑升级客户端。

          1251：Client 不能支持 authentication protocol 的要求
                       Client does not support authentication protocol requested by server; consider upgrading MySQL client
                      Quote:
                      方法1：
                                   mysql> SET PASSWORD FOR
                                         -> ‘ some_user ‘@’ some_host ‘ = OLD_PASSWORD(’ newpwd ‘);
                    结合我们的实际情况,在 MySQL Command Line Client 下运行:
                    set password for root@localhost = old_password(’123456′);

                       方法2：
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(’newpwd’)
    　　　　　　　　-> WHERE Host = ’some_host’ AND User = ’some_user’;
mysql> FLUSH PRIVILEGES;
                           <上面的部分请按自己实际情况修改。>

        1267：不合法的混合字符集。

        2002：服务器端口不对，请咨询空间商正确的端口。

        2003：MySQL 服务没有启动，请启动该服务。

         2008：MySQL client ran out of memory
                    错误指向了MySQL客户mysql。这个错误的原因很简单，客户没有足够的内存存储全部结果。

         2013：远程连接数据库是有时会有这个问题，MySQL 服务器在执行一条 SQL 语句的时候
                    失去了连接造成的。

        10048：最大连接数等问题
　 Quote:
                        建议在my.ini文件中修改最大连接数，
                        把 mysql_connect() 方法都改成了 mysql_pconnect() 方法.
                        要修改mysql_pconnect()，可以在论坛的data目录的sql_config.php中
                       $pconnect = 0; //是否持久连接
                        修改成$pconnect = 1;
                        开启防刷新,严禁刷新太快.

         10055：没有缓存空间可利用
                         Quote:
                        查看下你的C盘空间是否已经满,清除一些没有用的文件.
                        可以在后台的”论坛核心设置”,”核心功能设置”里”进程优化”开启,”GZIP 压缩输出”关闭.
                        查找了一下10055（没有缓存空间可利用）出错的原因，分析了my.ini的配制文件，
                        在my.ini中如下：
                         default-storage-engine=INNODB
                         innodb_additional_mem_pool_size=2M
                          innodb_flush_log_at_trx_commit=1
                          innodb_log_buffer_size=1M
                         innodb_buffer_pool_size=10M
                         innodb_log_file_size=10M
                         innodb_thread_concurrency=8
                        觉得可以把innodb_buffer_pool_size=10M　加大如100M或是1000M
                         以上是对mysql5的

                          如果是mysql4可以在my.ini中增加如下：
                          #innodb_data_file_path = ibdata1：2000M;ibdata2：2000M
                         #innodb_data_home_dir = c：ibdata
                         #innodb_log_group_home_dir = c：iblogs
                         #innodb_log_arch_dir = c：iblogs
                         #set-variable = innodb_mirrored_log_groups=1
                          #set-variable = innodb_log_files_in_group=3
                          #set-variable = innodb_log_file_size=5M
                          #set-variable = innodb_log_buffer_size=8M
                          #innodb_flush_log_at_trx_commit=1
                          #innodb_log_archive=0
                          #set-variable = innodb_buffer_pool_size=16M
                          #set-variable = innodb_additional_mem_pool_size=2M
                          #set-variable = innodb_file_io_threads=4
                          #set-variable = innodb_lock_wait_timeout=50
                          把前面的#去了

         10061：MySQL服务不能正常启动
                         Quote:
                         启动这台机器上的MySQL服务
                         如服务启动失败，一定是你的my.ini文件出了差错， MySQL服务不能正常启动
                         你删除了它后，MySQL就会按其默认配置运行，那就没有问题了

--查询出各（某）表字段的属性
[code=SQL]
--sql server 2000
SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
    sysproperties g
on
    a.id=g.id and a.colid=g.smallid 
left join
    sysproperties f
on
    d.id=f.id and f.smallid=0
where
    d.name='要查询的表'    --如果只查询指定表,加上此条件
order by
    a.id,a.colorder

/*
表名    表说明 字段序号 字段名    标识 主键    类型    占用字节数 长度 小数位数 允许空  默认值      字段说明
------- ----- -------  -------- ---- ------- ------ ------- --------------- ------ ---------- ----------
authors       1        au_id          √     id      11     11      0                         
              2        au_lname              varchar 40     40      0                         
              3        au_fname              varchar 20     20      0                         
              4        phone                 char    12     12      0              ('UNKNOWN')
              5        address               varchar 40     40      0       √                
              6        city                  varchar 20     20      0       √                
              7        state                 char    2      2       0       √                
              8        zip                   char    5      5       0       √                
              9        contract              bit     1      1       0                         
（所影响的行数为 9 行）
*/

 
--修改一个字段里的内容

06财经报/NJ-Q-W-6.2.2-001周前集会管理办法
06财经报/NJ-Q-W-4.2.3-004文印管理制度
06财经报/NJ-Q-W-5.5.1-001组织权责管理办法

通过什么语句达到：这样效果
06财经/NJ-Q-W-6.2.2-001周前集会管理办法
06财经/NJ-Q-W-4.2.3-004文印管理制度
06财经/NJ-Q-W-5.5.1-001组织权责管理办法
update tb set lj=replace(lj,'财经报','财经')

--同一表中二列值互换
update xxx set A=B,B=A

--对2个列同时分组的查询 
rq(日期)    bh(编号)  sl(数量)  dj(单价)
2008/10/17  102030      2          40
2008/10/17  102231      4          5
2009/04/12  102030      6          20
2009/05/05  201020      10          8
 求每年中每种产品销售情况???
( 编号的前2位数代表 产品 类型)
select
  left(rq,4) as 年份,
  bh 产品编号,
  sum(sl) 销售数量,
  sum(sl*dj) as 销售额
from 表
group by bh

select c.cost_center,c.name,b.jibengongzi  from employee as a ,salary_200808 as b,
department as c ,position as d
where
a.cost_center_xref_department_id=c.department_id and a.employee_id=b.employee_id
 and a.position_id=d.position_id
and  d.department_id=c.department_id
select * from employee
update employee set cost_center_xref_department_id=4 where employee_id=16

select * from department

--
表table有3个字段id, type, num分别代表每笔货物的标识，类型，数量；请写出sql语句，
可以得到各种type货物中num值最高的数据；

if not object_id('tb') is null
    drop table tb
Go
Create table tb([bh] nvarchar(3),[name] nvarchar(2),[num] int)
Insert tb
select N'001',N'依利',1000 union all
select N'002',N'三鹿',5000 union all
select N'003',N'依利',7000 union all
select N'004',N'蒙牛',1000 union all
select N'005',N'三鹿',2000 union all
select N'006',N'依利',6000 union all
select N'007',N'蒙牛',2000 union all
select N'008',N'依利',8000
Go
Select * from tb

select bh,name,num
from tb t
where not exists(select 1 from tb where name=t.name and num>t.num)

select *
from @tb a
where num=(select max(num) from @tb where name=a.name)
/**

select * from tb a
where num = (select top 1 num
from tb
where type=a.type
order by num desc)

select type,max(num) from tb group by type

select type as type,max(num) as maximum from testTable1 group by type

--多维数据库分析

http://topic.csdn.net/u/20081127/15/d916bf00-fd27-4d39-9d7b-0d0437efad0c.html
现有5个表,最前面的一列都是自动编号)
一.分类表
  分类ID,  分类名称
  1        一类
  2        二类
  .............
二.物资表
  物资ID,  物资名称,  物资单价,  分类ID
  1        固化剂      20        2
  2        塑料桶      10        1
  3        加热板      35        2
  ............
三.部门表
  部门ID,  部门名称
  1          A部门
  2          B部门
  ........
四.领用表
  领用ID,  领用日期,  物资ID, 领用数量 领用部门ID
  1      2008-10-20    2        20        1
  2      2008-11-2    1        30        3
  3      2008-11-25    2        40        2
  ........
五.计划表
  计划ID,  部门ID,  月计划金额,  计划月份
  1          1        2000      2008-10-1
  2          2        1000      2008-10-1
  3          3        3000      2008-10-1
  .......
  7          1        2100      2008-11-1
  8          2        1100      2008-11-1
  9          3        3100      2008-11-1
  ......

计划表中,每月会输入当月计划金额,以便和实际领用金额比较,计算出差值,做成本控制.请SQL高手帮忙写一下,非常感谢

要求结果1: 某年某月领用额和差额
'------------------------------------------------------------
                xxxx年x月物资领用量
            A部门      B部门  ....
一类          x￥        x￥
二类          x￥        x￥
....          ...        ...
合计金额      x￥        x￥
计划金额      x￥        x￥
差额          x￥        x￥

'要求结果2:  指定日期内的领用量
'-------------------------------------------------
          xxxx年x月x日---xxxx年x月x日物资领用量
            A部门      B部门  ....
一类          x￥        x￥
二类          x￥        x￥
....          ...        ...
合计金额      x￥        x￥
---------------------------------------------------
create table 分类表(分类ID int , 分类名称 varchar(10))
insert into 分类表 values(1 , '一类')
insert into 分类表 values(2 , '二类')
insert into 分类表 values(3 , '三类')
create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int)
insert into 物资表 values(1 , '固化剂' , 20 , 2 )
insert into 物资表 values(2 , '塑料桶' , 10 , 1 )
insert into 物资表 values(3 , '加热板' , 35 , 3 )
create table 部门表(部门ID int, 部门名称 varchar(10))
insert into 部门表 values(1 , 'A部门')
insert into 部门表 values(2 , 'B部门')
insert into 部门表 values(3 , 'C部门')
create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int)
insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1)
insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3)
insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2)
insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3)
insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)
insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1)
create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime)
insert into 计划表 values(1 , 1 , 2000 , '2008-10-1')
insert into 计划表 values(2 , 2 , 1000 , '2008-10-1')
insert into 计划表 values(3 , 3 , 3000 , '2008-10-1')
insert into 计划表 values(7 , 1 , 2100 , '2008-11-1')
insert into 计划表 values(8 , 2 , 1100 , '2008-11-1')
insert into 计划表 values(9 , 3 , 3100 , '2008-11-1')

declare @sql varchar(4000)
set @sql='select b.分类名称'
select @sql=@sql+',max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'
from (select distinct 部门名称 from 部门表) a
set @sql=@sql+' from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称'
print @sql
exec(@sql)

--打印出来的SQL静态语句
select b.分类名称,
max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],
max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],
max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门]
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称

--结果如下：
--分类名称 A部门 B部门 C部门
--二类     1200  0    600
--三类     2100  1400 0
--一类     200   0   

create table 分类表(分类ID int , 分类名称 varchar(10))
insert into 分类表 values(1 , '一类')
insert into 分类表 values(2 , '二类')
insert into 分类表 values(3 , '三类')
create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int)
insert into 物资表 values(1 , '固化剂' , 20 , 2 )
insert into 物资表 values(2 , '塑料桶' , 10 , 1 )
insert into 物资表 values(3 , '加热板' , 35 , 3 )
create table 部门表(部门ID int, 部门名称 varchar(10))
insert into 部门表 values(1 , 'A部门')
insert into 部门表 values(2 , 'B部门')
insert into 部门表 values(3 , 'C部门')
create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int)
insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1)
insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3)
insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2)
insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3)
insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)
insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1)
create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime)
insert into 计划表 values(1 , 1 , 2000 , '2008-10-1')
insert into 计划表 values(2 , 2 , 1000 , '2008-10-1')
insert into 计划表 values(3 , 3 , 3000 , '2008-10-1')
insert into 计划表 values(7 , 1 , 2100 , '2008-11-1')
insert into 计划表 values(8 , 2 , 1100 , '2008-11-1')
insert into 计划表 values(9 , 3 , 3100 , '2008-11-1')

declare @sql varchar(4000)
set @sql='if (object_id(''temptd1'')) is not null
drop table temptd1 '
set @sql=@sql+'
if object_id(''temptd2'') is not null
drop table temptd2 '
set @sql=@sql+'
if object_id(''temptd3'') is not null
drop table temptd3 '
set @sql=@sql+'
if object_id(''temptd4'') is not null
drop table temptd4 '
set @sql=@sql+'
select * into temptd1 from'
set @sql=@sql+'
(select b.分类名称'
select @sql=@sql+',
max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'
from (select distinct 部门名称 from 部门表) a
set @sql=@sql+'
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1 '

set @sql=@sql+'
select * into temptd2 from(select ''合计金额'' as 分类名称'
select @sql=@sql+',
sum('+部门名称+') ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+' from temptd1) 表2 '

set @sql=@sql+'
select * into temptd3 from(select ''计划金额'' as 分类名称'
select @sql=@sql+',
sum(case 部门名称 when '''+部门名称+''' then 月计划金额 else 0 end) ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+'
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3 '

set @sql=@sql+'
select * into temptd4 from(select ''差额'' as 分类名称'
select @sql=@sql+',
(temptd3.'+部门名称+'-temptd2.'+部门名称+') ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+' from temptd2,temptd3) 表4'
set @sql=@sql+'

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)'
print @sql
exec(@sql)

--打印出来的SQL静态语句
if (object_id('temptd1')) is not null
drop table temptd1
if object_id('temptd2') is not null
drop table temptd2
if object_id('temptd3') is not null
drop table temptd3
if object_id('temptd4') is not null
drop table temptd4
select * into temptd1 from
(select b.分类名称,
max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],
max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],
max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门]
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1
select * into temptd2 from(select '合计金额' as 分类名称,
sum(A部门) [A部门],
sum(B部门) [B部门],
sum(C部门) [C部门] from temptd1) 表2
select * into temptd3 from(select '计划金额' as 分类名称,
sum(case 部门名称 when 'A部门' then 月计划金额 else 0 end) [A部门],
sum(case 部门名称 when 'B部门' then 月计划金额 else 0 end) [B部门],
sum(case 部门名称 when 'C部门' then 月计划金额 else 0 end) [C部门]
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3
select * into temptd4 from(select '差额' as 分类名称,
(temptd3.A部门-temptd2.A部门) [A部门],
(temptd3.B部门-temptd2.B部门) [B部门],
(temptd3.C部门-temptd2.C部门) [C部门] from temptd2,temptd3) 表4

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)

（所影响的行数为 3 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

--结果如下：
--分类名称 A部门 B部门 C部门
--二类     1200  0    600
--三类     2100  1400 0
--一类     200   0    500
--合计金额 3500  1400 1100
--计划金额 4100  2100 6100
--差额     600   700  5000

---------------------------------------------------------------
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

---------------------------------

/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/

--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')

drop table tb   

------------------
------------------

/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/

create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go

--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end

--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/

select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb
---------------------------------------------------------------

有一表t1 保存每次访问网站IP访问数据如
id  FromIP    AddTime
1 
我查查询的结果为 每天的独立IP 总数
--
select count(distinct FromIP)
from t1
where convert(varchar(10),AddTime ,120) ='2008-01-01'--条件
--
select convert(varchar(10),addtime,120) [天] , count(1) [多少独立IP] from tb group by convert(varchar(10),addtime,120)
--
select convert(varchar(10),AddTime ,120) ev_day,count(distinct FromIP)
from t1
group by convert(varchar(10),AddTime ,120)

---------------------------------------------------------------
用SQL语句来备份数据库,如何做?
substring

datediff
select * from table1
where datediff(day,insertdate,getdate()) = 3

-------------------------------------------
表 account(userid,username)
userid  username
1        zhangshan
2        lishi
3        wangwu
4        liliu
4        liqi

查询结果    :
1        zhangshan
2        lishi
3        wangwu
4        liliu

请各位高手帮帮忙     
--> --> (Andy)生成测试数据 2008-10-19
Set Nocount On
declare @accoun table([userid] int,[username] nvarchar(9))
Insert @accoun
select 1,N'zhangshan' union all
select 2,N'lishi' union all
select 3,N'wangwu' union all
select 4,N'liliu' union all
select 4,N'liqi'

Select * From @accoun a Where username=(Select Top 1 username From @accoun Where userid=a.userid)

/*
userid      username
----------- ---------
1           zhangshan
2           lishi
3           wangwu
4           liliu

*/

-----------------------
如何用存储过程实现在表的分页浏览，传入参数pageindex和pagesize,
http://topic.csdn.net/u/20081021/12/f42039c7-fc93-44e4-b96c-92800b997ac0.html
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
        newsid, topic, ntime, hits
      FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC

----------------------------SQL查询列转行(多表查询,并分组)  -----------------------------------------
http://topic.csdn.net/u/20081027/12/4f803ea0-00d2-4bd6-8853-3b9968d95a81.html
问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）

1. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id

-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT
        id
    FROM @t
)A
OUTER APPLY(
    SELECT
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

------------基础查询统计-------------------------------------------------------------
表a：
-----------
姓名
-----------
张三
李四
王五
马六
林七
朱八
--------------
表B:
--------------
姓名  金额  货币
--------------
张三    1    1
李四    2    1
王五    1    2
马六    1    3
张三    1    3
张三    2    1
--------------
通过数据表得到视图C

-------------------------------------------
姓名      货币1汇总      货币2汇总    货币3汇总
-------------------------------------------
张三        3                        1
李四        2
王五                        1
马六                                      1
林七
朱八
--------------------------------------------
总计        5            1          2
--------------------------------------------

要求说明：
1、表A中的姓名列不管是否有货币收付发生，均需出现在视图C中
2、表B中的货币收付记录按照表A的列进行分币种汇总，每个币种在视图C中为单独一列
3、视图C中增加一行为总计行，并按货币进行小计
--> liangCK小梁 于2008-10-13
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (姓名 NVARCHAR(4))
INSERT INTO #tb1
SELECT '张三' UNION ALL
SELECT '李四' UNION ALL
SELECT '王五' UNION ALL
SELECT '马六' UNION ALL
SELECT '林七' UNION ALL
SELECT '朱八'
--> liangCK小梁 于2008-10-13
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (姓名 NVARCHAR(4),金额 INT,货币 INT)
INSERT INTO #tb2
SELECT '张三','1','1' UNION ALL
SELECT '李四','2','1' UNION ALL
SELECT '王五','1','2' UNION ALL
SELECT '马六','1','3' UNION ALL
SELECT '张三','1','3' UNION ALL
SELECT '张三','2','1'

--SQL查询如下:

SELECT ISNULL(a.姓名,'合计') AS 姓名,
       SUM(CASE WHEN 货币=1 THEN 金额 ELSE 0 END) AS [货币1金额],
       SUM(CASE WHEN 货币=2 THEN 金额 ELSE 0 END) AS [货币2金额],
       SUM(CASE WHEN 货币=3 THEN 金额 ELSE 0 END) AS [货币3金额]
FROM #tb1 AS a
 LEFT OUTER JOIN
   #tb2 AS b
 ON a.姓名=b.姓名
GROUP BY a.姓名
WITH ROLLUP

/*
姓名   货币1金额       货币2金额       货币3金额
---- ----------- ----------- -----------
张三   3           0           1
朱八   0           0           0
李四   2           0           0
林七   0           0           0
王五   0           1           0
马六   0           0           1
合计   5           1           2

(7 行受影响)

*/

---
select * , 工资 = (select max(工资) from tb where 部门ID = t.部门ID) from tb t
select * from tb where 工资=（select max(工资) from tb ）

[b]select 姓名,max(工资) as 工资,部门 from 部门 group by 部门
select * from tb a
where not exists (select 1 from tb where 部门=a.部门 and 员工<>a.员工 and 工资>a.工资)

select 部门,max(工资) as 最高工资
from 工资表
group by 部门

----------------------------------------------------------
用CTE处理表重复数据的更新和删除
http://blog.csdn.net/roy_88/archive/2008/07/26/2715965.aspx
处理表重复记录(查询和删除)_整理贴4
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1

方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/

--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1

方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

生成结果2:
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/

 

--2、删除重复记录有大小关系时,保留大或小其中一个记录

--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)

方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)

 

select * from #T

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/

--II、Name相同ID保留最大的一条记录:

方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
delete a  from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)

方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)

select * from #T
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/

 

 

--3、删除重复记录没有大小关系时，处理重复值

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go

方法1:
if object_id('Tempdb..#') is not null
    drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#

truncate table #T--清空表

insert #T select * from #    --把临时表#插入到表#T中

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:

alter table #T add ID int identity--新增标识列
go
delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin
    set rowcount @con;
    delete #T where Num=@Num and Name=@Name
    set rowcount 0;
    fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor

--查看结果
select * from #T
/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

表结构：
Companies(单位表)
------------------
id
companyName

DepartCategories(单位分类)
---------------------------
id
categoryName
companyID (外键--单位表)

DepartMents(部门表)
--------------------
id
departMentName
departCategoryID(外键--单位分类)

car (汽车表)
------------
id
carName
carNumber
departMentID(外键--部门表)

maintenaces(汽车维修记录)
---------------------------
id        主键
carid      外键
repairdate 报修日期
price      金额

目前:select id,carid,repairdate,price from maintenaces
返回结果:

id          carid      repairdate              price
----------- ----------- ----------------------- ---------------------------------------
1          1          2008-07-20 00:00:00.000 250.00
2          2          2008-05-09 00:00:00.000 10.00
3          4          2007-10-09 00:00:00.000 20.00
4          2          2008-10-09 00:00:00.000 20.00
7          1          2008-06-21 00:00:00.000 0.00
9          1          2008-08-02 00:00:00.000 0.00
10          1          2008-06-21 00:00:00.000 800.00
11          2          2008-05-22 00:00:00.000 0.00
12          1          2008-07-23 00:00:00.000 0.00

------------------------------------------------------------------------
1.如何用一个存储过程实现上述功能？
我想统计单车(查询一个车)全部车的年,半年,季,月的维修记录费用汇总报表，结构如下：
http://topic.csdn.net/u/20080923/10/c685d537-b96e-4c42-8ed5-1462ed7b608e.html
序号  汽车名称  汽车牌号 使用部门  总计金额
年,半年,季,月的维修记录费用汇总,在一个存储过程中，这些参数根据条件动态变化，如何实现，
如按季或月或半年，前提年参数一定要有
 2。我用上面的maintenaces表测试如下

declare @year char(4)
set @year='2008'
declare @Month char(1)
set @Month='6'

select c.carName 汽车名称,c.carnumber 汽车牌号,d.departmentName 使用部门,sum(m.price) as 金额总计
from
  Maintenaces m inner join cars c on  m.carid = c.id
inner join departMents d on c.departMentID  =  d.id
inner join DepartCategories dc on d.departCategoryID = dc.id
inner join Companies cp on dc.companyID = cp.id
where YEAR(m.repairDate) = @year and
      Month(m.repairDate) = @Month
group by c.carname,c.carnumber,d.departmentName

显示

汽车名称      汽车牌号    使用部门  金额总计
--------------------------------------------
大轿车        晋C10695    汽车队    800.00

可将上面的参数@Month设置为10，查询为空

汽车名称      汽车牌号    使用部门  金额总计
--------------------------------------------
declare @year char(4)
set @year='2008'
declare @Month char(1)
set @Month='6'

select c.carName 汽车名称,c.carnumber 汽车牌号,d.departmentName 使用部门,sum(m.price) as 金额总计
from
  Maintenaces m inner join cars c on  m.carid = c.id
inner join departMents d on c.departMentID  =  d.id
where YEAR(m.repairDate) = @year and
      Month(m.repairDate) = @Month
group by c.carname,c.carnumber,d.departmentName

---
Companies(单位表) 
DepartCategories(单位分类)
DepartMents(部门表)
car (汽车表)
maintenaces(汽车维修记录)

-----------------------------------------------------
http://topic.csdn.net/u/20090110/22/97c412d0-f90e-4ab2-8aed-4765e3a6ac21.html
SQLSERVEER基础语法知识
having子句是在组上放置条件的
看例子
select   orderid sum(orderMoney) from sales where orderid between 3 and 123 group by orderid  having sum(orderMoney)>5
group by得到了一个记录的集合
然后通过having子句，再在这个集合上做筛选
----------------------------------------------------

 

本周只是从后台来做测试。

了解了关联的几个表结构，数据流的变化。学到了一些基本的测试语句。

Update attendance_machine_data_log Set FlashTime=dbo.FROM_UNIXTIME(time_log_stamp,'%Y-%m-%d  %H:%i:%s') Where FlashTime Is Null

－－日期的截取后的年月日查询

select  * from attendance_machine_data_log 
where employee_id=16 and CONVERT(varchar(10),FlashTime,102)='2008-10-23'

--将一个字段的属性改变

alter table shift_time  ALTER column attendance_rule_id   int  NULL

--5、创建表A、B、C
CREATE TABLE A
(id DECIMAL(9) NOT NULL,
 name CHAR(10));

CREATE TABLE B
(id DECIMAL(9) NOT NULL,
 hobby CHAR(10));

CREATE TABLE C
(id DECIMAL(9) NOT NULL,
 name CHAR(10));

--6、增加表字段
ALTER TABLE C
     add hobbya CHAR(10)

--7、修改表字段
ALTER TABLE C
     ALTER COLUMN hobby CHAR(5)

--8、删除表
ALTER TABLE C
     DROP COLUMN id

--9、删除表
DROP TABLE C

--10、添加主键
ALTER TABLE A
     ADD PRIMARY KEY(ID)

--10、删除主键
ALTER TABLE A
     DROP PRIMARY KEY(ID)

--11、创建索引
CREATE UNIQUE INDEX INDEXID ON A(ID)

--12、删除索引
DROP INDEX A.INDEXID

--13、创建视图
CREATE VIEW VIEWA
     AS SELECT ID FROM A WHERE ID>100

--14、删除视图
DROP VIEW VIEWA

 

--1.1、查询指定列
SELECT id FROM A

--1.2、查询全部列
SELECT * FROM A
SELECT ID,NAME FROM A

--1.3、取消相同取值的行
SELECT DISTINCT ID FROM A

--1.4、比较大小
SELECT * FROM A WHERE ID>5

--1.5、多重条件查询
SELECT * FROM A WHERE ID>5 AND NAME='NANCY' OR NAME='TONY'

--1.6、确认范围查询
SELECT * FROM A WHERE ID  BETWEEN 1 AND 4
SELECT * FROM A WHERE ID  NOT BETWEEN 1 AND 4
--这与下等价
SELECT * FROM A WHERE ID>=1 AND ID<=4
SELECT * FROM A WHERE ID<1 OR ID>4

--1.7、确认集合
SELECT * FORM A WHERE ID IN  (1,2,3)

--1.8字符匹配查询
--查询Northwind数据库中orders表的ShipCountry列以B，C，D，F开始且第三个字
 -- ，符为“a”的OrderlD、CustomerID和ShipCountry的信息。
SELECT ORDERID,CUSTOMERID,SHIPCOUNTRY FROM ORDERS WHERE SHIPCOUNTRY LIKE '[BCDF]%' AND SHIPCOUNTRY LIKE '__A%'

--1.9空值查询
SELECT * FROM A WHERE ID IS  NULL

--1.10常用库函数及统计汇总查询
  SELECT ID,NAME FROM A
 Where ID=(select max(ID) from A)

--1.11分组查询
SELECT ID FROM A GROUP BY ID HAVING ID>0

--1.12对查询结果进行排序
SELECT * FROM A ORDER BY ID,NAME DESC

--1.13数据表连接查询
----A、内联接
select A.Name,B.Hobby from A, B where A.id = B.id
select A.Name,B.Hobby from A Inner join B ON A.id = B.id

----B、左外联接
select A.Name,B.Hobby from A Left join B ON A.id = B.id

----C、右外联接
select A.Name,B.Hobby from A Right join B ON A.id = B.id

----D、此句以上右外联接结果是一样的
select A.Name,B.Hobby from B Left join A ON A.id = B.id

----E、全联接,以下两句同
select A.Name,B.Hobby from A cross join B
select A.Name,B.Hobby from A,B

--1.14使用TOP限制结果集
SELECT TOP 3 WITH TIES ID,NAME FROM A ORDER BY ID

--1.15将查询结果存入表中
SELECT * INTO #TEMP FROM A
SELECT * INTO ##TEMP1 FROM A
SELECT * INTO D FROM A

--1.16合并查询
SELECT * FROM A
UNION
SELECT * FROM B

--1.17子查询
--A、使用子查询进行比较测试
    SELECT ID,NAME FROM A
    WHERE NAME='NANCY'
    and ID>( SELECT AVG(ID) from B)

--B、使用子查询基于集合的测试
    SELECT * FROM A
    WHERE ID IN
    (SELECT ID FROM B)

--C、使用子查询进行存在性测试
    SELECT NAME FROM A  
    WHERE EXISTS
    (SELECT * FROM B
    WHERE ID=A.ID)

--2.1插入一行新记录
INSERT INTO A(ID,NAME) VALUES(1,'TONY')

--2.2插入一行的部分数据值
INSERT INTO A(ID) VALUES(2)

--2.3插入多行记录
INSERT INTO B(ID,HOBBY) SELECT * FROM A

--2.4修改数据
UPDATE A
SET NAME='NANCY' WHERE ID=2

UPDATE A
SET NAME='A' WHERE ID IN(SELECT ID FROM B)

--2.5删除记录
DELETE A WHERE ID=2
DELETE A WHERE ID IN(SELECT ID FROM B)

---随机取出两条数据
select top 2 * from B order by newid()

---列出数据库里所有的表名
select name from sysobjects where type='U'

---列出表里所有的列
select name from syscolumns where id=object_id('B')

---初始化表,将内容清空
TRUNCATE TABLE A

 

--其次，大家来看一些不错的sql语句
--1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
--法一：where 1 <>1 或用where 1=1在程序里是经常使用的，后面可根据加个多个条件也可不加，非常灵活
select * into b from a where 1 <>1
--法二：
select top 0 * into b from a

--2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b

--3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
--例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

--4、说明：两张关联表，删除主表中已经在副表中没有的信息 
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

--5、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

--6、说明：日程安排提前五分钟提醒 
select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

--7、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
select top 10 yonghu.* from (select top 20 * from yonghu)a,yonghu where a.yhdm=yonghu.yhdm

--8、说明：前10条记录
select top 10 * FROM table1 where 范围

--9、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

--10、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

--11、说明：随机取出10条数据
select top 10 * from tablename order by newid()

--12、说明：随机选择记录
select newid()

--13、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

--14、说明：列出数据库里所有的表名
select name from sysobjects where type='U' 

--15、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
select name from syscolumns where id=object_id('yonghu')

--16、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3

--17、说明：初始化表table1
TRUNCATE TABLE table1

--18、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
　
--随机读取若干条记录，测试过
--Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
--Sql server:select top n * from 表名 order by newid()
--mysql:select * From 表名 Order By rand() Limit n
--使用SQL语句 用...代替过长的字符串显示
--语法：
--SQL数据库：
select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
---=Access数据库：
SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename; 
　

--19、查询时字符串连接（用+号）
select id+'['+name+']' YH from a