要得到这个结果 SQL 语句该如何写(100分)

  • 主题发起人 主题发起人 zl555
  • 开始时间 开始时间
Z

zl555

Unregistered / Unconfirmed
GUEST, unregistred user!
有一个表 有两个字段 FP,HM 都是字符型的字段<br>FP &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HM<br>8296553 &nbsp; &nbsp; &nbsp; &nbsp; 3401-20080827001-0001<br>8296553 &nbsp; &nbsp; &nbsp; &nbsp; 3401-20080827001-0001<br>8296552 &nbsp; &nbsp; &nbsp; &nbsp; 3401-20080827001-0001<br>8296551 &nbsp; &nbsp; &nbsp; &nbsp; 3401-20080827002-0002<br>我现在要得到下面这个结果,SQL语句该如何写呢?<br>FP &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HM<br>8296553,8296552 &nbsp; &nbsp; &nbsp; &nbsp; 3401-20080827001-0001<br>8296551 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3401-20080827002-0002
 
按hm分组不可以吗
 
如果是SQL Server, 可以先建一个自定义函数:<br>CREATE Function dbo.F_GetFPs(@HM nvarchar(30)) Returns nvarchar(8000) <br>As &nbsp;<br>Begin &nbsp;<br>Declare @S nvarchar(8000) &nbsp;<br>Set @S='' &nbsp;<br><br>Select @S=rtrim(@S)+','+ Q.[FP] From (<br>SELECT DISTINCT [FP] FROM [myTableName] WHERE ([HM] =@HM)<br>)Q<br>Return Stuff(@S,1,1,'') <br><br>end<br><br>然后一个很简单的查询:<br>Select dbo.F_GetFPs([HM]) as [FP], [HM] From [myTableName]<br>Group By [HM]
 
HM如果要是一个临时的表结构怎么用SQL实现,这个问题也困了我很长时间了,不过可以使用DISTINCT参数试试能不能达到你想要的结果。
 
后退
顶部