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

[upshania]

表Ａ

ID Name Sex

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




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


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

[nzperfect]

是第几条数据，要看是按谁排序了

[hery2002]

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

select id from 表A where [name]='小乔'

二、我想获得 NAME = “小乔”　相邻的四条数据（孔明、大乔、孙尚香、张飞）要怎么写？

select top 2 * from 表A where id < (select id from 表A where [name]='小乔') order by id desc
union all
select top 2 * from 表A where id > (select id from 表A where [name]='小乔') order by id asc

三、我想用一个ＳＱＬ比较这个表里面　男性　是否　比　女性　数据多，比如　男的有9条，女的只有３条，但是怎么用一个ＳＱＬ语句判断出来？

select [Sex],count([Sex]) as cnt from 表A group by [sex]

[hery2002]

[Quote=引用 1 楼 perfectaction 的回复:]
小姐,你喜欢吃青椒吗?
[/Quote]
........

[utpcb]

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

[utpcb]

[Quote=引用 4 楼 hery2002 的回复:]
引用 1 楼 perfectaction 的回复:
小姐,你喜欢吃青椒吗?

........
[/Quote]
他的ID 不一定是经过排序的哦

[wzy_love_sly]

--1

select oid as '第几条' from (
select row_number() over (order by id) as oid,* from 表
) t where Name='小乔'

[utpcb]

[Quote=引用 1 楼 perfectaction 的回复:]
小姐,你喜欢吃青椒吗?
[/Quote]
打酱油

[M1CR0S0FT]

看到小乔,突然想起林志玲.

[zhiguo2008]

drop table A
create 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='小乔')+1

select [Sex],count([Sex]) as cnt from # group by [sex]

[zhiguo2008]

select px=identity(int,1,1),* into # from a

[upshania]

谢谢大家，，这里我要说明一下，我的ID 是ＧＵＩＤ，不是有序的

这该怎么办呢？

[llj0209013]

[Quote=引用 3 楼 hery2002 的回复:]
一、我想获得 NAME = “小乔” 在“表Ａ”中是第几条数据，ＳＱＬ语句要怎么写？

SQL codeselect id from 表A where [name]='小乔'


二、我想获得 NAME = “小乔”　相邻的四条数据（孔明、大乔、孙尚香、张飞）要怎么写？

SQL codeselect top 2 * from 表A where id < (select id from 表A where [name]='小乔') order by id desc
union all
select top 2 * from 表A where id > (select id from 表A where [name…
[/Quote]

[hery2002]

-->生成测试数据
 
declare @tb table([ID] nvarchar(3),[Name] nvarchar(3),[Sex] nvarchar(1))
Insert @tb
select N'aaa',N'刘备',N'男' union all
select N'bbb',N'赵云',N'男' union all
select N'ccc',N'马超',N'男' union all
select N'ddd',N'孔明',N'男' union all
select N'eee',N'小乔',N'女' union all
select N'fff',N'大乔',N'女' union all
select N'ggg',N'孙尚香',N'女' union all
select N'hhh',N'张飞',N'男' union all
select N'jjj',N'黄忠',N'男' union all
select N'kkk',N'关羽',N'男' union all
select N'lll',N'甘宁',N'男' union all
select 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
)A
where 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           男多于女
*/
 

[hery2002]

[Quote=引用 12 楼 upshania 的回复:]
谢谢大家，，这里我要说明一下，我的ID 是ＧＵＩＤ，不是有序的
这该怎么办呢？
[/Quote]
那你按照什么来排序,总要个排序规则啥,
否则出不来结果.

[upshania]

按照ＩＤ这个字段来排序，我在试楼上的代码，这么快就有回复了，谢谢你

[律己修心]

用临时表

identity(int,1,1)

[qizhengsheng]

和尚大师好详细啊

[octwind]

第一三问

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      男数据多

[sqq521]

drop table A
create 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='小乔')+1

select [Sex],count([Sex]) as cnt from # group by [sex]

[sqq521]

drop table A
create 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='小乔')+1

select [Sex],count([Sex]) as cnt from # group by [sex]