实用
1 return (from DataRow dr in dt.Rows where dr["txtContractAmount"] != null && dr["txtContractAmount"].ToString() != "" select decimal.Parse(dr["txtContractAmount"].ToString())).Sum();
2 相当于 where [Building] = 'B3' and Department='ES' and (wc like '%aoi%' or wc like '%ROU%') WC个数不定
q=from a in q
from t in wcs where a.wc.contains(t)
select t
3 分页
USE [BPM_K2Sln]
GO
/****** Object: StoredProcedure [dbo].[PagingCursor] Script Date: 2019/2/10 星期日 13:52:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PagingCursor] (
@Table varchar(max),
@PK varchar(max),
@Sort varchar(max) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields nvarchar(max) = '*',
@Filter varchar(max) = NULL,
@Group varchar(max) = NULL,
@Join varchar(max) = NUll,
@recordCount int out,
@sql varchar(max) out)
AS
DECLARE @strPageSize varchar(max)
DECLARE @strSkippedRows varchar(max)
DECLARE @strFilter varchar(max)
DECLARE @strSimpleFilter varchar(max)
DECLARE @strGroup varchar(max)
/*Default Sorting*/
IF @Sort IS not NULL and len(@Sort) > 0 --SET @Sort = @PK
--set @sort = @pk
SET @Sort = ' ORDER BY ' + @Sort + ' '
/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/*Set paging variables.*/
SET @strPageSize = CONVERT(varchar(50), @PageSize)
SET @strSkippedRows = CONVERT(varchar(50), @PageSize * (@PageNumber - 1))
/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
IF @join IS NULL
SET @join = ''
declare @strResult nvarchar(max)
if(@strGroup = '')
set @strResult =N' select @count = count(1) from ' + @Table + @join + @strFilter
else
set @strResult =N' select @count = count(1) from (select ' + @Fields + ' from ' + @Table + @join + @strFilter + N' ' + @strGroup + ') T'
exec sp_executeSQL @strResult,N'@count int output', @recordCount output
IF @PageNumber = 1 -- In this case we can execute a more efficient query with no subqueries.
begin
set @sql = 'SELECT TOP ' + @strPageSize + ' ' + @Fields + ' FROM ' + @Table +@join+
@strFilter + @strGroup + @Sort
end
ELSE -- Execute a structure of subqueries that brings the correct page.
begin
set @sql =
'SELECT TOP ' + @strPageSize + ' ' + @Fields + ' FROM ' + @Table + @join + ' WHERE ' + @PK + ' NOT IN
(SELECT TOP ' + @strSkippedRows + ' ' + @PK + ' FROM ' + @Table +@join+
@strFilter + @strGroup + @Sort + ') ' +
@strSimpleFilter +
@strGroup +
@Sort
end
print @sql
exec (@sql)
select @recordCount
JS正则表达式获取小括号中括号花括号内的内容