3.3w+
社区成员
- 全部
- 基础类
- 应用实例
- 新技术前沿
在线求教,SQL查询问题!!!
有两个产品数据表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的记录.
请各路高手, 不吝赐教! 在下感激不尽.
举个例子:
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的记录.
请各路高手, 不吝赐教! 在下感激不尽.
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
S__S 2005-11-18
我知道的.
Sophia_Sun 2005-07-18
求救啊!! 有没有其他的大侠知道怎么做啊?
Sophia_Sun 2005-07-16
谢谢楼上的幽灵,可是你写的是SQL语句么?
brother2605 2005-07-16
---试试这样行吗
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'
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 2005-07-16
要这么复杂吗? 我是在ACCESS中导入这两个表,生成查询表后,然后在SQL视图中写SQL语句来得到我想要的东西.我的问题是不会写这个语句. 楼上的似乎有点复杂了.我看不明白.
Sophia_Sun 2005-07-15
抱歉,只看了"--创建测试环境"就回复了上面的话, 我还是先试一下了.谢谢!
Sophia_Sun 2005-07-15
感谢楼上的,但是您误解了我的意思.
这个问题我感觉不是很复杂, 但不懂SQL语法,所以对于我还是很难.
我举的只是两个产品的例子,实际上有成千上万个产品呢.
所以你所提供的程序可能没法用.
这个问题我感觉不是很复杂, 但不懂SQL语法,所以对于我还是很难.
我举的只是两个产品的例子,实际上有成千上万个产品呢.
所以你所提供的程序可能没法用.
vivianfdlpw 2005-07-15
--创建测试环境
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
*/
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
*/
tanxiangfeng 2005-07-15
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运行,就会得到你险要的结果
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运行,就会得到你险要的结果
Sophia_Sun 2005-07-15
有哪位大哥高手,帮帮忙! 着急啊! 可以再加分的!!
