这个表tt
id name yuefen xiaoshou1 aaa 1 56
2 aaa 1 34
3 aaa 1 36
4 aaa 2 23
5 aaa 2 87
6 aaa 3 12
7 aaa 3 23
8 bbb 3 56
9 bbb 3 83
10 bbb 2 87
11 bbb 1 98
12 bbb 1 45
13 ccc 1 34
要求查询:
每个name里有的人的yuefen为1时的xiaoshou总和
和全部的销售总和
id name yuefen xiaoshou1 aaa 1 56
2 aaa 1 34
3 aaa 1 36
4 aaa 2 23
5 aaa 2 87
6 aaa 3 12
7 aaa 3 23
8 bbb 3 56
9 bbb 3 83
10 bbb 2 87
11 bbb 1 98
12 bbb 1 45
13 ccc 1 34
要求查询:
每个name里有的人的yuefen为1时的xiaoshou总和
和全部的销售总和
解决方案 »
- 请问linQ能取代正则表达式吗?
- 如何用<p>,<P>等..用正则分割字符串
- button 怎么去除边框
- C# 发生了 System.AccessViolationException
- persist security info=true
- 大家帮忙看下这个结构在c#里怎么写
- 下载一个C#网站,布置到IIS上,访问出现问题:对路径“d:\windows\system32\inetsrv\RuntimeError.log”的访问被拒绝。
- TreeView问题
- 这个SQL数据库里的表要怎样写约束呢?
- C#中怎样能取得局域网内所有电脑的IP地址和当前登录用户名,包括win98,winxp,winnt,win2000的.
- 请问在C#中,静态函数和动态函数的作用区别是?
- 关于调用WebService生成的XML(wsdl)文档的问题?
union
select "总和",sum(xiaoshou) form tt where yuefen = 1
姓名 1月 全年
aaa xxx xxx
(
id int,
name nvarchar(10),
yuefen nvarchar(10),
xiaoshou int
)insert into testsum
select 1,'aaa',1,56
union all select 2,'aaa',1,34
union all select 3,'aaa',1,36
union all select 4,'aaa',2,23
union all select 5,'aaa',2,87
union all select 6,'aaa',3,12
union all select 7,'aaa',3,23
union all select 8,'bbb',3,56
union all select 9,'bbb',3,83
union all select 10,'bbb',2,87
SELECT case when yuefen is null then 'Total' else yuefen end as 'yuefen', SUM(xiaoshou) as 'total xiaoshou
FROM testsum
GROUP BY yuefen WITH ROLLUP
FROM testsum
GROUP BY yuefen WITH ROLLUP少了一“'”
'全年' = (select sum(xiaohou)from 表)
from 表
where yuefen=1
统计每个人的销售纪录
姓名,每月,全年
aaa,月和,年和
就是统计每一个人的
利用视图create view tempview as
select distinct name,sum(xiaoshou) as yuefensum,0 as zongsum from tt where yuefen = '1' group by name
union
select distinct name,0 as yuefensum,sum(xiaoshou) as zongsum from tt group by name
go
select distinct name as 姓名,sum(yuefensum) as '1月',sum(zongsum) as 全年 from tempview group by name
go
drop view tempview
go
有没有语句稍微简单点的?如分月
select name,sum(xiaoshou) from tt where yuefen='1' group by name年
select name,sum(xiaoshou) from tt group by name能不能inner join起来呢
where a.name = b.name
group by a.name,a.yuefen,b.year_sales
order by a.name,a.yuefen
select * from aaa了,呵呵
多谢各位