多行查询的结果中只有一行 [英] Query with multiple line results in only one line

问题描述

我开发asp.net中一所学校的项目，我试图让一个查询的结果数行（这是corret），以示对每一个结果的结果只有一个排，就是该项目是一个网页游戏的销售。每个游戏有几个developper，流派（动作，FPS等），格式（数字或物理的），平台（PS3，PS4等）等设置喜欢这些。我测试了它和它返回几行，正如我以前说过，可能被合成为一个单一的一行在列重复几个值。

I am developing a school project in asp.net and I was trying to get a query, that results in several lines (which is corret), to show the results for each "result" on only one row, the thing is that the project is a web page to sell games. Each of the games has several developper, Genres(Action, FPS etc.), formats (digital or physical), platforms (ps3, ps4 etc.) and other setting like these. I tested it and It returns several rows, as I said before, that could be synthesised into a single row with several values in which the columns repeat.

下面是查询：

SELECT 
    dbo.Jogos.NomeJogo AS Jogo, 
    dbo.Plataforma.Plataforma,
    dbo.Jogos.disponibilidade, 
    dbo.Jogos.Preco AS Preço, 
    dbo.Jogos.Stock, 
    dbo.Desenvolvedora.NomeDesenvolvedora AS Desenvolvedora, 
    dbo.PEGI.PEGI, 
    dbo.Formato.Formato, 
    dbo.Genero.Genero, 
    dbo.Fornecedor.NomeFornecedor AS Fornecedor 
FROM dbo.Jogos 
    INNER JOIN dbo.Desenvolvedora ON dbo.Jogos.IdDesenvolvedora = dbo.Desenvolvedora.IdDesenvolvedora 
    INNER JOIN dbo.PEGI ON dbo.Jogos.IdPegi = dbo.PEGI.IdPEGI 
    INNER JOIN dbo.GeneroJogo ON dbo.Jogos.IdJogo = dbo.GeneroJogo.IdJogo 
    INNER JOIN dbo.Genero ON dbo.GeneroJogo.IdGenero = dbo.Genero.IdGenero 
    INNER JOIN dbo.JogosFormato ON dbo.Jogos.IdJogo = dbo.JogosFormato.IdJogo 
    INNER JOIN dbo.Formato ON dbo.JogosFormato.IdFormato = dbo.Formato.IdFormato 
    INNER JOIN dbo.JogosFornecedor ON dbo.Jogos.IdJogo = dbo.JogosFornecedor.IdJogo 
    INNER JOIN dbo.Fornecedor ON dbo.JogosFornecedor.IdFornecedor = dbo.Fornecedor.IdFornecedor 
    INNER JOIN dbo.JogosPlataforma ON dbo.Jogos.IdJogo = dbo.JogosPlataforma.IdJogo 
    INNER JOIN dbo.Plataforma ON dbo.JogosPlataforma.IdPlataforma = dbo.Plataforma.IdPlataforma

查询返回的几行的量可以恢复到仅一个相同的游戏。

The query returns several lines for the same game which could be resumed to only one.

例如：

Game              |  Genre   |  Platform  |  Developper
___________________________________________________________

Assassin's Creed  |  Action  |  Ps3       |  Ubisoft
Assassin's Creed  |  Stealth |  Ps3       |  Ubisoft
Assassin's Creed  |  Action  |  xBox 360  |  Ubisoft

我想获得这样的：

I'd like to obtain something like:

Game              |  Genre           |  Platform       |  Developper
_____________________________________________________________________

Assassin's Creed  |  Action, Stealth |  Ps3, Xbox 360  |  Ubisoft

我所要求的协助，因为我看不出这是可能的。

I'm asking for assistance because I don't see how this could be possible.

任何帮助将是AP preciated。

Any help would be appreciated.

P.S：在查询的一些值是葡萄牙语。
我也查了这个线程
<一href=\"http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server\">How使用GROUP BY来连接SQL Server中的字符串？
但是这一次只使用一个表，我取从几个表中的数据，所以我真的不知道如何在这一个继续。

P.S.: A few values on the query are in Portuguese. I've also checked this thread How to use GROUP BY to concatenate strings in SQL Server? But this one uses only one table and I'm fetching data from several tables so I really don't know how to proceed in this one.

感谢您提前
诚挚的问候，
凯文

Thank you in advance Best regards, Kevin

推荐答案

有几种方法去这个问题。接受的问题的答案Lanorkin在他/她的评论中引用的问题表明其中一个与SQL塞雷尔语（XML PATH）的作品。如果这听起来以任何方式或深奥可怕的，你可以使用相关子查询与并置的值将用户定义的函数。

There are several ways to go about this. The accepted answer to the question that Lanorkin referenced in his/her comment shows one of them that works with SQL Serer (XML PATH). If this sounds esoteric or scary in any way, you can use a correlated subquery with a user-defined function that concatenates the values for you.

其基本思想是，你必须返回各自的游戏和拉级联名单流派，平台，或开发一个内部查询的唯一值的外部查询。

The basic idea is that you have an outer query that returns each of the unique values for the games and an inner query that pulls the concatenated lists for genre, platform, or developer.

create function getPlatforms (@jogoId nvarchar(50))
returns nvarchar(1024)
as 
begin
  declare @platforms nvarchar(1024);
  select @platforms = '';
  -- I understand that your Platform is normalized in your schema, but 
  -- you get the ide. Just make this a join. You can also strip out the 
  -- leading comma one you are done.
  select @platforms = @platforms + ', ' + Plataforma from dbo.Jogos 
        where JogoId = @jogoid;
  return @platforms;
end;
go;


SELECT 
    dbo.Jogos.NomeJogo AS Jogo, 
    dbo.getPlatforms(JogoId)
FROM dbo.Jogos 
GROUP BY NomeJogo, JogoId;

不幸的是，GROUP_CONCAT不可用（开箱即用）在SQL Server中，即串联不是聚合操作之一（不像SUM，COUNT，AVG等它们）。

Unfortunately, group_concat is not available (out of the box) in SQL Server, i.e. concatenation is not one of the aggregation operations (unlike SUM, COUNT, AVG etc which are).

的各种方法，包括他们如何进行比较，是的这里。

A comparison of the various methods, including how they perform, is here.

这篇关于多行查询的结果中只有一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！