一个常见Sql问题的解决
很多时候我们都需要实现如下的功能:
表TableM
结构如下:
数据如下:
表TableD
结构如下:
数据如下:
需要实现的功能如下:
具体的就不说了,大家仔细看了这几张图就知道实现什么了^-^
实现起来最直接方法就是用游标了,而且是嵌套游标,这个当然效率相当低了,不过随后会给出更好的实现方法^-^
这个方法需要建立一个临时表TableResult,它只有一个字段AllStr!!
SQL代码如下:
-- 表名 字段名
-- TableD AStr BStr CStr
-- TableM AStr BStr
-- TableResult AllStr
-- 符合条件的数据放到表TheSameRecord
Select TableD.AStr,TableD.BStr,TableD.CStr
Into TheSameRecord
From TableM,TableD
Where TableM.AStr = TableD.AStr And TableM.BStr = TableD.BStr
Declare @AStr VarChar ( 50 )
Declare @BStr VarChar ( 50 )
Declare @CStr VarChar ( 50 )
Declare @AllCStr VarChar ( 500 )
Set @AllCStr = ''
Declare @iNext Bit
Set @iNext = 0
-- 外层游标
Declare Outer_Cursor Cursor For
Select Distinct AStr,BStr From TheSameRecord
Open Outer_Cursor
Fetch Next From Outer_Cursor Into @AStr , @BStr
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
if ( @iNext = 0 )
Begin
Delete TableResult
Set @iNext = 1
End
Insert Into TableResult (AllStr) Values ( SubString ( @AllCStr , 1 , Len ( @AllCStr ) - 1 ))
Print SubString ( @AllCStr , 1 , Len ( @AllCStr ) - 1 )
Close Inner_Cursor
DealLocate Inner_Cursor
Fetch Next From Outer_Cursor Into @AStr , @BStr
End
Close Outer_Cursor
DealLocate Outer_Cursor
-- 删除表TheSameRecord
Drop Table TheSameRecord
Select * From TableResult
结果如下:
Create Function AddStr( @AStr VarChar ( 50 ), @BStr VarChar ( 50 ))
Returns VarChar ( 8000 )
As
Begin
Declare @ALLStr Varchar ( 8000 )
Set @ALLStr = ''
Select @ALLStr = @ALLStr + ' , ' + Cast (CStr As VarChar ) From TheSameRecord
Where AStr = @AStr And BStr = @BStr
Set @ALLStr = Right ( @ALLStr , Len ( @ALLStr ) - 1 )
Return ( @ALLStr )
End
Go
Select TableD.AStr,TableD.BStr,TableD.CStr
Into TheSameRecord
From TableM,TableD
Where TableM.AStr = TableD.AStr And TableM.BStr = TableD.BStr
Go
Select Distinct AStr,BStr,dbo.AddStr(AStr,BStr) As AllStr From TheSameRecord
Drop Table TheSameRecord
结果如下: