在线求教,SQL查询问题!!!

[Sophia_Sun]

有两个产品数据表Products01, Products02, Products02中的Code字段的前6位与Products01相同,后面又增加了三位附属码.
举个例子:
Products01中的两条记录为:
Code Name Color
IRA123R Apple Red
IRB456Y Banana Yellow

Products02中的与这两款产品相关的记录为:
Code Name Number Area
IRA123RPRJ Apple 1000 Yan Tai
IRA123RUNJ Apple 2000 Ji Nan
IRB456YUNJ Banana 5000 Hai Nan

我想做的是:根据Products01中的Code字段,从Products02中找出同款产品的记录,与Products01合并.条件是,先查找Code中后三位是PRJ的记录,若此条记录不存在,再查找后三位是UNJ的记录.

请各路高手, 不吝赐教! 在下感激不尽.

[Sophia_Sun]

有哪位大哥高手,帮帮忙! 着急啊! 可以再加分的!!

[tanxiangfeng]

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Find'
AND type = 'P')
DROP PROCEDURE Find
GO

CREATE PROCEDURE Find
as

declare @TJ as varchar(100)

declare MyCur cursor for
select distinct right(Code,3) from Products02
order by Code
open MyCur
fetch from MyCur into @TJ
while @@fetch_status=0
begin
if exit(select A.*,B.* from Products01 A join Products02 B
on A.Code=left(B.Code,7) where B.Code like right(B.Code,3)=@TJ)
begin
close MyCur
deallocate MyCur

select A.*,B.* from Products01 A join Products02 B
on A.Code=left(B.Code,7) where B.Code like right(B.Code,3)=@TJ
break
end

fetch from MyCur into @TJ
end
close MyCur
deallocate MyCur

我从你的描述中就是这个意思,
你在查询分析器运行这个脚本后,

直接在查询分析器里输入FInd运行,就会得到你险要的结果

[vivianfdlpw]

--创建测试环境
create table Products01
(
Code varchar(10),name varchar(10),Color varchar(10)
)
create table Products02
(
Code varchar(10),name varchar(10),Number int,Area varchar(10)
)
insert Products01
select 'IRA123R','Apple','Red' union select 'IRB456Y','Banana','Yellow'
insert Products02
select 'IRA123RPRJ','Apple',1000,'Yan Tai' union
select 'IRA123RUNJ','Apple',2000,'Ji Nan' union
select 'IRB456YUNJ','Banana',5000,'Hai Nan'

--测试
select A.*,B.*
from Products01 A join Products02 B
on A.Code=left(B.Code,7)
and B.Code in (select min(Code) from Products02 group by left(Code,7))

--删除测试环境
drop table Products01,Products01

--结果
/*

Code name Color Code name Number Area
---------- ---------- ---------- ---------- ---------- ----------- ----------
IRA123R Apple Red IRA123RPRJ Apple 1000 Yan Tai
IRB456Y Banana Yellow IRB456YUNJ Banana 5000 Hai Nan
*/

[Sophia_Sun]

感谢楼上的,但是您误解了我的意思.
这个问题我感觉不是很复杂, 但不懂SQL语法,所以对于我还是很难.

我举的只是两个产品的例子,实际上有成千上万个产品呢.
所以你所提供的程序可能没法用.

[Sophia_Sun]

抱歉,只看了"--创建测试环境"就回复了上面的话, 我还是先试一下了.谢谢!

[Sophia_Sun]

要这么复杂吗? 我是在ACCESS中导入这两个表,生成查询表后,然后在SQL视图中写SQL语句来得到我想要的东西.我的问题是不会写这个语句. 楼上的似乎有点复杂了.我看不明白.

[brother2605]

---试试这样行吗
if exists(select*from Products02 where right(code,3)='PRJ')
select Products01.*,Products02.* from Products01 inner join Products02
on Products01.code=left(Products02.code,6)
where right(Products02.code,3)='PRJ'
else
select Products01.*,Products02.* from Products01 inner join Products02
on Products01.code=left(Products02.code,6)
where right(Products02.code,3)='UNJ'

[Sophia_Sun]

谢谢楼上的幽灵,可是你写的是SQL语句么?

[Sophia_Sun]

求救啊!! 有没有其他的大侠知道怎么做啊?

[S__S]

我知道的.