如何在sql server中获取alernative maleandfemale数据 [英] how to get alernative maleandfemale data in sql server
问题描述
您好我对sql server有疑问 如何获取男性和女性数据的替代数据 $
CREATE TABLE [dbo]。[EmpGenders](
[id] [int] NULL,
[名称] [varchar](50)NULL,
[性别] [varchar](50)NULL
)
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(1,N'Manish',N'Male')
GO
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(2,N'Nayak',N'Male')
GO
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(3,N'Martin',N'Male')
GO
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(4,N'Roy',N'Male')
GO
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(5,N'Hardik',N'Male')
GO¥
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(6,N'Manisha',N'Female')
GO
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(7,N'Kruti',N'Female')
GO
INSERT [dbo]。[EmpGenders]([id],[Name],[Gender])VALUES(8,N'Maria',N'Female ')b
基于以上数据我希望输出如下。
Id |名称  |性别
1 | Manish |男性$
6 | Manisha |女士$
2 |纳亚克  |男士$
7 | Kruti  |女士$
3 | Martin |男士
8 |玛丽亚  |女士$
4 |罗伊    |男性
5 | Hardik |男性 $
我喜欢以下内容:
选择ID,姓名,性别 从(&
select *,row_number()over(按ID,性别排序)作为rn
来自 [测试]。[dbo]。[EmpGenders ]¥b $ b)a where %2 = 1
工会全部¥b $ b 选择ID,名称,性别 从(&
select *,ROW_NUMBER()结束(由id desc,性别desc订购)作为来自 [Test]。[dbo]的
。 [EmpGenders]
)a 其中  %2 = 0
但以上 查询没有给出结果。
你能告诉我怎么样 在sql server中编写查询以执行此任务
Hi I Have one doubt in sql server how to get the alternate data of male and female data
CREATE TABLE [dbo].[EmpGenders](
[id] [int] NULL,
[Name] [varchar](50) NULL,
[Gender] [varchar](50) NULL
)
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (1, N'Manish', N'Male')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (2, N'Nayak', N'Male')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (3, N'Martin', N'Male')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (4, N'Roy', N'Male')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (5, N'Hardik', N'Male')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (6, N'Manisha', N'Female')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (7, N'Kruti', N'Female')
GO
INSERT [dbo].[EmpGenders] ([id], [Name], [Gender]) VALUES (8, N'Maria', N'Female')
based on above data I want output like below .
Id | Name |Gender
1 |Manish |Male
6 |Manisha|Female
2 |Nayak |Male
7 |Kruti |Female
3 |Martin |Male
8 |Maria |Female
4 |Roy |Male
5 |Hardik |Male
I treid like below :
select id,name,gender from (
select * ,row_number()over ( order by id,gender )as rn
from [Test].[dbo].[EmpGenders]
) a where rn%2=1
union all
select id,name,gender from (
select * ,ROW_NUMBER()over ( order by id desc,gender desc )as rn
from [Test].[dbo].[EmpGenders]
) a where rn%2=0
but above query not given expeted result.
can you please tell me how to write query to achive this task in sql server
推荐答案
-- can you try this
SELECT id , Name, Gender FROM
(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Gender ORDER BY id ) AS rn FROM [EmpGenders]
) b
ORDER BY rn , Gender Desc
这篇关于如何在sql server中获取alernative maleandfemale数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!