22,207
社区成员
发帖
与我相关
我的任务
分享
select RestaurantInfo.* from C_RI join Cuisine on C_RI.C_ID = Cuisine.C_ID inner join RestaurantInfo on RestaurantInfo.RI_ID = C_RI. RI_ID or RestaurantInfo.RI_RIName = RestaurantInfo.RI_RIName or RestaurantInfo.RI_Address = RestaurantInfo.RI_Address
where CONTAINS(Cuisine.C_Name,'飞')or CONTAINS(RestaurantInfo.RI_RIName,'飞')or CONTAINS(RestaurantInfo.RI_Address,'飞')
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-25 20:49:46
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[关联表]
if object_id('[关联表]') is not null drop table [关联表]
go
create table [关联表]([CRI_ID] int,[RI_ID] int,[C_ID] int)
insert [关联表]
select 1,1,1 union all
select 2,1,2 union all
select 3,2,3 union all
select 4,2,4
--> 测试数据:[菜系表]
if object_id('[菜系表]') is not null drop table [菜系表]
go
create table [菜系表]([C_ID] int,[C_NAME] varchar(4))
insert [菜系表]
select 1,'川菜' union all
select 2,'粤菜' union all
select 3,'鲁菜' union all
select 4,'湘菜'
--> 测试数据:[饭店信息表]
if object_id('[饭店信息表]') is not null drop table [饭店信息表]
go
create table [饭店信息表]([RI_ID] int,[RI_RIName] varchar(8),[RI_Address] varchar(14))
insert [饭店信息表]
select 1,'湘君府','石家庄市跃进路' union all
select 2,'保定会馆','石家庄市广安街'
--------------开始查询--------------------------
select
c.RI_RIName,c.RI_Address
from
关联表 a,菜系表 b,饭店信息表 c
where
a.C_ID=b.C_ID and a.RI_ID=c.RI_ID
and
a.CRI_ID=(select min(CRI_ID) from 关联表 where RI_ID=a.RI_ID)
----------------结果----------------------------
/* RI_RIName RI_Address
--------- --------------
湘君府 石家庄市跃进路
保定会馆 石家庄市广安街
(2 行受影响)
*/
select distinct RestaurantInfo.*
from C_RI join Cuisine on C_RI.C_ID = Cuisine.C_ID
inner join RestaurantInfo
on RestaurantInfo.RI_ID = C_RI. RI_ID
or RestaurantInfo.RI_RIName = RestaurantInfo.RI_RIName
or RestaurantInfo.RI_Address = RestaurantInfo.RI_Address
where CONTAINS(Cuisine.C_Name,'飞')or CONTAINS(RestaurantInfo.RI_RIName,'飞')or CONTAINS(RestaurantInfo.RI_Address,'飞')