目标:根据条件选取指定数量的记录
表1.学生
create table student
(
age int, --年龄
weight int --体重
)
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70);insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);表2:选取条件
create table conditions
(
minage int, --年龄下限
maxage int, --年龄上限
minweight int,--体重下限
maxweight int,--体重上限
counts int --该条件总人数
)insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);说明:根据如下语句选取符合条件的人 但是不知道如何 符合数量counts
也就是说 满足条件1 的我只要2条 但是如下查出符合条件1的不止两条
请各位大虾指点一二 不胜感激
select * from student st
where exists
(
select * from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)
表1.学生
create table student
(
age int, --年龄
weight int --体重
)
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70);insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);表2:选取条件
create table conditions
(
minage int, --年龄下限
maxage int, --年龄上限
minweight int,--体重下限
maxweight int,--体重上限
counts int --该条件总人数
)insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);说明:根据如下语句选取符合条件的人 但是不知道如何 符合数量counts
也就是说 满足条件1 的我只要2条 但是如下查出符合条件1的不止两条
请各位大虾指点一二 不胜感激
select * from student st
where exists
(
select * from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)
where
(
select COUNT(1) from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)=counts
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student( age int,weight int )
go
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70); insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);
go
IF OBJECT_ID('conditions') IS NOT NULL
DROP TABLE conditions
GO
CREATE TABLE conditions( minage int,maxage int,minweight int,maxweight int,count int )
go
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);
go
select COUNT(*) as 数量
from student st
where exists
(
select * from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)
/
数量
-----------
6
**/
where t.minage <= st.age
and t.maxage >= st.age
and t.minweight <= st.weight
and t.maxweight >= st.weight
and
(
select COUNT(1) from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)=t.counts
要把两个条件都用上
insert into conditions values(20,25,50,90,2);
在学生表中 取出年龄在20-25之间 体重在50-90之间的2人
--呵呵,我的笨方法
select top 2* from student st
where 20 <= st.age and 25 >= st.age and 50 <= st.weight and 90 >= st.weight
union all
select top 3* from student st
where 30 <= st.age and 40 >= st.age and 40 <= st.weight and 80 >= st.weight age weight
----------- -----------
20 50
21 80
30 70
32 50
32 60(5 行受影响)
drop table conditions
drop table student
count()内是否应为索引字段啊?
1和索引字段哪个好?还有
这样要加子查询 海量数据会否很慢?
请指教
有上千条件sql就爆了 T_T
where exists
(
select 1 from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
and co.counts=2
)
/*age weight
----------- -----------
20 50
21 80
22 60(3 行受影响)
*/
--这三条都是符合限制条件的[/code]
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student( age int,weight int )
go
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70); insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);
go
IF OBJECT_ID('conditions') IS NOT NULL
DROP TABLE conditions
GO
CREATE TABLE conditions( minage int,maxage int,minweight int,maxweight int,count int )
go
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);
go
select distinct st.*
from student st , conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight /*
age weight
----------- -----------
20 50
21 80
22 60
30 70
32 50
32 60
**/怎么试六个人阿
select top 2 * from student st
where exists
(
select 1 from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
and co.counts=2
)
/*
age weight
----------- -----------
20 50
21 80(2 行受影响)*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student( age int,weight int )
go
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70); insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);
go
IF OBJECT_ID('conditions') IS NOT NULL
DROP TABLE conditions
GO
CREATE TABLE conditions( minage int,maxage int,minweight int,maxweight int,count int )
go
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);
go
--在学生表中 取出年龄在20-25之间
declare @n int
set @n=(select count from conditions where minage=20 and maxage=25)
SET ROWCOUNT @n
select * from student st
where exists
(
select 1 from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)
/*
age weight
----------- -----------
20 50
21 80**/
的两条+满足counts=3的两条吗?
insert into conditions values(20,25,50,90,2);
年龄在20-25,体重在50-90的只选2个
2.
insert into conditions values(30,40,40,80,3);
年龄在30-4,体重在40-80的只选3个
。
(也许还有很多条件)
。
选择的数目和counts值一致吗?
(
select age,weight,
(select count(1) from
(
select * from student st
inner join conditions co
on co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)B
where A.minage = B.minage
and A.maxage = B.maxage
and A.minweight = B.minweight
and A.maxweight = B.maxweight
and A.age <=B.age
and A.weight <=B.weight
)
as pid,[count]
from
(
select * from student st
inner join conditions co
on co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)A
)T where pid <= count
不唯一,也不能增加自增列,student此表无主键 无唯一索引
elect age,weight from
(
select age,weight,
(select count(1) from
(
select * from student st
inner join conditions co
on co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)B
where A.minage = B.minage
and A.maxage = B.maxage
and A.minweight = B.minweight
and A.maxweight = B.maxweight
and A.age <=B.age
and A.weight <=B.weight
)
as pid,counts
from
(
select * from student st
inner join conditions co
on co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)A
)T where pid <= counts/*age weight
----------- -----------
21 80
22 60
30 70
32 50
32 60(5 行受影响)*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student( age int,weight int )
go
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70); insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);
go
IF OBJECT_ID('conditions') IS NOT NULL
DROP TABLE conditions
GO
CREATE TABLE conditions( minage int,maxage int,minweight int,maxweight int,count int )
go
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);
goselect identity(int,1,1) as id,[COUNT]into # from conditions --select * from #
declare @N int,@n1 int
set @N=(select MAX(id) from # )
set @n1=1
while @n1<=@n
begin
declare @x int
set @x=(select [COUNT] from # where ID=@n1)
SET ROWCOUNT @xselect *
from student st
where exists
(
select 1 from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
and count=@x
)
set @n1=@n1+1
end /*
age weight
----------- -----------
20 50
21 80(2 行受影响)age weight
----------- -----------
30 70
32 50
32 60
**/
student这个表的数据不是唯一的 而且不能增加任何字段
declare @N int,@n1 int
set @N=(select MAX(id) from # )
set @n1=1
while @n1<=@n
begin
declare @x int
set @x=(select [COUNT] from # where ID=@n1)
SET ROWCOUNT @xselect *
from student st
where exists
(
select 1 from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
and count=@x
)
set @n1=@n1+1
end
set rowcount 0
select * from kp1age weight
----------- -----------
20 50
21 80
30 70
32 50
32 60--起码能出结果
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student( age int,weight int )
go
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70);
insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);
go
IF OBJECT_ID('conditions') IS NOT NULL
DROP TABLE conditions
GO
CREATE TABLE conditions( minage int,maxage int,minweight int,maxweight int,count int )
go
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,3);
go--select identity(int,1,1) as id ,* into #ppp from conditions
--select * from #ppppselect st.*,co.count into #pppp
from student st , conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight select age , weight
from(
select *,id=(select COUNT(*) from #pppp where COUNT=t.[count] and age<=t.age)
from #pppp t) k
where k.count>=id
/*
age weight
----------- -----------
20 50
21 80
30 70
32 50
32 60*/
用临时表也可以做
from #pppp t当我两个条件都是最多2条时
1 20 50 2 1
2 21 80 2 2
3 22 60 2 3
4 30 70 2 4
5 32 50 2 6
6 32 60 2 6
30岁开始变成了从4开始?应该是从1开始吧?
select *,id=(select COUNT(*) from #pppp where COUNT=t.[count] and age<=t.age)
from #pppp t
/*
age weight count id
----------- ----------- ----------- -----------
20 50 2 1
21 80 2 2
22 60 2 3
30 70 3 1
32 50 3 3
32 60 3 3*/
执行当中的是这个结果
count全为2
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,2);
这里都是通过where count=t.count关联的 所以把所有count相同的都算进去了 应该是这样理解吧?
id=identity(int,1,1),
st.*,
co.[count]
into #
from
student st, conditions co
where
co.minage <= st.age and co.maxage >= st.age
and
co.minweight <= st.weight and co.maxweight >= st.weightselect
age,
weight
from
(select *,px=(select count(1) from # where [count]=t.[count] and id<t.id) from # t) tt
where
px/[count]=0drop table #/**
age weight
----------- -----------
20 50
21 80
30 70
32 50
32 60(所影响的行数为 5 行)
**/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student( age int,weight int )
go
insert into student values(20,50);
insert into student values(21,80);
insert into student values(22,40);
insert into student values(22,60);
insert into student values(26,70);
insert into student values(30,70);
insert into student values(32,50);
insert into student values(32,60);
insert into student values(33,120);
insert into student values(44,60);
go
IF OBJECT_ID('conditions') IS NOT NULL
DROP TABLE conditions
GO
CREATE TABLE conditions( minage int,maxage int,minweight int,maxweight int,count int )
go
insert into conditions values(20,25,50,90,2);
insert into conditions values(30,40,40,80,2);
go
select identity(int,1,1) as ids,st.*,co.count,co.id into #pi2
from student st , #pi co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
select identity(int,1,1) as id,* into #pi from conditionsselect age , weight
from(
select *,idd=(select COUNT(*) from #pi2 where id=t.id and ids<=t.ids )
from #pi2 t) k
where k.count>=idd/*
age weight
----------- -----------
20 50
21 80
30 70
32 50
*/
做出来了
select identity(int,1,1) as ids,st.*,co.count,co.id into #pi2
from student st , #pi co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
select age , weight
from(
select *,idd=(select COUNT(*) from #pi2 where id=t.id and ids<=t.ids )
from #pi2 t) k
where k.count>=idd
排版下 呵呵
嘿嘿 不好意思
select
id=identity(int,1,1),
st.*,
co.*
into #
from
student st, conditions co
where
co.minage <= st.age and co.maxage >= st.age
and
co.minweight <= st.weight and co.maxweight >= st.weightselect
age,
weight
from
(select *,px=(select count(1) from # where [count]=t.[count] and minage=t. minage and id<t.id) from # t) tt
where
px/[count]=0drop table #select * from #/**
age weight
----------- -----------
20 50
21 80
30 70
32 50(所影响的行数为 4 行)
**/
我这里没mssqlserver2k 在别的环境上测的 ╮(╯▽╰)╭
很晚了 各位高人也早点休息吧 非常感谢
早上去找环境玩一把( ̄ˇ ̄)
牛人就是不睡觉
where
(
select COUNT(1) from conditions co
where co.minage <= st.age
and co.maxage >= st.age
and co.minweight <= st.weight
and co.maxweight >= st.weight
)=counts