连接select语句以获取SQL中的列

SELECT COUNT(Type) from House where Type = 1
SELECT COUNT(Type) from House where Type = 2
SELECT COUNT(Type) from House where Type = 3

My question is: I want to join the above 3 statements to get: 3 columns i.e. eg: ColumnType1: '50', ColumnType2: '60', columnType3: '45'

我的问题是:我想加入上面的3个语句来得到:3列，例如:ColumnType1: '50'， ColumnType2: '60'， columnType3: '45'

thanks

谢谢

4 个解决方案

#1

You can create the columns using an aggregate function with a CASE expression:

您可以使用具有CASE表达式的聚合函数创建列:

SELECT 
  count(case when Type = 1 then Type end) as type_1,
  count(case when Type = 2 then Type end) as type_2,
  count(case when Type = 3 then Type end) as type_3
from House

#2

You can use a case and add up if the Type matches

如果类型匹配，您可以使用一个case并将其相加

SELECT sum(case when Type = 1 then 1 else 0 end) as type_1,
       sum(case when Type = 2 then 1 else 0 end) as type_2,
       sum(case when Type = 3 then 1 else 0 end) as type_3
from House

#3

There is a cleaner type of SQL which can give you this answer, but you will have each type on a different row:

有一种更干净的SQL类型可以提供这个答案，但是您将在不同的行中拥有每种类型:

SELECT Type, COUNT(Type) FROM House GROUP BY Type

It has the disadvantage of not giving you columns as you asked for; but the advantage is that it works for any number of different types without needing to change the query.

它的缺点是没有按照你的要求提供专栏;但是优点是，它适用于任意数量的不同类型，而不需要更改查询。

#4

SELECT
    COUNT(Type) as val1,
    (SELECT COUNT(Type) from House where Type = 2) as val2,
    (SELECT COUNT(Type) from House where Type = 3) as val3
from House where Type = 1

#1