27,579
社区成员
发帖
与我相关
我的任务
分享
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)