解决方案 »

  1.   


    declare @tb table(id int,newsid int, parentid int)
    insert into @tb 
    select 1,12,0 union all
    select 2,12,1 union all
    select 3,12,1 union all
    select 4,12,2 union all
    select 5,12,0 union all
    select 6,12,0 union all
    select 7,12,0;
    with cte as (
    select * from @tb where id=1
    union all
    select t1.* from @tb t1 join cte t2 on t1.parentid=t2.id
    )select * from cte
      

  2.   

    @hjywyj  大神假如它不是根据parentID查的 是根据userid查的 这个sql 怎么写呢?
      

  3.   

    id newsid  parentid
    1   12             0
    2   12             1
    3    12             1
    4    12             2
    5    12            0
    6     12           0
    7      12          0 就是一个评论表 ,newsid 代表新闻id
    根据新闻ID 获取结果
    1     12    0
    2     12    1
    3     12     1
    4     12     2
      

  4.   


    create table test(id int,newsid int, parentid int)
    insert test
    select 1,12,0 union all
    select 2,12,1 union all
    select 3,12,1 union all
    select 4,12,2 union all
    select 5,12,0 union all
    select 6,12,0 union all
    select 7,12,0select id=MIN(id),newsid,parentid
    from test 
    where parentid=0 and newsid=12
    group by newsid,parentid
    union all
    select id,newsid,parentid
    from test 
    where parentid<>0 and newsid=12/*
    1 12 0
    2 12 1
    3 12 1
    4 12 2
    */
      

  5.   

     如果按照这种方法,如果数据表数据变成1 12 0
    id    newsid  parentid
    2 12 1
    3 12 1
    4 12 2
    5 12 0
    6 12 5
    7 12 0
    那么你的sql语句获取的结果是
    1 12 0
    2 12 1
    3 12 1
    4 12 2
    6 12 5 
      

  6.   

    如果数据是
    id  newsid  parentid
    1 12 0 
    2 12 1
    3 12 1
    4 12 2
    5 12 0
    6 12 5
    7 12 0
    我只想要结果
    1 12 0 
    2 12 1
    3 12 1
    4 12 2
      

  7.   

    select top 4 id,  newsid,  parentid from 你的表
    order by id
      

  8.   

    我要根据newsid来得到我这个结果
      

  9.   

    跟上面说的  TOP 4 就好了 
      

  10.   


     
    if object_id('Tempdb..#news') is not null drop table #news
    if object_id('Tempdb..#comment') is not null drop table #commentcreate table #news(
    NId int identity(1,1) not null,
    NTitle nvarchar(100) null
    )create table #comment(
    CId int identity(1,1) not null,
    CNId int  null,
    Ccontent nvarchar(max) null
    )
    Insert Into #news 
    select '第 1 条新闻' union all
    select '第 2 条新闻' union all
    select '第 3 条新闻' union all
    select '第 4 条新闻' union all
    select '第 5 条新闻'  
    Insert Into #comment
    select 1,'第1条新闻的 第 1 条评论' union all
    select 1,'第1条新闻的 第 2 条评论' union all
    select 1,'第1条新闻的 第 3 条评论' union all
    select 1,'第1条新闻的 第 4 条评论' union all
    select 1,'第1条新闻的 第 5 条评论' union all
    select 2,'第2条新闻的 第 1 条评论' union all
    select 2,'第2条新闻的 第 2 条评论' union all
    select 2,'第2条新闻的 第 3 条评论' union all
    select 2,'第2条新闻的 第 4 条评论' union all
    select 2,'第2条新闻的 第 5 条评论' union all
    select 2,'第2条新闻的 第 6 条评论' union all
    select 2,'第2条新闻的 第 7 条评论' union all
    select 2,'第2条新闻的 第 8 条评论' union all
    select 3,'第3条新闻的 第 1 条评论' union all
    select 3,'第3条新闻的 第 2 条评论' union all 
    select 1,'第1条新闻的 第 6 条评论' union all
    select 1,'第1条新闻的 第 7 条评论' union all
    select 1,'第1条新闻的 第 8 条评论' union all
    select 1,'第1条新闻的 第 9 条评论' union all
    select 1,'第1条新闻的 第 10 条评论' union all
    select 2,'第2条新闻的 第 9 条评论' union all
    select 2,'第2条新闻的 第 10 条评论' union all
    select 2,'第2条新闻的 第 11 条评论' union all
    select 2,'第2条新闻的 第 12 条评论' union all
    select 2,'第2条新闻的 第 13 条评论' union all
    select 4,'第4条新闻的 第 1 条评论' union all
    select 5,'第5条新闻的 第 1 条评论' union all
    select 3,'第3条新闻的 第 3 条评论' union all
    select 5,'第5条新闻的 第 2 条评论' union all
    select 5,'第5条新闻的 第 3 条评论'  declare @n int
    set @n=6
    ;with cte as(
    select *, row_number() over(partition by CNId order by CId) rid from #comment
    )select  * from #news join cte on NId=CNId
    where rid<=@n 
      

  11.   


    --------------------------
    --结果(5 行受影响)(30 行受影响)
    NId         NTitle                                                                                               CId         CNId        Ccontent                                                                                                                                                                                                                                                         rid
    ----------- ---------------------------------------------------------------------------------------------------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
    1           第 1 条新闻                                                                                              1           1           第1条新闻的 第 1 条评论                                                                                                                                                                                                                                                   1
    1           第 1 条新闻                                                                                              2           1           第1条新闻的 第 2 条评论                                                                                                                                                                                                                                                   2
    1           第 1 条新闻                                                                                              3           1           第1条新闻的 第 3 条评论                                                                                                                                                                                                                                                   3
    1           第 1 条新闻                                                                                              4           1           第1条新闻的 第 4 条评论                                                                                                                                                                                                                                                   4
    1           第 1 条新闻                                                                                              5           1           第1条新闻的 第 5 条评论                                                                                                                                                                                                                                                   5
    1           第 1 条新闻                                                                                              16          1           第1条新闻的 第 6 条评论                                                                                                                                                                                                                                                   6
    2           第 2 条新闻                                                                                              6           2           第2条新闻的 第 1 条评论                                                                                                                                                                                                                                                   1
    2           第 2 条新闻                                                                                              7           2           第2条新闻的 第 2 条评论                                                                                                                                                                                                                                                   2
    2           第 2 条新闻                                                                                              8           2           第2条新闻的 第 3 条评论                                                                                                                                                                                                                                                   3
    2           第 2 条新闻                                                                                              9           2           第2条新闻的 第 4 条评论                                                                                                                                                                                                                                                   4
    2           第 2 条新闻                                                                                              10          2           第2条新闻的 第 5 条评论                                                                                                                                                                                                                                                   5
    2           第 2 条新闻                                                                                              11          2           第2条新闻的 第 6 条评论                                                                                                                                                                                                                                                   6
    3           第 3 条新闻                                                                                              14          3           第3条新闻的 第 1 条评论                                                                                                                                                                                                                                                   1
    3           第 3 条新闻                                                                                              15          3           第3条新闻的 第 2 条评论                                                                                                                                                                                                                                                   2
    3           第 3 条新闻                                                                                              28          3           第3条新闻的 第 3 条评论                                                                                                                                                                                                                                                   3
    4           第 4 条新闻                                                                                              26          4           第4条新闻的 第 1 条评论                                                                                                                                                                                                                                                   1
    5           第 5 条新闻                                                                                              27          5           第5条新闻的 第 1 条评论                                                                                                                                                                                                                                                   1
    5           第 5 条新闻                                                                                              29          5           第5条新闻的 第 2 条评论                                                                                                                                                                                                                                                   2
    5           第 5 条新闻                                                                                              30          5           第5条新闻的 第 3 条评论                                                                                                                                                                                                                                                   3(19 行受影响)