我的数据库表是这样设计的，想得到以下的查询结果，请问sqlserver语句如何写？

ruan1978 2018-11-19 10:33:24
我设计的数据表有一张表结构是这样的：
id cityname postcode iParent
分别表示的意思：id唯一字段 cityname城市名 postcode邮编 iParent父结点id
表的内容如下：
id areaname postcode iParent
1 浙江 nulll 0
2 杭州 315300 1
3 宁波 315400 1
4 温州 315500 1
5 上海 null 0
6 浦东 210013 5
7 张江 210023 5
8 北京 null 0
9 海淀 100014 8
10 朝阳 100026 8
现在想得到如下查询效果：
浙江
杭州
宁波
温州
上海
浦东
张江
北京
海淀
朝阳
就是先查询出大项，再查询出子项。
...全文
216 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
Dear SQL(燊) 2018-12-06
  • 打赏
  • 举报
 回复
支持层级不受限制 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[areaname] nvarchar(22),[postcode] nvarchar(26),[iParent] int)
Insert #T
select 1,N'浙江',N'nulll',0 union all
select 10,N'杭州',N'315300',1 union all
select 3,N'宁波',N'315400',1 union all
select 4,N'温州',N'315500',1 union all
select 5,N'上海',null,0 union all
select 6,N'浦东',N'210013',5 union all
select 7,N'张江',N'210023',5 union all
select 8,N'北京',null,0 union all
select 9,N'海淀',N'100014',8 union all
select 11,N'海淀1',N'100014',9 union all
select 3,N'朝阳',N'100026',8
Go
--测试数据结束
;
WITH cte AS (
SELECT   id,areaname,iParent,lvl=cast(1 as int)
        ,sort=cast(id as varchar(500))
FROM     #T
WHERE    iParent = 0
UNION ALL
SELECT   #T.id,#T.areaname,#T.iParent,lvl=cast(cte.lvl+1 as int)
        ,sort=cast(concat(cte.sort,'/',#t.id) as varchar(500))
FROM     cte
        INNER JOIN #T ON cte.Id = #T.iParent
)
SELECT areaname=REPLICATE('  ',lvl-1)+areaname
FROM cte ORDER BY cte.sort

areaname
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
浙江
  杭州
  宁波
  温州
上海
  浦东
  张江
北京
  朝阳
  海淀
    海淀1

(11 行受影响)
二月十六 2018-11-20
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([id] int,[areaname] nvarchar(22),[postcode] nvarchar(26),[iParent] int)

Insert #T

select 1,N'浙江',N'nulll',0 union all

select 2,N'杭州',N'315300',1 union all

select 3,N'宁波',N'315400',1 union all

select 4,N'温州',N'315500',1 union all

select 5,N'上海',null,0 union all

select 6,N'浦东',N'210013',5 union all

select 7,N'张江',N'210023',5 union all

select 8,N'北京',null,0 union all

select 9,N'海淀',N'100014',8 union all

select 10,N'朝阳',N'100026',8

Go

--测试数据结束

;WITH cte AS (

SELECT   id,areaname,iParent,

        CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort

FROM     #T

WHERE    iParent = 0

UNION ALL

SELECT   #T.id,CONVERT(NVARCHAR(22),'   '+#T.areaname),#T.iParent,

        CAST(sort + RIGHT('000' + CAST(#T.[Id] AS VARCHAR),

                            3) AS VARCHAR(MAX))

FROM     cte

        INNER JOIN #T ON cte.Id = #T.iParent



)

SELECT cte.areaname FROM cte ORDER BY cte.sort


RINK_1 2018-11-19
  • 打赏
  • 举报
 回复
如果你的上下级关系只有2层，那可以用以下的试试，否则就得递归了。 

SELECT *
FROM
(SELECT *,CASE WHEN IPARENT=0 THEN THEN ID ELSE IPARENT END AS PARENT_ID,
CASE WHEN IPARENT=0 THEN 1 ELSE 2 END AS LEVEL
FROM TABLE) AS A
ORDER BY PARENT_ID,LEVEL,ID
吉普赛的歌 2018-11-19
  • 打赏
  • 举报
 回复 
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] NVARCHAR(MAX)
,[areaname] NVARCHAR(MAX)
,[postcode] NVARCHAR(MAX)
,[iParent] NVARCHAR(MAX)	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'浙江',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'2',N'杭州',N'315300',N'1')
INSERT INTO dbo.[t] VALUES(N'3',N'宁波',N'315400',N'1')
INSERT INTO dbo.[t] VALUES(N'4',N'温州',N'315500',N'1')
INSERT INTO dbo.[t] VALUES(N'5',N'上海',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'6',N'浦东',N'210013',N'5')
INSERT INTO dbo.[t] VALUES(N'7',N'张江',N'210023',N'5')
INSERT INTO dbo.[t] VALUES(N'8',N'北京',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'9',N'海淀',N'100014',N'8')
INSERT INTO dbo.[t] VALUES(N'10',N'朝阳',N'100026',N'8')
INSERT INTO dbo.[t] VALUES(N'11',N'西湖',N'315300',N'2')
GO

--增加一个排序函数
IF OBJECT_ID('[Fun_GetOrderBy]') IS NOT NULL DROP FUNCTION [Fun_GetOrderBy]
GO
CREATE FUNCTION [dbo].[Fun_GetOrderBy]
(
	@id NVARCHAR(MAX)
)
RETURNS VARCHAR(500)
AS
BEGIN
	DECLARE @r VARCHAR(500)
	;with cte as
	(
	select id,iParent,id as orderBy from t where id=@id
	union all
	select a.id,a.iParent,a.id as orderBy from t A,
			cte B where A.id=B.iParent
	)
	select @r=STUFF((SELECT '_'+RIGHT('0000'+ltrim(orderBy),4) from cte order BY RIGHT('0000'+ltrim(orderBy),4) ASC FOR XML PATH('')),1,1,'')
	RETURN @r;
END
GO

--查询
SELECT
	REPLICATE(' ',LEN(tt.orderBy)-4-charindex('-',reverse(tt.orderBy))) 
		+tt.areaname AS areaName
	,tt.orderBy
FROM (
	SELECT 
	t.areaname
	,dbo.[Fun_GetOrderBy](id) AS orderBy 
	FROM t
) AS tt
ORDER BY tt.orderBy
ruan1978 2018-11-19
  • 打赏
  • 举报
 回复
引用 3 楼 RINK_1 的回复:
[quote=引用 2 楼 ruan1978 的回复:] [quote=引用 1 楼 RINK_1 的回复:] 如果你的上下级关系只有2层，那可以用以下的试试，否则就得递归了。 

SELECT *
FROM
(SELECT *,CASE WHEN IPARENT=0 THEN THEN ID ELSE IPARENT END AS PARENT_ID,
CASE WHEN IPARENT=0 THEN 1 ELSE 2 END AS LEVEL
FROM TABLE) AS A
ORDER BY PARENT_ID,LEVEL,ID
如下语法错误 SELECT 子句出错: “THEN”附近的表达式。 缺少 FROM 子句。 SELECT 子句出错: “,”附近的表达式。 SELECT 子句出错: “FROM”附近的表达式。 无法分析查询文本。[/quote] 再试试 

SELECT *
FROM
(SELECT *,
        CASE WHEN IPARENT=0 THEN ID ELSE IPARENT END AS PARENT_ID,
        CASE WHEN IPARENT=0 THEN 1 ELSE 2 END AS LEVEL
 FROM TABLE) AS A
ORDER BY PARENT_ID,LEVEL,ID
[/quote] 结果出来前，还是有这个错误 "SELECT 子句出错: “LEVEL”附近的表达式。 ORDER BY 子句出错。 无法分析查询文本。"
美丽的小妮子 2018-11-19
  • 打赏
  • 举报
 回复
IF OBJECT_ID('dbo.tbCitys') IS NOT NULL DROP TABLE dbo.tbCitys GO CREATE TABLE [dbo].[tbCitys]( [id] [int] IDENTITY(1,1) NOT NULL, [cityname] [nvarchar](50) NULL, [postcode] [int] NULL, [iParent] [int] NULL, CONSTRAINT [PK_tbCitys] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO insert into tbCitys(cityname, postcode, iParent) select '浙江',null,0 union all select '杭州',315300,1 union all select '宁波',315400,1 union all select '温州',315500,1 union all select '上海',null,0 union all select '浦东',210013,5 union all select '张江',210023,5 union all select '北京',null,0 union all select '海淀',100014,8 union all select '朝阳',100026,8 union all select '温州1',315501,1 go with city(id,cityname,iparent,sort) as ( select c.id,CONVERT(varchar(255),'' + c.cityname) cityname,c.iparent,CONVERT(varchar(255), '' + c.cityname) sort from tbCitys c where iparent=0 union all select c.id, CONVERT(varchar(255),' ' + c.cityname) cityname,c.iparent,CONVERT(varchar(255), RTRIM(Sort) +'|' + c.cityname) sort from tbCitys c inner join city on c.iparent=city.id ) select cityname from city order by sort
吉普赛的歌 2018-11-19
  • 打赏
  • 举报
 回复
加一个函数吧， 一劳永逸， 级数多也不怕： 
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] NVARCHAR(MAX)
,[areaname] NVARCHAR(MAX)
,[postcode] NVARCHAR(MAX)
,[iParent] NVARCHAR(MAX)	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'浙江',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'2',N'杭州',N'315300',N'1')
INSERT INTO dbo.[t] VALUES(N'3',N'宁波',N'315400',N'1')
INSERT INTO dbo.[t] VALUES(N'4',N'温州',N'315500',N'1')
INSERT INTO dbo.[t] VALUES(N'5',N'上海',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'6',N'浦东',N'210013',N'5')
INSERT INTO dbo.[t] VALUES(N'7',N'张江',N'210023',N'5')
INSERT INTO dbo.[t] VALUES(N'8',N'北京',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'9',N'海淀',N'100014',N'8')
INSERT INTO dbo.[t] VALUES(N'10',N'朝阳',N'100026',N'8')
INSERT INTO dbo.[t] VALUES(N'11',N'西湖',N'315300',N'2')
GO

