4 如何删除表中的重复数据,用 Transact-SQL 写出代码。 select distinct * into #Tmp from t2 drop table t2 select * into t2 from #Tmp drop table #Tmp 6 人员情况表( employee )t3中字段包括,员工号( ID ),姓名( name ),年龄( age ),文化程度( wh ):包括四种情况(本科以上,大专,高中,初中以下) , 现在我要根据年龄字段查询统计出:表中文化程度为本科以上,大专,高中,初中以下,各有多少人,占总人数多少。结果如下: 学历 年龄 人数 百分比 本科以上 20 34 14.45 大专 20 33 13.69 高中 20 33 13.57 初中以下 20 100 40.95 本科以上 21 50 20.11 。。。。。。 Transact-SQL 查询语句如何写? --- 方法一: 一句 SQL 即可 ----生成的%为整数; ----前两个count(*)是group by分组后,各组的记录数;而第三个count(*)为表t3在group by前表中所有的记录数。 select edu,age,count(*) as '人数',count(*)*100/(select count(*) from t3) as '%' from t3 group by edu,age order by age --- 方法二:写一个存储过程 create proc see as declare @ren int select @ren=count(*) from t3 --select edu,age,count(*) as ' 人数 ',cast(count(*) as float(2)) *100/cast(@ren as float(2)) as '%' ---- 注:生成的 % 小数位数不确定 . select edu,age,count(*) as ' 人数 ' , convert ( decimal ( 5,2),(cast(count(*) as float(2)) *100/cast(@ren as float(2))),0) as '%' ---- 生成的 % 小数位为两位 . from t3 group by edu,age order by age exec see 7 表一 ( t1) 商品名称 t1id 商品总量 t1num A 100 B 120 A 10 B 20 表二 ( t2) 商品名称 t2id 出库数量t2num A 10 A 20 B 10 B 20 B 30 用一条 Transact-SQL 语句算出商品 A,B 目前还剩多少? --- 方法一: 一句 SQL 即可 select t1id as 产品 ID,(t1num-t2num) as 库存量 from (select t1id,sum(t1num) as t1num from t1 group by t1id) t1, (select t2id,sum(t2num) as t2num from t2 group by t2id) t2 where t1.t1id=t2.t2id --- 方法二:写一个存储过程 create proc seekucun as select t1id,sum(t1num) as t1num into #t1 from t1 group by t1id select t2id,sum(t2num) as t2num into #t2 from t2 group by t2id select t1id as 产品 ID,(t1num-t2num) as 库存量 from #t1 inner join #t2 on #t1.t1id=#t2.t2id order by (t1num-t2num) desc exec seekucun