34,594
社区成员
发帖
与我相关
我的任务
分享
if object_id('T') is not null drop table T
create table T (id char(3),name nvarchar(20),age int,companyid int)
insert into T select '001','张三',56,1 union all
select '002','李强',25,2 union all
select '003','王春',56,2 union all
select '004','刘芳',21,2 union all
select '005','黄军',39,1 union all
select '006','赵宁',56,1
--
create table company(companyid int,companyname nvarchar(20))
insert into company select 1,'百度' union all
select 2,'21cn'
--1.
select top 1 * from T join
(select max(age) as age from T ) b
on T.age=b.age order by id;
--2.
select a.*,b.renshu from company a join
(select companyid,count(*) as renshu from T group by companyid) b
on a.companyid=b.companyid;
--3.已建好表
insert into info(companyname,name) select companyname,name from T,company where T.companyid=company.companyid;
没建好表
select * into info from (select companyname,name from T,company where T.companyid=company.companyid) x;
companyname name
-------------------------------------------------- --------------------------------------------------
百度 张三
21cn 李强
21cn 王春
21cn 刘芳
百度 黄军
百度 赵宁
create table Employee
(
id int,
[name] varchar(50),
age int,
companyid int
)
insert into Employee select '001','张三',56,1
union all select '002','李强',25,2
union all select '003','王春',56,2
union all select '004','刘芳',21,2
union all select '005','黄军',39,1
union all select '006','赵宁',56,1
create table Company
(
companyid int,
companyname varchar(50)
)
insert into Company select 1,'百度'
insert into Company select 2,'21cn'
/*
*/
--1 由于人员的年龄有可能相等,请查询显示age的最大的人员中,id最小的一条记录
--2 统计公司的人员数
select C.companyname,count(E.[name])'人数' from Company C join Employee E
on C.companyid=E.companyid group by C.companyname
--3 如果想取出来companyname,name字段信息插入到表info(companyname,name)中
select companyname,[name] into info from (select C.companyname,E.[name] from Company C join Employee E on C.companyid=E.companyid) bb
select * from info
select a.ompanyname , count(1) 人员数 from company a inner join comanyid b on a.companyid = b.companyid group by a.companyname
1、select * from 人员信息表 where id = (select min(id) from (select * from 人员信息表 where age = (select max(age) from 人员信息表)) a)
2、select a.ompanyname , count(1) 人员数 from company a inner join b on a.companyid = b.companyid group by a.companyname
1 由于人员的年龄有可能相等,请查询显示age的最大的人员中,id最小的一条记录
select * from 人员信息表 where id = (select min(id) from (select * from 人员信息表 where age = (select max(age) from 人员信息表)) t)
2 统计公司的人员数
select m.companyname , count(1) 人员数 from company m , 人员信息表 n where m.companyid = n.companyid group by m.companyname
3 如果想取出来companyname,name字段信息插入到表info(companyname,name)中
insert into info(companyname,name) select m.companyname , n.name from company m , 人员信息表 n where m.companyid = n.companyid