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