SQL数据库资料整理

kunge 收录于2007-03-23 阅读数：

公众公开

原文来源　

SQL游标学习游标一般格式：
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
WHILE @@FETCH_STATUS=0
      BEGIN
               SQL语句执行过程... ...
               FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
      END
CLOSE 游标名称
DEALLOCATE 游标名称
例子：
/*
功能：数据库表格tbl_users数据
deptid userid username
1       100    a
1    101    b
2    102    c
要求用一个sql语句输出下面结果
deptid username
1       ab
2       c
[要求用游标实现]

设计： OK_008
时间： 2006-05
备注：无
*/
create table #Temp1(deptid int,userid int,username varchar(20)) --待测试的数据表
create table #Temp2(deptid int,username varchar(20))                --结果表
--先把一些待测试的数据插入到待测试表#Temp1中
insert into #Temp1
select 1,100,'a' union all
select 1,101,'b' union all
select 1,131,'d' union all
select 1,201,'f' union all
select 2,302,'c' union all
select 2,202,'a' union all
select 2,221,'e' union all
select 3,102,'y' union all
select 3,302,'e' union all
select 3,121,'t'
--
declare @deptid int,@username varchar(20)
--定义游标
declare Select_cursor cursor for
      select deptid,username from #Temp1
open Select_cursor
fetch next from Select_cursor into @deptid,@username    --提取操作的列数据放到局部变量中
while @@fetch_status=0    --返回被 FETCH 语句执行的最后游标的状态
/*
@@FETCH_STATUS =0          FETCH 语句成功
@@FETCH_STATUS =-1 FETCH 语句失败或此行不在结果集中
@@FETCH_STATUS =-2 被提取的行不存在
*/
      begin
               --当表#Temp2列deptid存在相同的数据时，就直接在列username上追加@username值
               if(exists(select * from #Temp2 where deptid=@deptid ))
                        update #Temp2 set username=username +@username where deptid=@deptid
               else
               --插入新数据
                        insert into #Temp2 select @deptid,@username
               fetch next from Select_cursor into @deptid,@username
      end
close Select_cursor
deallocate Select_cursor
select * from #Temp2 --测试结果
Drop table #Temp1,#Temp2

[ 本帖最后由 DVD 于 2006-12-14 17:08 编辑 ]

作者: DVD 时间: 2006-12-14 16:36

自动生成表的更新数据的存储过程

设计原因：在数据库设计中，有时候建立了很多表，每个表都有Insert、Update、Delete结构基本相同的存储，要是能有个自动生成表的更新数据的存储过程，就方便了我们不必浪费时间去写每一张表的Insert、Update、Delete存储过程。
设计方法：先提取表的各字段信息，包含字段的数据类型、数据定义长度、是否主键等。再根据提取出来的信息构造成表的更新数据的存储过程。下面的方法是有一个用户自定义函数FN_GetObjColInfo和一个存储过程SP_CreateProcdure来实现。

用户自定义函数FN_GetObjColInfo：

/*
功能：返回某一表的所有字段、存储过程、函数的参数信息
设计：OK_008
时间：2006-05
*/
CREATE FUNCTION FN_GetObjColInfo
(@ObjName varchar(50))
RETURNS  @Return_Table TABLE(
               TName nvarchar(50),
               TypeName nvarchar(50),
               TypeLength nvarchar(50),
               Colstat    Bit
               )
AS
BEGIN
      INSERT  @Return_Table
               /*
               主要是从系统表中提取表(对象)的各字段信息。
               sysobjects: 在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行
               syscolumns:每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行
               systypes: 保存数据类型和用户定义数据类型
               */
               select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长
               from sysobjects a
               inner join syscolumns b on a.id=b.id
               inner join systypes c on c.xusertype=b.xtype
               where a.name =@ObjName
               order by  B.ColID
      RETURN
END
GO
存储过程SP_CreateProcdure：

CREATE PROCEDURE SP_CreateProcdure
@TableName nvarchar(50)
AS
/*
功能：自动生成表的更新数据的存储过程
            如：当建立表MyTable后，执行SP_CreateProcdure ，生成表MyTable的数据更
               新的存储过程UP_MyTable
设计： OK_008
时间： 2006-05
备注：
      1、请在查询分析器上执行：EXEC SP_CreateProcdure TableName
      2、由于生成的字符串长度合计很多时候存在>4000以上，所有只使用Print输出，
            再Copy即可。
      3、该方法能生成一般表的更新数据的存储过程，其中更新格式可以根据实际
            情况修改。
设计方法：
      1、提取表的各个字段信息
      2、 ──┰─ 构造更新数据过程
                     ├─ 构造存储过程参数部分
                     ├─ 构造新增数据部分
                     ├─ 构造更新数据部分
                     ├─ 构造删除数据部分
      3、分段PRINT
      4、把输出来的结果复制到新建立存储过程界面中即可使用。
*/
DECLARE @strParameter nvarchar(3000)
DECLARE @strInsert nvarchar(3000)
DECLARE @strUpdate nvarchar(3000)
DECLARE @strDelete nvarchar(500)
DECLARE @strWhere  nvarchar(100)
DECLARE @strNewID  nvarchar(100)
DECLARE @SQL_CreateProc nvarchar(4000)

SET @SQL_CreateProc='CREATE PROCEDURE UP_'+@TableName +char(13)+'@INTUpdateID int,' +' /* -1 删除  0 修改  1新增 */'
SET @strParameter=''
SET @strInsert=''
SET @strUpdate=''
SET @strWhere=''

DECLARE @TName nvarchar(50),@TypeName nvarchar(50),@TypeLength nvarchar(50),@Colstat bit
DECLARE Obj_Cursor CURSOR FOR
SELECT * FROM  FN_GetObjColInfo(@TableName)
OPEN Obj_Cursor
FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat
WHILE @@FETCH_STATUS=0
  BEGIN
      --构造存储过程参数部分
      SET @strParameter=@strParameter +CHAR(13)+'@'+ @TName + ' ' +@TypeName+
                                 (CASE
                                 WHEN @TypeName='nvarchar' THEN '('+@TypeLength+')'
                                 ELSE ''
                                 END)+','
      --构造新增数据部分
      IF @Colstat=0 SET @strInsert=@strInsert + '@'+ @TName  +','
      --构造更新数据部分
      IF (@strWhere='')
         BEGIN
               IF @Colstat=0 SET @strNewID='SET @'+@TName+'=(Select ISNULL(MAX('+@TName+'),0) From '+@TableName+')+1 --取新的ID'
               SET @strWhere=' WHERE '+@TName+'='+'@'+@TName
         END
      ELSE
               SET @strUpdate=@strUpdate+@TName+'='+'@'+@TName +','
      --构造删除数据部分
      FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat
  END
CLOSE Obj_Cursor
DEALLOCATE Obj_Cursor

SET @strParameter=LEFT(@strParameter,LEN(@strParameter)-1) --去掉最右边的逗号
SET @strUpdate=LEFT(@strUpdate,LEN(@strUpdate)-1)
SET @strInsert=LEFT(@strInsert,LEN(@strInsert)-1)

--存储过程名、参数
PRINT @SQL_CreateProc+@strParameter +CHAR(13)+'AS'
--修改
PRINT 'IF (@INTUpdateID=0)'
PRINT'  BEGIN'+CHAR(13)
PRINT CHAR(9)+'UPDATE '+@TableName+' SET '+@strUpdate+CHAR(13)+CHAR(9)+@strWhere
PRINT '  END'
--增加
PRINT 'ELSE IF (@INTUpdateID=1)'
PRINT '  BEGIN'
PRINT CHAR(9)+@strNewID
PRINT CHAR(9)+'INSERT INTO '+@TableName+'  ('+REPLACE(@strInsert,'@','') +')  VALUES  ( '+@strInsert +')'
PRINT '  END'
--删除
PRINT 'ELSE'
PRINT ' BEGIN'
PRINT CHAR(9)+'DELETE FROM '+@TableName +@strWhere
PRINT ' END'
PRINT 'GO'
GO

[ 本帖最后由 DVD 于 2006-12-14 16:39 编辑 ]

作者: DVD 时间: 2006-12-14 16:40

绘画日历

设计原因：记得那时，周末没事干随便想到的，只是为了学习。
设计方法：先构造一个月的日历图，再使用循环绘画1-12月的日历图。
/*
功能：绘画日历
设计：OK_008
时间：2006-05
*/
DECLARE @Year nvarchar(4)
DECLARE @YearMonth nvarchar(7)    --月份
DECLARE @strTop nvarchar(200)
DECLARE @ForI INT,@ForYear INT ,@MaxDay INT
DECLARE @RowX INT --行位置
DECLARE @strWeekDayList nvarchar(20)
DECLARE @strPrint nvarchar(300)

-- ======================================
SET @Year='2006'    --请在这里输入年份
-- ======================================
--设置日历上边的标题格式
SET @strTop= '日'+char(9)+'一'+char(9)+'二' +char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六' +char(13)+
            '───────────────────────────'
--设置星期列表
SET @strWeekDayList='日一二三四五六'
SET @ForYear=1
WHILE @ForYear<=12  --1月份至12月份
BEGIN
      --取当月格式
      SET @YearMonth=@Year + '-' +CAST( @ForYear AS nvarchar(2))
      --取当月的最大日期
      SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01')))
      --找出1号的开始位置
      SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1
      SET @strPrint=''
      SET @ForI=1
      WHILE @ForI<=@RowX --构造1号的位置，并绘画空白处
      BEGIN
               SET @strPrint=@strPrint+CHAR(9)
               SET @ForI=@ForI+1
      END
      SET @ForI=1
      WHILE @ForI<=@MaxDay       --构造2号到月底的位置，并绘画
      BEGIN
               SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9)
               SET @RowX=@RowX+1
               SET @ForI=@ForI+1
               IF (@RowX%7=0) --满一个星期就换行
                  BEGIN
                        SET @RowX=0
                        SET @strPrint=@strPrint+CHAR(13)
               END
         END
      SET @ForYear=@ForYear+1
      -- 打印输出一个月的结果
      PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━'
      PRINT +Char(9)++Char(9)+' '+@YearMonth+CHAR(10)
      PRINT @strTop
      PRINT @strPrint +CHAR(10)
  END
执行结果：

[ 本帖最后由 DVD 于 2006-12-14 16:48 编辑 ]

附件: 绘制结果图.jpg (2006-12-14 16:47, 32.08 K) / 该附件被下载次数 1
http://bbs.54master.com/attachment.php?aid=31905

作者: DVD 时间: 2006-12-14 16:49

一个金额转化的问题

设计原因：记得是有一客户的特别要求，开始有点晕，后来还是能做好。
/*
功能：把Money类型转换成nvarchar类型,保留三位小数，而且把数值后面多余的0去掉。
   如money类型的数值2.59100，转化成nvarchar(20)类型时，要求的结果是2.591
   如money类型的数值89.7800，转化成nvarchar(20)类型时，要求的结果是89.78
设计：OK_008
时间：2006-07
*/
CREATE TABLE #Temp([ID] int ,F1 nvarchar(20),F2 nvarchar(20),SMoney  money,Dmoney money)
INSERT INTO #Temp SELECT 1,'F1','F2',45.895,23.89000
UNION SELECT 2,'F2','F3',45.895,234.67
UNION SELECT 3,'F3','F4',25.835,32.123
UNION SELECT 4,'F4','F5',13.7600,31.6754
UNION SELECT 5,'F5','F6',34.783,78.345
/*
要求的结果如下：
SMoney +'X'+ Dmoney
------------------------------------------------------
45.895X23.89
45.895X234.67
25.835X32.123
13.76X31.675
34.783X78.345
*/
SELECT *,cast(SMoney as nvarchar(20)) FROM  #Temp
/*
      经过3次的数据才可以把money类型的数据后边多余的零给去掉，
      money先转换成decimal,再转换成float,然后是nvarchar
*/
SELECT CAST(CAST(CAST(SmoneyAS decimal(20,3)) AS float) as nvarchar)+'X'+
               CAST(CAST(CAST(DmoneyAS decimal(20,3)) AS float)AS nvarchar)
FROM  #Temp
DROP TABLE #Temp

作者: DVD 时间: 2006-12-14 16:50

多个变量赋值问题.

设计原因：记得在itpub上有一朋友问到这个问题，感觉顶有意思的，顺便拿过来。
/*
=================================
原来的问题是这样的：
=================================
现在的表结构是这样的
ID ReferID ReferCount
1 0 2
2 1 NULL
3 2 NULL
4 3 1

然后声明四个变量
declare @t1 int
declare @t2 int
declare @t3 int
declare @t4 int

然后使用四条记录中的ReferCount为值四个变量赋值
问题是能不能使用一条sql语句完成，用case行不行的
请高手们指教
*/
--解决方法：

CREATE TABLE #Temp(ID int,ReferID int,ReferCount int)
INSERT INTO #Temp
SELECT 1, 0, 2 UNION ALL
SELECT 2, 1, NULL UNION ALL
SELECT 3, 2, NULL UNION ALL
SELECT 4, 3, 1
DECLARE @t1 int
DECLARE @t2 int
DECLARE @t3 int
DECLARE @t4 int
--根据ID的唯一性来查询
SELECT @t1=CASE ID WHEN 1 THEN ReferCount ELSE @t1 END
               ,@t2=CASE ID WHEN 2 THEN ReferCount ELSE @t2 END
               ,@t3=CASE ID WHEN 3 THEN ReferCount ELSE @t3 END
               ,@t4=CASE ID WHEN 4 THEN ReferCount ELSE @t4 END
      FROM #Temp
SELECT @t1 AS '@t1',@t2 AS '@t2',@t3 AS '@t3',@t4 AS '@t4'
DROP TABLE #Temp
/*
运行结果：
@t1|@t2|@t3|@t4
--------------------
2  |NULL|1 |NULL
*/

作者: DVD 时间: 2006-12-14 16:52

计算在一段时间内某周几（如星期一）的所有日期

设计方法：先提取第1个星期一，然后就是循环+7。难点就是怎么样提取第1个星期一。
/*
功能: 计算在某一段时间内某周几（如星期一）的所有日期
设计：OK_008
时间：2006-10
*/
DECLARE @Date datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @WeekDay int
DECLARE @i int

SET DATEFIRST 7                      --设置每周的第一天
SET @StartDate='2006-01-01'    --统计的开始日期
SET @EndDate='2006-12-31'       --统计的结束日期
SET @WeekDay=2                      --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二
SET @i=DATEPART(weekday,@StartDate)
PRINT '每周的第1天设置@@DATEFIRST:       '+CAST(@@DATEFIRST AS nvarchar(1))
PRINT '开始日期对应一周的第几天:          '+CAST(@i AS nvarchar(1))
IF(@i<=@WeekDay AND @i<7)
      SET @i=@WeekDay-@i
ELSE IF(@i<=@WeekDay AND @i=7)
      SET @i=@i-@WeekDay
ELSE
      SET @i=@@DATEFIRST-@i+@WeekDay

SET @Date=DATEADD(day,@i,@StartDate)
WHILE @Date<=@EndDate
      BEGIN
               IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)
               SET @Date=DATEADD(Week,1,@Date)
      END
