22,199
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID(N'TEMPDB.DBO.#COUNTRY') IS NOT NULL
DROP TABLE #COUNTRY
GO
CREATE TABLE #COUNTRY
(ID INT IDENTITY(1,1),
PRODUCT_ID INT,
COUNTRY_NAME VARCHAR(10))
INSERT INTO #COUNTRY
SELECT 4,'AUS' UNION ALL
SELECT 4,'USA' UNION ALL
SELECT 4,'JAP' UNION ALL
SELECT 4,'IND'
GO
IF OBJECT_ID(N'TEMPDB.DBO.#PORT') IS NOT NULL
DROP TABLE #PORT
GO
CREATE TABLE #PORT
(ID INT IDENTITY(1,1),
COUNTRY_ID VARCHAR(5),
PORT_NAME VARCHAR(10))
INSERT INTO #PORT
SELECT '1','SYN' UNION ALL
SELECT '1','MEL' UNION ALL
SELECT '2','SFN' UNION ALL
SELECT '2','NOL' UNION ALL
SELECT '2','NYK' UNION ALL
SELECT '3','OSA' UNION ALL
SELECT '3','TYO' UNION ALL
SELECT '4','NED' UNION ALL
SELECT '4','MEM'
GO
INSERT INTO #COUNTRY
SELECT PRODUCT_ID+1,COUNTRY_NAME
FROM #COUNTRY WHERE PRODUCT_ID='4'
INSERT INTO #PORT
SELECT C.ID,A.PORT_NAME
FROM #PORT A
JOIN #COUNTRY B ON A.COUNTRY_ID=B.ID
JOIN #COUNTRY C ON B.COUNTRY_NAME=C.COUNTRY_NAME
WHERE B.PRODUCT_ID='4' AND C.PRODUCT_ID=B.PRODUCT_ID+1
SELECT * FROM #PORT
--测试数据
if not object_id(N'Tempdb..#Country') is null
drop table #Country
Go
Create table #Country([ID] INT IDENTITY,[ProductID] int,[CountryName] nvarchar(24))
Insert #Country
select 4,N'澳大利亚' union all
select 4,N'美国'
GO
if not object_id(N'Tempdb..#Port') is null
drop table #Port
Go
Create table #Port([ID] INT IDENTITY,[CountryID] int,[PortName] nvarchar(24))
Insert #Port
select 1,N'悉尼' union all
select 1,N'墨尔本' union all
select 2,N'旧金山' union all
select 2,N'新奥尔良'
Go
--测试数据结束
INSERT INTO #Country
SELECT 5,CountryName FROM #Country
INSERT INTO #Port
SELECT b.ID,
PortName
FROM #Port
JOIN #Country a
ON CountryID = a.ID
JOIN #Country b
ON b.CountryName = a.CountryName
WHERE b.ProductID = 5;
SELECT * FROM #Country
SELECT * FROM #Port