SELECT 料号, 品名, 归并后序号, 品名2=CASE WHEN 品名2 IS NULL THEN ( SELECT TOP 1 品名2 FROM tb_name WHERE 归并后序号=t.归并后序号 ORDER BY 料号 ASC ) ELSE 品名2 END FROM tb_name AS t;
--> liangCK小梁 于2008-10-13 --> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (料号 INT,品名 NVARCHAR(4),归并后序号 INT,品名2 NVARCHAR(5)) INSERT INTO #T SELECT '1010200002','胶壳','1','胶壳1' UNION ALL SELECT '1010200003','胶壳','1',null UNION ALL SELECT '1010200004','胶壳','1',null UNION ALL SELECT '1010200005','胶壳','2','胶壳2' UNION ALL SELECT '1010200006','胶壳','2',null UNION ALL SELECT '1010200007','胶壳','2',null--SQL查询如下:SELECT 料号, 品名, 归并后序号, 品名2=CASE WHEN ISNULL(品名2,'')='' THEN ( SELECT TOP 1 品名2 FROM #T WHERE 归并后序号=t.归并后序号 ORDER BY 料号 ASC ) ELSE 品名2 END FROM #T AS t/* 料号 品名 归并后序号 品名2 ----------- ---- ----------- ----- 1010200002 胶壳 1 胶壳1 1010200003 胶壳 1 胶壳1 1010200004 胶壳 1 胶壳1 1010200005 胶壳 2 胶壳2 1010200006 胶壳 2 胶壳2 1010200007 胶壳 2 胶壳2(6 行受影响) */
select a.料号, a.品名1, a.归并后序号, 品名2 = ( select top 1 品名2 from @t where 归并后序号 = a.归并后序号 order by 料号 ) from @t a
select 料号,品名,归并后序号 ,新品名 = (select 新品名 from tb where 归并后序号 = a.归并后序号 and ISNULL(新品名,'')='') from tb a
写错了select 料号,品名,归并后序号 ,新品名 = (select 新品名 from tb where 归并后序号 = a.归并后序号 and ISNULL(新品名,'')<>'') from tb a
更新update T set 品名2=品名1+归并后序号 where isnull(品名2,'')=''
IF object_id('tb','u') IS NOT NULL DROP TABLE tb GOCREATE TABLE tb(料号 INT,品名 NVARCHAR(4),归并后序号 INT,品名2 NVARCHAR(5)) INSERT tb SELECT '1010200002','胶壳','1','胶壳1' UNION ALL SELECT '1010200003','胶壳','1',null UNION ALL SELECT '1010200004','胶壳','1',null UNION ALL SELECT '1010200005','胶壳','2','胶壳2' UNION ALL SELECT '1010200006','胶壳','2',null UNION ALL SELECT '1010200007','胶壳','2',null GODECLARE @s VARCHAR(10) UPDATE tb SET @s=ISNULL(品名2,@s),品名2=ISNULL(品名2,@s) SELECT * FROM tb /* 1010200002 胶壳 1 胶壳1 1010200003 胶壳 1 胶壳1 1010200004 胶壳 1 胶壳1 1010200005 胶壳 2 胶壳2 1010200006 胶壳 2 胶壳2 1010200007 胶壳 2 胶壳2 */
create table #CC ( ID varchar (20), [Name] nvarchar(20), Num int, NameNum nvarchar(20) default null ) insert into #CC select '1010200002','胶壳',1,'胶壳1' insert into #CC select '1010200003','胶壳',1,'' insert into #CC select '1010200004','胶壳',1,'' insert into #CC select '1010200005','胶壳',2,'胶壳2' insert into #CC select '1010200006','胶壳',2,'' insert into #CC select '1010200007','胶壳',2,''select ID,[Name],Num,([Name]+Cast(Num as nvarchar(10))) as '品名' from #CC
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (料号 INT,品名 NVARCHAR(4),归并后序号 INT,品名2 NVARCHAR(5))
INSERT INTO #T
SELECT '1010200002','胶壳','1','胶壳1' UNION ALL
SELECT '1010200003','胶壳','1',null UNION ALL
SELECT '1010200004','胶壳','1',null UNION ALL
SELECT '1010200005','胶壳','2','胶壳2' UNION ALL
SELECT '1010200006','胶壳','2',null UNION ALL
SELECT '1010200007','胶壳','2',null--SQL查询如下:SELECT 料号,
品名,
归并后序号,
品名2=CASE
WHEN ISNULL(品名2,'')=''
THEN
(
SELECT TOP 1 品名2
FROM #T
WHERE 归并后序号=t.归并后序号
ORDER BY 料号 ASC
)
ELSE 品名2
END
FROM #T AS t/*
料号 品名 归并后序号 品名2
----------- ---- ----------- -----
1010200002 胶壳 1 胶壳1
1010200003 胶壳 1 胶壳1
1010200004 胶壳 1 胶壳1
1010200005 胶壳 2 胶壳2
1010200006 胶壳 2 胶壳2
1010200007 胶壳 2 胶壳2(6 行受影响)
*/
select
a.料号,
a.品名1,
a.归并后序号,
品名2 = (
select top 1 品名2
from @t
where 归并后序号 = a.归并后序号
order by 料号
)
from @t a
select 料号,品名,归并后序号
,新品名 = (select 新品名 from tb where 归并后序号 = a.归并后序号 and ISNULL(新品名,'')='')
from tb a
,新品名 = (select 新品名 from tb where 归并后序号 = a.归并后序号 and ISNULL(新品名,'')<>'')
from tb a
set 品名2=品名1+归并后序号
where isnull(品名2,'')=''
DROP TABLE tb
GOCREATE TABLE tb(料号 INT,品名 NVARCHAR(4),归并后序号 INT,品名2 NVARCHAR(5))
INSERT tb SELECT '1010200002','胶壳','1','胶壳1'
UNION ALL SELECT '1010200003','胶壳','1',null
UNION ALL SELECT '1010200004','胶壳','1',null
UNION ALL SELECT '1010200005','胶壳','2','胶壳2'
UNION ALL SELECT '1010200006','胶壳','2',null
UNION ALL SELECT '1010200007','胶壳','2',null
GODECLARE @s VARCHAR(10)
UPDATE tb SET @s=ISNULL(品名2,@s),品名2=ISNULL(品名2,@s)
SELECT * FROM tb
/*
1010200002 胶壳 1 胶壳1
1010200003 胶壳 1 胶壳1
1010200004 胶壳 1 胶壳1
1010200005 胶壳 2 胶壳2
1010200006 胶壳 2 胶壳2
1010200007 胶壳 2 胶壳2
*/
(
ID varchar (20),
[Name] nvarchar(20),
Num int,
NameNum nvarchar(20) default null
)
insert into #CC select '1010200002','胶壳',1,'胶壳1'
insert into #CC select '1010200003','胶壳',1,''
insert into #CC select '1010200004','胶壳',1,''
insert into #CC select '1010200005','胶壳',2,'胶壳2'
insert into #CC select '1010200006','胶壳',2,''
insert into #CC select '1010200007','胶壳',2,''select ID,[Name],Num,([Name]+Cast(Num as nvarchar(10))) as '品名' from #CC