GO

/*  ==============运行结果================*/
/*
每周的第1天设置@@DATEFIRST: 7
开始日期对应一周的第几天:             1
2006-01-02
2006-01-09
... ...
2006-12-18
2006-12-25
*/

作者: DVD 时间: 2006-12-14 16:52

字符串的分割

/*
功能：实现字符串的分割，达到批量提交数据参数和分批处理功能。
设计：wgh
时间：2006-09
*/
declare @str1 nvarchar(100) --要分割的字符串
declare @str2 nvarchar(100) --分割得的子字符串
declare @split nvarchar(40) --分割字符or字符串
declare @patIndex int    --分割字符or字符串第一次出现的起始位置
set @split='★■->'
set @str1='334,345,''dfd'',★■->select * from Employee★■->45654DFG★■->452897★■->97887657★■->123445'
set @str2=''
--从左至右分割
while  @str1<>@str2
begin
  set @patIndex=patindex('%'+@split+'%',@str1) --patindex函数返回@str1中@split第一次出现的起始位置
  if @patIndex>0
   set @str2=left(@str1,@patIndex-1) --截取@str1中以@split为分割的左边的字符串
  else
   set @str2=@str1
  print @str2    --调试
/*
  执行过程 ....
*/
  if @patIndex>0  set @str1=right(@str1,len(@str1)-len(@str2)-len(@split)) --截断字符串，为的是下一次循环能取得，@str1中@split第一次出现的起始位置
