使用Min（）SQL使用多个表，多个值

I am using SQLite in an application that I am developing.
I am trying to run a pretty complex query (complex for me!) and I have gotten the basic results I need but I am stuck on getting over the last hurdle.

我在我正在开发的应用程序中使用SQLite。我正在尝试运行一个相当复杂的查询(对我而言很复杂!)并且我已经得到了我需要的基本结果但我仍然坚持克服最后一道障碍。

I presently have this query that does what i need it to do...

我现在有这个查询,做我需要它做的...

SELECT SUM(activity)
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20);

I need to add another part to this query but this is where it has gotten a little complicated for me. There are three tables....dates, foods, activities and i need to find the sum of a result set which contains the minimum number between two values from two different tables as long as a certain statement is true.

我需要在此查询中添加另一个部分,但这对我来说有点复杂。有三个表....日期,食物,活动,我需要找到一个结果集的总和,只要某个陈述为真,它就包含两个不同表中两个值之间的最小数字。

Basically..

SELECT SUM(total) FROM (SELECT MIN(value from table1 which is determined by a value in table2, value from table3) AS total
FROM table3
WHERE value from table3 is contained in a result set from table1);

The below query is something I came up with that would work if there was no syntax whatsoever (lol!). This doesn't work but i just wanted to show it to better understand what I'm trying to do.

以下查询是我提出的,如果没有任何语法(lol!),它将起作用。这不起作用,但我只是想表明它更好地了解我正在尝试做什么。

SELECT SUM(activity_amount) FROM (SELECT min((SELECT SUM(points) - 20 FROM foods WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id), activity) AS activity_amount
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20));

The problem is with the first value in the MIN()....

问题是MIN()中的第一个值....

SELECT SUM(points) - 20 FROM food WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id

That statement yields more than one value but even though I do need those values to compare against others in the MIN(), I only need them one at a time...not as a whole set.

该语句产生多个值,但即使我确实需要将这些值与MIN()中的其他值进行比较,我一次只需要一个...而不是整个集合。

How can I get something like the above query I created to work?

我怎样才能得到像我创建的上述查询一样的工作?

EDIT...some example tables to better help. Thanks jellomonkey and hainstech

编辑...一些示例表,以提供更好的帮助。谢谢jellomonkey和hainstech

Table#1(dates)  

CREATE TABLE dates (pk INTEGER PRIMARY KEY, date INTEGER, weekly INTEGER)
pk  date       weekly
1   05062009    1  
2   05072009    1  
3   05082009    2 

Table #2(foods)  

CREATE TABLE foods (pk INTEGER PRIMARY KEY, food VARCHAR(64), points DOUBLE, name_id INTEGER)
pk   food   points   name_id  
 1   food1    12.0     1  
 2   food2    9.0      1  
 3   food3    5.0      1  
 4   food4    15.0     2
 5   food5    14.0     2  
 6   food6    12.0     3  

Table#3(activities)  

CREATE TABLE activities (pk INTEGER PRIMARY KEY, activity DOUBLE, activity_id INTEGER)
pk   activity   activity_id
 1     5.0           1  
 2     4.0           1  
 3     2.0           2  
 4     4.0           3

With this ex and query from my original post (one that doesn't work), I would be looking for a result set containing one value..8.0

有了这个ex和来自我原始帖子的查询(一个不起作用),我会寻找一个包含一个值的结果集.8.0

MIN(26.0-20, 9.0) = 6.0
MIN(29.0-20, 2.0) = 2.0
6.0 + 2.0 = 8.0

MIN(26.0-20,9.0)= 6.0 MIN(29.0-20,2.0)= 2.0 6.0 + 2.0 = 8.0

I hope this helps!

我希望这有帮助!

2 个解决方案

#1

After much head-scratching and divining I suspect what you want might be:

在经历了许多令人头疼的事情后,我怀疑你想要的是什么:

SELECT SUM(MIN(fp-20, ap)) FROM
  (SELECT dates.pk AS fd, SUM(points) AS fp
  FROM dates
  JOIN foods ON name_id = fd
  GROUP BY fd
  HAVING fp >= 20)
    JOIN
  (SELECT dates.pk AS ad, SUM(activity) AS ap
  FROM dates
  JOIN activities ON activity_id = ad
  GROUP BY ad)
    ON fd = ad

The column names appear to have zero connection to their meaning but, hey, at least this does give 8.0 and the subselects give the other numbers you mention!-)

列名似乎与它们的含义没有任何联系,但是,嘿,至少这确实给出8.0而子选择给出你提到的其他数字! - )

#2

Agree with jellomonkey - I can't understand exactly what you want unless you show more info. Simple table definitions, a few rows of example data, and expected output would help.

同意jellomonkey - 除非你显示更多信息,否则我无法理解你想要什么。简单的表定义,几行示例数据和预期的输出都会有所帮助。

Here is a simple example for getting a min value from a table based on a range whose bounds are defined in two other tables:

下面是一个简单的示例,用于根据在其他两个表中定义其边界的范围从表中获取最小值:

create table t1(t1id int primary key, value int)
create table t2(t1id int, t2id int primary key, value int)
create table t3(t2id int, t3id int primary key, value int)

select
    MIN(t1.value)
from table1 as t1 
    join table2 as t2 on t2.t1id = t1.t1id
    join table3 as t3 on t3.t2id = t2.t2id
where t1.value between t2.value and t3.value

This uses t2.value and t3.value as the bounds of the range to find the MIN() in.

这使用t2.value和t3.value作为范围的边界来查找MIN()。

EDIT - thanks for posting the sample tables, that helps a ton. You could use some convoluted CASE statements to compare the two values, but I would go with bringing them into one rowset and doing a normal MIN aggregation. This will be more readable and will also allow you to more easily add an additional set of info to include in your MIN inputs if need be. So here is a quick whack at what I would do:

编辑 - 感谢发布样本表,这有助于提高成本。您可以使用一些复杂的CASE语句来比较这两个值,但我会将它们放入一个行集并进行正常的MIN聚合。这将更具可读性,并且如果需要,还可以让您更轻松地添加一组额外信息以包含在MIN输入中。所以这是对我会做什么的快速打击:

select
    SUM(activity)
from (
    select
        min(activity) as activity
        ,activity_id
    from (
        select
            SUM(activity) as activity, activity_id
        from activities 
        group by activity_id
        union all
        select
            SUM(points)-20
            ,name_id
        from foods 
        group by name_id
        having SUM(points) > 20
    ) as activitySum
    where activity_id in (select pk from dates where weekly=1)
    group by activity_id
) as activityLesserOf

Edit #2 - added HAVING to above query per the needs clarification

编辑#2 - 根据需要澄清添加到上述查询

#1