看看这个 select t1,max(t2),t3=min(t2) from aa a group by t1
或者select identity(int,1,1)as id,t1,t2,t3=(select t2 from aa where t1=a.t1 and t2!=a.t2) into #a from aa a select t1,t2,t3 from #a a where id in(select min(id) from #a where t1=a.t1)
如果房号是很多 不是两个select t1,max(t2),t3=min(t2) from aa a group by t1 就实现不了
谢谢Allen_Chen_ 以前跟本不直到的东西 证号可以学习学习
-- 创建合并函数fn_strSum,根据楼层合并房间号 CREATE FUNCTION dbo.fn_strSum(@楼层 int) RETURNS varchar(8000) AS BEGIN DECLARE @房间号 varchar(8000) SET @房间号 = '' SELECT @房间号 = @房间号 + ' ' + 房间号 FROM tb2 WHERE 楼层=@楼层 RETURN STUFF(@房间号, 1, 1, '') END GO-- 调用函数 SELECT 楼层, 房间号 = dbo.fn_strSum(楼层) FROM tb2 GROUP BY 楼层
select t1,max(t2),t3=min(t2) from aa a group by t1
-- 创建合并函数fn_strSum,根据楼层合并房间号
CREATE FUNCTION dbo.fn_strSum(@楼层 int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @房间号 varchar(8000)
SET @房间号 = ''
SELECT @房间号 = @房间号 + ' ' + 房间号 FROM tb2 WHERE 楼层=@楼层
RETURN STUFF(@房间号, 1, 1, '')
END
GO-- 调用函数
SELECT 楼层, 房间号 = dbo.fn_strSum(楼层) FROM tb2 GROUP BY 楼层