表如下3张wupininfogoodscode goodsname
001 苹果
002 鸭梨dizhi dizhicode dizhiname003 北京
004 上海zonghegoodscode dizhicode renyuan
001 003 张三
002 002 李四
需要求出苹果 北京 张三
鸭梨 上海 李四在线等,立刻结账
001 苹果
002 鸭梨dizhi dizhicode dizhiname003 北京
004 上海zonghegoodscode dizhicode renyuan
001 003 张三
002 002 李四
需要求出苹果 北京 张三
鸭梨 上海 李四在线等,立刻结账
select a.goodsname,b.dizhiname,c.renyuan from wupininfo a,dizhi b,zonghe c
where a.goodscode=c.goodscode and b.dizhicode=c.dizhicode
from zonghe as a
join wupinginfo as b
on a.goodscode = b.goodscode
join dizhi as c
on a.dizhicode=c.dizhicode
from zonghe a left join dizhi b on a.dizhicode=b.dizhicode
left join wupininfo c on a.googdscode=c.googdscode
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
if object_id('[wupininfo]') is not null drop table [wupininfo]
go
create table [wupininfo]([goodscode] varchar(3),[goodsname] varchar(4))
insert [wupininfo]
select '001','苹果' union all
select '002','鸭梨'if object_id('[dizhi]') is not null drop table [dizhi]
go
create table [dizhi]([dizhicode] varchar(3),[dizhiname] varchar(4))
insert [dizhi]
select '003','北京' union all
select '004','上海'if object_id('[zonghe]') is not null drop table [zonghe]
go
create table [zonghe]([goodscode] varchar(3),[dizhicode] varchar(3),[renyuan] varchar(4))
insert [zonghe]
select '001','003','张三' union all
select '002','004','李四'SELECT DISTINCT A.[goodsname], B.[dizhiname], C.[renyuan]
FROM [wupininfo] A,[dizhi] B,[zonghe] C
WHERE A.[goodscode] = C.[goodscode] AND B.[dizhicode] = C.[dizhicode]DROP TABLE [wupininfo]
DROP TABLE [dizhi]
DROP TABLE [zonghe]--测试结果:
/*
goodsname dizhiname renyuan
--------- --------- -------
苹果 北京 张三
鸭梨 上海 李四
*/
inner join wupininfo a on c.goodscode=a.goodscode
Inner join dizhi b on c.dizhicode=b.dizhicode
select goodsname,dizhiname,renyuan
from zonghe A inner join wupininfo B
on A.goodscode=B.goodscode
inner join dizhi C
A.dizhicode=C.dizhicode
from zonghe a left join dizhi b on a.dizhicode=b.dizhicode
left join wupininfo c on a.googdscode=c.googdscode
c.goodsname,b.dizhiname,a.renyuan
from
zonghe a
left join dizhi b on
a.dizhicode=b.dizhicode
left join wupininfo c on
a.googdscode=c.googdscode
go
create table [wupininfo]([goodscode] varchar(3),[goodsname] varchar(4))
insert [wupininfo]
select '001','苹果' union all
select '002','鸭梨'if object_id('[dizhi]') is not null drop table [dizhi]
go
create table [dizhi]([dizhicode] varchar(3),[dizhiname] varchar(4))
insert [dizhi]
select '003','北京' union all
select '004','上海'if object_id('[zonghe]') is not null drop table [zonghe]
go
create table [zonghe]([goodscode] varchar(3),[dizhicode] varchar(3),[renyuan] varchar(4))
insert [zonghe]
select '001','003','张三' union all
select '002','004','李四'
select goodsname,dizhiname,renyuan from zonghe a inner join dizhi b on a.dizhicode=b.dizhicode
inner join wupininfo c on c.goodscode=a.goodscode