select * from (select count(*) as shul,age from table group age ) where shul<>0
declare @t table(name varchar(50)) insert into @t select 'a' union all select 'a' union all select 'b' union all select 'b' union all select 'c'select * from @t where name in(select name from @t group by name having count(name)>1)
不大明楼主的意思 1.各年龄人数统计,楼上各位已给出 select 统计=count(*) from table group by age 2.给出一个年龄,查询出该年龄人员列表 declare @age int declare @sql varchar(200) set @sql='select * from table where 1=1 ' if(@age>0) set @sql = @sql+' and age='+@age exec(@sql)
select ID,A.* from A where exists (select count(ID) as shul from table group age where A.ID=ID and having count(id) > 0 )
上面寫錯drop table tab create table tab (id int ,age int) goinsert into tab select 1,20 union select 2,20 union select 3,23 union select 4,24 union select 5,25 union select 6,25select id ,age from tab A where exists ( select age from tab where A.age = age group by age having count(age) > 1 )
select * from yourtable a where (select count(*) from yourtable b where b.age=a.age)>1orselect a.* from yourtable a join (select count(*),age from yourtable group by age having count(*)>1) b on a.age=b.age
select count(*) as shul,age from tbalename group by age having (count(*)>1)
谢谢大家的热心解答,大家的意思是先按不同年龄分组,然后再出结果为了更有针对性,我降低要求,就是不用管分组。首先,取第一条数据,然后就知道该数据的年龄字段的值然后,只要返回符合该字段值的所有记录即可就是那个语句里该怎么表示啊?? group by ? distinct ? 都不像啊! where = ?? 也不像
select * from tablename where age =(select top 1 age from tablename )
谢谢楼上,如果我更进一步想要查下一个 age 值的呢?? 谢谢!有希望了!!
select name from where age in (select age from (select count(1) cc,age from student group by age)aaa where aaa.cc > 1)
比如:学生表student:id name age 001 jack 18 002 rose 17 003 hallen 16 004 bill 17 005 mike 18 006 hills 17要求把相同年龄的那几个返回,事先要查的人不知道age是多少查了这个表后(查的时候不知道age),要求得出的结果(包括所有记录):table 1:id name age 001 jack 18 005 mike 18table 2:id name age 002 rose 17 004 bill 17 006 hills 17table 3:id name age 003 hallen 16就是这样的情况,我就是不知道 where 条件该怎么写。。??
select * from yourtable a where (select count(*) from yourtable b where b.age=a.age)>1 and a.age=17 --17可用一个变量来替代
c为age字段,cs1为表名create proc dzy_cursor as declare @age varchar(200) declare cs_cursor cursor for select distinct c from cs1open cs_cursor fetch next from cs_cursor into @agewhile (@@fetch_status=0) begin exec('select * from cs1 where c='+@age) fetch next from cs_cursor into @age end close cs_cursor deallocate cs_cursor go exec dzy_cursor
解决方案如下:select * from student where age in ( select age from student group by age having count(*) > 1 )可否正确! 如果不正确联系我好了,这个问题我以前解决过的.
(select count(*) as shul,age from table group age ) where shul<>0
insert into @t
select 'a'
union all select 'a'
union all select 'b'
union all select 'b'
union all select 'c'select * from @t where name
in(select name from @t group by name having count(name)>1)
1.各年龄人数统计,楼上各位已给出
select 统计=count(*) from table group by age
2.给出一个年龄,查询出该年龄人员列表
declare @age int
declare @sql varchar(200)
set @sql='select * from table where 1=1 '
if(@age>0)
set @sql = @sql+' and age='+@age
exec(@sql)
(select count(ID) as shul from table group age where A.ID=ID and having count(id) > 0 )
上面寫錯drop table tab
create table tab (id int ,age int)
goinsert into tab select 1,20
union select 2,20
union select 3,23
union select 4,24
union select 5,25
union select 6,25select id ,age from tab A where exists ( select age from tab where A.age = age group by age having count(age) > 1 )
where age in
(select age from
(select count(1) cc,age from student group by age)aaa
where aaa.cc > 1)
001 jack 18
002 rose 17
003 hallen 16
004 bill 17
005 mike 18
006 hills 17要求把相同年龄的那几个返回,事先要查的人不知道age是多少查了这个表后(查的时候不知道age),要求得出的结果(包括所有记录):table 1:id name age
001 jack 18
005 mike 18table 2:id name age
002 rose 17
004 bill 17
006 hills 17table 3:id name age
003 hallen 16就是这样的情况,我就是不知道 where 条件该怎么写。。??
as
declare @age varchar(200)
declare cs_cursor cursor for
select distinct c from cs1open cs_cursor
fetch next from cs_cursor into @agewhile (@@fetch_status=0)
begin
exec('select * from cs1 where c='+@age)
fetch next from cs_cursor into @age
end
close cs_cursor
deallocate cs_cursor
go
exec dzy_cursor
where age in (
select age from student
group by age
having count(*) > 1
)可否正确!
如果不正确联系我好了,这个问题我以前解决过的.