一个sql查询的问题，望不吝赐教……
0
最佳答案
0
use tempdb
go
declare @tb table(name nvarchar(20),sex nvarchar(10))
insert into @tb
select '张三','男'
union all
select '李四','男'
union all
select '王五','男'
union all
select '赵六','女'
union all
select '曹七','女'
select * from @tb
declare @newtb table(name nvarchar(20),sex nvarchar(10),ID int)
declare @tbman table(name nvarchar(20),sex nvarchar(10),ID int identity(1,2))
declare @tbwoman table(name nvarchar(20),sex nvarchar(10),ID int identity(2,2))
insert into @tbman select * from @tb where sex='男'
insert into @tbwoman select * from @tb where sex='女'
insert into @newtb
select * from @tbman
union all
select * from @tbwoman
order by ID
select * from @newtb
收获园豆：8
其他回答(6)
0
使用union连接
能否详细一点呢
0
个人认为这个还不如在前台显示的是通过JS来操作一行男，一行女的格式
0
存储过程试试
0
不创建2个临时表，仅用一条语句的方法，使用ROW_NUMBER() 结合 UNION ALL
--产生测试表 和数据 CREATE TABLE #tb ( name NVARCHAR(20) , sex NVARCHAR(10) ) INSERT INTO #tb SELECT '张三', '男' UNION ALL SELECT '李四', '男' UNION ALL SELECT '王五', '男' UNION ALL SELECT '赵六', '女' UNION ALL SELECT '曹七', '女' SELECT * FROM #tb ORDER BY name, sex ----查询语句 SELECT * FROM ( SELECT * , (ROW_NUMBER() OVER ( PARTITION BY A.sex ORDER BY A.sex ))*2 rn FROM ( SELECT name, sex FROM #tb WHERE sex = '女' ) A ) AA UNION ALL SELECT * FROM ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY A.sex ORDER BY A.sex )*2-1 rn FROM ( SELECT name, sex FROM #tb WHERE sex = '男' ) A ) AA ORDER BY RN DROP TABLE #tb
0
--因我的测试表没有性别,所以将isvalid列作为性别使用
--采用sex分组然后排序给出行号
--后根据行号和性别排序得到交叉
;WITH a AS ( SELECT CASE IsValid
WHEN 0 THEN '男'
ELSE '女'
END sex ,
Account ,
ROW_NUMBER() OVER ( PARTITION BY IsValid ORDER BY Account ) rownumber
FROM dbo.frmuser
WHERE IsValid IS NOT NULL
)
SELECT *
FROM a
ORDER BY rownumber ,
sex
收获园豆：2
0
用两个临时表 这样容易懂
首先创建一个男女信息表
CREATE TABLE #tb
(
name NVARCHAR(20) ,
sex NVARCHAR(10)
)
INSERT INTO #tb
SELECT '张三', '男'
UNION ALL
SELECT '李四', '男'
UNION ALL
SELECT '王五', '男'
UNION ALL
SELECT '赵六', '女'
UNION ALL
SELECT '曹七', '女'
--把所有女性插入临时表tbnv 添加了一个自增列id 为偶数
select identity(int,2,2) as id,name, sex
into #tbnv
FROM #tb
WHERE sex = '女'
--把所有男性插入临时表tbnan 添加了一个自增列id 为奇数
SELECT identity(int,1,2) as id,name, sex
into #tbnan
FROM #tb
WHERE sex = '男'
---最后根据自增列排序显示
select * from #tbnv
union
select * from #tbnan
ORDER BY id
