select * from 表 group by 字段 having count(字段)>1
1.用一条SQL语句
2.学生表 如下:
自动编号
1
2
3
删除除了自动编号不同,其他都相同的学生冗余信息
A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)
3. 表A(单位名称,单位帐号), 表B(单位编号,个人账号)
列出各单位的名称,账号,以及单位的人数
select A.name, A.dwzh, isnull(Ct.Quantity,'0') as Quantity from A
left join
(select dwzh, count(*) as Quantity from B
group by dwzh) as Ct
on A.dwzh = Ct.dwzh
4. 股票表(股票代码,买卖类型,数量)
按照股票代码列出,买的数量,卖的数量。
select isnull(a.StockID, b.StockID), isnull(a.S,'0'), isnull(b.B,'0') from (
select StockID,sum(quantity) as S from stocks
where sType = 's'
group by StockID ) a
full join (
select StockID,sum(quantity) as B from stocks
where sType = 'b'
group by StockID ) b
on a.StockID = b.StockID
5. select * from tempT where ','+ tempT.description + ',' like '%,1,%'
某个表有两个字段(phone和sendtime)
phone
13908000800
13918001800
13908000800
13908000800
请写出一个sql语句,实现的功能为:取出每个号码(phone)的最新发送时间(sendtime)的记录?
select phone,max(sendtime) as sendtime from tablename group by phone
6.描述:
表1 student 学生信息表
ID
Name
Sex
Class
表2 schedule 课程信息表
ID
Name
表3 Grade
ID
UID
SID
Num
(a)求各班的总人数:
(b)求1班女生和男生的平均成绩
(c)各班"数据结构"(课程名称)不及格的人数:
答案:
(a) select Class,count(1) as num from student group by Class
(b) select S.Sex,avg(G.Num) as Num from student S,Grade G where S.ID=G.uID and S.Class=1 group by S.Sex
(c) select S.Class,count(1) as num from student S,Grade G,schedule SC where S.ID=G.uID and G.SID =Sc.ID and SC.Name='数据结构'