日期:2014-05-17 浏览次数:20774 次
--测试数据
if OBJECT_ID('student') is not null drop table student
create table student(ID int identity(1,1),姓名 nvarchar(20),性别 int,年龄 int,班级 int)
go
insert into student(姓名,性别,年龄,班级)
select '张三',1,18,1 union all
select '李四',1,18,1 union all
select '赵武',1,19,1 union all
select '小明',1,17,1 union all
select '刘涛',1,18,2 union all
select '小红',2,17,2 union all
select '小芳',2,18,2 union all
select '小红',2,18,2 union all
select '张三',1,18,1 union all
select '李四',1,17,1
--查询
select 姓名,性别,年龄 From student GROUP BY 班级,姓名,性别,年龄
union all
select 姓名,性别,年龄 From student GROUP BY 班级,姓名,性别,年龄 having COUNT(1)>1
/**
姓名 性别 年龄
-------------------- ----------- -----------
李四 1 17
李四