使用完整外部联接
若要通过在联接结果中包括不匹配的行保留不匹配信息，请使用完整外部联接。Microsoft® SQL Server™ 2000 提供完整外部联接运算符 FULL OUTER JOIN，不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet 和 Cheryl Carson）。SQL-92 FULL OUTER JOIN 运算符指明：不管表中是否有匹配的数据，结果将包括两个表中的所有行。
若要在结果中包括所有作者和出版商，而不管城市中是否有出版商或者出版商是否住在同一个城市，请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果：
USE pubs
Select a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
orDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
下面是结果集：
au_fname au_lname pub_name
-------------------- ---------------------------- --------------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
access 与 VBA
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books
(30 row(s) affected)
金额阿拉伯数字转换为中文的存储过程
Private Function CCh(N1) As String
Select Case N1
Case 0
CCh = "零"
Case 1
CCh = "壹"
Case 2
CCh = "贰"
Case 3
CCh = "叁"
Case 4
CCh = "肆"
Case 5
CCh = "伍"
Case 6
CCh = "陆"
Case 7
CCh = "柒"
Case 8
CCh = "捌"
Case 9
CCh = "玖"
End Select
End Function
'名称： ChMoney
'得到数字 N1 的汉字大写
'最大为 千万位
'O 返回 ""
Public Function ChMoney(N1) As String
Dim tMoney As String
Dim lMoney As String
Dim tn '小数位置
access 与 VBA
Dim s1 As String '临时STRING 小数部分
Dim s2 As String '1000 以内
Dim s3 As String '10000
If N1 = 0 Then
ChMoney = " "
Exit Function
End If
If N1 < 0 Then
ChMoney = "负" + ChMoney(Abs(N1))
Exit Function
End If
tMoney = Trim(Str(N1))
tn = InStr(tMoney, ".") '小数位置
s1 = ""
If tn <> 0 Then
ST1 = Right(tMoney, Len(tMoney) - tn)
If ST1 <> "" Then
t1 = Left(ST1, 1)
ST1 = Right(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s1 = s1 + CCh(Val(t1)) + "角"
End If
If ST1 <> "" Then
t1 = Left(ST1, 1)
s1 = s1 + CCh(Val(t1)) + "分"
End If
End If
ST1 = Left(tMoney, tn - 1)
Else
ST1 = tMoney
End If
s2 = ""
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
s2 = CCh(Val(t1)) + s2
End If
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s2 = CCh(Val(t1)) + "拾" + s2
Else
If Left(s2, 1) <> "零" Then s2 = "零" + s2
End If
End If
If ST1 <> "" Then
t1 = Right(ST1, 1)
access 与 VBA
ST1 = Left(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s2 = CCh(Val(t1)) + "佰" + s2
Else
If Left(s2, 1) <> "零" Then s2 = "零" + s2
End If
End If
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s2 = CCh(Val(t1)) + "仟" + s2
Else
If Left(s2, 1) <> "零" Then s2 = "零" + s2
End If
End If
s3 = ""
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
s3 = CCh(Val(t1)) + s3
End If
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s3 = CCh(Val(t1)) + "拾" + s3
Else
If Left(s3, 1) <> "零" Then s3 = "零" + s3
End If
End If
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s3 = CCh(Val(t1)) + "佰" + s3
Else
If Left(s3, 1) <> "零" Then s3 = "零" + s3
End If
End If
If ST1 <> "" Then
t1 = Right(ST1, 1)
ST1 = Left(ST1, Len(ST1) - 1)
If t1 <> "0" Then
s3 = CCh(Val(t1)) + "仟" + s3
End If
End If
If Right(s2, 1) = "零" Then s2 = Left(s2, Len(s2) - 1)
access 与 VBA
If Len(s3) > 0 Then
If Right(s3, 1) = "零" Then s3 = Left(s3, Len(s3) - 1)
s3 = s3 & "万"
End If
ChMoney = IIf(s3 & s2 = "", s1, s3 & s2 & "元" & s1)
End Function
金额阿拉伯数字转换为中文的存储过程
也谈此内容。
以下同:
Private Function Num2Char(ByVal i As Integer) As String
If i >= 0 And i <= 9 Then
Num2Char = Mid$("零壹贰叁肆伍陆柒捌玖", i + 1, 1)
Else
Num2Char = ""
End If
End Function
Private Function Num2RMB(ByVal sFourBitString As String, Optional _
ByVal sUnit As String = "元", Optional ByVal bMustHeader As _
Boolean = False) As String
'----------------------------------------------------------------------
Dim vNum, i, RX, BR, hdr
'------------------------------------------------------------------
BR = "仟佰拾元"
'------------------------------------------------------------------
vNum = Trim(Str(Val(sFourBitString))) ' 最多四位
'------------------------------------------------------------------
If (Len(vNum) < 4 And Len(vNum) > 0) And bMustHeader Then hdr = "零" _
Else hdr = ""
RX = ""
Do While Len(vNum) > 0
i = Right(vNum, 1)
If i > 0 Then
RX = Num2Char(i) + Right(BR, 1) + RX
Else
If Left(RX, 1) <> "零" Then RX = "零" + RX
End If
vNum = Left(vNum, Len(vNum) - 1)
BR = Left(BR, Len(BR) - 1)
Loop
RX = Left(RX, Len(RX) - 1)
If Right(RX, 1) = "零" Then ' 去除多余的零
RX = Left(RX, Len(RX) - 1)
End If
If Len(RX) > 0 Then
Num2RMB = hdr + RX + sUnit
Else
Num2RMB = RX + IIf(sUnit = "元", "元", "")
End If
access 与 VBA
End Function
Function GetDXJE(ByVal num As Currency) As String ' 得到大写金额
'----------------------------------------------------------------------
Dim vNum, vDec, ret, qb
'------------------------------------------------------------------
vNum = Right(Format(Int(num), "000000000000"), 12) ' 取十二位整数
vDec = Right(Format(Int(num * 100 + 0.5), "00"), 2) ' 取小数点后两位并自动四舍五入
'------------------------------------------------------------------
ret = Num2RMB(Left(vNum, 4), "亿", False)
If Len(ret) = 0 Then
ret = Num2RMB(Mid(vNum, 5, 4), "万", False)
Else
ret = ret + Num2RMB(Mid(vNum, 5, 4), "万", True)
End If
If Len(ret) = 0 Then
ret = Num2RMB(Right(vNum, 4), "元", False)
Else
ret = ret + Num2RMB(Right(vNum, 4), "元", True)
End If
'------------------------------------------------------------------
If ret = "元" Then
ret = ""
qb = ""
Else
qb = "xx"
End If
'------------------------------------------------------------------
If vDec = "00" And qb <> "" Then '1.00
ret = ret + "整"
End If
If vDec = "00" And qb = "" Then '0.00
ret = "(无金额)"
End If
If Left(vDec, 1) <> "0" And Right(vDec, 1) = 0 And qb <> "" Then '1.20
ret = ret + Num2Char(Left(vDec, 1)) + "角整"
End If
If Left(vDec, 1) = "0" And Right(vDec, 1) <> 0 And qb <> "" Then '1.03
ret = ret + "零" + Num2Char(Right(vDec, 1)) + "分"
End If
If Left(vDec, 1) <> "0" And Right(vDec, 1) <> 0 And qb <> "" Then '1.23
ret = ret + Num2Char(Left(vDec, 1)) + "角" + Num2Char(Right(vDec, 1)) + "分"
End If
If Left(vDec, 1) <> "0" And Right(vDec, 1) = 0 And qb = "" Then '0.20
ret = Num2Char(Left(vDec, 1)) + "角整"
End If
If Left(vDec, 1) = "0" And Right(vDec, 1) <> 0 And qb = "" Then '0.03
ret = Num2Char(Right(vDec, 1)) + "分"
End If
access 与 VBA
If Left(vDec, 1) <> "0" And Right(vDec, 1) <> 0 And qb = "" Then '0.23
ret = Num2Char(Left(vDec, 1)) + "角" + Num2Char(Right(vDec, 1)) + "分"
End If
GetDXJE = ret
'----------------------------------------------------------------------
End Function
