SQL数据库资料整理

 SQL游标学习游标一般格式:5 Y- d) H4 y5 O0 }- H5 a
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...8 C( o" t0 m- _: a
OPEN 游标名称
& b3 u( F6 m/ ^FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...0 u/ f, f  p: b" I2 B7 o- ~" ~2 F
WHILE @@FETCH_STATUS=0  j: X, C: N' q6 _/ V/ j
         BEGIN% h& M9 D$ w$ V; s- K5 I
                   SQL语句执行过程... ...
( g! @2 [2 z1 x                   FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...% R7 }' N0 s2 v/ H
         END
6 K9 W. k/ h* d) /, s& l' fCLOSE 游标名称& Q; V2 y! i* L! C" r, }) g
DEALLOCATE 游标名称
. z( h# ^% ^& o例子:
* `3 Q! x/ |4 G5 g+ Q; _/*
9 h" K+ C# D% y* s  _/ p
功能:数据库表格tbl_users数据
6 k1 G% l1 q% j( F6 A% }
deptid userid username
( b% P' L& t! {* i1          100      a
; y1 `9 /+ ?* m0 `$ ]% e( `9 V! C, M1       101      b
! ]/ P  I8 u/ /5 E% {4 }' ~2       102      c
: S- U, H$ /; h% X7 L
要求用一个sql语句输出下面结果
5 z4 S( k- P8 o  N  ]6 kdeptid username

% l2 D6 C) c$ i1 a% z1        ab1 e" d' X6 f' ?. z
2         c
& _2 n' D5 P8 Q7 [# O[
要求用游标实现
]2 b2 x; ]" l7 F4 e0 _1 `- _

1 R6 w2 ^7 G. Z( }, L
设计:
OK_008" Q2 m% m/ m& ~' U. ~. V
时间:
2006-05
$ y3 Y0 o, r: I$ o( a4 {1 J3 O1 t
备注:无
6 Z  S: q/ v: W; r- K1 b1 }7 r
*/
0 A# A8 |+ y1 p' q0 ]7 h
create table #Temp1(deptid int,userid int,username varchar(20)) --
待测试的数据表6 C" F/ b2 D( O+ R
create table #Temp2(deptid int,username varchar(20))                 --结果表: D* p( E; r3 F& L; h
--先把一些待测试的数据插入到待测试表#Temp1
, b" i2 t  z5 `; _insert into #Temp16 l* @$ J, s" z
select 1,100,'a' union all: k: D0 ^8 [" Z' V; M
select 1,101,'b' union all
4 r& w4 A( t- w9 {! m, F# n$ rselect 1,131,'d' union all
' Q! ]: ?) K* h- o: D% {7 gselect 1,201,'f' union all
8 q+ G- M4 y$ l  _3 ~1 dselect 2,302,'c' union all ) z' h' M) M& F5 R# g8 X$ X& |
select 2,202,'a' union all
1 C* V# g* t" ^- P3 j/ W: j' nselect 2,221,'e' union all
0 `2 V# Q- B( X5 X4 Pselect 3,102,'y' union all ; f+ x0 x; T& ~+ w1 u* D1 K
select 3,302,'e' union all
# I1 Z% u/ Q8 Z# Hselect 3,121,'t'
# f0 s0 s% f! b2 F4 G--( o$ X2 y7 O' @0 @; ?
declare @deptid int,@username varchar(20)" g& P: @, /8 /1 }
--定义游标
: e/ ?  i( Z& xdeclare Select_cursor cursor for
3 j) I$ L, k: H         select deptid,username from #Temp1
/ o7 M1 W* @7 w! p; ropen Select_cursor
# o7 E# W/ A9 i' y& Nfetch next from Select_cursor into @deptid,@username     --提取操作的列数据放到局部变量中
8 A# M, j; a/ H6 a2 rwhile @@fetch_status=0      --返回被 FETCH 语句执行的最后游标的状态' W' c+ u# ?$ w& f- r
/*$ l0 N9 A# W  e! Y* k: N' G
@@FETCH_STATUS =0           FETCH 语句成功
3 f& J8 Y" F+ d0 N& E( l. D/ e9 T: D@@FETCH_STATUS =-1 FETCH 语句失败或此行不在结果集中+ m& K. y" e7 ?" ^9 /$ F  _
@@FETCH_STATUS =-2 被提取的行不存在
4 }. Z$ u, k. P, F& x*/& u! y6 Y3 X' L' q- [
         begin
! /4 p' L+ O0 _3 M( T                   --当表#Temp2deptid存在相同的数据时,就直接在列username上追加@username  r( v! P+ A3 Y" N
                   if(exists(select * from #Temp2 where deptid=@deptid )) ! B% j& Y8 X2 ~) C+ {# ?5 K
                            update #Temp2 set username=username +@username where deptid=@deptid
, S' /. s6 D4 G5 L4 y                   else
+ }: l# q1 D. g" M8 l. c+ `                   --插入新数据
9 M, t+ W" i& F# h" `                            insert into #Temp2 select @deptid,@username4 y& j- X" L( w5 m# p% n8 b
                   fetch next from Select_cursor into @deptid,@username. Z8 c) ?- ^, J) ], }
         end& h% ^# c7 F) u+ R% y9 c
close Select_cursor       0 P# I; B9 O: A  I; F' ?7 z7 k% L( K
deallocate Select_cursor
& ?: ], C, }: G- n! Xselect * from #Temp2 --测试结果6 ~  _: {) g% X( S: m3 q( K
Drop table #Temp1,#Temp2
  Q1 q% R/ ^  k8 r7 T  H0 x
5 G% v) h! K% L/ K- a9 w1 L0 J
6 }, f% |$ V5 {% ?* |/ a( q. {2 r8 `" C8 a) b8 e
[ 本帖最后由 DVD 于 2006-12-14 17:08 编辑 ]


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

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

设计原因在数据库设计中,有时候建立了很多表,每个表都有InsertUpdateDelete结构基本相同的存储,要是能有个自动生成表的更新数据的存储过程,就方便了我们不必浪费时间去写每一张表的InsertUpdateDelete存储过程。
1 Y) G$ H9 o0 I  F: }
设计方法先提取表的各字段信息,包含字段的数据类型、数据定义长度、是否主键等。再根据提取出来的信息构造成表的更新数据的存储过程。下面的方法是有一个用户自定义函数FN_GetObjColInfo和一个存储过程SP_CreateProcdure来实现8 i+ g$ k* U5 T

( ]/ /' n1 J0 y" S6 |3 @! p% A用户自定义函数FN_GetObjColInfo) L' a1 C! S# g9 q4 x* u  k! {

* c+ T* W) q% U: i& |/*9 t) E7 v) r  c. ]
功能:返回某一表的所有字段、存储过程、函数的参数信息
" v/ b4 k6 m& a! /2 ]设计:OK_0081 P: U9 z/ s9 |: x! ?! d$ t' F( u
时间:2006-05
4 a0 V+ S! u' ?& ~* E0 D*/
: {4 J4 z/ u2 K" R& X: o! wCREATE FUNCTION FN_GetObjColInfo: O6 T( t" b) /
(@ObjName varchar(50))
5 ~, b9 H+ ~6 ~& k4 B) J( g" zRETURNS  @Return_Table TABLE(9 _" @, q* }4 ~- C! s. L3 D" T
                   TName nvarchar(50),
