SQL SELECT 语句
发布网友
共3个回答
热心网友
--学生表，字段：ID，名字,年龄
declare
@student
table(
stu_id
int,
stu_name
varchar(16)
age
int
)
--课程表，字段：ID,名字,学分
declare
@course
table(
cou_id
int,
cou_name
varchar(12)
cou_score
int
)
--学生选修课程表，字段：学生ID，课程ID，分数
declare
@sc
table(
stu_id
int,
cou_id
int,
score
int
)
--插入数据
insert
@student
select
1,'刘德华',47
union
all
select
1,'张学友',46
(1)select
count(distinct
cou_name)
as
'选修课程门数'
from
@course
(2)select
avg(sum(age))
as
'选修C4课程的学生的平均年龄'
from
@student
where
stu_id
in
(select
distinct
stu_id
from
@sc
where
cou_id
in
(select
distinct
cou_id
from
@course
where
cou_name='c4'))
(3)select
avg(sum(score))
as
'学分为3的每门课程的学生平均成绩'
from
@sc
where
cou_id
in
(select
distinct
cou_id
from
@course
where
cou_score=3)
(4)select
stu_name
as
'学号比王菲大，年龄比他小的学生姓名'
from
@student
where
stu_id>(select
distinct
stu_id
from
@studnet
where
stu_nme='王菲')
and
age<(select
distinct
age
from
@student
where
stu_name='王菲')
热心网友
1、select count(*)from 货物
2、select max(数量) from 入库记录 where 入库日期=getdate()
3、select sum(数量),入库日期 from 入库记录 where 入库日期>'20030320' group by 入库日期
4、select 货物号,sum(数量) from 入库记录 group by 货物号
热心网友
1、select distinct(货物名) from 货物;
2、select sum(数量),货物号 from 货物 group by 货物号 order by sum(数量);
3、select sum(数量) ,入库日期 from 货物 where 入库日期>'20030320' group by 入库日期 ;
4、select sum(数量),货物号 from 货物 group by 货物号;
