这个语句怎么写？热心参与者皆有分奖励！
现在有两个表tab1，tab2，是一个关于产品检验记录的，一个是物理性能，一个是化学性能，每个产品都有一个样品号，但同一个样品有可能试验多次，比如样品号是K1234－1表示是产品编号为K1234的第1个样品检验记录，K07－1234－2表示是07年产品编号为1234的第2个样品检验记录，tab2中也一样的含义，现在的需求是想一次取出两个表中同一个编号的记录，比如上边的1234的记录，但第一个表中取时间最早的一条记录，tab2中取时间最晚的一条记录，
表机构如下：
create table tab1(riqi datetime, no varchar(10),a1 varchar(6),a2 varchar(6),a3 varchar(6))
insert into tab1
select '2007-02-01 10:45:20 ', 'K07-1234-1 ', '11 ', '12 ', '21 '
union all select '2007-02-01 10:47:20 ', 'K07-1234-2 ', '12 ', '13 ', '23 '
union all select '2007-02-01 10:58:20 ', 'K07-1234 ', '16 ', '17 ', '19 '
union all select '2007-02-01 11:05:20 ', 'K07-1235-1 ', '13 ', '22 ', '22 '
union all select '2007-02-01 11:15:12 ', 'K07-1235-2 ', '21 ', '12 ', '44 '
union all select '2007-02-01 10:45:20 ', 'K07-1235-3 ', '31 ', '32 ', '34 '
union all select '2007-02-01 10:45:20 ', 'K07-1236-1 ', '11 ', '12 ', '21 '
create table tab2(riqi datetime, no varchar(10),b1 varchar(6),b2 varchar(6),b3 varchar(6))
insert into tab2
select '2007-02-01 11:45:20 ', 'K07-1234-1 ', 'A ', 'B ', 'C '
union all select '2007-02-01 11:47:20 ', 'K07-1234-2 ', 'B ', 'F ', 'D '
union all select '2007-02-01 11:58:20 ', 'K07-1234-3 ', 'E ', 'F ', 'G '
union all select '2007-02-01 12:05:20 ', 'K07-1235-1 ', 'T ', 'G ', 'W '
union all select '2007-02-01 11:15:12 ', 'K07-1235-2 ', 'T ', 'E ', 'G '
union all select '2007-02-01 10:45:20 ', 'K07-1235-3 ', 'Q3 ', 'W3 ', 'Y4 '
union all select '2007-02-01 10:45:20 ', 'K07-1236-1 ', 'D1 ', 'F2 ', 'R1 '
[解决办法]
那用union all把两个表的数据放一个表里就OK了啊
[解决办法]
那就
第一个表中取时间最早的一条记录
union all
tab2中取时间最晚的一条记录
[解决办法]
group by substring([no],1,8)
[解决办法]
你的那果....在看不懂
按我的理解了一
create table tab1(riqi datetime, no varchar(10),a1 varchar(6),a2 varchar(6),a3 varchar(6))
insert into tab1
select '2007-02-01 10:45:20 ', 'K07-1234-1 ', '11 ', '12 ', '21 '
union all select '2007-02-01 10:47:20 ', 'K07-1234-2 ', '12 ', '13 ', '23 '
union all select '2007-02-01 10:58:20 ', 'K07-1234 ', '16 ', '17 ', '19 '
union all select '2007-02-01 11:05:20 ', 'K07-1235-1 ', '13 ', '22 ', '22 '
union all select '2007-02-01 11:15:12 ', 'K07-1235-2 ', '21 ', '12 ', '44 '
union all select '2007-02-01 10:45:20 ', 'K07-1235-3 ', '31 ', '32 ', '34 '
union all select '2007-02-01 10:45:20 ', 'K07-1236-1 ', '11 ', '12 ', '21 '
create table tab2(riqi datetime, no varchar(10),b1 varchar(6),b2 varchar(6),b3 varchar(6))
insert into tab2
select '2007-02-01 11:45:20 ', 'K07-1234-1 ', 'A ', 'B ', 'C '
union all select '2007-02-01 11:47:20 ', 'K07-1234-2 ', 'B ', 'F ', 'D '
union all select '2007-02-01 11:58:20 ', 'K07-1234-3 ', 'E ', 'F ', 'G '
union all select '2007-02-01 12:05:20 ', 'K07-1235-1 ', 'T ', 'G ', 'W '
union all select '2007-02-01 11:15:12 ', 'K07-1235-2 ', 'T ', 'E ', 'G '
union all select '2007-02-01 10:45:20 ', 'K07-1235-3 ', 'Q3 ', 'W3 ', 'Y4 '
union all select '2007-02-01 10:45:20 ', 'K07-1236-1 ', 'D1 ', 'F2 ', 'R1 '
select T1.riqi,T1.[no],a1,a2,a3,b1,b2,b3
from
(select tab1.riqi,substring(tab1.[no],1,8) as [no],a1,a2,a3 from tab1
inner join
(select min(riqi) as riqi,substring([no],1,8) as [no] from tab1 group by substring([no],1,8)) A
on tab1.riqi=A.riqi and substring(tab1.[no],1,8)=A.[no]) T1
left join
(select tab2.riqi,substring(tab2.[no],1,8) as [no],b1,b2,b3 from tab2
inner join
(select max(riqi) as riqi,substring([no],1,8) as [no] from tab2 group by substring([no],1,8)) A
on tab2.riqi=A.riqi and substring(tab2.[no],1,8)=A.[no]) T2
on T1.[no]=T2.[no]
drop table tab1,tab2
/*
riqi no a1 a2 a3 b1 b2 b3
------------------------------------------------------ ---------- ------ ------
2007-02-01 10:45:20.000 K07-1234 11 12 21 E F G
2007-02-01 10:45:20.000 K07-1235 31 32 34 T G W
2007-02-01 10:45:20.000 K07-1236 11 12 21 D1 F2 R1
*/
[解决办法]
--那前面的 'K07- ' 固定的吧??
--用表,方便SQL
--如果 'K07- ' 4位都不固定,就 '4 '用charindex再理,非字符串理
create table tab1(riqi datetime, no varchar(10),a1 varchar(6),a2 varchar(6),a3 varchar(6))
insert into tab1
select '2007-02-01 10:45:20 ', 'K07-1234-1 ', '11 ', '12 ', '21 '
union all select '2007-02-01 10:47:20 ', 'K07-1234-2 ', '12 ', '13 ', '23 '
union all select '2007-02-01 10:58:20 ', 'K07-1234 ', '16 ', '17 ', '19 '
union all select '2007-02-01 11:05:20 ', 'K07-1235-1 ', '13 ', '22 ', '22 '
union all select '2007-02-01 11:15:12 ', 'K07-1235-2 ', '21 ', '12 ', '44 '
union all select '2007-02-01 10:45:20 ', 'K07-1235-3 ', '31 ', '32 ', '34 '
union all select '2007-02-01 10:45:20 ', 'K07-1236-1 ', '11 ', '12 ', '21 '
create table tab2(riqi datetime, no varchar(10),b1 varchar(6),b2 varchar(6),b3 varchar(6))
insert into tab2
select '2007-02-01 11:45:20 ', 'K07-1234-1 ', 'A ', 'B ', 'C '
union all select '2007-02-01 11:47:20 ', 'K07-1234-2 ', 'B ', 'F ', 'D '
union all select '2007-02-01 11:58:20 ', 'K07-1234-3 ', 'E ', 'F ', 'G '
union all select '2007-02-01 12:05:20 ', 'K07-1235-1 ', 'T ', 'G ', 'W '
union all select '2007-02-01 11:15:12 ', 'K07-1235-2 ', 'T ', 'E ', 'G '
union all select '2007-02-01 10:45:20 ', 'K07-1235-3 ', 'Q3 ', 'W3 ', 'Y4 '
union all select '2007-02-01 10:45:20 ', 'K07-1236-1 ', 'D1 ', 'F2 ', 'R1 '
select riqi,
left([no],4)+left(stuff([no],1,4, ' '),case when charindex( '- ',stuff([no],1,4, ' '))> 0 then charindex( '- ',stuff([no],1,4, ' '))-1 else len(stuff([no],1,4, ' ')) end ) as [no],
a1,a2,a3
into #tab1
from tab1
select riqi,
left([no],4)+left(stuff([no],1,4, ' '),case when charindex( '- ',stuff([no],1,4, ' '))> 0 then charindex( '- ',stuff([no],1,4, ' '))-1 else len(stuff([no],1,4, ' ')) end ) as [no],
b1,b2,b3
into #tab2
from tab2
select T1.riqi,T1.[no],a1,a2,a3,b1,b2,b3
from
(select #tab1.riqi, #tab1.[no],a1,a2,a3 from #tab1
inner join
(select min(riqi) as riqi, [no] from #tab1 group by [no]) A
on #tab1.riqi=A.riqi and #tab1.[no]=A.[no]) T1
left join
(select #tab2.riqi, #tab2.[no],b1,b2,b3 from #tab2
inner join
(select max(riqi) as riqi,[no] from #tab2 group by [no]) A
on #tab2.riqi=A.riqi and #tab2.[no]=A.[no]) T2
on T1.[no]=T2.[no]
drop table tab1,tab2,#tab1,#tab2
/*
riqi no a1 a2 a3 b1 b2 b3
------------------------------------------------------ -------------- ------ ------
2007-02-01 10:45:20.000 K07-1234 11 12 21 E F G
2007-02-01 10:45:20.000 K07-1235 31 32 34 T G W
2007-02-01 10:45:20.000 K07-1236 11 12 21 D1 F2 R1
*/