求sql语句

[柴九小哥]

有a表,b表
循环a表指定条目，如读取a表10条数据
读取a表10条数据的时候，同时显示出B表关联a表的id 其实就是个评论盖楼功能。

如下图


效果



测试过一种方法，循环a表的时候，根据a表的id 再次循环b表的aid显示，但性能就不说了，太差，

用left join on 无法实现
所有请高手们指点下。分数有限，不好意思，全给了。

给出sql语句，或方法，不影响性能的情况、

[柴九小哥]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([id] int,[text] nvarchar(22))
Insert #a
select 1,N'aa' union all
select 2,N'bb' union all
select 3,N'cc' union all
select 4,N'dd'
GO
if not object_id(N'Tempdb..#b') is null
	drop table #b
Go
Create table #b([id] int,[aid] int,[text] nvarchar(22))
Insert #b
select 1,1,N'd1' union all
select 2,1,N'd2' union all
select 3,1,N'd3' union all
select 4,2,N'dd'
Go
--测试数据结束
;WITH t AS (
SELECT  * ,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    #a
UNION ALL
SELECT  #b.id +1000000,
        #b.text ,
        CAST(sort + RIGHT('000' + CAST(#b.id AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN #b ON t.id = #b.aid
)
SELECT  [text]
FROM    t
ORDER BY sort 

另外我觉得可以用left join ，读取之后用程序处理一下就行了

谢谢，真实测试下，套上我自己的表，就出错，我的表如下 a表名称 names 结构如下 id uname utext B表名称 r_comment 结构如下 rid自动编号 ,pid关联a表的id ,rtext

WITH t AS (
SELECT  * ,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    names
UNION ALL
SELECT  rid +1000000,
        rtext ,
        CAST(sort + RIGHT('000' + CAST(rid AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN r_comment ON t.id = r_comment.pid
)
SELECT  pid
FROM    t
ORDER BY sort

运行错误：消息 205，级别 16，状态 1，第 1 行 使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。 感谢 指导，谢谢

[二月十六]

试试这个：

WITH t AS (
SELECT  id ,
        utext,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    names
UNION ALL
SELECT  rid +1000000,
        rtext ,
        CAST(sort + RIGHT('000' + CAST(rid AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN r_comment ON t.id = r_comment.pid
)
SELECT  pid
FROM    t
ORDER BY sort

[柴九小哥]

引用 3 楼 sinat_28984567 的回复:

试试这个：

WITH t AS (
SELECT  id ,
        utext,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    names
UNION ALL
SELECT  rid +1000000,
        rtext ,
        CAST(sort + RIGHT('000' + CAST(rid AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN r_comment ON t.id = r_comment.pid
)
SELECT  pid
FROM    t
ORDER BY sort

消息 207，级别 16，状态 1，第 13 行 列名 'pid' 无效。

[二月十六]

WITH t AS (
SELECT  id ,
        utext,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    names
UNION ALL
SELECT  rid +1000000,
        rtext ,
        CAST(sort + RIGHT('000' + CAST(rid AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN r_comment ON t.id = r_comment.pid
)
SELECT  *
FROM    t
ORDER BY sort

[柴九小哥]

引用 5 楼 sinat_28984567 的回复:

WITH t AS (
SELECT  id ,
        utext,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    names
UNION ALL
SELECT  rid +1000000,
        rtext ,
        CAST(sort + RIGHT('000' + CAST(rid AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN r_comment ON t.id = r_comment.pid
)
SELECT  *
FROM    t
ORDER BY sort

谢谢，用 join on感觉性能快些，显示出来的数据一样， 但还没折腾出，显示后的数据，如何处理 如：

<div>
  a表，内容
     <li> b表关联的条目内容1</li>
  <li> b表关联的条目内容2</li>
  <li> b表关联的条目内容3</li>
</div>

[二月十六]

类似这样，我用sql写的循环，用程序比这个还简单：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] nvarchar(25),[b] nvarchar(30))
Insert #T
select N'a表1，内容',N'b表关联的条目内容1' union all
select N'a表1，内容',N'b表关联的条目内容2' union all
select N'a表1，内容',N'b表关联的条目内容3' union all
select N'a表2，内容',N'b表关联的条目内容21' union all
select N'a表2，内容',N'b表关联的条目内容22'
Go
--测试数据结束
declare @a nvarchar(100),@b nvarchar(100),@temp nvarchar(100)=''
declare auth_cur cursor for
select [a],[b]
from #T
open auth_cur
fetch next from auth_cur into @a,@b
while (@@fetch_status=0)
  begin
     IF @a<>@temp
	 BEGIN
	 PRINT '楼主说：'+@a
	 SET @temp = @a
	 END
	 PRINT '层主说：'+@b
    FETCH next from auth_cur into @a,@b
  end
close auth_cur
deallocate auth_cur

[二月十六]

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([id] int,[text] nvarchar(22))
Insert #a
select 1,N'aa' union all
select 2,N'bb' union all
select 3,N'cc' union all
select 4,N'dd'
GO
if not object_id(N'Tempdb..#b') is null
	drop table #b
Go
Create table #b([id] int,[aid] int,[text] nvarchar(22))
Insert #b
select 1,1,N'd1' union all
select 2,1,N'd2' union all
select 3,1,N'd3' union all
select 4,2,N'dd'
Go
--测试数据结束
;WITH t AS (
SELECT  * ,
        CAST(RIGHT('000' + CAST([id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort
FROM    #a
UNION ALL
SELECT  #b.id +1000000,
        #b.text ,
        CAST(sort + RIGHT('000' + CAST(#b.id AS VARCHAR), 3) AS VARCHAR(MAX))
FROM    t
        INNER JOIN #b ON t.id = #b.aid
)
SELECT  [text]
FROM    t
ORDER BY sort 

另外我觉得可以用left join ，读取之后用程序处理一下就行了