表一:有11位侯选人,得票数最多的9个人选出来
s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11
10 21 12 31 14 15 16 17 19 29 29(注意:有票数相同的也做选出来,这个表就应该选出来10个)
表二:投票的人的id,他们投票了的人就是1,没投的就是0(如果选了票数相同的人那么也只能选对一个人)
投票规则:
至少要选5个人;
最多选9个人;
id s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11
1 0 0 1 1 1 1 0 0 1 1 (选对了5个)
2 1 1 1 1 1 1 1 0 0 0 (选对了6个)
3 0 1 1 1 1 1 1 1 1 1 (选对了9个)
....
....
....
那我想找出选对5个人的投票者的id,该怎么写?
全选对的id的,该怎么写?
s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11
10 21 12 31 14 15 16 17 19 29 29(注意:有票数相同的也做选出来,这个表就应该选出来10个)
表二:投票的人的id,他们投票了的人就是1,没投的就是0(如果选了票数相同的人那么也只能选对一个人)
投票规则:
至少要选5个人;
最多选9个人;
id s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11
1 0 0 1 1 1 1 0 0 1 1 (选对了5个)
2 1 1 1 1 1 1 1 0 0 0 (选对了6个)
3 0 1 1 1 1 1 1 1 1 1 (选对了9个)
....
....
....
那我想找出选对5个人的投票者的id,该怎么写?
全选对的id的,该怎么写?
解决方案 »
- 有关sqlserver数据库高可用性方式
- Access与SQL Server在安全方面的基本区别
- 求一个简单的自动编号的SQL语句
- sqlserver 怎么自动执行?
- 在本地远程连接Oracle数据库的问题 以及 在mssql查询分析器中 查询远程Oracle数据的问题
- 这条语句应该怎样写?
- 如何查看w3wp.exe占用的内存都是些什么对象。(148MB),和sql server的内存里都是些什么对象(1.7G)
- 客户端未安装SQL,运行一条什么命令能调用出SQL SERVER的客户端配置程序?~~急!!!!!!!在线等!
- 以password为列名需要注意什么?
- 求助关于连表更新数据的问题
- 繁体系统select简体MS-SQL库的问题!
- 急求写一条SQL语句实现将“题号”字段的值改为记录号,
id s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11)12列
1 0 0 1 1 1 1 0 0 1 1 数据11列
楼主你的表是横向还是纵向
create table ta(id int, s1 int,s2 int, s3 int, s4 int,s5 int, s6 int, s7 int,s8 int, s9 int,s10 int, s11 int)
insert ta
select 1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 1,0 union all
select 2, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0,0 union all
select 3, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1,0 create table tb(name varchar(5),num int)
insert tb
select 's1', 10 union all
select 's2', 21 union all
select 's3', 12 union all
select 's4', 31 union all
select 's5', 14 union all
select 's6', 15 union all
select 's7', 16 union all
select 's8', 17 union all
select 's9', 19 union all
select 's10', 29 union all
select 's11', 29 (select name+'+' from tb where num in(select top 9 num from tb group by num order by num desc))
--得出前9位,第1位有两个。有10条记录select * from ta where (s2+s3+s4+s5+s6+s7+s8+s9+s10+s11)>5
id s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 1 1 1 1 0 0 1 1 0
2 1 1 1 1 1 1 1 0 0 0 0
3 0 1 1 1 1 1 1 1 1 1 0(所影响的行数为 3 行)
id s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11
1 0 0 0 1 1 1 0 0 1 1 1 (选对5个)
2 1 1 1 1 1 1 1 0 0 0 0 (选对6个)
3 0 1 1 1 1 1 1 1 1 0 1 (选对9个)
insert ta
select 1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 1,0 union all
select 2, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0,0 union all
select 3, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1,0 create table tb(s1 int,s2 int, s3 int, s4 int,s5 int, s6 int, s7 int,s8 int, s9 int,s10 int, s11 int)
insert tb
select 10, 21, 12, 31, 14, 15, 16 ,17, 19, 29, 29
--只能用动态方法
--如上表的格式统计方法
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+侯选人+'+'
from (select 侯选人='s1',s1 as num from tb union all
select 侯选人='s2',s2 as num from tb union all
select 侯选人='s3',s3 as num from tb union all
select 侯选人='s4',s4 as num from tb union all
select 侯选人='s5',s5 as num from tb union all
select 侯选人='s6',s6 as num from tb union all
select 侯选人='s7',s7 as num from tb union all
select 侯选人='s8',s8 as num from tb union all
select 侯选人='s9',s9 as num from tb union all
select 侯选人='s10',s10 as num from tb union all
select 侯选人='s11',s11 as num from tb)tb1
where num in (
select top 9 num
from (select 侯选人='s1',s1 as num from tb union all
select 侯选人='s2',s2 as num from tb union all
select 侯选人='s3',s3 as num from tb union all
select 侯选人='s4',s4 as num from tb union all
select 侯选人='s5',s5 as num from tb union all
select 侯选人='s6',s6 as num from tb union all
select 侯选人='s7',s7 as num from tb union all
select 侯选人='s8',s8 as num from tb union all
select 侯选人='s9',s9 as num from tb union all
select 侯选人='s10',s10 as num from tb union all
select 侯选人='s11',s11 as num from tb)tb1
group by num order by num desc )
set @sql=left(@sql,len(@sql)-1)
select @sql='select * from ta where '+@sql+' >5'
exec(@sql)
结果为:
1 0 0 1 1 1 1 0 0 1 1 0
2 1 1 1 1 1 1 1 0 0 0 0
3 0 1 1 1 1 1 1 1 1 1 0
在上方— 管理(在贴后面有个方框)—打分—在上方输入密码—点结贴就可以了