9 P" w/ R" Z$ N9 U. B9 N+ X2 }                   TypeName nvarchar(50),1 `( ]$ w7 [6 x! C( H$ P# K6 R; ~
                   TypeLength nvarchar(50)," ~7 w  H4 Z! B0 F$ k
                   Colstat       Bit
) E# H- k7 e, S( y                   )  ' @1 J9 e: R/ e% ~( W2 b
AS  - E" @' f7 L9 W: |4 t3 `. U) }9 f
BEGIN $ |" ]* X( ?! x$ t
         INSERT  @Return_Table ' A0 _) c/ `( S9 Z0 H0 _) _3 f
                   /*9 f) o( A6 s* u! i, v) d! G
                   主要是从系统表中提取表(对象)的各字段信息。( `1 h# v8 A0 i7 Z# K
                   sysobjects: 在数据库内创建的每个对象(约束、默认值、日志、规则、存储过程等)在表中占一行
( c/ u2 c" }; Q" m8 Q+ R                   syscolumns:每个表和视图中的每列在表中占一行,存储过程中的每个参数在表中也占一行; J# f. J, R# f
                   systypes: 保存数据类型和用户定义数据类型/ u2 O. u. e. w
                   */. E" }& W. m# v& B
                   select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长2 y+ {* A; o: `  O) K% O, k! J- z
                   from sysobjects a
; n* }; Y4 }5 [0 j  B                   inner join syscolumns b on a.id=b.id ! e1 J& F' m3 r
                   inner join systypes c on c.xusertype=b.xtype
* t& m2 [9 R: @9 l7 }                   where a.name =@ObjName : h% o  _, m! U8 @
                   order by  B.ColID/ Z; q+ H0 y, a/ j( b
         RETURN2 I: o) O. j6 N) }! o# s8 _7 ]0 f. f
END; b0 {3 H2 j5 }: d6 l# U3 H% f2 /, O
GO
' a- A) X4 h# u* g! ]" y6 N存储过程SP_CreateProcdure0 b8 u8 e) W+ ?" ?6 z4 t. /" |
9 d/ x& x$ E4 l5 N$ E! X

# |2 N8 O' ], C) ?5 z2 b# e, ~
- Y5 `# p! L. K" c3 F3 k/ h+ S3 k5 H
CREATE PROCEDURE SP_CreateProcdure
' B) V- V3 y: ^@TableName nvarchar(50)- T. N. y0 p; /) D+ G7 `# A0 [, C
AS
/ N" Z1 R2 X, F1 c. X' C/*5 x; t* _! D! @  @; T, D$ q
功能: 自动生成表的更新数据的存储过程
/ @8 W( y: y5 p7 f8 E              如:当建立表MyTable后,执行SP_CreateProcdure ,生成表MyTable的数据更/ c. x9 R2 L+ t8 |
                 新的存储过程UP_MyTable
7 e+ N4 N) ~* S, I3 D设计: OK_008) u4 z  u2 ?$ J
时间: 2006-05
6 a) i6 c0 n8 `1 o# y) [0 W6 X备注:
9 k7 P5 F4 a# v  K: y, [0 x         1、请在查询分析器上执行:EXEC SP_CreateProcdure TableName" P; k. E$ W9 }$ `  M) K4 {; @
         2、由于生成的字符串长度合计很多时候存在>4000以上,所有只使用Print输出,
; T% d/ b- A$ r( T- I               Copy即可。1 ~8 /4 o0 O; p0 t4 d
         3、该方法能生成一般表的更新数据的存储过程,其中更新格式可以根据实际2 Z; t2 J& F9 i: X& e
               情况修改。! A( p0 ?7 k8 d/ N7 ^
设计方法:; ^- ]6 t4 l3 V( b1 V$ {$ h' ^
         1、提取表的各个字段信息7 {( I  ?0 G3 b+ d
         2 ──┰─ 构造更新数据过程
% T9 _% /1 p/ e' z                         ├─ 构造存储过程参数部分
+ i! ?6 h/ o5 F                         ├─ 构造新增数据部分  m8 c& Q0 f; w: X3 l" @' L
                         ├─ 构造更新数据部分8 {7 X% f; K/ y( N
                         ├─ 构造删除数据部分
% A4 n4 U# x* i- J         3、分段PRINT
$ Q1 P# V  O/ }         4、把输出来的结果复制到新建立存储过程界面中即可使用。
! b$ Y5 v0 q* p, X! f! j# u*/
  O$ N! u! y" T6 @8 j5 y3 o5 IDECLARE @strParameter nvarchar(3000)$ a3 G# _+ X4 a6 d( z
DECLARE @strInsert nvarchar(3000); B- _$ n" c2 o
DECLARE @strUpdate nvarchar(3000)# L! G0 v7 i3 o) Z3 ~2 G6 p8 r
DECLARE @strDelete nvarchar(500)
! q7 m5 o8 X# ?% ~7 ADECLARE @strWhere  nvarchar(100)
3 q/ I9 J8 @: `DECLARE @strNewID  nvarchar(100)' V' a$ x2 A8 K: K) p
DECLARE @SQL_CreateProc nvarchar(4000)' s) d6 C. h  z# ]8 F: r

# L5 X/ A8 U4 USET @SQL_CreateProc='CREATE PROCEDURE UP_'+@TableName +char(13)+'@INTUpdateID int,' +' /* -1 删除  0 修改  1新增 */'
9 `. V- s. W; n& g8 L9 DSET @strParameter=''. j/ n! Z. _" {
SET @strInsert=''
5 i% U' H2 F# |/ u3 ^% @SET @strUpdate=''
, Y" X/ b- Z) j9 J% w! ySET @strWhere=''
0 V, `; i2 G# Q3 J( t
8 P/ ?1 ~% G  XDECLARE @TName nvarchar(50),@TypeName nvarchar(50),@TypeLength nvarchar(50),@Colstat bit
6 ?+ E; I4 j# M6 y9 GDECLARE Obj_Cursor CURSOR FOR
1 [! s- F9 l' `4 O3 /. qSELECT * FROM  FN_GetObjColInfo(@TableName)7 J, O$ d7 a9 c6 U/ P; D# i* i2 i* j
OPEN Obj_Cursor: Y$ _/ M$ @$ Z/ W. J& k3 [, G# X
FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat2 p( e, l0 {: v! J% b
WHILE @@FETCH_STATUS=0
. @/ J6 Y! /4 P" n  BEGIN! H6 N, u3 l2 ~1 I
         --构造存储过程参数部分# O- x4 x3 W+ x7 ], g& _) y8 S
         SET @strParameter=@strParameter +CHAR(13)+'@'+ @TName + ' ' +@TypeName+
  L* _+ r1 }8 B: ^7 ^7 Q9 w2 O                                     (CASE , /; U4 @5 ?) H3 t) M" j) E! n
                                     WHEN @TypeName='nvarchar' THEN '('+@TypeLength+')'
! F$ W3 y1 n& h& U4 f* D                                     ELSE ''2 n5 U0 r) t4 C# |
                                     END)+','
! k: J% d, Z$ ~         --构造新增数据部分. l4 B! t( j+ k2 G) G
         IF @Colstat=0 SET @strInsert=@strInsert + '@'+ @TName  +','
# Q+ v2 {2 q" U7 i5 G         --构造更新数据部分
2 e7 B# W. Q4 z+ p) k         IF (@strWhere='')
. p$ m7 F  g0 E$ M: u$ H9 G            BEGIN) ]1 q5 I0 H" m1 K$ [
                   IF @Colstat=0 SET @strNewID='SET @'+@TName+'=(Select ISNULL(MAX('+@TName+'),0) From '+@TableName+')+1    --取新的ID'9 e) U! H( I( J
                   SET @strWhere=' WHERE '+@TName+'='+'@'+@TName; k8 n/ r- `% y1 a+ j; _
            END
1 m6 B' J+ P, p- ]) _: t" i' l         ELSE0 e, c$ s0 V2 s5 s
                   SET @strUpdate=@strUpdate+@TName+'='+'@'+@TName +','8 d: Y& o- b2 _5 b9 a/ m- W0 K, f$ D
         --构造删除数据部分 & [' ^- C+ r+ s
         FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat
% o: Q9 @5 n0 S; `  END$ P  |" M9 Q6 q* R
CLOSE Obj_Cursor
1 H# J' N( ~  k- ^+ fDEALLOCATE Obj_Cursor7 O6 A' |- {* F5 u6 Z

, F6 N: U3 {  r6 e( K+ `" L- f: w& kSET @strParameter=LEFT(@strParameter,LEN(@strParameter)-1) --去掉最右边的逗号
# V9 {' H8 ~! e. gSET @strUpdate=LEFT(@strUpdate,LEN(@strUpdate)-1) ! d: D. h' e( O  R
SET @strInsert=LEFT(@strInsert,LEN(@strInsert)-1)
% c& O2 A; [5 d3 h* @2 d5 Q
% m3 v7 e6 b" |# q# F" h4 D3 w--存储过程名、参数6 l) ?1 A$ }- k& g' e" m! F& Q
PRINT @SQL_CreateProc+@strParameter +CHAR(13)+'AS'
2 T9 `1 E. O% }) m. ?+ A9 Y' s--修改
0 T2 Y1 S$ ?) F. m) h; q- gPRINT 'IF (@INTUpdateID=0)'
/ R3 ^& c) C- ?PRINT'  BEGIN'+CHAR(13)1 M; J$ K3 d' W$ K7 j. c5 e5 O
PRINT CHAR(9)+'UPDATE '+@TableName+' SET '+@strUpdate+CHAR(13)+CHAR(9)+@strWhere
7 m; B4 i  R7 c7 Y2 nPRINT '  END'
' ]( ^! `! C) E. R$ E$ K2 h6 M1 u--增加
; X9 t0 B7 q2 ]+ j: mPRINT 'ELSE IF (@INTUpdateID=1)'- o! @7 @, c5 M0 q* v5 M
PRINT '  BEGIN'% m: x+ x. f+ ?3 u/ r5 W; K
PRINT CHAR(9)+@strNewID3 r$ |: }: m! t- j! l% {6 J% x
PRINT CHAR(9)+'INSERT INTO '+@TableName+'  ('+REPLACE(@strInsert,'@','') +')  VALUES  ( '+@strInsert +')'6 i5 S; z. x) S  y. }6 _' `
PRINT '  END'1 |9 J1 m3 q2 a# }  L$ B: j
--删除
& y0 a# _. N+ P% xPRINT 'ELSE'
+ |/ T6 F4 _( s4 d+ IPRINT ' BEGIN'1 U3 T% n1 O) k! N- r/ G; `
PRINT CHAR(9)+'DELETE FROM '+@TableName +@strWhere
) s4 O+ r& {! `! p1 Y: {PRINT ' END'% A& E0 D: y1 a' A5 d9 U6 X% G
PRINT 'GO'* f7 u' L, @0 ?. `4 b$ s
GO# p& U1 O6 l$ l! k- E: O4 C1 s* y

. i+ k' ]! o' Q4 W[ 本帖最后由 DVD 于 2006-12-14 16:39 编辑 ]


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

绘画日历

设计原因:记得那时,周末没事干随便想到的,只是为了学习。% m; F" N: G* m( ^: w
设计方法:先构造一个月的日历图,再使用循环绘画1-12月的日历图。: [( S4 f: ^; l& }' L
/*
3 V0 ?2 w4 S7 o% H功能:绘画日历
2 O% Q; O2 V/ Q% H8 i5 }/ _- b设计:OK_008
6 N. u8 b, ^' K5 x" N时间:2006-05
  m. W2 m' ?+ o0 a*/* p) k  h# Y. t; e( K
DECLARE @Year nvarchar(4)" Y( ]  |8 K1 ^# C# l
DECLARE @YearMonth nvarchar(7)     --月份
0 J. S7 }0 b( ODECLARE @strTop nvarchar(200)
! f1 {8 z& i$ d! ?! gDECLARE @ForI INT,@ForYear INT ,@MaxDay INT
' P5 h6 b  ^, TDECLARE @RowX INT --行位置
! P* ?; P5 g! Q- K. M7 d' ~  aDECLARE @strWeekDayList nvarchar(20)7 n) @% z0 x$ ?; X
DECLARE @strPrint nvarchar(300)$ B6 H0 t( A. Y: T1 f- z
" c4 N" V1 R% Q* n$ ~! ]2 ?
-- ======================================
! X& i9 w( N8 c3 l3 ISET @Year='2006'       --请在这里输入年份
. j2 Q2 @1 q  V! [: {; |) w-- ======================================
+ Z+ H/ t+ {8 o& e! ?. A--设置日历上边的标题格式
$ ]- F0 Z1 `- ~8 y2 sSET @strTop= ''+char(9)+''+char(9)+'' +char(9)+''++char(9)+''++char(9)+''++char(9)+'' +char(13)+
2 K3 U+ h3 |0 ]8 ^, c$ g+ s7 P/ `              '───────────────────────────'- X, l! U  h5 ]' r: A; Z6 O! b
--设置星期列表9 i) b* G0 K2 G% B) [; Z) i4 P
SET @strWeekDayList='日一二三四五六'4 x! S, E. ^* Z% V' o
SET @ForYear=1- b! D* ~* |! |7 S
WHILE @ForYear<=12  --1月份至12月份
, Z2 K4 P6 w4 a7 ?4 n, fBEGIN
. Q. ~4 p9 G9 D8 R5 A' N         --取当月格式9 e/ T9 r# l3 y# j8 m
         SET @YearMonth=@Year + '-' +CAST( @ForYear AS nvarchar(2))   
8 W" a3 `9 t! d4 Y  l         --取当月的最大日期
+ y- s  A0 x& E* P+ H7 K         SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01')))      
1 u; G! ~; Q* I8 |& P7 n8 X         --找出1号的开始位置
$ F! R. ^# I+ A) k; R2 K% L, F- y/ i7 X         SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1, h' X! I4 h* c1 i: [% b
         SET @strPrint=''
7 ?% r& f- g$ b% q  r4 ?4 `         SET @ForI=1
; J: F' X: {4 f( ]* Y         WHILE @ForI<=@RowX   --构造1号的位置,并绘画空白处$ I9 r8 G  l) ~- X. u5 r+ a
          BEGIN5 G+ V: [7 `% V1 X2 @& d7 q
                   SET @strPrint=@strPrint+CHAR(9)* H0 F/ t# ?, n$ x
                   SET @ForI=@ForI+1
8 f7 s0 x, {( T6 E9 V: K          END
+ h5 N2 y  Y3 }1 @, Y0 G; c         SET @ForI=12 @$ U4 b! H% {, ?6 ], u" ]7 ?# J
         WHILE @ForI<=@MaxDay         --构造2号到月底的位置,并绘画, q. g* f, g. C, B1 p8 p& B
          BEGIN$ B/ I1 g6 a  }# F. j
                   SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9)   
