来自两个表的最大总和 [英] Max of sum from two tables

问题描述

您好！

假设我有两张这样的桌子

tablea



people | trees < br $> b $ b ------ | -----

jen | 2

玛丽| 4

约翰| 5

jen | 7

玛丽| 10



tableb



people | surname

----- ---------

mary | hue

jen | apple

john | orange



现在我想要他们拥有的树木的最大值，我想选择胜利者的姓氏和树木的数量。



我就像

Hello!
Let's say I have two tables like this
tablea

people|trees
------|-----
jen | 2
mary | 4
john | 5
jen | 7
mary | 10

tableb

people|surname
--------------
mary |hue
jen |apple
john |orange

Now I want the max of the sum for the trees they have and I want to select the winner's surname and number of trees.

and I am like

SELECT  b.surname, a.people, sum(trees)
FROM tablea AS a INNER JOIN tableb AS b
WHERE a.people=b.people

它总是显示正确数量的树木但是有jen的名字（第一个条目）。

我做错了什么？

谢谢！

And it always shows the correct amount of trees but with jen's name(the first entry).
What am I doing wrong?
Thank you!

推荐答案

啊你想要按照每个人的树木来计算在，然后得到谁拥有最多。对不起，对我来说很晚。这只是一种方法。还有更多，但这将让你拥有最多树木的人。

（我假设你想要的是什么？）我制作了名为#tree和#people的临时表而不是tablea和tableb。

Ah you want to sum the trees per person, then get who has the most. Sorry, late in the day for me. Here's just one way to do it. There are more, but this will get you the person with the most trees.
(I'm assuming thats what you want?) I made temp tables called #tree and #people instead of tablea and tableb.

--Solution 1
select a.people,b.surname,a.trees
From (Select top 1 people,SUM(trees) as trees
      from #tree
      Group by people
      Order by SUM(trees) desc) a
join #people b
on a.people = b.people  

SELECT TOP 1
  b.surname,
  a.people,
  SUM(a.trees)
FROM
  tablea AS a
  INNER JOIN tableb AS b ON a.people = b.people
GROUP BY
  b.surname,
  a.people
ORDER BY
  SUM(a.trees) DESC

在那里添加一些整数主键会带来查询性能。在字符串字段上加入两个表是一种可怕的习惯。

Adding some integer primary keys in there would bring it query performance. Joining two tables on a string field is a terrible habit.

这篇关于来自两个表的最大总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！