我有一张表:
iceno,goods,date,num我要查找数据表中的同一天的数据(date)且按相同的iceno号在后面按时间增加一列,如:2006110901,后面两位为区分不同iceno号所生成的不同序号。
如:
1000,xx,2006-11-9,1,200611090001
1000,yy,2006-11-9,1,200611090001
1000,zz,2006-11-9,2,200611090001
1001,xx,2006-11-9,1,200611090002
1001,hh,2006-11-9,2,200611090002
1002,hh,2006-11-9,1,200611090003
1002,xx,2006-11-9,1,200611090003
……
……
1201,hh,2006-11-9,1,200611092003
1201,xx,2006-11-9,1,200611092003
请各位帮忙!谢谢了
iceno,goods,date,num我要查找数据表中的同一天的数据(date)且按相同的iceno号在后面按时间增加一列,如:2006110901,后面两位为区分不同iceno号所生成的不同序号。
如:
1000,xx,2006-11-9,1,200611090001
1000,yy,2006-11-9,1,200611090001
1000,zz,2006-11-9,2,200611090001
1001,xx,2006-11-9,1,200611090002
1001,hh,2006-11-9,2,200611090002
1002,hh,2006-11-9,1,200611090003
1002,xx,2006-11-9,1,200611090003
……
……
1201,hh,2006-11-9,1,200611092003
1201,xx,2006-11-9,1,200611092003
请各位帮忙!谢谢了
INSERT INTO @tb
SELECT 1000, 'xx', '2006-11-9', 1, ''
UNION ALL SELECT 1000, 'yy', '2006-11-9', 1, ''
UNION ALL SELECT 1000, 'zz', '2006-11-9', 2, ''
UNION ALL SELECT 1001, 'xx', '2006-11-9', 1, ''
UNION ALL SELECT 1001, 'hh', '2006-11-9', 2, ''
UNION ALL SELECT 1002, 'hh', '2006-11-9', 1, ''
UNION ALL SELECT 1002, 'xx', '2006-11-9', 1, ''
UNION ALL SELECT 1201, 'hh', '2006-11-9', 1, ''
UNION ALL SELECT 1201, 'xx', '2006-11-9', 1, ''
DECLARE @IC INT
DECLARE @I INT
DECLARE @DT DATETIME
SET @DT = '1900/1/1'
SET @I = 0
UPDATE @tb
SET @I = CASE WHEN @IC = iceno AND @DT = DATE THEN @I WHEN @IC <> iceno AND @DT = DATE THEN @I + 1
WHEN @DT <> DATE THEN 1 END,
newcol = CONVERT(VARCHAR, DATE, 112) + RIGHT('0000' + CAST(@I AS VARCHAR), 4),
@DT = DATE,
@IC = iceno
SELECT * FROM @tb