+ D( S; `8 z3 m0 f4 B0 ^3 v5 X7 X" a                   SET @RowX=@RowX+1
# r3 @0 M/ t: H8 y, V0 @) d                   SET @ForI=@ForI+1
- J. e9 J; a. N+ ~3 B6 a                   IF (@RowX%7=0) --满一个星期就换行. C) ^# g; Q% t9 T; g0 S
                      BEGIN/ d' k8 n0 z: Y9 `3 ^# ?
                            SET @RowX=0
* U% H  b+ _. w# H1 K3 ^+ N                            SET @strPrint=@strPrint+CHAR(13)/ @8 ^  x0 r5 R+ {2 E5 [0 q
                   END& R$ a( @( b- ~4 V$ b) V8 L
            END
$ G& j" C  h6 a% M, W/ x) r2 g         SET @ForYear=@ForYear+1
! ^% e8 {; `: d$ e         -- 打印输出一个月的结果- Q. _+ p4 a5 V, r! Y% n+ I
         PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━'
7 b  E6 ~" U" h- [         PRINT +Char(9)++Char(9)+'    '+@YearMonth+CHAR(10)2 M, ?$ W. s$ e9 p* K" w1 e
         PRINT @strTop4 c! Y) g3 z. O# Y6 ~# _  }
         PRINT @strPrint +CHAR(10)
1 `. |: h, s* ]* {, ^4 |  END
* ]7 ^" H: Z8 h: S8 @9 d  J执行结果:1 k- M& c/ j. L) o" [

% a# D/ J7 f" r% }/ r' l1 /8 T$ J8 _" J8 d8 W; p. q

$ l( o9 p3 p+ T# R: N3 o8 }3 r[ 本帖最后由 DVD 于 2006-12-14 16:48 编辑 ]


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

一个金额转化的问题

设计原因:记得是有一客户的特别要求,开始有点晕,后来还是能做好。
/ X. U  j( l8 G. B/*
& J. H& p8 v; n, ]$ J7 @功能:把Money类型转换成nvarchar类型,保留三位小数,而且把数值后面多余的0去掉。
& D3 d9 s$ q; o& F- r      money类型的数值2.59100,转化成nvarchar(20)类型时,要求的结果是2.591
- {. K, q( t2 p      money类型的数值89.7800,转化成nvarchar(20)类型时,要求的结果是89.789 `% n7 a* t2 s
设计:OK_008
. T% h8 i( n5 Y7 ~时间:2006-07
/ s1 }/ `& _4 I0 a*/" ]5 N4 h) k" N9 a  [5 g
CREATE TABLE #Temp([ID] int ,F1 nvarchar(20),F2 nvarchar(20),SMoney  money,Dmoney money)
% C# p; Z8 ~+ z; WINSERT INTO #Temp SELECT 1,'F1','F2',45.895,23.89000: A7 x4 A, K7 j+ A& W1 k
UNION SELECT 2,'F2','F3',45.895,234.67. |" w" V: h1 b2 b
UNION SELECT 3,'F3','F4',25.835,32.1237 o$ S7 B1 {# R9 [; I
UNION SELECT 4,'F4','F5',13.7600,31.6754
5 x8 K/ G& e  [! p7 |! C+ E- n) g: B( aUNION SELECT 5,'F5','F6',34.783,78.345
; B  l% }$ U! V% }+ |/*
0 O' |/ Y* l5 m/ d) M* a要求的结果如下:1 ~* B- q2 ^; v1 V$ v1 X
SMoney +'X'+ Dmoney            
1 v2 A! [; A- H/ h: {, M  d------------------------------------------------------
$ l: X/ k$ d4 X) l45.895X23.89. r% m+ v0 b5 E: B
45.895X234.672 w6 T4 h* `7 u% E7 T% s9 F- F  h
25.835X32.123- f. g4 x' w4 E: m* ], s
13.76X31.675- G5 ?9 U1 W6 A, w
34.783X78.345$ k6 l6 g4 Y+ W6 w/ n
*/
! M1 r3 L5 Y/ q; d+ x" xSELECT *,cast(SMoney as nvarchar(20)) FROM  #Temp
$ o/ O+ E; U3 k5 K6 V8 G* P/*- j8 |* I4 l+ P. k
         经过3次的数据才可以把money类型的数据后边多余的零给去掉,
( E3 ]6 i0 [. i  }+ x8 i         money先转换成decimal,再转换成float,然后是nvarchar
% N" _# d$ z0 [# G5 Q0 b) C, o*/+ |9 @% L5 |" w- m1 M! D7 o
SELECT CAST(CAST(CAST(SmoneyAS decimal(20,3)) AS float) as nvarchar)+'X'+
" g2 W+ w/ G1 k4 v/ f) _) [' d9 X                   CAST(CAST(CAST(DmoneyAS decimal(20,3)) AS float)AS nvarchar)
0 ^* W9 p5 /  }$ S2 X/ m7 mFROM  #Temp
" C  c; ]# t( O7 F( Y8 j5 i- SDROP TABLE #Temp& d% y  T# M/ R. R. L, P/ y- z8 w$ i

! w( N& K. c' ^  b. N6 ]& B


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

多个变量赋值问题.

设计原因:记得在itpub上有一朋友问到这个问题,感觉顶有意思的,顺便拿过来。3 F- w; A  F& B. f3 u6 L5 n/ J
/*" i+ M2 U3 A9 ]: s# e- H
=================================9 }$ c' t9 }# L
原来的问题是这样的:
# z! N! [8 X7 ]- /. O=================================' Y, b! _) E' b! q4 G3 `, D$ F; I
现在的表结构是这样的
$ k4 Z# H/ y: A8 j5 J2 ^- HID ReferID ReferCount: C) ~4 /9 B/ ^0 r. s% E1 c
1 0 21 j; P$ _8 K& I# |7 u
2 1 NULL, t. e" d6 |' o5 k7 a: [
3 2 NULL, E" g- /  [. o( _" z
4 3 16 s2 A% m9 c6 S, m5 d

& N1 P7 R, w8 u, P5 |* E% B6 w然后声明四个变量
" K$ E# `% L, p* X( w: L& G  ldeclare @t1 int 1 Q7 J% S: b1 B/ d2 e
declare @t2 int
8 m- q1 x& o% Q: M4 pdeclare @t3 int, _% l# C& E7 h% G
declare @t4 int
# Y, D9 X- I4 V* O/ Z; R/ ~$ g! w# j" Y9 }9 N9 C/ q
然后使用四条记录中的ReferCount为值四个变量赋值) N5 m! {6 H  g8 ?, o* b8 S
问题是能不能使用一条sql语句完成,用case行不行的6 X) o; h1 @/ V, I3 j7 V
请高手们指教
5 R% _  v; }+ m: w1 M0 K# o*/) ~" W- X# g7 w& L
--解决方法:
3 R6 ^. ^# i" x  y2 O' f. _: R& N; K+ E* F. }7 o4 `% l8 c
CREATE TABLE #Temp(ID int,ReferID int,ReferCount int)0 f) y" P+ {5 }& O* S
INSERT INTO #Temp
1 S% P) |9 w& u( ~4 z7 mSELECT 1, 0, 2 UNION ALL! d. /% ?$ n& c0 ]" Z4 Z4 _( L
SELECT 2, 1, NULL UNION ALL
) `- p! E+ ?5 `( `" GSELECT 3, 2, NULL UNION ALL7 j$ Y$ q" w( d2 `. m$ M+ ?+ j
SELECT 4, 3, 1$ J$ ~+ `! M; W  V* ^- n9 Q" @
DECLARE @t1 int # d/ y$ T2 y8 v1 t* h
DECLARE @t2 int% d: n1 D! X- @
DECLARE @t3 int/ I9 C! U" o" Y- A$ h7 o
DECLARE @t4 int
, c- [1 c( R5 T* /+ z1 ^--根据ID的唯一性来查询
4 L  y4 R+ {2 t# f& ^$ S; N+ ~SELECT @t1=CASE ID WHEN 1 THEN ReferCount ELSE @t1 END
+ n+ O( L/ M. J8 J                   ,@t2=CASE ID WHEN 2 THEN ReferCount ELSE @t2 END
( B# J# s9 E% N2 w$ R4 q! v4 |                   ,@t3=CASE ID WHEN 3 THEN ReferCount ELSE @t3 END
0 ~7 S7 h3 l# R4 D8 ?" e                   ,@t4=CASE ID WHEN 4 THEN ReferCount ELSE @t4 END  q8 H8 t# Z* Y! d3 @1 n: ]
         FROM #Temp
: l/ _: G( P' D2 x% C# F6 U0 tSELECT @t1 AS '@t1',@t2 AS '@t2',@t3 AS '@t3',@t4 AS '@t4'
; P$ e; S2 p' d4 @: f! IDROP TABLE #Temp
! ^6 B9 Q# c7 X" A5 O/*
4 P3 Q& M: _! ?+ |/ F9 J运行结果:
  t5 t6 b) I& P7 q@t1|@t2|@t3|@t48 x2 H1 H( U* D$ w! r
--------------------
2 Y7 /" ^( U  D# o  j! Z5 z0 d2  |NULL|1 |NULL' R+ x7 y4 Q3 f4 @% /  U% i( r* M
*/


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

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

设计方法:先提取第1个星期一,然后就是循环+7。难点就是怎么样提取第1个星期一。; @5 y) u; q+ P9 b8 C& Z
/*
. s4 u! C% Z! o  i' W# V; r. @功能: 计算在某一段时间内某周几(如星期一)的所有日期
+ _% h5 I  O" M/ X' t$ X设计:OK_008
- m* |" Q: t( h0 w7 j( h时间:2006-10& C; {# t+ /) `. ^0 y
*/* c- w: U5 T& `! [0 j
DECLARE @Date datetime
5 ^, s9 m3 b, yDECLARE @StartDate datetime
: _) J- M. _! }) ?  y! zDECLARE @EndDate datetime
8 `8 i+ f6 U( L. {) A  NDECLARE @WeekDay int
8 e) h3 /  f; |1 p! U3 kDECLARE @i int
2 T( g+ Y4 t  P" ~# M
7 {. k& d) G% z3 e& D8 v% {0 J/ uSET DATEFIRST 7                       --设置每周的第一天 4 A0 h- S3 S3 l; |  ~" I
SET @StartDate='2006-01-01'       --统计的开始日期3 |; g4 J" [) c! _+ q
SET @EndDate='2006-12-31'        --统计的结束日期% `1 v0 o9 c* G( d: y& J4 M( L
SET @WeekDay=2                        --根据实际的@@DATEFIRST而定,一般默认是7,如 @StartDate='2006-01-01'时候, @WeekDay=3表示星期二
+ z% i% s# r. E! `7 R. Z: MSET @i=DATEPART(weekday,@StartDate), h; {+ I/ {. ^5 V1 K
PRINT '每周的第1天设置@@DATEFIRST:         '+CAST(@@DATEFIRST AS nvarchar(1))# K" U* V7 v1 [& B' |
PRINT '开始日期对应一周的第几天:            '+CAST(@i AS nvarchar(1))7 |; `7 y+ t* }/ e" ^+ u% i
IF(@i<=@WeekDay AND @i<7)8 s" F5 X) R% v! f. J
         SET @i=@WeekDay-@i      
4 /6 i; [, {9 W& N- P, vELSE IF(@i<=@WeekDay AND @i=7)
& I+ {8 N1 C2 Y" l2 x  p1 K- X         SET @i=@i-@WeekDay+ O8 @1 F: p$ K
ELSE
. N1 o; ~+ C; G9 j6 E/ ?  R         SET @i=@@DATEFIRST-@i+@WeekDay) F, ]2 W8 R2 n3 _

: e! F  F2 t+ v! Q# c4 s7 c% p4 l5 tSET @Date=DATEADD(day,@i,@StartDate); g$ G9 o% |# y) Q# p* w# c, ?
WHILE @Date<=@EndDate
4 z3 u' e" S) ~4 O) t1 B; [         BEGIN
( ]6 W9 R) e$ f0 S) M                   IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)( c8 I6 q; h  x# b- S2 f
                   SET @Date=DATEADD(Week,1,@Date)- p( b$ K1 U, W
         END
! n/ O7 I6 ?( r  R7 w- X! [GO5 }2 T1 r" z- ^& S/ K

: z! k1 w% @0 U* h# ]/*  ==============运行结果================*/' g3 d7 B# o/ [* g
/*
6 Z& S# Z: q9 ?% i每周的第1天设置@@DATEFIRST:    7
) c9 q8 S& W1 X' Z2 ~开始日期对应一周的第几天:                1
) A6 t: R9 j5 Y- E" ]# D2006-01-02, R9 Y; m4 ~8 C
2006-01-09
2 R) U5 @: J( ~1 U0 c) W... ...! B: w: c# h* o
2006-12-18
& X7 k# W! N- x' c) ~2006-12-25( @) S* p2 x- T$ B2 o2 z
*/


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

字符串的分割

/*
$ Z9 c) c4 S- ^) r# a0 B7 /- F功能:实现字符串的分割,达到批量提交数据参数和分批处理功能。
1 e+ j# [4 Y) A& W; z/ ?设计:wgh& }! L5 K$ u9 l+ Y& {
时间:2006-09% K: s/ ]. _  E' Y3 o) N+ f
*/
% D1 t/ Z* v: v1 k, F* p( ldeclare @str1 nvarchar(100) --要分割的字符串
. |+ h3 ~! h2 M+ c& Cdeclare @str2 nvarchar(100) --分割得的子字符串  u: a+ m1 `  p3 Z' |
declare @split nvarchar(40) --分割字符or字符串
" N( I5 A% B: M6 E) qdeclare @patIndex int       --分割字符or字符串第一次出现的起始位置. x& e+ ^2 A9 L% X! R$ P  m
set @split='★■->'
" V5 d3 j9 d) Z) e* nset @str1='334,345,''dfd'',★■->select * from Employee★■->45654DFG★■->452897★■->97887657★■->123445'( b) B6 _7 h" J
set @str2=''
, L% k, {+ I5 o. {6 y--从左至右分割
' f3 ~+ M" E' h/ U/ A* H; A! W% Twhile  @str1<>@str2  6 u  k" v2 O) /2 V  i1 j
begin+ w* O4 t$ z  V0 ~
  set @patIndex=patindex('%'+@split+'%',@str1) --patindex函数返回@str1@split第一次出现的起始位置# C% b6 z$ i, P4 [1 R) e
  if @patIndex>0 . Q) {& G4 /+ b9 Y
     set @str2=left(@str1,@patIndex-1) --截取@str1中以@split为分割的左边的字符串
4 w4 h# [3 G8 ]9 D3 D: }1 ~7 z$ P2 |  else0 C& E) v& u: ?4 /2 c- ]
     set @str2=@str18 y8 R7 c. f3 q  D7 h9 Q+ t9 s
  print @str2     --调试
/ U* D! W8 k( O4 /# u7 X5 [/*
1 }6 G; a3 p+ }  执行过程 ...., p- a6 p3 X. G/ B
*/6 v" M+ [2 ?: |
  if @patIndex>0  set @str1=right(@str1,len(@str1)-len(@str2)-len(@split)) --截断字符串,为的是下一次循环能取得,@str1@split第一次出现的起始位置
: j2 K1 /! j, F, Z, o# t+ Yend
6 E! ~& d! [9 p
0 M; K- h8 t) D6 G. L) |: k3 {0 K2 j


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

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

/ _: F. }+ e( M0 Q( ?
/*! c- a% D; X5 D' v- @
功能:查找所有(某一)数据库没有主鍵的所有用户表- ?$ a+ A5 P2 J8 y9 o
设计:wgh
, @# Z  ^. m8 m8 {' b) ]  Q2 E时间:2006-06
! o+ g/ D1 g# B- q6 x' q$ |*/# j) |! q. `' @- ~
/ m& Z0 d' y; }  _% u( d: ?
/*
; G1 @$ ]+ c5 @6 e设计方法:使用游标的方法,从系统表sysobjectssyscolumnssysindexes提取表的相关信息。8 Y9 k. W7 G( j' Z  ~* h: O% W
*/
4 z, Q4 p/ q: ^& G) x+ E2 b! u5 ?DECLARE @DatabaseName nvarchar(20)4 k8 H9 ?$ {" {2 p5 I
DECLARE @Execute_Sql nvarchar(4000)& j# P9 [1 p) B4 M3 L
SET @DatabaseName='ydhr' --要是要检查所有数据库就SET @DatabaseName=''$ F: D7 w. Q/ a& t* x3 v
CREATE table #Temp(DatabaseName nvarchar(20),TableName nvarchar(50))0 A; }0 l) |7 e! F9 A  ?; p
IF ISNULL(@DatabaseName,'')=''
! a% `  n& S7 q, I) G, {         DECLARE cursor_Sql CURSOR FOR! ^( y: S& Z" I" ~( ]/ N4 X
         SELECT name FROM master..sysdatabases WHERE dbid>=7  --dbid>=7 都是属于用户建立的数据库( r8 R, W8 }" @! C
ELSE* l! |" M0 |7 G
         DECLARE cursor_Sql CURSOR FOR
* d+ D9 m6 n+ P7 V* p# T4 b) i  R* n         SELECT name FROM master..sysdatabases WHERE dbid>=7 AND name=@DatabaseName
. W. S. W* `1 p6 gOPEN cursor_Sql
4 Q: h' B6 ~, ~% B1 p5 TFETCH NEXT FROM cursor_Sql INTO @DatabaseName
( O% @2 U+ k9 k( V0 o$ J: vWHILE @@FETCH_STATUS=0$ }4 K* }; p6 O2 ~) d  C
         BEGIN( V. |- _7 n/ X1 m/ b6 G
                   SET @Execute_Sql='INSERT INTO #Temp + S2 /  A; x$ I9 G$ d4 J/ {
                            SELECT '''+@DatabaseName+''',A.name FROM '+@DatabaseName+'..sysobjects AS A WHERE OBJECTPROPERTY(A.id, N''IsUserTable'') = 1 AND NOT EXISTS(. M  o! i  R1 `1 I, d
                                     SELECT 1 FROM '+@DatabaseName+'..sysindexes AS i INNER JOIN '+@DatabaseName+'..sysindexkeys k
  |; D3 Z) B! |                                     ON i.id = k.id AND i.indid = k.indid INNER JOIN '+@DatabaseName+'..sysobjects AS o
9 o4 ^, V; M' y  f: i! j  N                                     ON i.id = o.id INNER JOIN '+@DatabaseName+'..syscolumns c on i.id=c.id and k.colid = c.colid
- U' Y/ X& /) m                                     WHERE o.xtype = ''U'' AND A.id=i.id/ c% a: n( Y4 N* k6 W
                                               AND EXISTS(SELECT 1 FROM '+@DatabaseName+'..sysobjects WHERE xtype = ''PK'' AND name = i.name)) x  z; /+ _- F9 y8 X) [
                            )'
- [. v  {5 k, S, @# d6 P9 ?                   /*& L- ~: y. [( I* X* _
                   OBJECTPROPERTY()返回当前数据库中对象的有关信息,OBJECTPROPERTY(对象id, N''IsUserTable'') 表示是否为用户表8 G; j- }1 f( ?/ y) a* `
                   xtype='U' 在系统表sysobjects中表示查询的对象属于用户表,与OBJECTPROPERTY(对象id, N''IsUserTable'')功能一致,只是用法格式不同$ R" /9 e. q6 J5 z# E+ R  Y& s  t5 D
                   xtype='PK' PRIMARY KEY 约束(类型是 K) U6 Z; R- f# ?. ]2 z
                   */8 c, N2 f0 c" z& W8 e
                   EXECUTE(@Execute_Sql)8 ?9 Y- p& t$ W# H1 Y* ]
                   PRINT @Execute_Sql  --为了能更清楚语句的含义,我这里可以使用PRINT查看整条执行语句4 ]3 Y# _  u4 g/ V0 [+ R
                   FETCH NEXT FROM cursor_Sql INTO @DatabaseName
+ M+ d' U. O5 O' g: u         END
1 x" j$ H( c. gCLOSE cursor_Sql% t; Y" x- P" ?9 a% z% k
DEALLOCATE cursor_Sql* M8 o  e  M3 Z" o' E: Q
SELECT * FROM #Temp# g9 f7 g7 W3 O. /
DROP TABLE #Temp, n4 {  r- q. x0 H0 C" }: u

9 P8 `; s) F$ d' i


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

重命名数据库、表、列

重命名数据库、表、列名,使用系统存储过程sp_rename是最简单的了。
# w; n: j. C. }& j1 ]( ?0 h2 b0 N我们先来看看帮助文档的说明:" A+ k+ l0 v3 J. `. J
sp_rename+ w% U- F$ n  j' c
更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。
+ A) `" H+ R; d4 k3 g语法
  V; A2 i: f* s2 V% r+ e3 L. isp_rename [ @objname = ] 'object_name
' ,
) D/ N- u3 |' i9 B! v4 z" S( Z   
[ @newname = ] 'new_name'
9 c$ L; G2 u7 g" f  J* W    [ , [ @objtype = ] 'object_type' ]5 P, F1 k7 K/ @( X7 d

/ C2 u! }8 W. n@objname 对象名- {3 Z  J4 X; K
@objtype  对象类型
0 w2 b9 S0 S' k. f2 |6 T$ o1 G

描述

COLUMN) }9 T3 ^; q& X8 g$ B
要重命名的列。
* k, H" {- v( q7 c: b- O8 |" I
DATABASE
7 q, ]- t# R% D/ [) J
用户定义的数据库。要重命名数据库时需用此选项。
6 z( w3 T' ^* I( `* I/ y
INDEX) `+ v% U2 c( E9 F9 s
用户定义的索引。
8 v: y7 c  e) P$ s8 ^0 ^
OBJECT, /# l. a% b$ |/ }
sysobjects 中跟踪的类型的项目。例如,OBJECT 可用来重命名约束(CHECKFOREIGN KEYPRIMARY/UNIQUE KEY)、用户表、视图、存储过程、触发器和规则等对象。
  a% ^2 K+ S& R2 z9 O8 w$ f$ ^
USERDATATYPE
% w9 N. H4 K" /. d! E: O
通过执行 sp_addtype 而添加的用户定义数据类型。
- I1 i8 P+ ?1 ]/ w* g
+ Q7 K8 T0 ]! g7 x, h. s& Q
/* * Q6 {, Q- }7 q4 J: }2 f
功能:重命名数据库、表、列
+ L; k9 {( `) [( x9 M- x0 J& y0 g( A6 @ 编写: wgh
8 f& Y; V$ S; D: r) g9 ?( w 时间: 2006-11
( h) w: T' L. i */ 5 g& Y& L9 d/ _! Q  }, y/ I/ |7 c

  k1 b% /" h) Q/ {& w& h- x -- 重新命名数据库
: z, U) _' ]! y IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name='BBS')
6 G- T3 m$ B9 S          EXECUTE sp_rename @objname='BBS',@newname='BBS1',@objtype='DATABASE' / f9 H1 s* m4 w$ d- q% i! g

8 G- K; c( T! @" Y5 O0 [0 M2 V# }0 m -- 重新命名表名
( j5 Z0 |2 F6 R$ L- I) B5 `9 I$ J, h IF EXISTS(SELECT 1 FROM sysobjects WHERE name='bbs_log' AND OBJECTPROPERTY(id,'IsUserTable')=1)          2 X/ ?" i: H# S$ X1 q4 d. _
         EXECUTE sp_rename @objname='bbs_log',@newname='bbs_log1',@objtype='OBJECT' ) u# v; Y  O3 g! f+ Y

8 B! u" @+ K: E8 R% D& Y  g8 I- y -- 重新命名列名 9 e6 }( b! g0 `0 {
IF EXISTS(SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('bbs_log') AND name='ColumnName' AND OBJECTPROPERTY(id,'IsUserTable')=1)       & O7 Q1 T9 K) v& n+ D, m8 H3 k- N
         EXECUTE sp_rename @objname='bbs_log.[ColumnName]',@newname='NewColumnName',@objtype='COLUMN'


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

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

/*===================================================$ d- /- v( ]* g( B1 Y- b
   功能:格式化字符串 ,如输入01 自动生成 0000001 # Q, N. @# q3 [2 L- B, b
   备注:当位数达到10以上,要另写算法,否则出错
- T4 `2 l) ]$ z   设计:weiguohao
1 a5 i8 y0 B4 |9 V0 H   日期:2006-03-11
% ~/ v' ~1 M. k$ p1 f: w  ===================================================*/
* c0 P( N# O( j7 A- /4 MCREATE FUNCTION FORMAT_STR
' o3 ?  O8 {; |: /2 Q(@strX Nvarchar(20),@FormatLength INT)
! |2 d" h" M" _( gRETURNS NVARCHAR(20) AS  / x0 g& _3 Q4 S( t8 Y1 s% n
BEGIN 1 T  q9 U/ B! c# z& R/ g; l4 A0 z
DECLARE @Return NVARCHAR(20)* d* {6 ~+ M: A" s! Y3 S
DECLARE @Length INT. Q. D2 l2 _! ^
    SET @Length=LEN(@strX)
+ v5 a3 y" ~3 c: n2 }    IF @Length>=@FormatLength; S& |: v$ y! Z  o1 g
         SET @Return=@strX* @' a! D( A5 Z$ I
    ELSE. ?* a( T3 X3 J
        BEGIN. r, X1 O: M# G9 E& e- n6 ^
         -- 格式化,只要是调用到数学函数POWER,其他就不再说明,太简单了
7 ~! G0 ^, d: P7 P# h' c         SET @Return=CAST(RIGHT(POWER(10,@FormatLength),@FormatLength-@Length) AS NVARCHAR(20))+@strX
# z; /$ w( U7 n7 q6 z        END% }7 s3 D0 E8 e3 k7 h) s2 x: f1 _; C0 [& G
RETURN @Return
. X- r. i# u& a& tEND


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

一个厂家与销售商的查询问题问题来来自itpub论坛上的一位朋友,如下:' B" q. W4 w6 w
多个厂家多个销售商每个厂家对任一销售商都免费前10次的货款有厂家表,销售表,销售商表想求得销售商三个表拼合一起的视图视图中有个字段标出免费的销售记录?
& {" n, v' ~! T! ?3 }' T$ P8 c" W8 @2 x厂家表:ID ,名称销售表:ID,销售单号# X% a& p, z: y# X9 @. r
厂家:ID,销售商ID) p& n$ V% J; ], w& j+ C, n9 G5 c0 m
销售商:ID,销售商名称预得视图:销售单ID,厂家ID,销售商ID,是否免费谢谢哥哥姐姐了。帮忙写写…… * [% i5 i0 S( Q; ]* a: z% ^
解答:
- ]# B. g3 b" w6 O+ _CREATE TABLE #Suppliers(SupplierID int,CompanyName nvarchar(40)) --厂家表
2 H* c5 J. j9 K: g0 BCREATE TABLE #Sell(SellID int,SupplierID int,CustomerID int) --销售表
: E! I4 D6 }9 q& i! ~CREATE TABLE #Customers(CustomerID int,CompanyName nvarchar(40)) --销售商、客户表% _; L4 P% o* x' j' p- F. X! y8 Z8 Y
INSERT INTO #Suppliers) p8 R$ V5 X: Q2 w4 x/ e3 ~
SELECT 1,N'Supplier1' UNION ALL
1 U2 o  u, O; v" K5 tSELECT 2,N'Supplier2' / _/ w# C# q. L! R/ ]- I
INSERT INTO #Customers
3 }& P" f1 t' V9 w! |SELECT 1,N'Customer1' UNION ALL9 l* ?$ m8 B1 K- _' r
SELECT 2,N'Customer2'
. o2 ]5 q, r* {
INSERT INTO #Sell7 T. r) h, r0 T, I" i" D
SELECT 1,1,1 UNION ALL
7 X8 y5 o3 I+ e  w3 ^+ F! TSELECT 2,1,1 UNION ALL) v/ @  P/ w( p
SELECT 3,1,1 UNION ALL
" [- d9 {' y& q# v6 jSELECT 4,1,1 UNION ALL
% Y" J* ^' c- A7 q$ E$ m$ @6 M3 vSELECT 5,1,1 UNION ALL
! {8 h( /1 ~# V6 A: Q2 aSELECT 6,1,1 UNION ALL
! j5 h: B5 J8 {SELECT 7,1,1 UNION ALL$ Z- `& y/ T) S$ N0 w( K* r9 ^
SELECT 8,1,2 UNION ALL: f! _( I& l% J( s8 G
SELECT 9,1,2 UNION ALL
+ F( s+ t7 Y( g- a& s; J* rSELECT 10,1,2 UNION ALL2 |& X' Q/ O' P1 @$ k1 J
SELECT 11,1,2 UNION ALL7 `' V6 M! L2 @3 /% _: m* X
SELECT 12,1,2 UNION ALL! }7 L' u! ~* i. W8 w! d
SELECT 13,1,2 UNION ALL& q/ t$ A+ x7 G, M8 ^
SELECT 14,1,2 UNION ALL
# /% h- @% Y# S, k1 S3 e" y2 JSELECT 15,2,1 UNION ALL# w: ?/ w+ K5 L" q$ ^
SELECT 16,2,1 UNION ALL
0 Q4 |" F7 W7 i+ hSELECT 17,2,2 UNION ALL
2 Z* M; o) R( ]/ e7 w- K0 cSELECT 18,2,2 UNION ALL
% l# |4 s( S  |( y3 K5 G# {6 |/ wSELECT 18,2,2 , t8 n1 P+ @7 n
--每个厂家对任一销售商都免费前3次的货款(根据实际修改提前多少次是免费的货款)

  A& h5 l7 z2 {' d/ ]3 ?--主要是使用到一个GROUP BY ...HAVING ...在对数据进行分组和聚合后,就会用到 HAVING 子句中的条件) N" m2 z. y2 H3 i& y  Y8 ~
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
2 _, E' ]0 a# m! T/ l+ nDROP TABLE #Suppliers,#Sell,#Customers

8 J! Q2 P; R: d& ~' H- o. F! f" {' L 5 i" `+ ]$ F1 O2 H5 |" q
结果. H% ?' E" t; h) Z7 P* P/ _0 s  c

0 B9 Q8 W* Z# g0 J! |* x- a[ 本帖最后由 DVD 于 2006-12-14 17:01 编辑 ]


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

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


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

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

原来问题如下:
0 T4 d& o1 ^" z4 d! m" {如果有2个表T1aid,bid),T2bid,bname,bprice
7 L/ ]$ @& ]7 VT1
的字段表示顾客ID和商品ID,一个顾客可以买多种商品现在T1有记录:9 Z' G* ~4 k9 U
1,10
4 j! d( n- Q& d. k* u! I7 `* w' M* g1,11
. Z. W7 K9 g% v( B& x1,12/ Z* G6 a4 E' Y5 l8 e- z1 Q$ i/ D
2,101 b. c4 ]$ N' A3 c* D0 u, l- d
2,131 u3 N% ?5 `  [/ /- X+ v  B# v
T2
的字段表示商品ID,商品名称,商品价格现在T2有记录:
/ R5 ^. E$ v. N10,aaa,1( T8 ?4 Y9 t9 W, U, /* V7 W' V/ F+ R
11,bbb,2# g  ^+ Z/ k/ i" M- e& k
12,ccc,3( v6 g4 q$ j- d, d3 k- x
13,ddd,4  [- y. A$ Y/ ^' U1 }" I  g
现在要实现显示顾客买的商品名称和价钱即字段:
6 {" e9 G$ Q: V  l# baid,bname,bprice8 j$ {; {' _& I! ]: M( l) u9 y
但是同一个顾客只保留一个ID,其他为null请问怎么实现?即将上面的记录实现为:9 f0 q! D' x, C0 L
1,aaa,1
4 c7 _4 U2 ]& q% d,bbb,28 ]" v( Y. A$ v' J: F# f
,ccc,3
, O. I, u9 F2 d0 m2,aaa,1
( C% y# c7 W$ H" v  Q6 j1 t,ddd,4
0 i) c; b; i. {7 |
+ j4 _3 e4 r# l/ I$ [3 ~
如果不用游标有办法实现吗?. t/ E5 L$ z  q% T* F# p8 a- y
解决方法:" `: t6 G2 T/ v% v; t3 X' T
CREATE TABLE #T1(aid int ,bid int)
' G- R/ [* }( t. S7 ?0 O7 PINSERT INTO #T1# _2 j  S5 k4 w( D" Z! O6 U
SELECT 1,10 UNION ALL
/ q( u2 [  D# E! N1 Z# f2 gSELECT 1,11 UNION ALL
8 U, T* @" Y# S' gSELECT 1,12 UNION ALL* W3 H) x; c2 X4 a
SELECT 2,10 UNION ALL
2 j; D) V7 D/ ~2 {; ]6 M6 I/ @SELECT 2,13
; y# M0 |' z- M% J/ SCREATE TABLE #T2(bid int,bname nvarchar(10),bprice int)
! t3 w2 v. d* z" K) A$ k+ j/ Q& CINSERT INTO #T2
3 V+ /8 h: [& l! X4 i& p2 ^SELECT 10,'aaa',1 UNION ALL
& n  v2 T; U: V. b8 p, mSELECT 11,'bbb',2 UNION ALL
6 j7 F# _: m; x8 k0 qSELECT 12,'ccc',3 UNION ALL, o4 l& x  r% c8 q
SELECT 13,'ddd',4. P( z; E# K# a4 j
SELECT A.aid,B.bname,B.bprice INTO #T3 FROM #T1 AS A LEFT OUTER JOIN #T2 AS B ON A.bid=B.bid+ z) S. N. Q4 x' t% u- P7 O
DECLARE @aid int ,@lastaid int

! ]% t6 ]- Y- b, y, _7 L" LUPDATE #T3 SET @aid=(CASE WHEN ISNULL(@lastaid,'') =aid THEN NULL ELSE aid END),aid=@aid,@lastaid=aid% R' u5 w1 L$ b& R3 u" A2 ]$ k8 W6 G
SELECT * FROM #T3+ e' v! y  M5 l5 O) F( k1 r# G% i
DROP TABLE #T1,#T2,#T3

! m% p) R: S/ H- F$ q结果:
3 B% n) X/ v- @" R+ Y& /; d/*
+ K' E0 ?  g: P' N% ^% {$ b1 aaa 1& B8 z9 q% ^" e/ /% b  u
NULL bbb 2
, s0 p7 e# E- Q; H% bNULL ccc 3
& a% B1 _+ q: g; `0 o4 E/ o0 |2 aaa 1/ Z; C& t# H" Z8 @. `6 L
NULL ddd 4
; k& U- A# O) }) p( z, h6 t*/


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

一个数据替换问题


8 P5 C9 }& E" ~原来的问题:
6 A  G; s+ v$ H9 z1 |我要將([4000]+[3900])/([3100]+[3200]+[3900])*[3900]替换成

- V  r+ e/ D" x. R/ f/ a. `  J(0+0)/(0+0+0)*0
1 m# D0 d: F$ V: W( ^% ~
就是把[4000]樣的替換成0
* p& R2 W" s# x# N解决方法如下,' R# s1 m( P9 m! d2 x* /6 I
建立一个自定义函数:
* v0 d/ W4 s* ~1 K: o8 NCREATE FUNCTION CreateMathExpression$ b% Y  t$ p! d
(@ColumnName nvarchar(500))4 F  d' L5 C$ H4 Z* p' Q2 ^
RETURNS nvarchar(1000) AS
8 H1 t4 F* f5 X  G9 PBEGIN - W1 J$ h" S$ b+ t* D4 Z9 /
DECLARE @FieldValue nvarchar(20)& U% ^) n1 e8 w: /
DECLARE @Return nvarchar(1000)9 B* v( M2 t9 E7 k% H4 u
DECLARE @Sql nvarchar(1000)
4 k8 g. y& q, x, zDECLARE @Index int
* r- }! R; S  _, g; FDECLARE @End bit
: B! H4 c% @  H0 r" j5 ^* g$ lSET @End=0
2 J  E6 v. M  i! G) qSET @Return='': M  ^4 /; ]' B
--SET @ColumnName='[3900]/222.8*([3200]/[3900])'5 b0 w0 C* |% U" P
SET @Index=CHARINDEX('[',@ColumnName)/ E" h4 W  T& D, u
WHILE @Index<>09 M) x" r6 A9 l) @" L5 ?$ c
BEGIN
3 _! K2 k$ |+ C  FIF @End=0; [/ _8 F2 u5 _5 `( P, j
BEGIN. b! R9 Q$ I& `! N4 z6 i
SET @Index=CHARINDEX('[',@ColumnName)* z, F- a6 x5 g+ Z9 B6 S
IF @Index=0 GOTO Exit_While- t% p/ {. G: o% @: W" /7 w
SET @Return=@Return+LEFT(@ColumnName,@Index-1)
6 X0 Y: J6 q- j5 q& t5 ySET @End=1
$ c$ }: j8 {0 M: c) `" FEND
5 k3 R3 I/ w- q3 Z, M( ~ELSE
8 Q; P  S% R" e. S3 y; }* y- lBEGIN3 Q; z' G2 A  a
SET @Index=CHARINDEX(']',@ColumnName)
  g0 [* /$ p7 Y$ y% ASET @FieldValue=LEFT(@ColumnName,@Index-1)
; n) ~+ O  v9 U" w4 y9 O
7 e7 o2 W5 R0 l' P7 X8 A  m9 N, JSET @Return=@Return+ '0'8 n  S9 o! j% }; ?3 K  r+ O
SET @End=0
+ ~8 C4 u; D* W+ HEND+ X- Q. x  P! R5 ~+ I5 Z3 a
SET @ColumnName=RIGHT(@ColumnName,len(@ColumnName)-@Index)6 e- }  o! j2 I3 H
END
( o! `( /7 }6 kExit_While:* `* O( z4 T  e) {9 W& O4 y: H
SET @Return=@Return+@ColumnName
7 V- g0 U6 p) Q# c0 I( s* Q" N4 O9 YRETURN @Return
0 [7 /6 T0 k5 P( S) t$ rEND
- T! Q, Y4 |# r* ^1 F
--------------------------------------------------------------------例子:DECLARE @N nvarchar(200)) O/ e/ U& B& j5 A
SET @N='([4000]+[3900])/([3100]+[3200]+[3900])*[3900]'
0 S' Q8 d. n- d2 S5 n, aSELECT @N
7 ?9 _! R2 i, V  h* y8 v8 B# N* hSELECT dbo.CreateMathExpression(@N)& D0 I% T3 l# }2 e/ K/ |
-------------------------结果:' K, S$ s+ w9 e. E9 r+ Q* c
(0+0)/(0+0+0)*0

; e  d2 U% d1 @. K  |备注:函数中的一些代码是别的用途,可以忽略。


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

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


8 S; e" N: ?# sBBS上看到一朋友问到这样的一个问题:
; G- i# I( i) T9 ~6 R1 /求助:向表中一列插入1-100的连续数
+ j: ^/ @" ^$ G( ^- l; j. I* c# l# U! j1 T! W
table A
中有BH字段(INT),怎么连续给BH赋值1-100,象下面的一样
! u& {6 g' D$ T3 a$ L4 M. `" q+ s$ ^& q7 x
BH % M, w3 l6 A0 n
------------------------------------------------
, @$ Q2 e. D- R# B1
7 [  c. K7 q3 g2 H! O: v28 B5 ^) h0 ?$ `3 _. b: ^" I
., B. o0 ~5 [  U
.
9 O3 ?9 K/ q7 K; R.  j+ P& j! {  x( U( R# n
100
  f7 j9 s( {" J1 u
根据常规的方法,我们会想到使用While来实现以上的功能。如,一朋友所写的这样:& G& c; j$ w& F& H* d
CREATE TABLE TestTable(BH INT)( Y) r! o8 v# _4 x
GO
; l" ~2 E8 T2 F3 ?) V5 E5 z% ~SET NOCOUNT ON
- P' R, f% h/ ^& v7 p1 L
/*' B1 O% _: D3 G- S6 ]! K9 f
SET NOCOUNT { ON | OFF } SET NOCOUNT ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。当 SET NOCOUNT OFF 时,返回计数
+ K# I. X* B, X" P9 F: F7 e" T*/7 @3 i- a/ x9 E: W$ v; R
GO
- d! [: R( Q; eDECLARE @MyCounter INT, U/ m: H/ w5 U, S& |8 b
SET @MyCounter = 15 o$ E# e; E6 e" E; B
WHILE (@MyCounter < 101)! J  /4 W9 Z# h, P, Y/ o. [
BEGIN
8 N0 @4 H2 S" _) z0 L; q; A: N7 v! K/ IINSERT INTO TestTable VALUES8 r% l! D, Z' C2 G! |2 V

! y! O# ~4 T- W$ o/ g- k2 W) z7 S* m(@MyCounter
6 e* D' p& a' k' h' D! t! y)! H: K& I& ]* I" I9 e' _" [( w6 a
  K7 l1 |4 a( w9 q0 W' [/ ~+ @7 {
SET @MyCounter = @MyCounter + 1, /# ]: N4 J- |
END
7 W: l/ {& w5 O0 KGO
6 k) M/ L* O" k( @% V: A( ^SET NOCOUNT OFF
# c  Y4 M) Q1 D+ v% B3 KGO
6 D# F. W! Z! U! }select * from TestTable
- S. t+ h: /2 c3 r) V. F--drop table TestTable
# n+ p1 z/ o9 H% e7 |5 u. u" M
我看一下,突然想到一个使用变量的方法来实现,如下:
0 O9 }5 m, N7 y6 i" [3 y
DECLARE @i int! {1 f, s2 Z9 i4 n
CREATE TABLE #Temp ([id] int, C1 nvarchar(20),BH int)
8 L* r9 F' v$ zSET @i=400
' A" y$ R8 s8 UWHILE @i<=500# I% K5 {7 Y4 V0 G6 l, f6 j( N
BEGIN: z9 t  u% m. r# f' v' A
INSERT INTO #Temp SELECT @i,CAST(@i+rand() as nvarchar(20)),null5 x& |! E  _3 |  s( ?
SET @i=@i+14 ]+ Y8 P: }" B. R8 m; m! I3 Q
END
% q  t+ ^. t/ `, m5 s6 JSELECT * FROM #Temp3 ^; T. W" @& y
--更新字段BH,更新行数为前100
! t: F+ n$ _- K: p) o" BSET @i=0
- p+ M! m8 @, K1 _. y  e1 M--@i相当于For循环中的i,每更新一条记录设置加@i=@i+1" Z" G- J2 F" R
UPDATE #Temp SET @i=@i+1 ,BH=@i WHERE [id] IN(SELECT TOP 100 [id] FROM #Temp)7 {, p# h# f3 ~- R) o
SELECT * FROM #Temp
$ I- k* y  M4 P8 s" D$ aDROP TABLE #Temp

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页