Question

sql语句计算一年里分别每个月的收入

比如我有一张商品卖出记录表shopp：id shopp date employee里面字段就只有3个：一个主ID、卖出的商品、时间、对应的员工，要求是分别查询出每一年里每个月的某个员工卖出商品的个数。而提成工资是该员工必需在每个月你卖出衣服的件数大于3件。。。

在这里我只需要查出在每年里每个员工每一个卖出的件数？？？
有哪为高手帮忙写个sql

Answer 1

问题是,你一条记录只卖一件商品?
如果时的话: 每年每个月

select datepart(year,date)*100+datepart(month,date) YEARMONTH,count(*) SPSL,distinct(employee) from shopp
group by employee

如果表中shopp记录的是商品数量: 每年每个月

select datepart(year,date)*100+datepart(month,date) YEARMONTH,sum(shopp) SPSL,distinct(employee) from shopp
group by employee

Answer 2

select employee,sum(shopp) as 商品数,CONVERT (char(6), date ,112) as 时间
from shopp
group by employee,时间

select employee,sum(shopp) as 商品数,CONVERT (char(6), date ,112) as 时间
from shopp
group by employee,时间
having sum(shopp) >3

Answer 3

建议你行列转换，把列换成商品，员工和时间（1-12月）共14列来做，至于怎么行列转换，去网上一搜一堆，具体不写了

Answer 4

update
配件
set
套数
=(
select
round(min(a))
from
((select
零件1/1
as
a
from
配件)
union(select
零件2/2
as
a
from
配件)
union
(select
零件3/3
as
a
from
配件))
);