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

最新推荐文章于 2020-09-25 08:55:58 发布

丰云

最新推荐文章于 2020-09-25 08:55:58 发布

阅读量291

点赞数

分类专栏：数据库应用文章标签： sql 取表结构返回数据串

本文链接：https://blog.csdn.net/foren_whb/article/details/84377827

版权

数据库应用专栏收录该内容

4 篇文章 0 订阅

订阅专栏

当报表需要一次获取所有数据并统计时，采用纯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 + '%')

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