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

2018-11-19 10:33:24 309 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

学习经验之MVC的SQLServer数据库两表查询 MVC的SQLServer数据库进行两表在SqlServer上创建数据库：StudentsDb，并建立Student和Stujz两表，在两表添加数据 1.在解决方案中的Models文件夹添加新建项目-数据-ADO.NET实体数据模型 2.选择来自数据库的Code First 3.点击下一步新建连接，连接数据库 4.在解决方案中的Controlle 阅读详情

我设计的数据表有一张表结构是这样的：
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
现在想得到如下查询效果：
浙江
杭州
宁波
温州
上海
浦东
张江
北京
海淀
朝阳
就是先查询出大项，再查询出子项。

...全文

全面解析：时延扩展与相干带宽、多普勒扩展与相干时间——无线通信基础时延扩展与相干带宽多径时延扩展与多径衰落接收机所接收到的信号是通过不同的直射、反射、折射等路径到达接收机。由于电波通过各个路径的距离不同，因而各条路径中发射波的到达时间不同，造成多径时延扩展。距离不同所以到达接收机的相位也不相同，不同相位的多个信号在接收端叠加，如果同相叠加则会使信号幅度增强，而反相叠加则会削弱信号幅度。这样，接收信号的幅度将会发生急剧变化，就会产生多径衰落。 ... 阅读详情

草履虫程序员

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

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

草履虫程序员 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 *
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

草履虫程序员 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”附近的表达式。无法分析查询文本。

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

相关推荐