一段专为大数据量table写的sql,包含获取表结构并返回的功能

当报表需要一次获取所有数据并统计时,采用纯json格式传输数据代价太大,所以写了这个sql语句:

 DECLARE  @Type       VARCHAR(100);
 DECLARE  @Keywords   VARCHAR(100);
 DECLARE  @PageSize   INT;
 DECLARE  @PageIndex  INT;
     SET  @Type       = '';
     SET  @Keywords   = '';
     SET  @PageSize  = 10;
     SET  @PageIndex = 0;
  SELECT * FROM (
  SELECT  id = CASE COLUMN_NAME 
               WHEN 'id'          THEN 1 
               WHEN 'Type'        THEN 2 
               WHEN 'IsNew'       THEN 3 
               WHEN 'Title'       THEN 4 
               WHEN 'Image'       THEN 5 
               WHEN 'Author'      THEN 6
               WHEN 'Status'      THEN 7
               WHEN 'Keywords'    THEN 8  
               WHEN 'Description' THEN 9
               WHEN 'PublishTime' THEN 10  
               WHEN 'Content'     THEN 11 ELSE 0 END
         ,Name         = COLUMN_NAME
         ,DataType     = DATA_TYPE
         ,NullAble     = IS_NULLABLE
         ,DefaultValue = COLUMN_DEFAULT
    FROM  information_schema.columns 
   WHERE  TABLE_NAME   = 'blog'
     AND  COLUMN_NAME IN ('id','Type','IsNew','Title','Image','Author','Status','Keywords','Description','PublishTime','Content')) AS t
ORDER BY  id;
  SELECT  CAST(id AS VARCHAR(10)) + '§' + Type + '§' + CAST(IsNew AS VARCHAR(10)) + '§' + Title + '§' + ISNULL(Image, '') + '§' + Author + '§' + CAST([Status] AS VARCHAR(10)) + '§' + ISNULL(Keywords, '') + '§' + ISNULL(Description, '') + '§' + CONVERT(varchar(100), PublishTime, 21) + '§' + Content
    FROM  blog
   WHERE (@Type     = '' OR  [Type] = @Type)
     AND (@Keywords = '' OR  Keywords LIKE '%' + @Keywords + '%')
ORDER BY  xPublishTime OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
  SELECT  TotalCount = Count(1)
    FROM  blog
   WHERE (@Type     = '' OR  [Type] = @Type)
     AND (@Keywords = '' OR  Keywords LIKE '%' + @Keywords + '%')

虽然觉得这么做意义不大,但作为技术代码保留,以备参考。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值