productid qty
18kt 2
18ks 3
18kc 3
14kt 5
14kb 8
14kt 3
19kct 1
19kts 2
统计 18k 8
14k 16
19k 3即是只对productid取K之前的字眼进行分组统计
18kt 2
18ks 3
18kc 3
14kt 5
14kb 8
14kt 3
19kct 1
19kts 2
统计 18k 8
14k 16
19k 3即是只对productid取K之前的字眼进行分组统计
sum(qty) as qty
from tb
group by left(productid,charindex('k',productid))
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-27 08:57:15
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (productid varchar(5),qty int)
INSERT INTO @tb
SELECT '18kt',2 UNION ALL
SELECT '18ks',3 UNION ALL
SELECT '18kc',3 UNION ALL
SELECT '14kt',5 UNION ALL
SELECT '14kb',8 UNION ALL
SELECT '14kt',3 UNION ALL
SELECT '19kct',1 UNION ALL
SELECT '19kts',2--SQL查询如下:select left(productid,charindex('k',productid)) as productid,
sum(qty) as qty
from @tb
group by left(productid,charindex('k',productid))
/*
productid qty
--------- -----------
14k 16
18k 8
19k 3(3 行受影响)
*/
select productid=CONVERT(nvarchar(3),productid),qty=SUM(qty)
from tb
group by CONVERT(nvarchar(3),productid)/*
14k 16
18k 8
19k 3*/
left(productid,charindex('k',productid)) productid,
sum(qty)qty
from tb
group by left(productid,charindex('k',productid))
DECLARE @tb TABLE (productid varchar(5),qty int)
INSERT INTO @tb
SELECT '18kt',2 UNION ALL
SELECT '18ks',3 UNION ALL
SELECT '18kc',3 UNION ALL
SELECT '14kt',5 UNION ALL
SELECT '14kb',8 UNION ALL
SELECT '14kt',3 UNION ALL
SELECT '19kct',1 UNION ALL
SELECT '19kts',2select substring(productid,0,4) ,sum(qty) from @tb group by substring(productid,0,4)----- -----------
14k 16
18k 8
19k 3(3 行受影响)
INSERT INTO @tb
SELECT '18kt',2 UNION ALL
SELECT '18ks',3 UNION ALL
SELECT '18kc',3 UNION ALL
SELECT '14kt',5 UNION ALL
SELECT '14kb',8 UNION ALL
SELECT '14kt',3 UNION ALL
SELECT '19kct',1 UNION ALL
SELECT '19kts',2select productid,sum(qty) qty
from
(
select distinct substring(productid,0,charindex('k',productid)+1) productid,qty from @tb
) tt
group by productidproductid qty
--------- -----------
14k 16
18k 5
19k 3
INSERT INTO @tb
SELECT '18kt',2 UNION ALL
SELECT '18ks',3 UNION ALL
SELECT '18kc',3 UNION ALL
SELECT '14kt',5 UNION ALL
SELECT '14kb',8 UNION ALL
SELECT '14kt',3 UNION ALL
SELECT '19kct',1 UNION ALL
SELECT '19kts',2select productid,sum(qty) qty
from
(
select substring(productid,0,charindex('k',productid)+1) productid,qty from @tb
) tt
group by productidproductid qty
--------- -----------
14k 16
18k 8
19k 3
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-27 09:31:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([productid] varchar(5),[qty] int)
insert [tb]
select '18kt',2 union all
select '18ks',3 union all
select '18kc',3 union all
select '14kt',5 union all
select '14kb',8 union all
select '14kt',3 union all
select '19kct',1 union all
select '19kts',2
--------------开始查询--------------------------
select
substring(productid,1,charindex('k',productid)) as productid,
sum(qty) as qty
from
tb
group by
left(productid,charindex('k',productid))
----------------结果----------------------------
/* productid qty
--------- -----------
14k 16
18k 8
19k 3(3 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-27 09:31:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([productid] varchar(5),[qty] int)
insert [tb]
select '18kt',2 union all
select '18ks',3 union all
select '18kc',3 union all
select '14kt',5 union all
select '14kb',8 union all
select '14kt',3 union all
select '19kct',1 union all
select '19kts',2
--------------开始查询--------------------------
select
left(productid,charindex('k',productid)) as productid,
sum(qty) as qty
from
tb
group by
left(productid,charindex('k',productid))
----------------结果----------------------------
/* productid qty
--------- -----------
14k 16
18k 8
19k 3(3 行受影响)*/
sum(qty) as qty from tb
group by left(productid,charindex('k',productid)) 就这,把productid拆分一下就行了。