end

作者: DVD 时间: 2006-12-14 16:54

查找某一数据库没有主鍵的所有用户表

/*
功能：查找所有(某一)数据库没有主鍵的所有用户表
设计：wgh
时间：2006-06
*/

/*
设计方法：使用游标的方法，从系统表sysobjects、syscolumns、sysindexes提取表的相关信息。
*/
DECLARE @DatabaseName nvarchar(20)
DECLARE @Execute_Sql nvarchar(4000)
SET @DatabaseName='ydhr' --要是要检查所有数据库就SET @DatabaseName=''
CREATE table #Temp(DatabaseName nvarchar(20),TableName nvarchar(50))
IF ISNULL(@DatabaseName,'')=''
      DECLARE cursor_Sql CURSOR FOR
      SELECT name FROM master..sysdatabases WHERE dbid>=7  --dbid>=7 都是属于用户建立的数据库
ELSE
      DECLARE cursor_Sql CURSOR FOR
      SELECT name FROM master..sysdatabases WHERE dbid>=7 AND name=@DatabaseName
OPEN cursor_Sql
FETCH NEXT FROM cursor_Sql INTO @DatabaseName
WHILE @@FETCH_STATUS=0
      BEGIN
               SET @Execute_Sql='INSERT INTO #Temp
                        SELECT '''+@DatabaseName+''',A.name FROM '+@DatabaseName+'..sysobjects AS A WHERE OBJECTPROPERTY(A.id, N''IsUserTable'') = 1 AND NOT EXISTS(
                                 SELECT 1 FROM '+@DatabaseName+'..sysindexes AS i INNER JOIN '+@DatabaseName+'..sysindexkeys k
                                 ON i.id = k.id AND i.indid = k.indid INNER JOIN '+@DatabaseName+'..sysobjects AS o
                                 ON i.id = o.id INNER JOIN '+@DatabaseName+'..syscolumns c on i.id=c.id and k.colid = c.colid
                                 WHERE o.xtype = ''U'' AND A.id=i.id
                                             AND EXISTS(SELECT 1 FROM '+@DatabaseName+'..sysobjects WHERE xtype = ''PK'' AND name = i.name)
                        )'
               /*
               OBJECTPROPERTY（）返回当前数据库中对象的有关信息,OBJECTPROPERTY(对象id, N''IsUserTable'') 表示是否为用户表
               xtype='U' 在系统表sysobjects中表示查询的对象属于用户表，与OBJECTPROPERTY(对象id, N''IsUserTable'')功能一致，只是用法格式不同
               xtype='PK' PRIMARY KEY 约束（类型是 K）
               */
               EXECUTE(@Execute_Sql)
               PRINT @Execute_Sql  --为了能更清楚语句的含义，我这里可以使用PRINT查看整条执行语句
               FETCH NEXT FROM cursor_Sql INTO @DatabaseName
      END
