求一个sql的问题急求！！

junjun19885871 2012-10-15 04:08:38

输入
date code change
2011.04.11 000000703-005523-029 s
2011.06.18 000000703-005523-029 d
2010.06.03 000000703-005736-007 s
2010.08.14 000000703-008227-020 s
2010.04.30 000000703-008227-021 s
2010.05.14 000000703-008240-004 s
2010.11.20 000000703-008278-013 s
2010.11.20 000000703-008278-022 s
2010.04.30 000000703-008282-014 s
2010.08.18 000000703-008299-016 s
2010.08.18 000000703-008299-019 s
2011.06.28 000000703-008899-000 s
2011.03.10 000000703-008899-004 s
2011.04.09 000000703-008899-004 d
2011.04.11 000000703-008899-004 u
2010.06.17 000000703-008899-005 s
2010.11.16 000000703-008899-005 d

输出

date code change
2011.04.11 000000703-005523-029 sd
2010.06.03 000000703-005736-007 s
2010.08.14 000000703-008227-020 s
2010.04.30 000000703-008227-021 s
2010.05.14 000000703-008240-004 s
2010.11.20 000000703-008278-013 s
2010.11.20 000000703-008278-022 s
2010.04.30 000000703-008282-014 s
2010.08.18 000000703-008299-016 s
2010.08.18 000000703-008299-019 s
2011.06.28 000000703-008899-000 s
2011.03.10 000000703-008899-004 sdu
2010.06.17 000000703-008899-005 sd

...全文

65 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

E次奥 2012-10-15

打赏
举报

如果数据是现成的在某个文件里保存的话，写个程序循环插入。

junjun19885871 2012-10-15

打赏
举报

如果是插入到csv形式呢？数据上万呢楼上的先谢谢了啊

汤姆克鲁斯 2012-10-15

打赏
举报

--> 测试数据：[tb]

IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]

GO 

CREATE TABLE [tb]([date] DATETIME,[code] VARCHAR(20),[change] VARCHAR(1))

INSERT [tb]

SELECT '2011.04.11','000000703-005523-029','s' UNION ALL

SELECT '2011.06.18','000000703-005523-029','d' UNION ALL

SELECT '2010.06.03','000000703-005736-007','s' UNION ALL

SELECT '2010.08.14','000000703-008227-020','s' UNION ALL

SELECT '2010.04.30','000000703-008227-021','s' UNION ALL

SELECT '2010.05.14','000000703-008240-004','s' UNION ALL

SELECT '2010.11.20','000000703-008278-013','s' UNION ALL

SELECT '2010.11.20','000000703-008278-022','s' UNION ALL

SELECT '2010.04.30','000000703-008282-014','s' UNION ALL

SELECT '2010.08.18','000000703-008299-016','s' UNION ALL

SELECT '2010.08.18','000000703-008299-019','s' UNION ALL

SELECT '2011.06.28','000000703-008899-000','s' UNION ALL

SELECT '2011.03.10','000000703-008899-004','s' UNION ALL

SELECT '2011.04.09','000000703-008899-004','d' UNION ALL

SELECT '2011.04.11','000000703-008899-004','u' UNION ALL

SELECT '2010.06.17','000000703-008899-005','s' UNION ALL

SELECT '2010.11.16','000000703-008899-005','d'

--------------开始查询--------------------------



SELECT [date]=MIN([date]),[code],[change]=(SELECT ''+[change] FROM [tb] WHERE [code]=t.[code] FOR XML PATH('')) FROM [tb] AS t

GROUP BY [code]

----------------结果----------------------------

/* 

date	code	change

2011-04-11 00:00:00.000	000000703-005523-029	sd

2010-06-03 00:00:00.000	000000703-005736-007	s

2010-08-14 00:00:00.000	000000703-008227-020	s

2010-04-30 00:00:00.000	000000703-008227-021	s

2010-05-14 00:00:00.000	000000703-008240-004	s

2010-11-20 00:00:00.000	000000703-008278-013	s

2010-11-20 00:00:00.000	000000703-008278-022	s

2010-04-30 00:00:00.000	000000703-008282-014	s

2010-08-18 00:00:00.000	000000703-008299-016	s

2010-08-18 00:00:00.000	000000703-008299-019	s

2011-06-28 00:00:00.000	000000703-008899-000	s

2011-03-10 00:00:00.000	000000703-008899-004	sdu

2010-06-17 00:00:00.000	000000703-008899-005	sd

*/