如何从另一个表中添加每个返回行的计数列？ [英] How to add a count column of each returned row from another table?

问题描述

有没有人可以告诉我一个如何从另一个表中添加引用列的每个返回行的新计数列的示例。



以下示例是我或多或少都在寻找（不工作）。

Could anyone please show me an example of how to add a new counted column of each returned row of a referenced column from another table.

The below example is what I''m more or less looking for (doesn''t work).

<pre lang="sql">SELECT
e.id,
e.video_id,
SELECT count(r.id) FROM reported as r where r.embed_id = e.id as countedItems
FROM videostreamer.embed e;

结果......

e.ID e。 Video_ID countsItems

1 ..... 20 ........... 0

2 ..... 22 ...... ..... 2

3 ..... 23 ........... 9

4 ..... 25 .. ......... 0

Result...
e.ID e.Video_ID countedItems
1.....20...........0
2.....22...........2
3.....23...........9
4.....25...........0

推荐答案

我最终通过添加一个函数（如果有人想）来做到这一点......

I eventually did it by adding a Function (if anyone would like to)...

<pre lang="sql">DELIMITER

DROP FUNCTION IF EXISTS`videostreamer``countReported`

DROP FUNCTION IF EXISTS `videostreamer`.`countReported`

CREATE FUNCTION`videostreamer``cou ntReported`（aembed_ID int）RETURNS INTEGER
BEGIN

DECLARE Temp INTEGER;
SELECT count（id）INTO Temp FROM报告其中embed_id = aembed_ID;
RETURN IFNULL（临时，0）;

END

CREATE FUNCTION `videostreamer`.`countReported` (aembed_ID int) RETURNS INTEGER BEGIN DECLARE Temp INTEGER; SELECT count(id) INTO Temp FROM reported where embed_id = aembed_ID; RETURN IFNULL(Temp, 0); END

这篇关于如何从另一个表中添加每个返回行的计数列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！