UPDATE d SET d.单价 = p.单价 FROM 明细表 d INNER JOIN ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY 组织名称, 物料名称, 单位 ORDER BY 开始日期 DESC, 结束日期 DESC ) as rn FROM 价目表 WHERE 结束日期 >= (SELECT MIN(日期) FROM 明细表) -- 提前过滤 AND 开始日期 <= (SELECT MAX(日期) FROM 明细表) ) p ON d.组织名称 = p.组织名称 AND d.物料名称 = p.物料名称 AND d.单位 = p.单位 AND d.日期 BETWEEN p.开始日期 AND p.结束日期 AND p.rn = 1; -- 取最后一行
DECLARE TableCursor CURSOR FOR SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, ty.name AS DataType FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE ty.name IN ('nvarchar','varchar','char','nchar','text','ntext') -- and t.name = 'T_META_OBJECTTYPECACHE'
OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Schema, @TableName, @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0 BEGIN -- 对text/ntext类型字段特殊处理 IF @DataType IN ('text', 'ntext') BEGIN SET @SQL = 'INSERT INTO Tmp_FindTxt (SchemaName, TableName, ColumnName, ColumnValue, MatchCount) SELECT ''' + REPLACE(@Schema, '''', '''''') + ''', ''' + REPLACE(@TableName, '''', '''''') + ''', ''' + REPLACE(@ColumnName, '''', '''''') + ''', CAST(SUBSTRING(CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)), 1, 100) AS NVARCHAR(100)), COUNT(*) AS MatchCount FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' WHERE CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)) LIKE ''%' + REPLACE(@SearchStr, '''', '''''') + '%'' GROUP BY CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX))' END ELSE BEGIN SET @SQL = 'INSERT INTO Tmp_FindTxt (SchemaName, TableName, ColumnName, ColumnValue, MatchCount) SELECT ''' + REPLACE(@Schema, '''', '''''') + ''', ''' + REPLACE(@TableName, '''', '''''') + ''', ''' + REPLACE(@ColumnName, '''', '''''') + ''', CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)), COUNT(*) AS MatchCount FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + REPLACE(@SearchStr, '''', '''''') + '%'' GROUP BY ' + QUOTENAME(@ColumnName) END
BEGIN TRY EXEC sp_executesql @SQL END TRY BEGIN CATCH PRINT 'Error searching [' + @Schema + '].[' + @TableName + '].[' + @ColumnName + ']: ' + ERROR_MESSAGE() END CATCH
FETCH NEXT FROM TableCursor INTO @Schema, @TableName, @ColumnName, @DataType END
CLOSE TableCursor DEALLOCATE TableCursor
-- 显示结果
SELECT
'select '+ ColumnName +' ,* from '+ TableName +' where '+ ColumnName +' like ''%' + REPLACE(@SearchStr, '''', '''''') + '%''' AS '查询',
SchemaName AS '架构名', TableName AS '表名', ColumnName AS '字段名', ColumnValue AS '包含的字段值', MatchCount AS '匹配行数' FROM Tmp_FindTxt ORDER BY SchemaName, TableName, ColumnName
结果：
查询 架构名 表名 字段名 包含的字段值 匹配行数 select FID ,* from T_META_OBJECTTYPECACHE where FID like '%99291303-1d0d-42d2-9c5e-5ce8153172d2%' dbo T_META_OBJECTTYPECACHE FID 99291303-1d0d-42d2-9c5e-5ce8153172d2 1 郭少锋编辑 2025-10-16 11:05:11创建 2025-10-16 11:04:35SQLSQL : 读取 xml 字段SQL : 读取 xml 字段 ------------------------------------------------------------------------------------------------------------------------ -- 读取 xml 成 table
select * from ( -------------------------------------------------------------------------------------------- select
a1.value('(Seq)[1]', 'int') AS Seq, a1.value('(FUserId/User/Id)[1]', 'int') AS UserId, a1.value('(FUserId/User/Name)[1]', 'nvarchar(50)') AS UserName, a1.value('(FType)[1]', 'nvarchar(50)') AS FType, a1.value('(FValue)[1]', 'int') AS FValue
where FParameterObjId = 'aGsf_SysSet' -------------------------------------------------------------------------------------------- ) a where UserName = '郭少锋'
/* select FParameters from t_Bas_SysParameter where FParameterObjId = 'aGsf_SysSet'
------------------------------------------------------------------------------------------------------------------------ -- 取得 xml
from TMP2983AE495E5511F0ACB40050563 a join TMP2983AE495E5511F0ACB40050563 b on ( b.FStockName = a.FStockName ) and ( b.FMtrlNo = a.FMtrlNo ) and ( b.FMtrlName = a.FMtrlName ) and ( b.FUnitStockName = a.FUnitStockName )
and ( b.FIdentityId <= a.FIdentityId )
group by a.FStockName,a.FMtrlNo,a.FMtrlName,a.FUnitStockName ,a.FIdentityId ,a.FQtyStock4
order by a.FStockName,a.FMtrlNo,a.FMtrlName,a.FUnitStockName ,a.FIdentityId ;
SELECT a.group_column, a.date_column, a.value_column, SUM(b.value_column) AS group_running_total FROM your_table a JOIN your_table b ON b.group_column = a.group_column AND b.date_column < a.date_column GROUP BY a.group_column, a.date_column, a.value_column ORDER BY a.group_column, a.date_column;郭少锋编辑 2025-07-11 21:43:13创建 2025-07-11 21:07:36SQLSQL : 数字前面加 0SQL : 数字前面加 0DECLARE @num INT = 123; DECLARE @length INT = 6;
from ( select distinct FBillNo_Rpt,FSeq_Rpt,FBillNo_PpBom,FMtrlNo2,FUnitName2,FScrapRate,FDenominator ,FCreator from TMP69E1ED8B1F7811F0ACC00050563_1 where FCreator != '' ) t2
where t1.FBillNo_Rpt = t2.FBillNo_Rpt and t1.FSeq_Rpt = t2.FSeq_Rpt and t1.FBillNo_PpBom = t2.FBillNo_PpBom and t1.FMtrlNo2 = t2.FMtrlNo2 and t1.FUnitName2 = t2.FUnitName2 and t1.FScrapRate = t2.FScrapRate and t1.FDenominator = t2.FDenominator
for xml path('')
), 1, 1, '') ,'') as FCreatorTxt
from TMP69E1ED8B1F7811F0ACC00050563 t1
在MSSQL中，你可以使用STRING_AGG函数（SQL Server 2017及更高版本）或FOR XML PATH方法（旧版本）来实现按多个字段分组并将某个字段的不同值合并成逗号分隔的字符串。
## 方法1：使用STRING_AGG函数（SQL Server 2017+）
```sql SELECT field1, field2, field3, STRING_AGG(DISTINCT value_field, ',') AS concatenated_values FROM your_table GROUP BY field1, field2, field3; ```
## 方法2：使用FOR XML PATH方法（SQL Server 2005+）
```sql SELECT field1, field2, field3, STUFF(( SELECT ',' + value_field FROM your_table t2 WHERE t1.field1 = t2.field1 AND t1.field2 = t2.field2 AND t1.field3 = t2.field3 FOR XML PATH('') ), 1, 1, '') AS concatenated_values FROM your_table t1 GROUP BY field1, field2, field3; ```
## 示例
假设有一个订单明细表，你想按客户ID和订单日期分组，合并所有产品名称：
```sql -- 使用STRING_AGG SELECT CustomerID, CONVERT(date, OrderDate) AS OrderDate, STRING_AGG(DISTINCT ProductName, ', ') AS Products FROM OrderDetails GROUP BY CustomerID, CONVERT(date, OrderDate);
-- 使用FOR XML PATH SELECT CustomerID, CONVERT(date, OrderDate) AS OrderDate, STUFF(( SELECT ', ' + ProductName FROM OrderDetails t2 WHERE t1.CustomerID = t2.CustomerID AND CONVERT(date, t1.OrderDate) = CONVERT(date, t2.OrderDate) FOR XML PATH('') ), 1, 2, '') AS Products FROM OrderDetails t1 GROUP BY CustomerID, CONVERT(date, OrderDate); ```
注意： 1. STRING_AGG是更现代、更简洁的方法 2. 如果需要去除重复值，STRING_AGG可以直接使用DISTINCT关键字 3. FOR XML PATH方法需要手动处理第一个逗号（使用STUFF函数） 4. 两种方法都可以添加排序：STRING_AGG使用WITHIN GROUP，FOR XML PATH在子查询中添加ORDER BY郭少锋编辑 2025-04-22 21:17:17创建 2025-04-22 20:44:52SQL