CLOSE cursor_Sql
DEALLOCATE cursor_Sql
SELECT * FROM #Temp
DROP TABLE #Temp

作者: DVD 时间: 2006-12-14 16:55

重命名数据库、表、列

重命名数据库、表、列名，使用系统存储过程sp_rename是最简单的了。
我们先来看看帮助文档的说明：
sp_rename
更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。
语法
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]

@objname 对象名
@objtype 对象类型

值	描述
COLUMN	要重命名的列。
DATABASE	用户定义的数据库。要重命名数据库时需用此选项。
INDEX	用户定义的索引。
OBJECT	在 sysobjects 中跟踪的类型的项目。例如，OBJECT 可用来重命名约束（CHECK、FOREIGN KEY、PRIMARY/UNIQUE KEY）、用户表、视图、存储过程、触发器和规则等对象。
USERDATATYPE	通过执行 sp_addtype 而添加的用户定义数据类型。

/*
功能：重命名数据库、表、列
编写：wgh
时间：2006-11
*/

--重新命名数据库
IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name='BBS')
      EXECUTE sp_rename @objname='BBS',@newname='BBS1',@objtype='DATABASE'

--重新命名表名
IF EXISTS(SELECT 1 FROM sysobjects WHERE name='bbs_log' AND OBJECTPROPERTY(id,'IsUserTable')=1)
      EXECUTE sp_rename @objname='bbs_log',@newname='bbs_log1',@objtype='OBJECT'

