SELECT SUBSTRING(名称, 1) 名称 , SUM(金额) 金额 FROM TB GROUP BY SUBSTRING(名称, 1)
比较严谨的方法:SELECT SUBSTRING(名称, 1, CHARINDEX('(', 名称, 1)-1) 名称 , SUM(金额) 金额 FROM TB GROUP BY SUBSTRING(名称, 1, CHARINDEX('(', 名称, 1)-1)
好像不对吧,向 substring 函数传递了无效的 length 参数。
错误提示如下: 向 substring 函数传递了无效的 length 参数。
DBA_Huangzj:而且这种只能统计出A(1),B(1)这一类的数据,A,B的数据就没统计上.
忘了处理没有括号的那些,这个可以了SELECT CASE WHEN CHARINDEX('(', 名称, 1)=0 THEN 名称 ELSE SUBSTRING(名称, 1, CHARINDEX('(', 名称, 1)-1 )END 名称 , SUM(金额) 金额 FROM TB GROUP BY CASE WHEN CHARINDEX('(', 名称, 1)=0 THEN 名称 ELSE SUBSTRING(名称, 1, CHARINDEX('(', 名称, 1)-1 )END
USE tempdb; /* CREATE TABLE t1 ( 序号 INT NOT NULL, 名称 NVARCHAR(10) NOT NULL, 金额 INT NOT NULL );INSERT INTO t1(序号,名称,金额) VALUES(1,'A',55),(2,'A(1)',55),(3,'B',44),(4,'B(2)',44); */ SELECT t2.Name AS 名称, SUM(t2.金额) AS 金额合计 FROM ( SELECT *, CASE WHEN CHARINDEX('(',名称,1) > 0 THEN SUBSTRING(名称,1,CHARINDEX('(',名称,1)-1) WHEN CHARINDEX('(',名称,1) = 0 THEN 名称 END AS Name FROM t1 ) AS t2 GROUP BY t2.Name
--创建表以及初始化数据 IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA CREATE TABLE TA(ID INT ,TName VARCHAR(10),Price MONEY) INSERT INTO TA SELECT 1,'A',55 UNION ALL SELECT 2,'A(1)',55 UNION ALL SELECT 3,'B',44 UNION ALL SELECT 3,'B(2)',44 UNION ALL SELECT 4,'BB2(2)',44
--查询 SELECT A.TName,SUM(A.Price) AS SumPrice FROM ( SELECT CASE WHEN CHARINDEX('(',TName)>0 THEN LEFT(TName,CHARINDEX('(',TName)-1) ELSE TName END AS TName,Price FROM TA )AS A GROUP BY TName--查询结果 TName SumPrice ---------- --------------------- A 110.00 B 88.00 BB2 44.00(3 行受影响)
SUM(金额) 金额
FROM TB
GROUP BY SUBSTRING(名称, 1)
SUM(金额) 金额
FROM TB
GROUP BY SUBSTRING(名称, 1, CHARINDEX('(', 名称, 1)-1)
向 substring 函数传递了无效的 length 参数。
SUM(金额) 金额
FROM TB
GROUP BY CASE WHEN CHARINDEX('(', 名称, 1)=0 THEN 名称 ELSE SUBSTRING(名称, 1, CHARINDEX('(', 名称, 1)-1 )END
USE tempdb;
/*
CREATE TABLE t1
(
序号 INT NOT NULL,
名称 NVARCHAR(10) NOT NULL,
金额 INT NOT NULL
);INSERT INTO t1(序号,名称,金额) VALUES(1,'A',55),(2,'A(1)',55),(3,'B',44),(4,'B(2)',44);
*/
SELECT
t2.Name AS 名称,
SUM(t2.金额) AS 金额合计
FROM
(
SELECT
*,
CASE
WHEN CHARINDEX('(',名称,1) > 0 THEN SUBSTRING(名称,1,CHARINDEX('(',名称,1)-1)
WHEN CHARINDEX('(',名称,1) = 0 THEN 名称
END AS Name
FROM t1
) AS t2
GROUP BY t2.Name
--创建表以及初始化数据
IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA(ID INT ,TName VARCHAR(10),Price MONEY)
INSERT INTO TA
SELECT 1,'A',55 UNION ALL
SELECT 2,'A(1)',55 UNION ALL
SELECT 3,'B',44 UNION ALL
SELECT 3,'B(2)',44 UNION ALL
SELECT 4,'BB2(2)',44
--查询
SELECT A.TName,SUM(A.Price) AS SumPrice FROM (
SELECT CASE WHEN CHARINDEX('(',TName)>0 THEN LEFT(TName,CHARINDEX('(',TName)-1)
ELSE TName END AS TName,Price
FROM TA )AS A
GROUP BY TName--查询结果
TName SumPrice
---------- ---------------------
A 110.00
B 88.00
BB2 44.00(3 行受影响)