sinat_38913556
2018-03-14 06:37
采纳率: 91.4%
浏览 814

sql server数据库查询语句的问题

有这样一张表

NO name age
1 张三 18
1 李四 17
1 王五 23
1 赵六 40
2 Tom 17
3 Bob 19
3 Tony 36
3 Petter 25

现在想查询出这样的内容

NO name1 age1 name2 age2 name3 age3 name4 age4
1 张三 18 李四 17 王五 23 赵六 40
2 Tom 17
3 Bob 19 Tony 36 Petter 25

求教高手怎么做啊

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • 红帽01 2018-03-15 06:25
    已采纳

    这样的方法只能使用动态sql,以下sql亲测可用
    我问我自己,为什么花一个小时回答你的问题?
    给分吧。

    DROP TABLE #temp1
    DROP TABLE #temp2
    CREATE TABLE #temp1
    (
    [No] INT ,
    name VARCHAR(40) ,
    age INT
    )
    INSERT INTO #temp1
    VALUES ( 1, '张三', 18 )
    INSERT INTO #temp1
    VALUES ( 1, '李四', 17 )
    INSERT INTO #temp1
    VALUES ( 1, '王五', 23 )
    INSERT INTO #temp1
    VALUES ( 1, '赵六', 40 )
    INSERT INTO #temp1
    VALUES ( 2, 'Tom', 17 )

    INSERT INTO #temp1
    VALUES ( 3, 'Bob', 19 )
    INSERT INTO #temp1
    VALUES ( 3, 'Tony', 36 )
    INSERT INTO #temp1
    VALUES ( 3, 'Petter', 25 )
    --SELECT * FROM #temp1

    DECLARE @iCounts INT
    SELECT @iCounts = MAX(counts)
    FROM ( SELECT [No] ,
    COUNT(*) AS counts
    FROM #temp1
    GROUP BY [No]
    ) a
    PRINT @iCounts

    SELECT [No]
    INTO #temp2
    FROM ( SELECT [No] ,
    COUNT(*) AS counts
    FROM #temp1
    GROUP BY [No]
    ) a
    ORDER BY counts DESC

    --SELECT * FROM #temp2

    DECLARE @SQL1 VARCHAR(8000)
    SET @SQL1 = ''
    DECLARE @SQL2 VARCHAR(8000)
    SET @SQL2 = ''

    DECLARE @SQL3 VARCHAR(8000)
    SET @SQL3 = ''

    DECLARE @nos INT
    DECLARE @nos2 INT
    DECLARE @num INT
    DECLARE @num1 INT
    SET @num1 = 0

    DECLARE @name VARCHAR(8000)
    DECLARE @age INT

    DECLARE @sqlCur VARCHAR(8000)
    DECLARE @TableTmp VARCHAR(8000)

    DECLARE cur CURSOR
    FOR
    SELECT [No] AS nos
    FROM #temp2

    OPEN cur
    FETCH NEXT FROM cur INTO @nos
    WHILE @@fetch_status = 0
    BEGIN
    SELECT @nos2 = COUNT(*)
    FROM #temp1
    WHERE [No] = @nos
    PRINT @nos2

        SET @num = 0 
    
        SET @sqlCur = 'declare c_record cursor global for select name,age from #temp1 where [no] ='''
            + CONVERT(VARCHAR(20), @nos) + ''''
        PRINT @sqlCur
        EXEC (@sqlCur)
        OPEN c_record
        FETCH NEXT FROM c_record INTO @name, @age
        WHILE @@FETCH_STATUS = 0 --fetch语句成功
            BEGIN 
                IF @num < @nos2 
                    BEGIN
                        PRINT '@num<@nos2'
                        SET @TableTmp = 'temp' + CONVERT(VARCHAR(10), @nos)
                            + '_' + CONVERT(VARCHAR(10), @num)
                        SET @SQL1 = @SQL1 + 'left outer join #temp1 '
                            + @TableTmp + ' on (a.[no]=' + @TableTmp
                            + '.[no] and ' + @TableTmp + '.name=''' + @name
                            + ''' and ' + @TableTmp + '.[no]='
                            + CONVERT(VARCHAR(10), @nos) + ') '
                        SET @SQL2 = @SQL2 + ',' + @TableTmp + '.name AS name'
                            + CONVERT(VARCHAR(10), @num + 1) + ',' + @TableTmp
                            + '.age as age' + CONVERT(VARCHAR(10), @num + 1)
                    END
    
    
                SET @num = @num + 1 
                SET @num1 = @num1 + 1 
    
                FETCH NEXT FROM c_record INTO @name, @age
            END
    
        CLOSE c_record
        DEALLOCATE c_record
    
        WHILE @num < @iCounts 
            BEGIN 
    
                SET @SQL2 = @SQL2 + ','''',0'
                SET @num = @num + 1 
            END
    
    
        PRINT @SQL1
        PRINT @SQL2
    
        IF ( @SQL3 = '' ) 
            BEGIN
    
                SET @SQL3 = @SQL3 + 'select a.*' + @SQL2 + ' from #temp2 a '
                    + @SQL1 + ' where a.[no]=' + CONVERT(VARCHAR(10), @nos)
            END 
        ELSE 
            BEGIN
                SET @SQL3 = @SQL3 + ' union all select a.*' + @SQL2
                    + ' from #temp2 a ' + @SQL1 + ' where a.[no]='
                    + CONVERT(VARCHAR(10), @nos)
            END 
        SET @SQL1 = ''
        SET @SQL2 = ''
    
        --SET @SQL1 = @SQL1 + CONVERT(VARCHAR(10), @nos)
    
        FETCH NEXT FROM cur INTO @nos
    END
    

    CLOSE cur
    DEALLOCATE cur

    PRINT @SQL3

    EXEC (@SQL3)
    图片说明

    点赞 打赏 评论
  • qq_30040333 2018-03-14 06:50

    spl? 通过id关联 在就是人名也可以 建议多看看书

    点赞 打赏 评论
  • 矿物质的水 2018-03-14 06:52

    做一个列转行
    SELECT * FROM [StudentScores] /*数据源*/
    AS P
    PIVOT
    (
    字段/*行转列后 列的值*/

    字段/*需要行转列的列*/
    ) AS T

    点赞 打赏 评论
  • 单剑撩花猫 2018-03-14 06:55

    这种结果出不来的,列数要相同。用分析函数。可以得到这种结果:
    1 李四 17
    1 张三 18
    1 王五 23
    1 赵六 40
    2 Tom 17
    3 Bob 19
    3 Petter 25
    3 Tony 36

    SELECT CUME_DIST( ) OVER (PARTITION BY NO ORDER BY age ) AS Num,name,age FROM table_name ORDER BY NO;

    点赞 打赏 评论
  • 阳新 2018-03-14 07:10

    select * from where no=1;
    select * from where no=2;
    select * from where no=3;

    点赞 打赏 评论
  • weixin_41767033 2018-03-14 07:13

    select cume_dist() over(partition by no order by age)as num,name,age from table_name order by no;

    点赞 打赏 评论
  • 灿儿哈察苏 2018-03-14 09:04

    select * from table where no=1
    union select * from table where no=2
    union select * from table where no=3

    点赞 打赏 评论
  • whfwait 2018-03-15 02:47

    select cume_dist() over(partition by no order by age)as num,name,age from table_name order by no asg;

    点赞 打赏 评论

相关推荐 更多相似问题