--重新命名列名
IF EXISTS(SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('bbs_log') AND name='ColumnName' AND OBJECTPROPERTY(id,'IsUserTable')=1)
      EXECUTE sp_rename @objname='bbs_log.[ColumnName]',@newname='NewColumnName',@objtype='COLUMN'

作者: DVD 时间: 2006-12-14 16:56

格式化字符串，如输入01 自动生成 0000001

/*===================================================
功能：格式化字符串，如输入01 自动生成 0000001
备注：当位数达到10以上,要另写算法,否则出错
设计：weiguohao
日期：2006-03-11
  ===================================================*/
CREATE FUNCTION FORMAT_STR
(@strX Nvarchar(20),@FormatLength INT)
RETURNS NVARCHAR(20) AS
BEGIN
DECLARE @Return NVARCHAR(20)
DECLARE @Length INT
SET @Length=LEN(@strX)
IF @Length>=@FormatLength
      SET @Return=@strX
ELSE
      BEGIN
      -- 格式化，只要是调用到数学函数POWER，其他就不再说明，太简单了
      SET @Return=CAST(RIGHT(POWER(10,@FormatLength),@FormatLength-@Length) AS NVARCHAR(20))+@strX
      END
RETURN @Return
END

作者: DVD 时间: 2006-12-14 16:57

