结合三个SQL查询 [英] Combine Three SQL Queries

问题描述

我有以下三个SQL查询.我想将它们组合成一个可能的方法，或者使用当前方法，或者使用另一种方法(如果这样做会更有效).

I have the following three SQL queries. I would like to combine them into one is possible, either using the current method, or another method if it would be more efficient.

注意:我需要知道所有三个WHERE子句的不同计数值.

NOTE: I need to know the distinct count values for all three WHERE clauses.

查询1:

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 = $maxImageWidth AND imgHeight = $maxImageHeight;

查询2:

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 = $maxImageWidth AND imgHeight != $maxImageHeight;

查询3:

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 != $maxImageWidth AND imgHeight = $maxImageHeight;

---

此外，我的数据库是MySQL.我听说不能在所有情况下都将!=用作不等于"，但是<>更好.就我而言，!=应该可以吗?

---

Also, my database is MySQL. I have heard that using != for "not equal" doesn't work in all cases, but that <> is better. In my case, should != be fine?

推荐答案

这可能对您有用.

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 != $maxImageWidth AND imgHeight = $maxImageHeight
OR imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight
OR imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight);

尽管不确定，我也相信这是等效的.

I also believe this is equivalent, although I'm not sure.

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 = $maxImageWidth OR imgHeight = $maxImageHeight);

根据请求，这是一个返回3行的查询.

As per the request, here is a single query that returns 3 rows.

SELECT 'ALL EQUAL' AS COL1,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 = $maxImageWidth AND imgHeight = $maxImageHeight
UNION ALL
SELECT 'WIDTH EQUAL' AS COL1,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 = $maxImageWidth AND imgHeight != $maxImageHeight;
UNION ALL
SELECT 'HEIGHT EQUAL' AS COL1,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 != $maxImageWidth AND imgHeight = $maxImageHeight;

这篇关于结合三个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！