将查询数据转为json格式（SELECT TO JSON）

问题

需要将一个表的数据输出为json串

环境

SQL Server 2008

解决思路

1、查询出来的数据必须按输出顺序整理好。

2、末级要能够通过行数据区分出来。（建议是整理成这样一个字段）我这里是根据f_bm是否为空作为依据。

3、关键点：当上一行级别大于下一级别的时候，就是需要补全】}的时候。补几个，补上一行级别减当前行级别。结束时，需要将未补全的补全，通过order计数监控。

代码

CREATE TABLE tb_test
(
    f_bm    VARCHAR(50) DEFAULT(''),            --编码
    f_mc    VARCHAR(200) DEFAULT(''),            --名称
    f_jb    INTEGER                                --级别
)

Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','总部',1)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','配送中心',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011702','轶',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','北京中心',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010574','易',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','设计部',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('000001','松',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','销售部',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('000005','佳',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('000086','洋',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001152','英',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001168','治',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001200','艳',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001403','娟',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001606','静',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010568','莹',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010853','静',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011090','彗',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011774','丽',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011780','志',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012123','舒',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012831','江',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013284','洁',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','财务部',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('000038','霞',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013204','时',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013225','1',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013226','2',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013227','3',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013228','4',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013229','5',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013245','务',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','市场部',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010271','瑶',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011593','崇',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','总办',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('009999','杰',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010314','航',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010760','虎',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','培训部',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001602','秀',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','外阜销售',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001169','丽',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010917','涛',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011481','忆',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011482','宁',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','管培生',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012873','亮',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012876','杰',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','直营店',1)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','华北区',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','河北办事处',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012923','艳',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','西南区',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','重庆分公司',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010120','丹',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010132','诗',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010721','玘',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011209','晓',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011830','港',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012169','莉',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012236','雯',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','东北区',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','沈阳分公司',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('000008','岩',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('003009','磊',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('008800',')',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010327','1',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011584','洋',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','华东区',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','南京分公司',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('001113','米',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010350','琼',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010651','骏',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010778','鸣',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011244','子',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('011793','雪',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012486','妮',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('012899','琴',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('013269','云',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','西北区',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','西安办事处',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('005620','俊',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010694','芮',4)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','华南区',2)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('','福州分公司',3)
Insert [tb_test] ([f_bm],[f_mc],[f_jb]) values ('010759','添',4)

Create PROCEDURE [dbo].[SelectToJSON] 
AS 
    DECLARE zdfzr CURSOR
    FOR
        SELECT  f_bm, f_mc, f_jb
        FROM   tb_test;

    
    DECLARE @bm VARCHAR(20) ,
        @mc VARCHAR(60) ,
        @curjb INTEGER ,            --当前级别
        @upjb INTEGER ,            --上一级别
        @order INTEGER,                                        
        @json VARCHAR(8000);

    SET @bm = '';
    SET @mc = '';
    SET @upjb = 1;
    SET @curjb = 1;
    SET @order = 0;
    SET @json = '{"success":true,"data":[';

    OPEN zdfzr;
    FETCH zdfzr INTO @bm, @mc, @curjb;
    PRINT 'bm:'+@bm+',mc:'+@mc+',curjb:'+CAST(@curjb AS VARCHAR)+',upjb:'+CAST(@upjb AS VARCHAR)+',order:'+CAST(@order AS VARCHAR);
    WHILE @@fetch_status = 0 
        BEGIN
            SET @json = @json + '{"id":"' + @bm + '",' + '"name":"' + @mc
                + '","leaf":' + CASE @bm
                                WHEN '' THEN 'false'
                                ELSE 'true'
                              END + CASE @bm WHEN '' THEN ',"children":[' ELSE ',"children":[]}' END;
            IF @bm='' SET @order=@order+1;

            SET @upjb = @curjb
            FETCH zdfzr INTO @bm, @mc, @curjb;
            
            IF @@fetch_status = 0 
                
                IF @curjb<@upjb
                begin
                    SET @json = @json + REPLACE(REPLACE(STR(0,@upjb - @curjb ),' ', ']}'), '0', ']},');
                    SET @order=@order-(@upjb-@curjb);
                END
                ELSE IF @curjb=@upjb SET @json=@json+','
                    
            PRINT 'bm:'+@bm+',mc:'+@mc+',curjb:'+CAST(@curjb AS VARCHAR)+',upjb:'+CAST(@upjb AS VARCHAR)+',order:'+CAST(@order AS VARCHAR);
            PRINT @json;
        END
        
        IF @order>0 
        begin    
            SET @json = @json + REPLACE(REPLACE(STR(0,@order),' ', ']}'), '0', ']}');        
        END

    CLOSE zdfzr;
    DEALLOCATE zdfzr;

    SELECT  @json+']}'PRINT @json;

经验总结

前面浪费了很多时间在写代码和验证结果上，其实不正确的原因是没有对数据进行全面的分析，找出特点。如果没有找出特点（要点）就下手，就会形成边写边思考，但又考虑不周全的问题。正确的处理方法应该是：

对数据进行全面的分析。找出特点。
理出解决的思路。如果可能写成文字的步骤。
编写代码并验证。

posted @ 2014-05-09 23:14 爱编程的文子阅读(1391) 评论(0) 收藏举报

刷新页面返回顶部