select *
from A01
where (select count(*) from A32 where A01.Z0001 = Z0001)
=
select max(f)
from
(
select count(*) as f
from A32
group by Z0001
) T
from A01
where (select count(*) from A32 where A01.Z0001 = Z0001)
=
select max(f)
from
(
select count(*) as f
from A32
group by Z0001
) T
else '不及格' end)
,count(*)
from t2
group by (case when 成绩 >= 60 then '及格'
else '不及格' end)
select * from a01 where z0001=(select top 1 z0001 from a32 group by z0001 order by count(*) desc)
第二题
select a.a+b.a*10+c.a*100+d.a*1000 from z0001 a,z0001 b,z0001 c,z0001 d order by a.a+b.a*10+c.a*100+d.a*1000
第三题
select case when 成绩<60 then '不及格' else '及格' end 类别,count(*) 人数 from t2 group by case when 成绩<60 then '不及格' else '及格' end
from table1,table1 as a,table1 as b,table1 as c
order by #table1.Z0001*1000 + a.Z0001 *100 + b.Z0001 *10 + c.Z0001
第四题
有如下表T记录人员的情况:
/*create table t
(RenID INT PRTMARY KEY,
RenMing CHAR(8),
RenParentID INT REFERENCES T(RenID))*/
=======================================
RenID RenMing RenParentID
2 张一 10
3 张二 10
5 王一 11
8 王二 11
10 张_父 15
11 王_父 NULL
15 张_父_父 16
16 NULL
问题:
1、选择出所有没有父的人,注意查询性能。
2、选择出所有人名中含有“_”的人。
3、选择出“张父”的子。
4、选择出有“孙子”的人。
5、选择出如下结果:
项目 张 王
人数 4 3
6、选择出如下结果:
项目 人数
王 3
张 4
7、将T表中的RenID从1~8排列,不要打乱现有表中数据的关系。
8、选择出“张父”的子(一列一行),两个人名中用“,”相隔。
9、编写一存储过程可以列出给定RenID的所有子(包括子的子)。
select a0101 from A01 where z0001 in(select z0001 from a32 group by z0001 having count(z0001)=(select max(count(z0001)) from a32 group by z0001))
第二题:
select top 9999 convert(int,convert(char(1),a.id)+convert(char(1),b.id)+convert(char(1),c.id)+convert(char(1),d.id))+1 as cc from aaa a,aaa b,aaa c,aaa d order by cc
第三题:
select '及格' as 类别,count(cont) as 人数 from ccc where cont>=60
union all
select '不及格' as 类别,count(cont) as 人数 from ccc where cont<60
select * from t where RenParentID is NULL
2.
select * from t where RenMing like '%[_]%'
3.
select * from t where RenParentID in (select RenId from t where RenMing='张_父')
4.
select * from t where RenID in (select RenParentID from (select * from t where RenID in(select distinct RenParentID from t)) as tmp where RenParentID is not NULL)
select '人数' as 项目,(select count(*) from t where RenMing like '张%') as 张,(select count(*) from t where RenMing like '王%') as 王
6.
select '王' as 项目,count(*) as 人数 from t where RenMing like '王%'
union all
select '张' as 项目,count(*) as 人数 from t where RenMing like '张%'
7.
select tt.RenID,tt.RenMing,tt.RenParentID,(select count(*)+1 from t where RenID<tt.RenID) as ID from t tt order by tt.RenID
8.9.
create procedure RenIDS
@RenID as int
AS
select * from t where RenParentID in(select RenID from t where RenID=@RenID)
Go
exec RenIDS '张_父'
1、选择出所有没有父的人,注意查询性能。
select * from t where RenParentID is null
2、选择出所有人名中含有“_”的人。
select * from t where RenMing like '%[_]%'
3、选择出“张父”的子。
select * from t where RenParentID in(select renid from t where RenMing='张父')
4、选择出有“孙子”的人。
select * from t a where exists(select 1 from t b where RenParentID=a.renid and exists(select 1 from t where RenParentID=b.renid))
5、选择出如下结果:
项目 张 王
人数 4 3
select '人数' 项目,sum(case when RenMing like '张%' then 1 else 0 end) 张,sum(case when RenMing like '王%' then 1 else 0 end) 王 from t
6、选择出如下结果:
项目 人数
王 3
张 4
select left(RenMing,1) 项目,count(*) 人数 from t group by left(RenMing,1)
7、将T表中的RenID从1~8排列,不要打乱现有表中数据的关系。
select (select count(*) from t where RenID<=tem.RenID) rowid,* from t tem
8、选择出“张父”的子(一列一行),两个人名中用“,”相隔。
declare @a varchar(8000)
set @a=''
select @a=@a+','+RenMing from t where RenParentID in(select renid from t where RenMing='张父')
select right(@a,len(@a)-1) 张父的子9、编写一存储过程可以列出给定RenID的所有子(包括子的子)。
create proc 存储过程
@renid int
as
begin
declare @a table (RenID int,RenMing varchar(10),RenParentID int)
insert @a select * from t where RenParentID=@RenID
while @@rowcount>0
insert @a select * from t where RenParentID in (select RenID from @a) and RenID not in (select RenID from @a)
select * from @a
end
go
select renming from t where renming like '%父!_父'escape'!'
----9
create proc p_getson (@renid int)
as
declare @sql varchar(200)
set @sql=''
select @sql=renid from t where renid in ( select renparentid from t where renparentid=@renid)
if @sql=''
select renming from t where renid=@sql
else
select reming from t where reparentid=@sql or renid=@sql
在access或sqlserver表的记录编辑中怎么插入记录(非程序)
access中好像只能在修改表结构时插入字段,我该怎么插入一条记录?
as
begin
declare @i int,@tid varchar(8),@trm varvhar(16)
set @i = 0
create table #t(t_id varchar(8),t_rm varchar(16))
insert into #t values (@parentID,@rm)
while 1=1
begin
set @i = @i + 1
declare cur cursor for select t_id from #t
open cur
fetch ABSOLUTE @i from cur into @tid
if @@FETCH_STATUS <> 0
begin
break
end
insert into #t select t.renid,t.renming from t where t.renparentid = @tid
CLOSE cur
DEALLOCATE cur
end
CLOSE cur
DEALLOCATE cur
select * from #t
drop table #t
end
http://expert.csdn.net/Expert/topic/2425/2425077.xml?temp=.4892542