如何取重复数据中的第一条.详细请看例子
年月, 工号,工资
200601, 001,900
200602, 001,900
200603, 001,900
200604, 001,900
200605, 001,1000
200606, 001,1000
200607, 001,1200
需求结果
200601, 001,900
200605, 001,1000
200607, 001,1200
还有个请求,能否把以上数据放横,即:
001,200601,900,200605,1000,200607,1200
谢谢!
[解决办法]
select min(年月) as 年月,工号,工资 from 表 group by 工号,工资
[解决办法]
年月, 工号,工资
200601, 001,900
200602, 001,900
200603, 001,900
200604, 001,900
200605, 001,1000
200606, 001,1000
200607, 001,1200
需求结果
200601, 001,900
200605, 001,1000
200607, 001,1200
--------
Select
Min(年月) As 年月,
工号,
工资
From
TableName
Group By
工号,
工资
[解决办法]
select min(年月) as 年月,工号,工资 from 表 group by 工号,工资
[解决办法]
还有个请求,能否把以上数据放横,即:
001,200601,900,200605,1000,200607,1200
--------
--如果只有這麼多數據
Declare @S Nvarchar(1000)
Select @S = ' '
Select @S = @S + ', ' + 工号 + ', ' + Min(年月) + ', ' + Rtrim(工资) From TableName Group By 工号,工资
Select @S = Stuff(@S, 1, 1, ' ')
Select @S
[解决办法]
Create Table TEST
(年月Varchar(10),
工号Varchar(10),
工资Int)
Insert TEST Select '200601 ', '001 ',900
Union All Select '200602 ', '001 ',900
Union All Select '200603 ', '001 ',900
Union All Select '200604 ', '001 ',900
Union All Select '200605 ', '001 ',1000
Union All Select '200606 ', '001 ',1000
Union All Select '200607 ', '001 ',1200
GO
Select
Min(年月) As 年月,
工号,
工资
From
TEST
Group By
工号,
工资
Declare @S Nvarchar(1000)
Select @S = ' '
Select @S = @S + ', ' + 工号 + ', ' + Min(年月) + ', ' + Rtrim(工资) From TEST Group By 工号,工资
Select @S = Stuff(@S, 1, 1, ' ')
Select @S
GO
Drop Table TEST
--Result
/*
年月工号工资
200601001900
2006050011000
2006070011200
001,200601,900,001,200605,1000,001,200607,1200
*/
我的异常网推荐解决方案:软件开发者薪资,http://www.myexception.cn/other/1391128.html