34,575
社区成员
发帖
与我相关
我的任务
分享
--补空白缺省数据问题
---客户的一张execl 有一列(Itype)单元格列(有规律)合并了,我把数据导入到数据表中出现有空白的情况。现想将之补全
--求思路分析、求解答
if exists (select * from sysobjects where id = OBJECT_ID('[ITest]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [ITest]
CREATE TABLE [ITest] (
[Itype] [nchar] (10) NULL,
[Idata] [nchar] (10) NULL)
INSERT [ITest] ([Itype],[Idata]) VALUES ( N'I',N'39.1')
INSERT [ITest] ([Idata]) VALUES ( N'12.12')
INSERT [ITest] ([Idata]) VALUES ( N'12.22')
INSERT [ITest] ([Idata]) VALUES ( N'253.69')
INSERT [ITest] ([Idata]) VALUES ( N'12.68')
INSERT [ITest] ([Itype],[Idata]) VALUES ( N'II',N'32.143')
INSERT [ITest] ([Idata]) VALUES ( N'35.31')
INSERT [ITest] ([Itype],[Idata]) VALUES ( N'III',N'25.36')
go
--原表数据
--Itype Idata
--I 39.1
--NULL 12.12
--NULL 12.22
--NULL 253.69
--NULL 12.68
--II 32.143
--NULL 35.31
--III 25.36
--期待结果
--Itype Idata
--I 39.1
--I 12.12
--I 12.22
--I 253.69
--I 12.68
--II 32.143
--II 35.31
--III 25.36
CREATE TABLE #ITest (
[Itype] [nchar] (10) NULL,
[Idata] [nchar] (10) NULL)
INSERT #ITest ([Itype],[Idata]) VALUES ( N'I',N'39.1')
INSERT #ITest ([Idata]) VALUES ( N'12.12')
INSERT #ITest ([Idata]) VALUES ( N'12.22')
INSERT #ITest ([Idata]) VALUES ( N'253.69')
INSERT #ITest ([Idata]) VALUES ( N'12.68')
INSERT #ITest ([Itype],[Idata]) VALUES ( N'II',N'32.143')
INSERT #ITest ([Idata]) VALUES ( N'35.31')
INSERT #ITest ([Itype],[Idata]) VALUES ( N'III',N'25.36')
SELECT * FROM #ITest
;WITH test1
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS ID
,*
FROM #ITest
)
,test2 AS
(
SELECT *,Itype AS Itype1 FROM test1 WHERE ID =1
UNION ALL
SELECT A.*,CASE WHEN A.Itype IS NULL THEN (CASE WHEN B.Itype1 IS NOT NULL THEN B.Itype1 END)ELSE A.Itype END
FROM test1 AS A,test2 AS B
WHERE A.ID=B.ID+1
)
SELECT IType1 AS Itype,Idata FROM test2