postgresql 在同一查询窗口中返回3个选择结果

insrf1ej 于 5个月前发布在 PostgreSQL

关注(0)|答案(2)|浏览(67)

我有3个查询，我需要在同一个查询编辑器窗口中看到，但似乎不能弄清楚Postgres如何想要它。我有T-SQL的背景，它足够直观地运行这个，并为每个不同的选择查询返回3个不同的结果集窗口。

select col1, count(col2)as col2
from table1
where col1 <> 'None'
and col2 = 'Yes'
group by col1

select col1, count(col3)as col3
from table1
where col1 <> 'None'
and col3 = 'Yes'
group by col1

select col1, count(col4)as col4
from table1
where col1 <> 'None'
and col4 = 'Yes'
group by col1

字符串
这是我现在看到的

<table>
<thead>
<tr>
<th>col1</th>
<th>col2</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>count1</td>
</tr>
<tr>
<td>2</td>
<td>count2</td>
</tr>
</tbody>
</table>

型
这才是我想看到的

<table>
<thead>
<tr>
<th>col1</th>
<th>col2</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>count1</td>
</tr>
<tr>
<td>2</td>
<td>count2</td>
</tr>
</tbody>
</table>

<table>
<thead>
<tr>
<th>col1</th>
<th>col3</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>count1</td>
</tr>
<tr>
<td>3</td>
<td>count3</td>
</tr>
</tbody>
</table>

<table>
<thead>
<tr>
<th>col1</th>
<th>col4</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>count1</td>
</tr>
<tr>
<td>4</td>
<td>count4</td>
</tr>
</tbody>
</table>

型

postgresql

来源：https://stackoverflow.com/questions/77655116/return-3-select-results-in-same-query-window

2条答案

按热度按时间

mbzjlibv1#

由于查询具有相同的列输出，那么我可能只需要将查询合并在一起。添加另一个别名列，以便您知道哪些结果与哪个查询相关联。

select 'col2_results', col1, count(col2)as total
from table1
where col1 <> 'None'
and col2 = 'Yes'
group by col1
union
select 'col3_results', col1, count(col3)as total
from table1
where col1 <> 'None'
and col3 = 'Yes'
group by col1
union 
select 'col4_results', col1, count(col4)as total
from table1
where col1 <> 'None'
and col4 = 'Yes'
group by col1
order by 1,2

字符串

赞(0）回复(0）举报 5个月前

wfypjpf42#

要获得结果，您应该将标题的文本添加为SELECT
由于表和结果集未排序，因此需要Order列。
如果不需要列顺序，可以使用CTE将其删除

CREATE tABLE table1 (col1 TEXT, col2 TEXT, col3 TEXT, col4 TEXT)

CREATE TABLE

INSERT INTO table1 VALUES (1,'None','None', 'Yes'),
(4,'None','None', 'Yes'),
(1,'Yes','None','None'),
(2,'Yes','None','None'),
(1,'None','Yes','None'),
(3,'None','Yes','None')

INSERT 0 6

WITH CTE AS (SELECT 'col1' as col1, 'col2' other, 1 "Order" from table1
UNION
select col1, count(col2)::TEXT,2 as col2
from table1
where col1 <> 'None'
and col2 = 'Yes'
group by col1
UNION
  SELECT 'col1', 'col3',3 from table1
UNION
select col1, count(col3)::TEXT as col3 , 4
from table1
where col1 <> 'None'
and col3 = 'Yes'
group by col1
UNION
  SELECT 'col1', 'col4' , 5 from table1
UNION
select col1, count(col4)::TEXT as col4 , 6
from table1
where col1 <> 'None'
and col4 = 'Yes'
group by col1

ORDER BY "Order", col1)
SELECT col1, other FROM CTE

| col1|其他|
| --|--|
| col1| col2|
| 1 | 1 |
| 2 | 1 |
| col1| col3|
| 1 | 1 |
| 3 | 1 |
| col1| col4|
| 1 | 1 |
| 4 | 1 |