一个常见Sql问题的解决
很多时候我们都需要实现如下的功能:
表TableM
结构如下:
数据如下:
表TableD
结构如下:
![](http://p.blog.csdn.net/images/p_blog_csdn_net/SysBug/12.gif)
数据如下:
需要实现的功能如下:
具体的就不说了,大家仔细看了这几张图就知道实现什么了^-^
实现起来最直接方法就是用游标了,而且是嵌套游标,这个当然效率相当低了,不过随后会给出更好的实现方法^-^
这个方法需要建立一个临时表TableResult,它只有一个字段AllStr!!
SQL代码如下:
--方法一:嵌套游标
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--表名 字段名
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--TableD AStr BStr CStr
--TableM AStr BStr
--TableResult AllStr
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--符合条件的数据放到表TheSameRecord
Select TableD.AStr,TableD.BStr,TableD.CStr
Into TheSameRecord
From TableM,TableD
Where TableM.AStr = TableD.AStr And TableM.BStr = TableD.BStr
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Declare @AStr VarChar(50)
Declare @BStr VarChar(50)
Declare @CStr VarChar(50)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Declare @AllCStr VarChar(500)
Set @AllCStr = ''
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Declare @iNext Bit
Set @iNext = 0
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--外层游标
Declare Outer_Cursor Cursor For
Select Distinct AStr,BStr From TheSameRecord
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Open Outer_Cursor
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Fetch Next From Outer_Cursor Into @AStr, @BStr
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
While @@Fetch_Status = 0
Begin
--内层游标
Declare Inner_Cursor Cursor For
Select CStr From TheSameRecord Where AStr = @AStr And BStr = @BStr
Set @AllCStr = ''
Open Inner_Cursor
Fetch Next From Inner_Cursor Into @CStr
While @@Fetch_Status = 0
Begin
Set @AllCStr = @AllCStr + @CStr + ','
Fetch Next From Inner_Cursor Into @CStr
End
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
if (@iNext = 0)
Begin
Delete TableResult
Set @iNext = 1
End
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Insert Into TableResult (AllStr) Values (SubString( @AllCStr,1, Len(@AllCStr)-1))
Print SubString( @AllCStr,1, Len(@AllCStr)-1 )
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Close Inner_Cursor
DealLocate Inner_Cursor
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Fetch Next From Outer_Cursor Into @AStr, @BStr
End
Close Outer_Cursor
DealLocate Outer_Cursor
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--删除表TheSameRecord
Drop Table TheSameRecord
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Select * From TableResult
结果如下:
![](http://p.blog.csdn.net/images/p_blog_csdn_net/SysBug/14.gif)
但是马上又想到了另外一种方法实现,因为函数本身就可以对数据进行循环,那么如果用函数来实现的话,
一则效率高,
二则代码量少
那么就有如下实现了:
--方法二:函数
Create Function AddStr(@AStr VarChar(50), @BStr VarChar(50))
Returns VarChar(8000)
As
Begin
Declare @ALLStr Varchar(8000)
Set @ALLStr=''
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Select @ALLStr = @ALLStr + ',' + Cast(CStr As VarChar) From TheSameRecord
Where AStr = @AStr And BStr = @BStr
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Set @ALLStr = Right(@ALLStr,Len(@ALLStr)-1)
Return(@ALLStr)
End
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Go
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Select TableD.AStr,TableD.BStr,TableD.CStr
Into TheSameRecord
From TableM,TableD
Where TableM.AStr = TableD.AStr And TableM.BStr = TableD.BStr
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Go
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Select Distinct AStr,BStr,dbo.AddStr(AStr,BStr) As AllStr From TheSameRecord
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
Drop Table TheSameRecord
结果如下:
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1420050