sql sever 数据列的合并问题

[gaof_lee]

select Name, 
[values]=stuff((select ','+HOUSESID from tb t where HOUSESID=tb.HOUSESID for xml path('')), 1, 1, '')
from
 (SELECT
 t2.ID,
t2.Name,
t1.ID HOUSESID 
FROM Houses t1 
left join Residential t2 on t1.ResidentialID = t2.ID  
where ISNUMERIC(t1.sellPrice)=1 
and  AreaName is not null) tb
group by Name

上边是一个对数据做了个分组和合并HOUSESID 列的sql语句，tb 是一个结果的子查询。
运行后提示“对象名 'tb' 无效。”错误。
我想问下，这种写法是不是没法在from时加个子查询？
如果不能改怎么解决呢？
下边就是tb做的那个子查询结果

[gongjian0628]

@DBA_Huangzj 哪里都能看到你的回复！

[gaof_lee]

引用 1 楼 DBA_Huangzj 的回复:

试试：

;
WITH    tb
          AS ( SELECT   t2.ID ,
                        t2.Name ,
                        t1.ID HOUSESID
               FROM     Houses t1
                        LEFT JOIN Residential t2 ON t1.ResidentialID = t2.ID
               WHERE    ISNUMERIC(t1.sellPrice) = 1
                        AND AreaName IS NOT NULL
             )
    SELECT  Name ,
            [values] = STUFF(( SELECT   ',' + HOUSESID
                               FROM     tb t
                               WHERE    HOUSESID = tb.HOUSESID
                             FOR
                               XML PATH('')
                             ), 1, 1, '')
    FROM    tb
    GROUP BY Name

非常感谢你的回答，问题已经解决了，新年快乐啊。

[gaof_lee]

引用 3 楼 yupeigu 的回复:

你上面的代码之所以会报错，是因为tb是一个子查询，用一次就得重新写一次，而你在stuff((select ','+HOUSESID from tb t where HOUSESID=tb.HOUSESID for xml path('')), 1, 1, '') 中直接引用了子查询，才报错的。 如果你用的是2005及以上版本的，可以考虑用with表达式来写，就定义一次，多次太用。

非常感谢啊

[發糞塗牆]

试试：

;
WITH    tb
          AS ( SELECT   t2.ID ,
                        t2.Name ,
                        t1.ID HOUSESID
               FROM     Houses t1
                        LEFT JOIN Residential t2 ON t1.ResidentialID = t2.ID
               WHERE    ISNUMERIC(t1.sellPrice) = 1
                        AND AreaName IS NOT NULL
             )
    SELECT  Name ,
            [values] = STUFF(( SELECT   ',' + HOUSESID
                               FROM     tb t
                               WHERE    HOUSESID = tb.HOUSESID
                             FOR
                               XML PATH('')
                             ), 1, 1, '')
    FROM    tb
    GROUP BY Name

[LongRui888]

改成这样试试：

select Name, 
[values]=stuff((select ','+HOUSESID FROM Houses t1 
				left join Residential t2 on t1.ResidentialID = t2.ID  
				where ISNUMERIC(t1.sellPrice)=1 
				and  AreaName is not null t
				and HOUSESID=tb.HOUSESID for xml path('')), 1, 1, '')
from
 (SELECT
 t2.ID,
t2.Name,
t1.ID HOUSESID 
FROM Houses t1 
left join Residential t2 on t1.ResidentialID = t2.ID  
where ISNUMERIC(t1.sellPrice)=1 
and  AreaName is not null
) tb
group by Name

[LongRui888]

你上面的代码之所以会报错，是因为tb是一个子查询，用一次就得重新写一次，而你在stuff((select ','+HOUSESID from tb t where HOUSESID=tb.HOUSESID for xml path('')), 1, 1, '') 中直接引用了子查询，才报错的。 如果你用的是2005及以上版本的，可以考虑用with表达式来写，就定义一次，多次太用。

[發糞塗牆]

还可以这样，不过不够cte直观

SELECT  Name ,
        [values] = STUFF(( SELECT   ',' + HOUSESID
                           FROM     ( SELECT    t2.ID ,
                                                t2.Name ,
                                                t1.ID HOUSESID
                                      FROM      Houses t1
                                                LEFT JOIN Residential t2 ON t1.ResidentialID = t2.ID
                                      WHERE     ISNUMERIC(t1.sellPrice) = 1
                                                AND AreaName IS NOT NULL
                                    ) t
                           WHERE    HOUSESID = tb.HOUSESID
                         FOR
                           XML PATH('')
                         ), 1, 1, '')
FROM    ( SELECT    t2.ID ,
                    t2.Name ,
                    t1.ID HOUSESID
          FROM      Houses t1
                    LEFT JOIN Residential t2 ON t1.ResidentialID = t2.ID
          WHERE     ISNUMERIC(t1.sellPrice) = 1
                    AND AreaName IS NOT NULL
        ) tb
GROUP BY Name