mysql stragg_如何在MySQL中將子查詢行的結果顯示為一列？

I have three tables Category, Movies and RelCatMov

我有三個表分類,電影和RelCatMov

Category-table

categoryid, categoryName

1 thriller

2 supsense

3 romantic

4 action

5 sci-fi

Movies-table

movieid, movieName

1 Avataar

2 Titanic

3 NinjaAssassin

RelCatMov-table

categoryid, MovieID

1 1

2 2

3 2

4 2

5 2

Now i Want to display a the record as

現在我想顯示一條記錄

MovieName Categories

Titanic Suspense,Romantic,Sci-fi,action

How to do this.

這個怎么做。

I am writing a query

我正在寫一個查詢

select MovieName,(select categoryname from category b,relcatmov c where b.categoryid=c.categoryid and c.movieid=a.movieid) as categories from movies a;

Error: Subquery returns more than one row!!!

How to display the result of rows in one column?

如何在一列中顯示行的結果?

Please help!!!

2 个解决方案

#1

11

In Oracle it's called stragg. In MySQL it's GROUP_CONCAT.

在Oracle中,它被稱為stragg。在MySQL中它是GROUP_CONCAT。

select MovieName,(select GROUP_CONCAT(categoryname) from category b,relcatmov c where b.categoryid=c.categoryid and c.movieid=a.movieid) as categories from movies a;

For reference, your problem is that MySQL wants you to return a single value and you're returning several rows instead.

作為參考,您的問題是MySQL希望您返回單個值,而您將返回多行。

#2

-2

For a similar need in MS-SQL, I wrote a function that returns me a concatenated list (string). So you can follow this approach.

對於MS-SQL中的類似需求,我編寫了一個函數,它返回一個連接列表(字符串)。所以你可以遵循這種方法。