select * into table2 from table -- 将表中内容导入到临时表 insert into table2 select substring(产品名称,1,3)+'小计',sum(区域1)),sum(区域2)),sum(区域3) from table group by substring(产品名称,1,3) --将小计内容插入 select * from table2 group by substring(产品名称,1,3) --查询结果
SELECT b + '小计' AS 产品名称, SUM(区域1) AS 区域1, SUM(区域2) AS 区域2,SUM(区域3) AS 区域3, s0 = v.b, s1 = 1 FROM (SELECT SUBSTRING(a, 1, 3) AS b, 区域1, 区域2,区域3 FROM <tablename>) v GROUP BY b UNION ALL SELECT 产品名称, 区域1, 区域2,区域3, s0 = substring(产品名称, 1, 3), s1 = 0 FROM <tablename> ORDER BY s0, s1
写错了,不好意思SELECT b + '小计' AS 产品名称, SUM(区域1) AS 区域1, SUM(区域2) AS 区域2,SUM(区域3) AS 区域3, s0 = v.b, s1 = 1 FROM (SELECT SUBSTRING(产品名称, 1, 3) AS b, 区域1, 区域2,区域3 FROM <tablename>) v GROUP BY b UNION ALL SELECT 产品名称, 区域1, 区域2,区域3, s0 = substring(产品名称, 1, 3), s1 = 0 FROM <tablename> ORDER BY s0, s1
写错了,不好意思,你的100g,200g这些应该是4个字符,呵呵SELECT b + '小计' AS 产品名称, SUM(区域1) AS 区域1, SUM(区域2) AS 区域2,SUM(区域3) AS 区域3, s0 = v.b, s1 = 1 FROM (SELECT SUBSTRING(产品名称, 1, 4) AS b, 区域1, 区域2,区域3 FROM <tablename>) v GROUP BY b UNION ALL SELECT 产品名称, 区域1, 区域2,区域3, s0 = substring(产品名称, 1, 4), s1 = 0 FROM <tablename> ORDER BY s0, s1
----------------------- insert into tb select '100g小计',区域一=(select sum(区域一) from tb where left(产品名称,4)='100g'),区域二=(select sum(区域二) from tb where left(产品名称,4)='100g'),区域三=(select sum(区域三) from tb where left(产品名称,4)='100g') from tb union select '250g小计',区域一=(select sum(区域一) from tb where left(产品名称,4)='250g'),区域二=(select sum(区域二) from tb where left(产品名称,4)='25g'),区域三=(select sum(区域三) from tb where left(产品名称,4)='250g') from tb union select '其他小计',区域一=(select sum(区域一) from tb where left(产品名称,4)<>'100g' and left(产品名称,4)<>'250g'),区域二=(select sum(区域二) from tb where left(产品名称,4)<>'100g' and left(产品名称,4)<>'250g'),区域三=(select sum(区域三) from tb where left(产品名称,4)<>'100g' and left(产品名称,4)<>'250g') from tb 比较白痴的做法
insert into table2 select substring(产品名称,1,3)+'小计',sum(区域1)),sum(区域2)),sum(区域3)
from table group by substring(产品名称,1,3) --将小计内容插入
select * from table2 group by substring(产品名称,1,3) --查询结果
FROM (SELECT SUBSTRING(a, 1, 3) AS b, 区域1, 区域2,区域3
FROM <tablename>) v
GROUP BY b
UNION ALL
SELECT 产品名称, 区域1, 区域2,区域3, s0 = substring(产品名称, 1, 3), s1 = 0
FROM <tablename>
ORDER BY s0, s1
FROM (SELECT SUBSTRING(产品名称, 1, 3) AS b, 区域1, 区域2,区域3
FROM <tablename>) v
GROUP BY b
UNION ALL
SELECT 产品名称, 区域1, 区域2,区域3, s0 = substring(产品名称, 1, 3), s1 = 0
FROM <tablename>
ORDER BY s0, s1
FROM (SELECT SUBSTRING(产品名称, 1, 4) AS b, 区域1, 区域2,区域3
FROM <tablename>) v
GROUP BY b
UNION ALL
SELECT 产品名称, 区域1, 区域2,区域3, s0 = substring(产品名称, 1, 4), s1 = 0
FROM <tablename>
ORDER BY s0, s1
insert into tb select '100g小计',区域一=(select sum(区域一) from tb where left(产品名称,4)='100g'),区域二=(select sum(区域二) from tb where left(产品名称,4)='100g'),区域三=(select sum(区域三) from tb where left(产品名称,4)='100g') from tb
union select '250g小计',区域一=(select sum(区域一) from tb where left(产品名称,4)='250g'),区域二=(select sum(区域二) from tb where left(产品名称,4)='25g'),区域三=(select sum(区域三) from tb where left(产品名称,4)='250g') from tb
union select '其他小计',区域一=(select sum(区域一) from tb where left(产品名称,4)<>'100g' and left(产品名称,4)<>'250g'),区域二=(select sum(区域二) from tb where left(产品名称,4)<>'100g' and left(产品名称,4)<>'250g'),区域三=(select sum(区域三) from tb where left(产品名称,4)<>'100g' and left(产品名称,4)<>'250g')
from tb
比较白痴的做法