--增加一个排序函数
IF OBJECT_ID('[Fun_GetOrderBy]') IS NOT NULL DROP FUNCTION [Fun_GetOrderBy]
GO
CREATE FUNCTION [dbo].[Fun_GetOrderBy]
(
	@id NVARCHAR(MAX)
)
RETURNS VARCHAR(500)
AS
BEGIN
	DECLARE @r VARCHAR(500)
	;with cte as
	(
	select id,iParent,id as orderBy from t where id=@id
	union all
	select a.id,a.iParent,a.id as orderBy from t A,
			cte B where A.id=B.iParent
	)
	select @r=STUFF((SELECT '_'+RIGHT('0000'+ltrim(orderBy),4) from cte order BY RIGHT('0000'+ltrim(orderBy),4) ASC FOR XML PATH('')),1,1,'')
	RETURN @r;
END
GO

--查询
SELECT *,dbo.[Fun_GetOrderBy](id) AS orderBy 
FROM t
ORDER BY dbo.[Fun_GetOrderBy](id)
二月十六 2018-11-19
  • 打赏
  • 举报
 回复
https://blog.csdn.net/sinat_28984567/article/details/79593497
RINK_1 2018-11-19
  • 打赏
  • 举报
 回复
引用 2 楼 ruan1978 的回复:
[quote=引用 1 楼 RINK_1 的回复:] 如果你的上下级关系只有2层，那可以用以下的试试，否则就得递归了。 

