连接别名计算列中的行 [英] Concatenating rows from an alias computed column

问题描述

我有两个表，一个叫Car，一个叫Mileage。汽车表具有以下列：
汽车名称varchar（max）
里程表具有以下列：

CarMileage int
CarName varchar（max）
TotalGasTrips int

我的代码如下：

I have two tables, one named Car and one named Mileage. Car table has the following column:
CarName varchar(max)
Mileage table has the following columns:
CarMileage int CarName varchar(max) TotalGasTrips int

My code is the following:

SELECT DISTINCT y.CarName,
                Substring((SELECT ', ' + Cast(c.MPG AS VARCHAR(max))
                           FROM   Mileage
                                  JOIN Car
                                    ON Mileage.CarName = Car.CarName
                           WHERE  Mileage.CarName = Car.CarName
                           FOR xml path ('')), 2, 1000) AS MPGString
FROM   Car y
       INNER JOIN (SELECT w.CarName,
                          CarMileage / TotalGasTrips AS MPG
                   FROM   Mileage w
                   GROUP  BY w.CarName,
                             w.CarMileage,
                             w.TotalGasTrips)c
               ON y.CarName = c.CarName
GROUP  BY c.MPG,
          y.CarName 

查询结果为：

CarName      MPGString
Acura NSX    20,20,20,20
Acura NSX    42,42,42,42
Pagani Zonda 24,24,24,24
Pagani Zonda 43,43,43,43

我想要的结果是：

The result I want is:

CarName     MPGString
Acura NSX    20,42
Pagani Zonda 24,43

是可以从别名计算列中获取我想要的正确的串联行？我尝试遍历所有内容，发现的是从已具有现有值的列进行连接，而不是从计算列进行连接。谢谢您的帮助。

Is it possible to get the correct concatenated rows I want from aliased computed columns? I tried looking all over and all I found was concatenation from columns that already had existing values and not from computed columns. Thanks for your help.

编辑：

这两张桌子的汽车名称相同（在这种情况下，NS歌NSX和为了测试字符串的串联，我给两辆车的TripGasTrips和CarMileage赋予了不同的编号。里程表中的示例如下：

Ac歌NSX 125471,6172
ura歌NSX 213776,5000
帕加尼Zonda 166793、6911
帕加尼Zonda 315531 7312

列分别为CarName，CarMileage和TotalGasTrips
，在Car Table中，我具有以下内容：

Ac歌NSX
帕加尼Zonda

，其中CarName是该表的唯一列。

编辑：

c表中的输出如下：

with the columns being CarName, CarMileage and TotalGasTrips respectively and in the Car Table I have the following:
Acura NSX Pagani Zonda
with CarName being the only column for the table.

Output from the c table is as follows:

CarName MPG
Acura NSX 20
Acura NSX 42
Pagani Zonda 24
Pagani Zonda 43

推荐答案

这对我有用（如果您提供一些数据进行测试，会更容易）：

This works for me (it would be easier if you gave some data to test with):

SELECT
  C.CarName,
  SUBSTRING((
             SELECT
              ', ' + CAST(CarMileage / TotalGasTrips AS VARCHAR(MAX))
             FROM
              Mileage
             WHERE
              Mileage.CarName = C.CarName
             GROUP BY
              dbo.Mileage.CarMileage,
              dbo.Mileage.TotalGasTrips
            FOR
             XML PATH('')
            ), 2, 1000) AS MPGString
FROM
  Car C
  JOIN dbo.Mileage M ON C.CarName = M.CarName
GROUP BY
  C.CarName

这篇关于连接别名计算列中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！