一个厂家与销售商的查询问题问题来来自itpub论坛上的一位朋友，如下：
多个厂家多个销售商每个厂家对任一销售商都免费前10次的货款有厂家表，销售表，销售商表想求得销售商三个表拼合一起的视图视图中有个字段标出免费的销售记录？
厂家表：ID ，名称销售表：ID，销售单号
厂家：ID，销售商ID
销售商：ID，销售商名称预得视图：销售单ID，厂家ID，销售商ID，是否免费谢谢哥哥姐姐了。帮忙写写……
解答：
CREATE TABLE #Suppliers(SupplierID int,CompanyName nvarchar(40)) --厂家表
CREATE TABLE #Sell(SellID int,SupplierID int,CustomerID int) --销售表
CREATE TABLE #Customers(CustomerID int,CompanyName nvarchar(40)) --销售商、客户表
INSERT INTO #Suppliers
SELECT 1,N'Supplier1' UNION ALL
SELECT 2,N'Supplier2'
INSERT INTO #Customers
SELECT 1,N'Customer1' UNION ALL
SELECT 2,N'Customer2'
INSERT INTO #Sell
SELECT 1,1,1 UNION ALL
SELECT 2,1,1 UNION ALL
SELECT 3,1,1 UNION ALL
SELECT 4,1,1 UNION ALL
SELECT 5,1,1 UNION ALL
SELECT 6,1,1 UNION ALL
SELECT 7,1,1 UNION ALL
SELECT 8,1,2 UNION ALL
SELECT 9,1,2 UNION ALL
SELECT 10,1,2 UNION ALL
SELECT 11,1,2 UNION ALL
SELECT 12,1,2 UNION ALL
SELECT 13,1,2 UNION ALL
SELECT 14,1,2 UNION ALL
SELECT 15,2,1 UNION ALL
SELECT 16,2,1 UNION ALL
SELECT 17,2,2 UNION ALL
SELECT 18,2,2 UNION ALL
SELECT 18,2,2
--每个厂家对任一销售商都免费前3次的货款（根据实际修改提前多少次是免费的货款）
--主要是使用到一个GROUP BY ...HAVING ...在对数据进行分组和聚合后，就会用到 HAVING 子句中的条件
SELECT A.*,(CASE WHEN EXISTS(SELECT 1 FROM #Sell AS B WHERE A.SupplierID=B.SupplierID AND A.CustomerID=B.CustomerID AND B.SellID<=A.SellID GROUP BY B.CustomerID HAVING COUNT(1)<=3 ) THEN '是' ELSE '否' END) AS '是否免费' FROM #Sell AS A
DROP TABLE #Suppliers,#Sell,#Customers

结果

[ 本帖最后由 DVD 于 2006-12-14 17:01 编辑 ]

附件: 图.JPG (2006-12-14 17:00, 31.84 K) / 该附件被下载次数 0
http://bbs.54master.com/attachment.php?aid=31906

作者: 绿竹居 时间: 2006-12-14 16:59

俺的天。好多。俺要好好学习。。谢谢DVD

作者: DVD 时间: 2006-12-14 17:01

不用游标就可以实现的一个问题

原来问题如下：
如果有2个表T1（aid,bid），T2（bid,bname,bprice）
T1的字段表示顾客ID和商品ID，一个顾客可以买多种商品现在T1有记录:
1,10
1,11
1,12
2,10
2,13
T2的字段表示商品ID，商品名称，商品价格现在T2有记录：
10,aaa,1
11,bbb,2
12,ccc,3
13,ddd,4
现在要实现显示顾客买的商品名称和价钱即字段：
aid,bname,bprice
但是同一个顾客只保留一个ID，其他为null请问怎么实现？即将上面的记录实现为：
1,aaa,1
,bbb,2
,ccc,3
2,aaa,1
,ddd,4

如果不用游标有办法实现吗？
解决方法：
CREATE TABLE #T1(aid int ,bid int)
INSERT INTO #T1
SELECT 1,10 UNION ALL
SELECT 1,11 UNION ALL
SELECT 1,12 UNION ALL
SELECT 2,10 UNION ALL
SELECT 2,13
CREATE TABLE #T2(bid int,bname nvarchar(10),bprice int)
INSERT INTO #T2
SELECT 10,'aaa',1 UNION ALL
SELECT 11,'bbb',2 UNION ALL
SELECT 12,'ccc',3 UNION ALL
SELECT 13,'ddd',4
SELECT A.aid,B.bname,B.bprice INTO #T3 FROM #T1 AS A LEFT OUTER JOIN #T2 AS B ON A.bid=B.bid
DECLARE @aid int ,@lastaid int
UPDATE #T3 SET @aid=(CASE WHEN ISNULL(@lastaid,'') =aid THEN NULL ELSE aid END),aid=@aid,@lastaid=aid
SELECT * FROM #T3
DROP TABLE #T1,#T2,#T3
结果：
/*
1 aaa 1
NULL bbb 2
NULL ccc 3
2 aaa 1
NULL ddd 4
*/

作者: DVD 时间: 2006-12-14 17:02

一个数据替换问题

原来的问题：
我要將([4000]+[3900])/([3100]+[3200]+[3900])*[3900]替换成
(0+0)/(0+0+0)*0
就是把[4000]樣的替換成０
解决方法如下，
建立一个自定义函数：
CREATE FUNCTION CreateMathExpression
(@ColumnName nvarchar(500))
RETURNS nvarchar(1000) AS
BEGIN
DECLARE @FieldValue nvarchar(20)
DECLARE @Return nvarchar(1000)
DECLARE @Sql nvarchar(1000)
DECLARE @Index int
DECLARE @End bit
SET @End=0
SET @Return=''
--SET @ColumnName='[3900]/222.8*([3200]/[3900])'
SET @Index=CHARINDEX('[',@ColumnName)
WHILE @Index<>0
BEGIN
IF @End=0
BEGIN
SET @Index=CHARINDEX('[',@ColumnName)
IF @Index=0 GOTO Exit_While
SET @Return=@Return+LEFT(@ColumnName,@Index-1)
SET @End=1
END
ELSE
BEGIN
SET @Index=CHARINDEX(']',@ColumnName)
SET @FieldValue=LEFT(@ColumnName,@Index-1)

SET @Return=@Return+ '0'
SET @End=0
END
SET @ColumnName=RIGHT(@ColumnName,len(@ColumnName)-@Index)
END
Exit_While:
SET @Return=@Return+@ColumnName
RETURN @Return
END
--------------------------------------------------------------------例子:DECLARE @N nvarchar(200)
SET @N='([4000]+[3900])/([3100]+[3200]+[3900])*[3900]'
SELECT @N
SELECT dbo.CreateMathExpression(@N)
-------------------------结果:
(0+0)/(0+0+0)*0
备注：函数中的一些代码是别的用途,可以忽略。

作者: DVD 时间: 2006-12-14 17:04

向表中一列插入1-100的连续数

在BBS上看到一朋友问到这样的一个问题：
求助：向表中一列插入1-100的连续数

table A 中有BH字段（INT），怎么连续给BH赋值1-100,象下面的一样

BH
------------------------------------------------
1
2
.
.
.
100
根据常规的