表Ａ
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 男多于女*/
------解决方案--------------------