修改 SQL MAX 查询以查找对应字段的值 [英] Amend SQL MAX Query to Find Corresponding Field's Value

问题描述

我有以下查询:

$imgDimensions_query = "SELECT MAX(imgWidth) maxWidth, MAX(imgHeight) maxHeight FROM (SELECT imgHeight, imgWidth FROM primary_images WHERE imgId=$imgId联盟SELECT imgHeight, imgWidth FROM secondary_images WHERE primaryId=$imgId) as MaxHeight";

它工作得很好，但我想知道如何找到 imgId 列的值以及表名，对于 maxWidth 和maxHeight 值?

我想要这个的原因是我需要知道 maxWidth 和 maxHeight 值是否属于数据库中的同一个项目.

我想知道是否可以通过修改当前的 SQL 查询来实现?

<小时>

如果在查询 maxWidth 和 maxHeight 值的同时，还可以设置一个布尔值来输出 truemaxWidth 和 maxHeight 都属于同一个条目(至少一次).

我在想，由于 primary_images 中的图像数据与 secondary_images 中的数据是唯一的(反之亦然)，因此可以在每个查询，只要存在一个true，就会输出true.那有意义吗?这可能吗?

<小时>

我设法将第二个查询放在一起，该查询使用第一个查询中的 maxWidth 和 maxHeight 的值来输出特定集合中包含的图像数量两个值.我真正关心的是是否有有或者没有一张或多张满足上述要求的图像，所以同样，布尔值会比总数好数字.如果您知道如何修改以下内容以显示布尔值而不是结果数，请告诉我！

我确信两个表中的最大条目数都在 1000 以下，使用两个查询而不是一个查询不会导致速度下降.如果您也这么认为，并且如果将这些查询合并为一个很荒谬，那么也请告诉我.

第二个查询:

$haveDimensions_query = "SELECT sum(rows) AS total_rows FROM (SELECT count(*) AS rows FROM primary_images WHERE imgId = $imgId and imgWidth = $maxImageWidth and imgHeight = $maxImageHeight联合所有SELECT count(*) AS rows FROM secondary_images WHERE primaryId = $imgId and imgWidth = $maxImageWidth and imgHeight = $maxImageHeight) 作为 union_table";

解决方案

您可以在两个查询中执行此操作，这样可能更容易阅读.第一个查询是您必须获得的最大高度和宽度.

然后您可以发出如下所示的第二个查询:

<前>选择primaryId FROM (从primary_images中选择imgHeight、imgWidth、imgId作为primaryId联盟从 secondary_images 中选择 imgHeight、imgWidth、primaryId) 作为 union_tableWHERE imgWidth = [maxWidth] 和 imgHeight = [maxHeight];

其中 [maxWidth] 和 [maxHeight] 是您从之前的查询中获得的两个值.如果它们属于同一个图像 ID，则查询结果将大于零，否则此查询将没有结果.

如果您需要知道哪个 id 属于哪个表，您可以创建人工列(例如源)，您的查询将变为:

<前>选择primaryId，来源FROM(SELECT imgHeight, imgWidth, imgId AS primaryId, 1 as source FROM primary_images联盟选择 imgHeight、imgWidth、primaryId、2 作为源 FROM secondary_images) 作为 union_tableWHERE imgWidth = [maxWidth] 和 imgHeight = [maxHeight];

请注意，现在有一个名为 source 的人工列.因此，如果您的查询结果是

<前>主 ID 源4 14 25 2

您知道 primary_images 中的 imgId 4 以及primaryId 4,5 from secondary_images 匹配上一个查询的最大高度和最大宽度

最后，如果您只想知道是否有匹配的图片，根据我们下面的评论和讨论，您可以这样做:

<前>选择计数(*) AS imgCount FROM (从primary_images中选择imgHeight、imgWidth、imgId作为primaryId联合所有从 secondary_images 中选择 imgHeight、imgWidth、primaryId) 作为 union_tableWHERE primaryId = $imgId 和 imgWidth = [maxWidth] 和 imgHeight = [maxHeight];

如果没有匹配的图像，imgCount 将为零，否则大于零

I have the following query:

$imgDimensions_query = "SELECT MAX(imgWidth) maxWidth, MAX(imgHeight) maxHeight FROM (
    SELECT imgHeight, imgWidth FROM primary_images WHERE imgId=$imgId
    UNION 
    SELECT imgHeight, imgWidth FROM secondary_images WHERE primaryId=$imgId) as MaxHeight";

It's working fantastic, but I would like to know how I can find the value of the column imgId, as well as the table name, for both the maxWidth and maxHeight values?

The reason I want this is I need to know if the maxWidth and maxHeight values belong to the same item in the database.

I'm wondering if this is possible by amending the current SQL query?

---

What would be perfect is if, along with querying the maxWidth and maxHeight values, a boolean could be set up to output true if both the maxWidth and maxHeight belong to the same entry (at least once).

I'm thinking, since the image data in primary_images is unique from the data in secondary_images (and vice versa), a boolean could be set up in each of the queries, and as long as one true exists, true is output. Does that make sense? Is that possible?

---

I have managed to put together a second query which uses the values of maxWidth and maxHeight from the first query to output the number of images in a specific set that hold both values. All I really care about is if there is or if there isn't one or more images that meet the above requirement, so again, a boolean would be better than the total number. If you have an idea of how to amend the following to show a boolean instead of the number of results, let me know!

I have been reassured that with a maximum number of entries in both tables being under 1000, using two queries instead of one shouldn't cause a hit to speed. If you think so as well, and if combining these queries into one is ridiculous, then let me know that as well.

The second query:

$haveDimensions_query = "SELECT sum(rows) AS total_rows FROM (
    SELECT count(*) AS rows FROM primary_images WHERE imgId = $imgId and imgWidth = $maxImageWidth and imgHeight = $maxImageHeight
    UNION ALL
    SELECT count(*) AS rows FROM secondary_images WHERE primaryId = $imgId and imgWidth = $maxImageWidth and imgHeight = $maxImageHeight
) as union_table";

解决方案

You could do this in two queries, it's probably easier to read that way. First query is what you have to get to get the max height and width.

You then can issue the second query which looks like:

SElECT primaryId FROM (
   SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) as union_table
WHERE imgWidth = [maxWidth] and imgHeight = [maxHeight];

Where [maxWidth] and [maxHeight] are the two values that you get from previous query. If they belong to the same image ID, you will have query result greater than zero, if not this query will have no result.

If you need to know which id is belong to which table, you could create artificial column (e.g. source) and your query would become:

SElECT primaryId, source FROM (
   SELECT imgHeight, imgWidth, imgId AS primaryId, 1 as source FROM primary_images
    UNION 
    SELECT imgHeight, imgWidth, primaryId, 2 as source FROM secondary_images
) as union_table
WHERE imgWidth = [maxWidth] and imgHeight = [maxHeight];

Note that there is now artificial column called source. So if your result from query is

primaryId     source
4             1 
4             2
5             2

You know that imgId 4 from primary_images as well as primaryId 4,5 from secondary_images match with the max height and max width of the previous query

And finally, if you just want to know whether there is image that is matching or not, per our comments and discussion below, you could do:

SElECT count(*) AS imgCount FROM (
   SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) as union_table
WHERE primaryId = $imgId and imgWidth = [maxWidth] and imgHeight = [maxHeight];

Where imgCount will be zero if there is no matching image or greater than zero otherwise

这篇关于修改 SQL MAX 查询以查找对应字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！