SQL汇总问题??

lingling82 2004-05-08 02:28:58
select name,wage,sum(wage) from mytable group by name
请问如何精确写出SQL语句?(上面的有问题)
我试了很久了,请大家帮帮忙啊!
...全文
35 12 点赞 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lingling82 2004-05-09
代码很乱,不好意思哦
  • 打赏
  • 举报
回复
lingling82 2004-05-09
这样我可以求出每个工人所有工资的和,但是我想添加个日期条件,开始日期和截止日期,需要由用户输入,这应该如何实现,这个日期条件要加在哪里?

create view wage_zs_view as
select distinct E37_worker.Wor_name,R8_4_workroom.Wor_type,DM_class.class_name,E4_workroom.Workroom_date,E4_workroom.Workroom_class,R8_4_workroom.Workroom_cq,E1_Material.M_name,R8_4_workroom.Workroom_peo_num,R8_4_workroom.Workroom_good_num,R8_4_workroom.Workroom_pro_price,R8_4_workroom.Workroom_is_add,R8_4_workroom.Workroom_ycf,R8_4_workroom.Workroom_time_s*1.5+R8_4_workroom.Workroom_time_f*2+R8_4_workroom.Workroom_stop_time*3 as wage_stop,R8_4_workroom.Workroom_pro_price*R8_4_workroom.Workroom_peo_num+R8_4_workroom.Workroom_is_add+R8_4_workroom.Workroom_time_s*1.5+R8_4_workroom.Workroom_time_f*2+R8_4_workroom.Workroom_stop_time*3 as wage
from R8_4_workroom,E4_workroom,E37_worker,DM_class,E1_Material
where E4_workroom.Workroom_id=R8_4_workroom.Workroom_id and R8_4_workroom.Wor_id=E37_worker.Wor_id and R8_4_workroom.M_id=E1_Material.M_id and E4_workroom.class_id =DM_class.class_id and E4_workroom.class_id in (select class_id from DM_class where class_name like '注塑%')

create view wage_zs_view2 as
select Wor_name,Workroom_date,Workroom_cq,Workroom_ycf,wage from wage_zs_view

select wage_zs_view.Wor_name,wage_zs_view.Wor_type,wage_zs_view.class_name,wage_zs_view.Workroom_date,wage_zs_view.Workroom_class,wage_zs_view.Workroom_cq,wage_zs_view.M_name,wage_zs_view.Workroom_peo_num,wage_zs_view.Workroom_good_num,wage_zs_view.Workroom_pro_price,wage_zs_view.Workroom_is_add,wage_zs_view.Workroom_ycf,wage_zs_view.wage_stop,wage_zs_view.wage,total_cq=(select sum(wage_zs_view2.Workroom_cq) from wage_zs_view2 where wage_zs_view.Wor_name=wage_zs_view2.Wor_name),total_ycf=(select sum(wage_zs_view2.Workroom_ycf) from wage_zs_view2 where wage_zs_view.Wor_name=wage_zs_view2.Wor_name),total_wage=(select sum(wage_zs_view2.wage) from wage_zs_view2 where wage_zs_view.Wor_name=wage_zs_view2.Wor_name) from wage_zs_view group by wage_zs_view.Wor_name,wage_zs_view.Wor_type,wage_zs_view.class_name,wage_zs_view.Workroom_date,wage_zs_view.Workroom_class,wage_zs_view.Workroom_cq,wage_zs_view.M_name,wage_zs_view.Workroom_peo_num,wage_zs_view.Workroom_good_num,wage_zs_view.Workroom_pro_price,wage_zs_view.Workroom_is_add,wage_zs_view.Workroom_ycf,wage_zs_view.wage_stop,wage_zs_view.wage
  • 打赏
  • 举报
回复
lingling82 2004-05-09
这样还不是两个求和,我是做个工资报表,每天的工资要显示,最后加个工资小计!
  • 打赏
  • 举报
回复
luckds 2004-05-08
恩 但是有意思吗 呵呵

select name,wage,sum(wage) from mytable group by name
请问如何精确写出SQL语句?(上面的有问题)

要不你这样吧

select name,sum(wage) as wage,sum(wage) as zwage from mytable group by name

GOOD LUCK!
  • 打赏
  • 举报
回复
DontWorry 2004-05-08
select name,
max(wage) as wage,
sum(wage)
from mytable group by name
  • 打赏
  • 举报
回复
lingling82 2004-05-08
可是事实上我需要wage字段啊,这样要如何解决呢?
  • 打赏
  • 举报
回复
lgs666 2004-05-08
姓名,平均工资,总工资:

select name,avg(wage),sum(wage) from mytable group by name
  • 打赏
  • 举报
回复
internetcsdn 2004-05-08
select name,sum(wage) as sum_wage from mytable group by name
  • 打赏
  • 举报
回复
yufile 2004-05-08
select name,sum(wage) from mytable
group by name
  • 打赏
  • 举报
回复
victorycyz 2004-05-08

select [name],sum(wage) as sumwage from mytable group by [name]
  • 打赏
  • 举报
回复
lazybone 2004-05-08
select name,sum(wage) from mytable group by name

想一下, wage 与sum(wage) 根本是不同的层次关系, 没可以出现在同一个表中吧?
  • 打赏
  • 举报
回复
jlmis 2004-05-08
去掉wage字段
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-05-08 02:28
社区公告
暂无公告