create table tb(商品类 nvarchar(10),重量 varchar(10),包装类 nvarchar(10),总编码 nvarchar(10)) insert into tb select '大蒜','2g','1级','AA1' insert into tb select '白菜','3g','2级','AA1' insert into tb select '大蒜','4g','2级','AA1' insert into tb select '生姜','5g','1级','AA1' insert into tb select '白菜','6g','2级','AA1' insert into tb select '苹果','7g','2级','AA1' insert into tb select '大蒜','8g','1级','AA1' gocreate table tc(商品类 nvarchar(10)) insert into tc select '大蒜' union all select '白菜' union all select '生姜' union all select '苹果' union all select '牛肉' union all select '鸡蛋' go select 商品类=t.商品类+isnull(rtrim(min(REPLACE(重量,'g','')*1)) +'g-'+rtrim(max(REPLACE(重量,'g','')*1))+'g',''), sum(case when e.包装类='1级' then 1 else 0 end) as 包装#1级数量, sum(case when e.包装类='2级' then 1 else 0 end) as 包装#2级数量, count(e.商品类) as 包装总数, t.总编码 as 名称编码 from (select distinct a.商品类,b.总编码 from tc a cross join tb b where 总编码 = 'AA1') t left join tb e on t.商品类 = e.商品类 and t.总编码 = e.总编码 and t.总编码 = 'AA1' group by t.商品类,t.总编码/* 商品类 包装#1级数量 包装#2级数量 包装总数 名称编码 ------------------------------------- ----------- ----------- ----------- ---------- 白菜3g-6g 0 2 2 AA1 大蒜2g-8g 2 1 3 AA1 鸡蛋 0 0 0 AA1 牛肉 0 0 0 AA1 苹果7g-7g 0 1 1 AA1 生姜5g-5g 1 0 1 AA1 警告: 聚合或其他 SET 操作消除了 Null 值。(6 行受影响)
create table tb(商品类 nvarchar(10),重量 varchar(10),包装类 nvarchar(10),总编码 nvarchar(10)) insert into tb select '大蒜','2g','1级','AA1' insert into tb select '白菜','3g','2级','AA1' insert into tb select '大蒜','4g','2级','AA1' insert into tb select '生姜','5g','1级','AA1' insert into tb select '白菜','6g','2级','AA1' insert into tb select '苹果','7g','2级','AA1' insert into tb select '大蒜','8g','1级','AA1' gocreate table tc(商品类 nvarchar(10)) insert into tc select '大蒜' union all select '白菜' union all select '生姜' union all select '苹果' union all select '牛肉' union all select '鸡蛋' goselect 商品类+'0-5' as 商品类, 包装#1级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='1级' and REPLACE(重量,'g','')*1 between 0 and 5), 包装#2级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='2级' and REPLACE(重量,'g','')*1 between 0 and 5), 总数=(select COUNT(*) from tb where 商品类=a.商品类), 名称编码=(select top 1 总编码 from tb where 商品类=a.商品类) from tc a where 商品类 ='大蒜' union all select 商品类+'6-10' as 商品类, 包装#1级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='1级' and REPLACE(重量,'g','')*1 between 6 and 10), 包装#2级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='2级' and REPLACE(重量,'g','')*1 between 6 and 10), 总数=(select COUNT(*) from tb where 商品类=a.商品类), 名称编码=(select top 1 总编码 from tb where 商品类=a.商品类) from tc a where 商品类 ='大蒜' union all select 商品类, 包装#1级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='1级'), 包装#2级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='2级'), 总数=(select COUNT(*) from tb where 商品类=a.商品类), 名称编码=(select top 1 总编码 from tb where 商品类=a.商品类) from tc a where 商品类 not in('大蒜','白菜')/* 商品类 包装#1级数量 包装#2级数量 总数 名称编码 -------------- ----------- ----------- ----------- ---------- 大蒜0-5 1 1 3 AA1 大蒜6-10 1 0 3 AA1 生姜 1 0 1 AA1 苹果 0 1 1 AA1 牛肉 0 0 0 NULL 鸡蛋 0 0 0 NULL(6 行受影响)
create table tb(商品类 nvarchar(10),重量 varchar(10),包装类 nvarchar(10),总编码 nvarchar(10))
insert into tb select '大蒜','2g','1级','AA1'
insert into tb select '白菜','3g','2级','AA1'
insert into tb select '大蒜','4g','2级','AA1'
insert into tb select '生姜','5g','1级','AA1'
insert into tb select '白菜','6g','2级','AA1'
insert into tb select '苹果','7g','2级','AA1'
insert into tb select '大蒜','8g','1级','AA1'
gocreate table tc(商品类 nvarchar(10))
insert into tc
select '大蒜' union all
select '白菜' union all
select '生姜' union all
select '苹果' union all
select '牛肉' union all
select '鸡蛋'
go
select 商品类=t.商品类+isnull(rtrim(min(REPLACE(重量,'g','')*1))
+'g-'+rtrim(max(REPLACE(重量,'g','')*1))+'g',''),
sum(case when e.包装类='1级' then 1 else 0 end) as 包装#1级数量,
sum(case when e.包装类='2级' then 1 else 0 end) as 包装#2级数量,
count(e.商品类) as 包装总数,
t.总编码 as 名称编码
from (select distinct a.商品类,b.总编码 from tc a cross join tb b where 总编码 = 'AA1') t
left join tb e on t.商品类 = e.商品类 and t.总编码 = e.总编码 and t.总编码 = 'AA1'
group by t.商品类,t.总编码/*
商品类 包装#1级数量 包装#2级数量 包装总数 名称编码
------------------------------------- ----------- ----------- ----------- ----------
白菜3g-6g 0 2 2 AA1
大蒜2g-8g 2 1 3 AA1
鸡蛋 0 0 0 AA1
牛肉 0 0 0 AA1
苹果7g-7g 0 1 1 AA1
生姜5g-5g 1 0 1 AA1
警告: 聚合或其他 SET 操作消除了 Null 值。(6 行受影响)
商品类 包装#1级数量 包装#2级数量 包装总数 名称编码大蒜0g-5g 2 1 3 AA1
大蒜6g-10g 0 1 1 AA1
大蒜11g-20g 1 0 1 AA1
白菜0g-10g 0 1 1 AA1
白菜11g-20g 0 1 1 AA1
生姜 1 0 1 AA1
苹果 0 1 1 AA1
牛肉 0 0 0 AA1
鸡蛋 0 0 0 AA1
TO:fredrickhu,只要能实现就可以如上面的需求就可以,我不知道我表达清楚了没有,如果没有,请指正,谢谢。
+'g-'+rtrim(max(REPLACE(重量,'g','')*1))+'g',''),
“大蒜0g-5g”这个名称可以手写,但是它只要代表那个重量为0g-5g的范围,不知道我这么说你明白了没有?我如果没有解释清楚,请说明,谢谢。
那几自己手写出来union all起来吧
create table tb(商品类 nvarchar(10),重量 varchar(10),包装类 nvarchar(10),总编码 nvarchar(10))
insert into tb select '大蒜','2g','1级','AA1'
insert into tb select '白菜','3g','2级','AA1'
insert into tb select '大蒜','4g','2级','AA1'
insert into tb select '生姜','5g','1级','AA1'
insert into tb select '白菜','6g','2级','AA1'
insert into tb select '苹果','7g','2级','AA1'
insert into tb select '大蒜','8g','1级','AA1'
gocreate table tc(商品类 nvarchar(10))
insert into tc
select '大蒜' union all
select '白菜' union all
select '生姜' union all
select '苹果' union all
select '牛肉' union all
select '鸡蛋'
goselect 商品类+'0-5' as 商品类,
包装#1级数量=(select COUNT(*) from tb
where 商品类=a.商品类 and 包装类='1级'
and REPLACE(重量,'g','')*1 between 0 and 5),
包装#2级数量=(select COUNT(*) from tb
where 商品类=a.商品类 and 包装类='2级'
and REPLACE(重量,'g','')*1 between 0 and 5),
总数=(select COUNT(*) from tb where 商品类=a.商品类),
名称编码=(select top 1 总编码 from tb where 商品类=a.商品类)
from tc a where 商品类 ='大蒜'
union all
select 商品类+'6-10' as 商品类,
包装#1级数量=(select COUNT(*) from tb
where 商品类=a.商品类 and 包装类='1级'
and REPLACE(重量,'g','')*1 between 6 and 10),
包装#2级数量=(select COUNT(*) from tb
where 商品类=a.商品类 and 包装类='2级'
and REPLACE(重量,'g','')*1 between 6 and 10),
总数=(select COUNT(*) from tb where 商品类=a.商品类),
名称编码=(select top 1 总编码 from tb where 商品类=a.商品类)
from tc a where 商品类 ='大蒜'
union all
select 商品类,
包装#1级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='1级'),
包装#2级数量=(select COUNT(*) from tb where 商品类=a.商品类 and 包装类='2级'),
总数=(select COUNT(*) from tb where 商品类=a.商品类),
名称编码=(select top 1 总编码 from tb where 商品类=a.商品类)
from tc a where 商品类 not in('大蒜','白菜')/*
商品类 包装#1级数量 包装#2级数量 总数 名称编码
-------------- ----------- ----------- ----------- ----------
大蒜0-5 1 1 3 AA1
大蒜6-10 1 0 3 AA1
生姜 1 0 1 AA1
苹果 0 1 1 AA1
牛肉 0 0 0 NULL
鸡蛋 0 0 0 NULL(6 行受影响)
TO:AcHerat大哥。是的,0-5和6-10和11-20等等都是根据实际需求手动设置的,ssp2009大哥那样做可以达到我的需求,只是如果我的需求量大则代码量也会很大,不知道是否还有简洁的方法?或者另一种实现的方法?