select tb_sport.fd_sport,fd_BeginTime,fd_EndTime,fd_Today,fd_Calorie
from tb_JKDA,tb_sport
where tb_JKDA.fd_sportID=tb_sport.fd_sportID and fd_userID= 1
检索出的结果为fd_userID= 1这个人的历史运动项目详细记录。
现需要检索出此人历史运动中最喜爱的运动项目(即记录最多的项目)详细记录。
注:tb_JKDA是健康档案表,列名有[fd_userID]、[fd_sportID]、[fd_BeginTime]、[fd_EndTime]、[fd_Today]、
[fd_Calorie]
tb_sport是运动项目表,列名有[fd_sportID]、[fd_sport]
from tb_JKDA,tb_sport
where tb_JKDA.fd_sportID=tb_sport.fd_sportID and fd_userID= 1
检索出的结果为fd_userID= 1这个人的历史运动项目详细记录。
现需要检索出此人历史运动中最喜爱的运动项目(即记录最多的项目)详细记录。
注:tb_JKDA是健康档案表,列名有[fd_userID]、[fd_sportID]、[fd_BeginTime]、[fd_EndTime]、[fd_Today]、
[fd_Calorie]
tb_sport是运动项目表,列名有[fd_sportID]、[fd_sport]
where fd_userID=1 order by c desc)
(
select max(count(fd_sportID)) as TopNum,fd_sportID from tb_JKDA
where fd_userID= 1
group by fd_sportID
order by TopNum desc
) A
inner join
(
select tb_sport.fd_sport,fd_BeginTime,fd_EndTime,fd_Today,fd_Calorie
from tb_JKDA,tb_sport
where tb_JKDA.fd_sportID=tb_sport.fd_sportID and fd_userID= 1
) Bon A.fd_sportID=B.fd_sportID
SELECT fd_sport,fd_BeginTime,fd_EndTime,fd_Today,fd_Calorie
FROM tb_JKDA jkda
INNER JOIN tb_sport sport ON jkda.fd_sportID=tb_sport.fd_sportID
WHERE jkda.sportID = (SELECT top 1 fd_sportID FROM dbo.tb_JKDA
GROUP BY fd_sportID,fd_userID
HAVING fd_userID=1
ORDER BY COUNT(fd_sportID) DESC) AND fd_userID=1
SELECT fd_sport,fd_BeginTime,fd_EndTime,fd_Today,fd_Calorie
FROM tb_JKDA jkda
INNER JOIN tb_sport sport ON jkda.fd_sportID=tb_sport.fd_sportID
WHERE jkda.sportID = (SELECT top 1 fd_sportID FROM dbo.tb_JKDA
GROUP BY fd_sportID,fd_userID
HAVING fd_userID=1
ORDER BY COUNT(fd_sportID) DESC) AND fd_userID=1
select tb_JKDA.fd_userID,tb_JKDA.fd_sportID,fd_Calorie,fd_BeginTime,fd_EndTime,fd_Today
from (select top 1 count(fd_userID) as [count],fd_sportID from tb_JKDA
where fd_userID=1 group by fd_sportID) c,tb_JKDA
where tb_JKDA.fd_sportID=c.fd_sportID