SQL语句如何根据模糊查询条件进行分组求和等运算
如有个表TABLE
ID Name Data
1 abc1 111
2 aac1 111
3 ddd2 111
4 sss1 111
5 ccc2 111
6 dds3 111
7 sdf3 111
上面有7条记录，如字段Name里的值，按最后一位来分有三类，为1的有3条记录（ID为1、2、4），为2的有2条记录（ID为3、5），为3的有2条记录（ID为6、7）。现在怎么根据这三类分组来统计求和或平均等。其中的1，2，3等关键字可能是其他的字符串，也可能在开始位置。
[解决办法]
PRINT SUBSTRING('abcd',2,1)
DECLARE @StartPosition INT, @Length INT
SET @StartPosition = 4
SET @Length = 1
SELECT 1 AS ID, 'abc1' AS Name, 111 AS Data
INTO #t
UNION ALL
SELECT 2 AS ID, 'aac1' AS Name, 111 AS Data
UNION ALL
SELECT 3 AS ID, 'ddd2' AS Name, 111 AS Data
UNION ALL
SELECT 4 AS ID, 'sss1' AS Name, 111 AS Data
UNION ALL
SELECT 5 AS ID, 'ccc2' AS Name, 111 AS Data
UNION ALL
SELECT 6 AS ID, 'dds3' AS Name, 111 AS Data
UNION ALL
SELECT 7 AS ID, 'sdf3' AS Name, 111 AS Data
SELECT * FROM #t
SELECT SUBSTRING([Name],@StartPosition, @Length) AS KeyValue, COUNT(*) AS KeyCount
FROM #t
GROUP BY SUBSTRING([Name],@StartPosition, @Length)
DROP TABLE #t
IF OBJECT_ID ('dbo.Ta') IS NOT NULL
DROP TABLE dbo.Ta
Go
CREATE TABLE dbo.Ta
(ID Int,
[Name] Varchar(15) Null,
Data int
);
Insert dbo.Ta
SELECT 1 AS ID, 'abc1' AS Name, 111 AS Data
UNION ALL
SELECT 2 AS ID, 'aac1' AS Name, 111 AS Data
UNION ALL SELECT 3 AS ID, 'ddd2' AS Name, 111 AS Data
UNION ALL SELECT 4 AS ID, 'sss1' AS Name, 111 AS Data
UNION ALL SELECT 5 AS ID, 'ccc2' AS Name, 111 AS Data
UNION ALL SELECT 6 AS ID, 'dds3' AS Name, 111 AS Data
UNION ALL SELECT 7 AS ID, 'sdf3' AS Name, 111 AS Data
Select b.[Name],Sum_Qty,Avg_Qty From
(
Select LastName,Sum_Qty=Sum(data),Avg_Qty=Avg(Data) From
(
select [LastName]=right(rtrim([Name]),1),Data
From dbo.Ta
) a
Group By LastName
) a
Left join
dbo.Ta b
On a.LastName=right(rtrim(b.[Name]),1)