SELECT *
FROM
(SELECT *,CASE WHEN IPARENT=0 THEN THEN ID ELSE IPARENT END AS PARENT_ID,
CASE WHEN IPARENT=0 THEN 1 ELSE 2 END AS LEVEL
FROM TABLE) AS A
ORDER BY PARENT_ID,LEVEL,ID
如下语法错误 SELECT 子句出错: “THEN”附近的表达式。 缺少 FROM 子句。 SELECT 子句出错: “,”附近的表达式。 SELECT 子句出错: “FROM”附近的表达式。 无法分析查询文本。[/quote] 再试试 

SELECT *
FROM
(SELECT *,
        CASE WHEN IPARENT=0 THEN ID ELSE IPARENT END AS PARENT_ID,
        CASE WHEN IPARENT=0 THEN 1 ELSE 2 END AS LEVEL
 FROM TABLE) AS A
ORDER BY PARENT_ID,LEVEL,ID
ruan1978 2018-11-19
  • 打赏
  • 举报
 回复
引用 1 楼 RINK_1 的回复:
如果你的上下级关系只有2层，那可以用以下的试试，否则就得递归了。 

SELECT *
FROM
(SELECT *,CASE WHEN IPARENT=0 THEN THEN ID ELSE IPARENT END AS PARENT_ID,
CASE WHEN IPARENT=0 THEN 1 ELSE 2 END AS LEVEL
FROM TABLE) AS A
ORDER BY PARENT_ID,LEVEL,ID
如下语法错误 SELECT 子句出错: “THEN”附近的表达式。 缺少 FROM 子句。 SELECT 子句出错: “,”附近的表达式。 SELECT 子句出错: “FROM”附近的表达式。 无法分析查询文本。
SQl Server数据库
讲述Sql Server数据库的企业级开发常用的关键知识点。尽量把概念讲的通俗易懂，了解其架构原理，不仅仅会操作应用，还要把握其本质。帮大家更深层次的理解数据库。 【课程目录】 一.Sql Server数据库架构 1.架构 2....
SQL Server 如何用sql语句查询数据库表对象语句
对于查询数据库中的表使用的sql语句有很多中写法，笔者大多数都是结合sql server自带的监听工具"sql server profile"的使用，打开监听后，再在数据库进行想要的操作后，返回到监听工具界面，如下图。 通过查询可以...
Sql Server查询语句
对于Sql Server创建的表中的数据进行查询，可以进行，基础查询，条件查询，模糊查询
SqlServer数据库SQL语句（超详细）
一、基础 1、说明：创建数据库 CREATE DATABASE database-name ...3、说明：备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind...
SQL Server数据库 -- 表的基础查询
查询了解清楚最重要的东西：顺序查询的基础语言其实很简单，只需要这些语法的顺序和语句就能很快上手，从上到下首先是找出这张表from 表，然后想要这张表的某些内容 select sno...，或者总分、个数等聚合函数、前几...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章