sinat_38913556 2018-03-14 06:37 采纳率: 0%
浏览 821
已采纳

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)
    图片说明

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(7条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题