select 期间,工号,工资 from table a where a.工号 in (select 工号 from table group by 工号 having count(工号)>1 )
select 期间=min(期间),工号,工资 from 表 group by 工号,工资
select 期间,工号,工资 from TableName where year(期间) = '2004' group by 工号
存储过程: CREATE PROCEDURE procAAAAA @Year_ varchar(4) AS select 期间,工号,工资 from TableName where year(期间) = @Year_ group by 工号
支持zhang_yzy(六子儿) 楼主只是要发生变动的,没变动的不要吧
select 期间=min(convert(datetime,期间)),工号,工资 from table group by 工号,工资
不好意思好像没有这么简单,以上都不对吧 也就是某一年中,列出有两个以上工资的员工(一年中没变动过的不要列) 且是列出变动后的第一个月份难点是那个变动的第一个月份. 直接这样写 select 期间=min(convert(datetime,期间)),工号,工资 from table group by 工号,工资 是不对的
我写的:感觉在兜圈子,不然真不好实现 alter proc vcchangetotalpay --'2004-10-10' @term smalldatetime as create table #temptotalpay ( term smalldatetime, badge varchar(10), totalpay decimal(18,2) )insert #temptotalpay(badge,totalpay) select a.badge,a.totalpay from ( select badge,totalpay from cstandard_all where datediff(year,@term,term)=0 group by badge, totalpay ) a where badge in (select badge from (select badge,totalpay from cstandard_all where datediff(year,@term,term)=0 group by badge, totalpay) b group by badge having count(badge)>1) update a set term=b.term from #temptotalpay a, (select * from cstandard_all where datediff(year,term,@term)=0) b where a.badge collate chinese_prc_ci_as =b.badge collate chinese_prc_ci_as and a.totalpay=b.totalpayselect * from #temptotalpay
我的写错了,最后update,有问题,还在思考中
我觉得把它分开来做 1、先列出按工号排最小日期的记录 into tableA 2、再列出按工号排最大日期的记录 into tableB 3、再把tableA里有tableB里的工号的记录插入tableB 这样tableB里的记录就是最后想要的结果 ----个人认为!
一年的数据也不多,放在前台处理也许方便点,没有必要都放在Sql Server上处理呀
select 工号,count(工资)
select 工号,工资,min(期间) from table a where (select count(distinct(工资)) from table where 工号=a.工号)>1 group by 工号,工资
select 工号,工资,min(期间) from table a where (select count(distinct(工资)) from table where 工号=a.工号)>1 group by 工号,工资
或者修改一下: select 工号,工资,min(期间) from table a where 工号 in (select 工号 from table group 工号 having count(distinct(工资))) group by 工号,工资
declare @t table(期间 datetime,工号 varchar(10),工资 int) insert @t select 期间=min(convert(datetime,期间)),工号,工资 from table group by 工号,工资 order by 工号,期间select t.* from @t t where (select count(*) from @t where 工号=t.工号)>1 没问题了
或者: select 期间=min(convert(datetime,期间)),工号,工资 from table t where (select count(distinct(工资)) from table where 工号=t.工号)>1 group by 工号,工资 order by 工号,期间
from table a
where a.工号 in (select 工号 from table group by 工号 having count(工号)>1 )
from TableName
where year(期间) = '2004'
group by 工号
CREATE PROCEDURE procAAAAA
@Year_ varchar(4)
AS
select 期间,工号,工资
from TableName
where year(期间) = @Year_
group by 工号
楼主只是要发生变动的,没变动的不要吧
from table
group by 工号,工资
也就是某一年中,列出有两个以上工资的员工(一年中没变动过的不要列) 且是列出变动后的第一个月份难点是那个变动的第一个月份.
直接这样写
select 期间=min(convert(datetime,期间)),工号,工资
from table
group by 工号,工资
是不对的
alter proc vcchangetotalpay --'2004-10-10'
@term smalldatetime
as
create table #temptotalpay
(
term smalldatetime,
badge varchar(10),
totalpay decimal(18,2)
)insert #temptotalpay(badge,totalpay)
select a.badge,a.totalpay from
(
select badge,totalpay from cstandard_all where datediff(year,@term,term)=0 group by badge, totalpay
) a
where badge in (select badge from (select badge,totalpay from cstandard_all where datediff(year,@term,term)=0 group by badge, totalpay) b
group by badge having count(badge)>1) update a set term=b.term from #temptotalpay a, (select * from cstandard_all where datediff(year,term,@term)=0) b
where a.badge collate chinese_prc_ci_as =b.badge collate chinese_prc_ci_as and a.totalpay=b.totalpayselect * from #temptotalpay
1、先列出按工号排最小日期的记录 into tableA
2、再列出按工号排最大日期的记录 into tableB
3、再把tableA里有tableB里的工号的记录插入tableB
这样tableB里的记录就是最后想要的结果
----个人认为!
select 工号,工资,min(期间)
from table a
where (select count(distinct(工资)) from table where 工号=a.工号)>1
group by 工号,工资
from table a
where (select count(distinct(工资)) from table where 工号=a.工号)>1
group by 工号,工资
select 工号,工资,min(期间)
from table a
where 工号 in (select 工号 from table group 工号 having count(distinct(工资)))
group by 工号,工资
insert @t
select 期间=min(convert(datetime,期间)),工号,工资
from table
group by 工号,工资
order by 工号,期间select t.* from @t t where (select count(*) from @t where 工号=t.工号)>1
没问题了
select 期间=min(convert(datetime,期间)),工号,工资
from table t
where (select count(distinct(工资)) from table where 工号=t.工号)>1
group by 工号,工资
order by 工号,期间