查询选择count(*)和列

I have this query which fetches rows from a table and the number of rows that the query is gonna return:

我有这个查询从表中获取行以及查询返回的行数:

SELECT tab.*, 
       (SELECT Count(*) 
        FROM   mytable mtb 
        WHERE  mtb.name = 'XYZ' 
               AND mtb.TYPE = 'TP') 
FROM   mytable tab 
WHERE  tab.name = 'XYZ' 
       AND tab.TYPE = 'TP'

Now if you see the I simply want the number of rows that the main query returns with all the other columns. I need to use this query as a subquery in a very large query. Now i want to know if there is a better way to write this query. I mean we are repeating the query just to get the count separately. So can you please provide a more optimized form

现在，如果您看到，我只是想要主查询返回的行数与所有其他列相同。我需要将这个查询用作一个非常大的查询中的子查询。现在我想知道是否有更好的方法来编写这个查询。我的意思是，我们重复查询只是为了分别获得计数。所以能否提供一个更优化的表单

1 个解决方案

#1

You should use the analytic function count():

使用解析函数count():

select tab.*, count(*) over () as totalcnt
from mytable tab
where tab.name = 'XYZ' and tab.type = 'TP'