X表: cable_no serial_no cdate 1
AAA 01 08-01
AAA 02 08-01
AAA 03 08-01
AAA 04 08-01
AAA 05 08-01
BBB 01 08-01
BBB 02 08-01
BBB 03 08-01
BBB 04 08-01
CCC 01 08-01
CCC 02 08-01
CCC 03 08-01
CCC 04 08-01
CCC 05 08-01
DDD 01 08-01
EEE 01 08-01
Y表:
cable_no amount cdate 2
AAA 05 08-02
BBB 04 08-02
CCC 05 08-02Z表:
tube_no cable_no cdate 3
AAA1 AAA 08-03
AAA2 AAA 08-03
BBB1 BBB 08-03
BBB2 BBB 08-03
BBB3 BBB 08-03
CCC1 CCC 08-03
CCC2 CCC 08-03
DDD1 DDD 08-03
DDD2 DDD 08-03
EEE1 EEE 08-03 通过以上三个表形成一个新表:
(表Y的amount 是表X中的serail_no 最大数)
tube_no cable_no cdate3 amount
AAA1 AAA 08-03 5
AAA2 AAA 08-03 5
BBB1 BBB 08-03 4
BBB2 BBB 08-03 4
BBB3 BBB 08-03 4
CCC1 CCC 08-03 5
CCC2 CCC 08-03 5
DDD1 DDD 08-03 1
DDD2 DDD 08-03 1
EEE1 EEE 08-03 1
EEE2 EEE 08-03 1
AAA 01 08-01
AAA 02 08-01
AAA 03 08-01
AAA 04 08-01
AAA 05 08-01
BBB 01 08-01
BBB 02 08-01
BBB 03 08-01
BBB 04 08-01
CCC 01 08-01
CCC 02 08-01
CCC 03 08-01
CCC 04 08-01
CCC 05 08-01
DDD 01 08-01
EEE 01 08-01
Y表:
cable_no amount cdate 2
AAA 05 08-02
BBB 04 08-02
CCC 05 08-02Z表:
tube_no cable_no cdate 3
AAA1 AAA 08-03
AAA2 AAA 08-03
BBB1 BBB 08-03
BBB2 BBB 08-03
BBB3 BBB 08-03
CCC1 CCC 08-03
CCC2 CCC 08-03
DDD1 DDD 08-03
DDD2 DDD 08-03
EEE1 EEE 08-03 通过以上三个表形成一个新表:
(表Y的amount 是表X中的serail_no 最大数)
tube_no cable_no cdate3 amount
AAA1 AAA 08-03 5
AAA2 AAA 08-03 5
BBB1 BBB 08-03 4
BBB2 BBB 08-03 4
BBB3 BBB 08-03 4
CCC1 CCC 08-03 5
CCC2 CCC 08-03 5
DDD1 DDD 08-03 1
DDD2 DDD 08-03 1
EEE1 EEE 08-03 1
EEE2 EEE 08-03 1
FROM Z
LEFT JOIN (
SELECT cable_no,MAX(serial_no) AMOUNT FROM X
GROUP BY cable_no
) T ON Z.CABLE_NO=T.CABLE_NO
FROM Z LEFT JOIN X ON Z.cable_no=X.cable_no
FROM Z LEFT JOIN X ON Z.cable_no=X.cable_noGROUP BY tube_no , Z.cable_no修改
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-20 10:33:58
---------------------------------
--> 生成测试数据表:xIf not object_id('[x]') is null
Drop table [x]
Go
Create table [x]([cable_no] nvarchar(3),[serial_no] int,[cdate] nvarchar(5))
Insert x
Select 'AAA',01,'08-01' union all
Select 'AAA',02,'08-01' union all
Select 'AAA',03,'08-01' union all
Select 'AAA',04,'08-01' union all
Select 'AAA',05,'08-01' union all
Select 'BBB',01,'08-01' union all
Select 'BBB',02,'08-01' union all
Select 'BBB',03,'08-01' union all
Select 'BBB',04,'08-01' union all
Select 'CCC',01,'08-01' union all
Select 'CCC',02,'08-01' union all
Select 'CCC',03,'08-01' union all
Select 'CCC',04,'08-01' union all
Select 'CCC',05,'08-01' union all
Select 'DDD',01,'08-01' union all
Select 'EEE',01,'08-01'
Go
--Select * from x--> 生成测试数据表:zIf not object_id('[z]') is null
Drop table [z]
Go
Create table [z]([tube_no] nvarchar(4),[cable_no] nvarchar(3),[cdate] nvarchar(5))
Insert z
Select 'AAA1','AAA','08-03' union all
Select 'AAA2','AAA','08-03' union all
Select 'BBB1','BBB','08-03' union all
Select 'BBB2','BBB','08-03' union all
Select 'BBB3','BBB','08-03' union all
Select 'CCC1','CCC','08-03' union all
Select 'CCC2','CCC','08-03' union all
Select 'DDD1','DDD','08-03' union all
Select 'DDD2','DDD','08-03' union all
Select 'EEE1','EEE','08-03'
Go
--Select * from z-->SQL查询如下:
select z.*,x.serial_no
from z
join(
select *
from x a
where not exists(
select 1
from x
where [cable_no]=a.[cable_no]
and [serial_no]>a.[serial_no])
) as x
on x.cable_no=z.cable_no/*
tube_no cable_no cdate serial_no
------- -------- ----- -----------
AAA1 AAA 08-03 5
AAA2 AAA 08-03 5
BBB1 BBB 08-03 4
BBB2 BBB 08-03 4
BBB3 BBB 08-03 4
CCC1 CCC 08-03 5
CCC2 CCC 08-03 5
DDD1 DDD 08-03 1
DDD2 DDD 08-03 1
EEE1 EEE 08-03 1(10 行受影响)
*/两表连查就可以得到了.
???
select z.* ,y.amount froma
left join y
on z.cable_no=y.cable_no
a.cdate3,b.amount
from z a join y b on a.cable_no=b.cable_no??