一、表结构
--测试表:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EMPLOYEE](
[ID] [int] NOT NULL,
[NAME] [varchar](30) NOT NULL,
[JOB] [varchar](30) NOT NULL,
[TIME] [varchar](30) NOT NULL,
[SAL] [int] NOT NULL, --工资
[COMM] [int] NULL,
[SECTION] [int] NULL,
[HIGHERUP] [int] NULL
) ON [PRIMARY]
END
问题:查询工资排在第3到第10的员工信息
select top 7 id,name,sal from
(select top 10 id,name,sal from employee order by sal desc) b
where id not in (select top 3 id from (select top 10 id,name,sal from employee order by sal desc) c)
--各部门工资大于部门平均工资的员工信息
select *
from personnel p
where sal > (select avg(sal) from personnel where p.section = section);
--打印选课数大于3的学生的id和姓名
select em.id,em.name from text em where em.age > (select age from text where em.manager = id);
--删除相同数据
delete from text t where t.id > (select min(x.id) from text x where t.name = x.name);