数连行

Question

我想进行一个SQL调用，在其中，我要为每一行计数联接调用中的行数。

SELECT *, COUNT(id_feed.id) as price_count, prices.id as id FROM prices
LEFT JOIN id_feed ON id_feed.id_prices = prices.id
WHERE prices.id_user = :id_user

价格表：

id name  id_user
1  name1 21
2  name2 21
3  name3 4

id_feed表：

id id_prices price
1  1         30
2  1         30
3  1         30
4  2         30
5  2         30
6  3         30

结果

id id_prices price_count
1  name1     3
2  name2     2

Answer 1

通过连接和聚合：

select p.id, p.name, count(*) price_count
from prices p inner join id_feed i
on i.id_prices = p.id
where p.id_user = :id_user
group by p.id, p.name

见演示。

id_user = 21结果

> id | name  | price_count
> -: | :---- | ----------:
>  1 | name1 |           3
>  2 | name2 |           2

Answer 2

您正在描述窗口函数。就像这样：

SELECT p.*, f.*, COUNT(f.id) OVER () as price_count
FROM prices p LEFT JOIN
     id_feed f
     ON f.id_prices = p.id
WHERE p.id_user = :id_user

Answer 3

您似乎希望使用窗口功能：

SELECT prices.*, prices.id as id ,
      count(*) over (partition by id_prices) as price_count
FROM prices LEFT JOIN 
     id_feed 
     ON id_feed.id_prices = prices.id
WHERE prices.id_user = :id_user