IF OBJECT_ID('省表') IS NOT NULL DROP TABLE [省表] IF OBJECT_ID('市表') IS NOT NULL DROP TABLE [市表] IF OBJECT_ID('景点表') IS NOT NULL DROP TABLE [景点表]CREATE TABLE [省表](s_id INT,sheng VARCHAR(100)) INSERT [省表] SELECT 1,'广东' UNION ALL SELECT 2,'广西' UNION ALL SELECT 3,'四川'CREATE TABLE [市表](o_id INT,s_id INT,shi VARCHAR(100)) INSERT [市表] SELECT 1,1, '深圳' UNION ALL SELECT 2,1, '广州' UNION ALL SELECT 3,3, '泸州' UNION ALL SELECT 4,2, '桂林'CREATE TABLE [景点表](id INT,o_id INT,s_id INT,jingdian VARCHAR(100)) INSERT [景点表] SELECT 1,1,1,'世界大观' UNION ALL SELECT 2,4,2,'象山公园' UNION ALL SELECT 3,1,1,'动物园' UNION ALL SELECT 4,3,3,'酒厂'SELECT CASE ROWNUM WHEN 1 THEN sheng ELSE '' END sheng ,CASE ROWNUM WHEN 1 THEN shi ELSE '' END shi ,jingdian FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY A.s_id,A.o_id ORDER BY A.s_id,A.o_id)ROWNUM,sheng,shi,jingdian,A.s_id,A.o_id FROM [景点表] A LEFT JOIN [省表] B ON A.s_id = B.s_id LEFT JOIN [市表] C ON A.o_id = C.o_id)T1 ORDER BY s_id,o_id/********************************** 广东 深圳 世界大观 动物园 广西 桂林 象山公园 四川 泸州 酒厂 **********************************/
DROP TABLE [省表]
IF OBJECT_ID('市表') IS NOT NULL
DROP TABLE [市表]
IF OBJECT_ID('景点表') IS NOT NULL
DROP TABLE [景点表]CREATE TABLE [省表](s_id INT,sheng VARCHAR(100))
INSERT [省表]
SELECT 1,'广东' UNION ALL
SELECT 2,'广西' UNION ALL
SELECT 3,'四川'CREATE TABLE [市表](o_id INT,s_id INT,shi VARCHAR(100))
INSERT [市表]
SELECT 1,1, '深圳' UNION ALL
SELECT 2,1, '广州' UNION ALL
SELECT 3,3, '泸州' UNION ALL
SELECT 4,2, '桂林'CREATE TABLE [景点表](id INT,o_id INT,s_id INT,jingdian VARCHAR(100))
INSERT [景点表]
SELECT 1,1,1,'世界大观' UNION ALL
SELECT 2,4,2,'象山公园' UNION ALL
SELECT 3,1,1,'动物园' UNION ALL
SELECT 4,3,3,'酒厂'SELECT CASE ROWNUM WHEN 1 THEN sheng ELSE '' END sheng
,CASE ROWNUM WHEN 1 THEN shi ELSE '' END shi
,jingdian FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY A.s_id,A.o_id ORDER BY A.s_id,A.o_id)ROWNUM,sheng,shi,jingdian,A.s_id,A.o_id FROM [景点表] A
LEFT JOIN [省表] B ON A.s_id = B.s_id
LEFT JOIN [市表] C ON A.o_id = C.o_id)T1
ORDER BY s_id,o_id/**********************************
广东 深圳 世界大观
动物园
广西 桂林 象山公园
四川 泸州 酒厂
**********************************/