请教两问题，select into 和 group by
Department 表有 DepartmentID ,DepartmentName
Teacher 表有 DepartmentID ,TeacherID,TeacherName
下面的 into 语句要怎么写？为什么group by 必须包含 DepartmentName ？
select DepartmentName 部门名,count(TeacherID) 教师数
from Department d,Teacher t
where d.DepartmentID=t.DepartmentID
group by d.DepartmentID,DepartmentName
into newtable
[解决办法]
select
DepartmentName as 部门名,
count(TeacherID) 教师数
into 新表
from Department d,Teacher t
where d.DepartmentID=t.DepartmentID
group by d.DepartmentID,DepartmentName
[解决办法]
select DepartmentName 部门名,count(TeacherID) 教师数 into new_tablename
from Department d,Teacher t
where d.DepartmentID=t.DepartmentID
group by d.DepartmentID,DepartmentName
至于为什么group by 那当然了。
你查的数据既然没有分组，那么就必须是个聚合函数计算数据啊
[解决办法]
create table Teacher
(
DepartmentID int ,
TeacherID int ,
TeacherName varchar(100)
)
DELETE Department
insert into Department
select 1, 'AAA ' union all
select 2, 'BBB ' union all
select 3, 'CCC '
insert into Teacher
select 1,101, 'WACKY ' UNION ALL
SELECT 1,102, 'XIAOWANG ' UNION ALL
SELECT 2,201, 'WIXAOZHANG ' UNION ALL
SELECT 3,301, 'JACKY ' UNION ALL
SELECT 3,302, 'ANDY ' UNION ALL
SELECT 3,303, 'JAKY '
select DepartmentName 部门名,count(TeacherID) 教师数 into newtable
from Department d,Teacher t
where d.DepartmentID=t.DepartmentID
group by D.DepartmentName
