当报表需要一次获取所有数据并统计时,采用纯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 + '%')
虽然觉得这么做意义不大,但作为技术代码保留,以备参考。