求SQL汇总语句 Group By 后统计个数问题 有示例

Question

如我表
收支表
ID 类型  物品   价格
1  支出  冰箱   2000    
2  支出  洗衣机 3000
3  支出  苹果   50
4  支出  梨     70
5  收入  电视   1000
-----------------------
最终查询为
1:
类型  分类      记录数 总价
支出  家用电器      2  5000
支出  水果          2  120
收入  家用电器      1  1000
2：
类型    家用电器  水果
支出    5000      120
收入    1000      0要得到上面两个结果SQL要怎么写
谢谢

Answer 1

1
select 类型 ,分类 ,记录数 ,sum(价格) as 总价 from tb group by 类型 ,分类 ,记录数

Answer 2

1.
select 类型 ,分类 ,count(1) as 记录数 ,sum(价格) as 总价 from tb group by 类型 ,分类 
2.
行转列另外问一下 如何判断是家用电器??直接判断么?

Answer 3

程序是不会自动识别苹果是水果的，可以用case when 判断，但是建议还是有个类别字段的好。

Answer 4

/*
如我表
收支表
ID 类型 物品 价格
1 支出 冰箱 2000   
2 支出 洗衣机 3000
3 支出 苹果 50
4 支出 梨 70
5 收入 电视 1000
-----------------------
最终查询为
1:
类型 分类 记录数 总价
支出 家用电器 2 5000
支出 水果 2 120
收入 家用电器 1 1000
2：
类型 家用电器 水果
支出 5000 120
收入 1000 0
*/
USE master;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
ID int,
 类型 nvarchar(10), 
 物品   nvarchar(10),
 价格 int
)
INSERT INTO A--插入测试数据
select 1, '支出', '冰箱', 2000    union all
select 2,'支出', '洗衣机', 3000  union all
select 3, '支出', '苹果', 50  union all
select 4, '支出', '梨', 70  union all
select 5, '收入','电视', 1000
 go
with cte as
(
select *,case when [物品]='苹果' or [物品]='梨' then '水果' else '家用电器' end as [物品类型] from A
)
,cte1 as
(
select [物品类型],[类型],count([物品类型]) as [总数],sum([价格]) as [价格] from cte group by [类型],[物品类型] 
)
,cte2 as
(
select 
[类型],
sum(case when [物品类型]='家用电器' then [价格] else 0 end) as [家用电器],
sum(case when [物品类型]='水果' then [价格] else 0 end) as [水果]
from cte1 group by [类型]
)
select * from cte1 --的结果如下
/*
物品类型     类型         总数          价格
-------- ---------- ----------- -----------
家用电器     收入         1           1000
家用电器     支出         2           5000
水果       支出         2           120
*/
--select * from cte2 --的结果如下
/*
类型         家用电器        水果
---------- ----------- -----------
收入         1000        0
支出         5000        120*/go
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO

Answer 5

先建立一个表 来存储 家用电器 和 水果种类，都给列举全了
例如：
1、
编码表
CREATE TABLE [dbo].[编码表](
[序号] [int] NOT NULL,
[名称] [varchar](50) NULL,
[细类ID] [int] NULL,
[大类ID] [int] NULL,
[大类名称] [varchar](50) NULL
) ON [PRIMARY]GO
序号 名称 细类ID 大类ID 大类名称
1 冰箱 1 1     家用电器
2 洗衣机 2 1     家用电器
3 电视 3 1     家用电器
4 苹果 4 2     水果
5 梨 5 2     水果2、
再在你给的表上添加一列 物品ID现在我重新给你设计了一下表
收支表
CREATE TABLE [dbo].[收支表](
[ID] [int] NULL,
[类型] [varchar](50) NULL,
[物品ID] [int] NULL,
[物品] [varchar](50) NULL,
[价格] [float] NULL
) ON [PRIMARY]GO
数据为
ID 类型 物品ID 物品 价格
1 支出 1 冰箱 2000
2 支出 3 洗衣机 3000
3 支出 4 苹果 50
4 支出 5 梨 70
5 收入 2 电视 1000
至此需要的两张表已经完成现在写实现第一查询
  select [类型],S1.大类名称 as 分类,COUNT(1) 记录数,SUM([价格]) 总价
  from dbo.收支表 S2 inner join dbo.编码表 S1 on S2.物品ID=S1.细类ID
  group by [类型],S1.大类名称
结果：
类型 分类 记录数 总价
收入 家用电器 1 1000
支出 家用电器 2 5000
支出 水果 2 120实现第二个查询
  select 类型,isnull(sum(case 大类ID when 1 then 价格 end),0) as '家用电器',
         isnull(sum(case 大类ID when 2 then 价格 end),0) as '水果'
   from  dbo.收支表 S2 inner join dbo.编码表 S1 on S2.物品ID=S1.细类ID
   group by 类型
结果：
类型 家用电器 水果
收入 1000 0
支出 5000 120