表的字段转换2

Question

如何将table1和table2转换为table3
//////////////////////////////////table1
batchNo allNumber
1 000001
1 000002
1 000003
1 000004
1 000005
2 000006
2 000007
2 000008
2 000009
2 000010
3 000011
3 000012
3 000013
3 000014
3 000015
//////////////////////////////////table2
batchno usednumber
1 2
1 3
1 4
2 9
3 11
3 12
3 13
3 15
//////////////////////////////////table3
batchNo allNumber flag_used
1 000001 0
1 000002 1
1 000003 1
1 000004 1
1 000005 0
2 000006 0
2 000007 0
2 000008 0
2 000009 1
2 000010 0
3 000011 1
3 000012 1
3 000013 1
3 000014 0
3 000015 1
请给出sql 语句，谢谢

Answer 1

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-24 23:51:31
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([batchNo] INT,[allNumber] NVARCHAR(10))
INSERT [tb]
SELECT 1,'000001' UNION ALL
SELECT 1,'000002' UNION ALL
SELECT 1,'000003' UNION ALL
SELECT 1,'000004' UNION ALL
SELECT 1,'000005' UNION ALL
SELECT 2,'000006' UNION ALL
SELECT 2,'000007' UNION ALL
SELECT 2,'000008' UNION ALL
SELECT 2,'000009' UNION ALL
SELECT 2,'000010' UNION ALL
SELECT 3,'000011' UNION ALL
SELECT 3,'000012' UNION ALL
SELECT 3,'000013' UNION ALL
SELECT 3,'000014' UNION ALL
SELECT 3,'000015'
GO
--SELECT * FROM [tb]--> 生成测试数据表:tcIF NOT OBJECT_ID('[tc]') IS NULL
DROP TABLE [tc]
GO
CREATE TABLE [tc]([batchno] INT,[usednumber] INT)
INSERT [tc]
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,9 UNION ALL
SELECT 3,11 UNION ALL
SELECT 3,12 UNION ALL
SELECT 3,13 UNION ALL
SELECT 3,15
GO
--SELECT * FROM [tc]-->SQL查询如下:
select a.*,flag=case when b.[batchno] IS null then 0 else 1 end
from tb a 
LEFT join tc b 
on a.[batchNo]=b.[batchno]
and a.[allNumber]*1=b.[usednumber]
/*
batchNo allNumber flag
1 000001 0
1 000002 1
1 000003 1
1 000004 1
1 000005 0
2 000006 0
2 000007 0
2 000008 0
2 000009 1
2 000010 0
3 000011 1
3 000012 1
3 000013 1
3 000014 0
3 000015 1
*/

Answer 2

flag=0代表没用过
flag=1代表usednumber

Answer 3

select t1.batchNo ,
t1.allNumber,
flag = case when t2.usednumber is not null then 1 else 0 end
from table1 t1 left join table2 t2
on t1.batchNo = t2.batchNo and t1.allNumber = convert(int ,t2.usednumber)
-- convert(int ,t2.usednumber) 不知道这样对么？

Answer 4

CREATE TABLE [tb]([batchNo] INT,[allNumber] NVARCHAR(10))
INSERT [tb]
SELECT 1,'000001' UNION ALL
SELECT 1,'000002' UNION ALL
SELECT 1,'000003' UNION ALL
SELECT 1,'000004' UNION ALL
SELECT 1,'000005' UNION ALL
SELECT 2,'000006' UNION ALL
SELECT 2,'000007' UNION ALL
SELECT 2,'000008' UNION ALL
SELECT 2,'000009' UNION ALL
SELECT 2,'000010' UNION ALL
SELECT 3,'000011' UNION ALL
SELECT 3,'000012' UNION ALL
SELECT 3,'000013' UNION ALL
SELECT 3,'000014' UNION ALL
SELECT 3,'000015'
GO
CREATE TABLE [tc]([batchno] INT,[usednumber] INT)
INSERT [tc]
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,9 UNION ALL
SELECT 3,11 UNION ALL
SELECT 3,12 UNION ALL
SELECT 3,13 UNION ALL
SELECT 3,15
GOselect m.* , flag_used =
  (case when exists (select 1 from tc n where m.batchno = n.batchno and cast(m.allNumber as int) = n.usednumber) then 1 else 0 end) 
from tb m
drop table tb , tc/*
batchNo     allNumber  flag_used   
----------- ---------- ----------- 
1           000001     0
1           000002     1
1           000003     1
1           000004     1
1           000005     0
2           000006     0
2           000007     0
2           000008     0
2           000009     1
2           000010     0
3           000011     1
3           000012     1
3           000013     1
3           000014     0
3           000015     1（所影响的行数为 15 行）*/