请教各位专家，关于查询表的记录数在表内的当前位置ＳＱＬ应该怎么写？
表Ａ

ID Name Sex

aaa 刘备 男
bbb 赵云 男
ccc 马超 男
ddd 孔明 男
eee 小乔 女
fff 大乔 女
ggg 孙尚香 女
hhh 张飞 男
jjj 黄忠 男
kkk 关羽 男
lll 甘宁 男
mmm 孙权 男




一、我想获得 NAME = “小乔” 在“表Ａ”中是第几条数据，ＳＱＬ语句要怎么写？
二、我想获得 NAME = “小乔”　相邻的四条数据（孔明、大乔、孙尚香、张飞）要怎么写？
三、我想用一个ＳＱＬ比较这个表里面　男性　是否　比　女性　数据多，比如　男的有9条，女的只有３条，但是怎么用一个ＳＱＬ语句判断出来？


我翻看了一下我的ＳＱＬ入门书，还是不懂，请教一下各位。非常感谢！

[解决办法]
一、我想获得 NAME = “小乔” 在“表Ａ”中是第几条数据，ＳＱＬ语句要怎么写？

SQL code

select id from 表A where [name]='小乔'
[解决办法]
create table #tid
(id1 li int identity,
id varchar(20),
name varchar(50),
sex varchar(2))

insert into #tid
select (ID,Name,Sex )
from 表A
1
select id1 from #tid where NAME = '小乔'
2
select t2.* from #tid t1, #tid  t2
  where t1.NAME = '小乔'
and  t1.id1+2>t2.id1
and t1.id1-2<t2.id1
and t2.name<>'小乔'
3
select count(sex),sex
from 表Ａ  
group by sex











[解决办法]
--1
SQL code
select oid as '第几条' from (select row_number() over (order by id) as oid,* from 表) t where Name='小乔'
[解决办法]
SQL code
drop table Acreate table A(id nvarchar(10),name nvarchar(10),sex char(2))insert into A select 'aaa','刘备','男' union select 'bbb','赵云','男' union select 'ccc','孙权','男' union select 'ccc','小乔','女'  select px from # where name='小乔'select  * from # where px between (select px from # where name='小乔')-1 and (select px  from # where name='小乔')+1select [Sex],count([Sex]) as cnt from # group by [sex]
[解决办法]
select px=identity(int,1,1),* into # from a
[解决办法]
SQL code
-->生成测试数据 declare @tb table([ID] nvarchar(3),[Name] nvarchar(3),[Sex] nvarchar(1))Insert @tbselect N'aaa',N'刘备',N'男' union allselect N'bbb',N'赵云',N'男' union allselect N'ccc',N'马超',N'男' union allselect N'ddd',N'孔明',N'男' union allselect N'eee',N'小乔',N'女' union allselect N'fff',N'大乔',N'女' union allselect N'ggg',N'孙尚香',N'女' union allselect N'hhh',N'张飞',N'男' union allselect N'jjj',N'黄忠',N'男' union allselect N'kkk',N'关羽',N'男' union allselect N'lll',N'甘宁',N'男' union allselect N'mmm',N'孙权',N'男'select id from @tb where [name] = '小乔'/*eee*/select px= count(1) from @tb where id<= (select id from @tb where [name]='小乔')/*5*/select * from (   select px =(select count(1) from @tb where id<=t.id),t.* from @tb t)Awhere px between (select count(1) from @tb where id <=(select id from @tb where [name]='小乔'))-2         and (select count(1) from @tb where id <=(select id from @tb where [name]='小乔'))+2         and [name]!='小乔'/*px          ID   Name Sex----------- ---- ---- ----3           ccc  马超   男4           ddd  孔明   男5           eee  小乔   女6           fff  大乔   女7           ggg  孙尚香  女*/select sum (case [Sex] when '男' then 1 else 0 end) as '男',        sum (case [Sex] when '女' then 1 else 0 end) as '女',       case when sum (case [Sex] when '男' then 1 else 0 end) >= sum (case [Sex] when '男' then 1 else 0 end) then '男多于女' else '女多于男' end as '结果'from @tb/*男           女           结果----------- ----------- --------9           3           男多于女*/
------解决方案-------------------- 
 
第一三问 
 [code=SQL] 
  
 if(object_id( '表A ') is not null) drop table 表A 
 create table 表A(id varchar(100),name varchar(100),sex varchar(100)) 
 insert into 表A  
 select  'aaa ', '刘备 ', '男 ' union all 
 select  'bbb ', '赵云 ', '男 ' union all 
 select  'ccc ', '马超 ', '男 '  union all 
 select  'ddd ', '孔明 ', '男 '  union all 
 select  'eee ', '小乔 ', '女 '  union all 
 select  'fff ', '大乔 ', '女 '  union all 
 select  'ggg ', '孙尚香 ', '女 ' union all 
 select  'hhh ', '张飞 ', '男 '  union all 
 select  'jjj ', '黄忠 ', '男 '  union all 
 select  'kkk ', '关羽 ', '男 '  union all 
 select  'lll ', '甘宁 ', '男 '  union all 
 select  'mmm ', '孙权 ', '男 '  
  
 if(object_id( 'getVal ') is not null) drop function getVal 
 create function getVal (@nameVal varchar(100)) 
 returns @retFindReports table (行号 int,男多女多 varchar(100)) 
 AS 
 begin  
         --取得记录号 
         declare @name varchar(100),@xh int,@xh_out varchar(100) 
 set @xh=1 
 declare getXh_Cursor cursor for 
         select name from 表A 
 open getXh_Cursor 
 fetch next from getXh_Cursor 
 into @name 
 while @@FETCH_STATUS = 0 
 begin 
    if(@name=@nameVal) 
       break 
    set @xh=@xh+1 
    fetch next from getXh_Cursor 
    into @name 
 end 
 close getXh_Cursor  
 deallocate getXh_Cursor  
  
         --取得人数判断 
         declare @male int,@female int,@mf varchar(100) 
         select @male=sum(case when sex= '男 ' then 1 else 0 end),@female=sum(case when sex= '女 ' then 1 else 0 end) from 表A 
         if @male=@female 
            set  @mf= '男女数据一样多 ' 
         else if @male> @female 
            set  @mf= '男数据多 ' 
         else 
            set  @mf= '女数据多 ' 
  
         INSERT @retFindReports 
 select @xh,@mf 
 RETURN 
 end 
  
 go 
 select * from dbo.getVal( '小乔 ') 
  
 -- 
 行号     男多女多 
 5      男数据多 
  
 [/code]