SQL查询一个表中类别字段中最大值对应的值
一、SQL查询一个表中类别字段中最大值对应的值(对一张表的操作):
问题:根据教师id查询checkOutAutoID最大的一条记录里的上期余额?
探索:
1.group by方法:
首先查询表中相同teacherID对应的checkOutAutoID的最大值:
select MAX(checkOutAutoID) from T_CheckOut group by teacherID order by teacherID
然后根据查出的最大值(假设查出的是3),查出对应的remainCash:
SELECT remainCash FROM T_CheckOut WHERE checkOutAutoID=3
合并SQL:
SELECT remainCash FROM T_CheckOut WHERE checkOutAutoID=(select MAX(checkOutAutoID) from T_CheckOut group by teacherID order by teacherID)
结果报错:
解决方法:
在子查询中加入top 1就可以了:
SELECT top 1 remainCash FROM T_CheckOut WHERE checkOutAutoID=(select top 1 MAX(checkOutAutoID) from T_CheckOut group by teacherID order by teacherID)
库里两条数据,运行时间0.035s。
2.嵌套:
select remainCash,teacherID,checkOutAutoID from T_CheckOut as a
where checkOutAutoID=(select max(b.checkOutAutoID)
from T_CheckOut as b
where a.teacherID = b.teacherID
)
库里两条数据,运行时间0.146s,查询效率相对以上方法较低,满了5倍。
那问题来了,我们的checkOutAutoID是唯一的,如果我们换一种想法,假设checkOutAutoID不唯一,可能我们查出的checkOutAutoID相同的记录有很多条,但是我们只要其中一条该怎么办呢?只需做一点小小的修改就OK:
select * from
(select remainCash,teacherID,checkOutAutoID from T_CheckOut as a
where checkOutAutoID=(select max(b.checkOutAutoID)
from T_CheckOut as b
where a.teacherID = b.teacherID
)
)as a
group by checkOutAutoID
二、根据卡表中的卡号,插入学生表一条记录(卡表与学生表存在外键关联):
insert into T_Student(cardAutoID,stuID,stuName,sex)select cardAutoID,@stuID,@stuName,@sexfrom T_Card where cardID=@cardID
总结:
group by方法相对嵌套来说,查询效率高,语句简单,不易出错,所以还是建议使用第一种方法。
感谢您的阅读!