tSQL按不相等的结果分组

Question

我正在使用postgres，但我也了解T-SQL，所以任何人给我的任何答案都会很感激，并能够进行转换。我正在为我的一家汽车销售公司查询。他们在美国各地销售汽车，主要在俄勒冈州和华盛顿州。我需要拿出一份报告，允许我返回OR和Wa的新销售额，然后我遇到的问题是所有其他非OR或Wa州的销售额。所以现在我运行一个与group的并集，其中一个查询用于WA，另一个语句用于OR。我希望结果看起来非常简单，只有3行。1 OR 1 WA和第3行所有其他状态的组合。我不知道如何将不等于OR或WA的值合并为一个值。这可能吗？

感谢您花时间查看此

我的查询还将包括二手车，但一旦我弄清楚如何解决这个问题，我就可以集成它，这就是为什么查询有多个工会的原因

这是我现在的问题：

SELECT buyerstate        AS "State", 
       Count(buyerstate) AS "Acura", 
       saletype          AS "N/U" 
FROM   lydeal 
WHERE  stocklocationid = '8' 
       AND buyerstate = 'OR' 
       AND saledate > '8/01/12' 
       AND saletype = 'N' 
GROUP  BY buyerstate, 
          stocklocationid, 
          saletype 
UNION 
SELECT buyerstate        AS "State", 
       Count(buyerstate) AS "Acura", 
       saletype          AS "N/U" 
FROM   lydeal 
WHERE  stocklocationid = '8' 
       AND buyerstate = 'OR' 
       AND saledate > '8/01/12' 
       AND saletype = 'U' 
GROUP  BY buyerstate, 
          stocklocationid, 
          saletype 
UNION 
SELECT buyerstate        AS "State", 
       Count(buyerstate) AS "Acura", 
       saletype          AS "N/U" 
FROM   lydeal 
WHERE  stocklocationid = '8' 
       AND buyerstate = 'WA' 
       AND saledate > '8/01/12' 
       AND saletype = 'N' 
GROUP  BY buyerstate, 
          stocklocationid, 
          saletype 
UNION 
SELECT buyerstate        AS "State", 
       Count(buyerstate) AS "Acura", 
       saletype          AS "N/U" 
FROM   lydeal 
WHERE  stocklocationid = '8' 
       AND buyerstate = 'WA' 
       AND saledate > '8/01/12' 
       AND saletype = 'U' 
GROUP  BY buyerstate, 
          stocklocationid, 
          saletype 

Answer 1

试试这个

SELECT CASE WHEN buyerstate IN ('OR','Wa') THEN buyerstate ELSE 'OTHER' END   AS "State", 
   Count(buyerstate) AS "Acura", 
   saletype          AS "N/U" 
FROM   lydeal 
WHERE  stocklocationid = '8' 
   AND saledate > '8/01/12' 
   AND saletype = 'N' 
GROUP  BY CASE WHEN buyerstate IN ('OR','Wa') THEN buyerstate ELSE 'OTHER' END, 
      stocklocationid, 
      saletype 

Answer 2

您可以使用CASE表达式：

SELECT CASE WHEN buyerstate IN ('OR', 'WA')
            THEN buyerstate
            ELSE 'other'
        END AS OR_or_WA_or_other,
       ...
  FROM lydeal
 WHERE ...
 GROUP
    BY CASE WHEN buyerstate IN ('OR', 'WA')
            THEN buyerstate
            ELSE 'other'
        END,
       ...
 ORDER
    BY OR_or_WA_or_other
;

（实际上，您可以将OR_or_WA_or_other更改为buyerstate，但我认为使用新名称会更清楚，所以很清楚哪些地方指的是什么。）

Answer 3

我认为你应该这样做：

SELECT buyerstate        AS "State", 
       Count(buyerstate) AS "Acura",
       saletype          AS "N/U" 
FROM   lydeal 
WHERE  stocklocationid = '8' 
       AND (
          (buyerstate in ('OR', 'WA') AND saledate > '8/01/12' AND saletype in ('N', 'U')) OR //Only new sales for Or and Wa
          (buyerstate not in ('OR', 'WA')) //all other sales
       )
       AND saletype = 'N' 
GROUP  BY buyerstate, 
          stocklocationid, 
          saletype 

Answer 4

您可以尝试以下操作：

select buyerstate as "State",count(buyerstate) as "Acura", saletype as "N/U"
from lydeal
where stocklocationid = '8' and buyerstate = 'OR' and saledate > '8/01/12' and saletype = 'N'
group by buyerstate,stocklocationid,saletype
union
select buyerstate as "State",count(buyerstate) as "Acura", saletype as "N/U"
from lydeal
where stocklocationid = '8' and buyerstate = 'WA' and saledate > '8/01/12' and saletype = 'N'
group by buyerstate,stocklocationid,saletype
union
select 'Others' as "State",count(buyerstate) as "Acura", saletype as "N/U"
from lydeal
where stocklocationid = '8' and buyerstate <> 'WA' and buyerstate <> 'OR'  and saledate > '8/01/12' and saletype = 'N'
group by stocklocationid,saletype

对于第三部分，只需使用一些东西而不是buyerstate，不要以此为基础进行分组，这应该会有所帮助。