在2表中显示数据列表中的数据 [英] Show data in datalist from 2 table

问题描述

我有两个表Tbl_Car_detail，其中列为

I Have Two Table Tbl_Car_detail where Column is

CarID(it is Private key)
CarBrand

和第二个表tbl_car_Image，其中列为

and 2nd Table tbl_car_Image where Column is

ImageID(it is Private key)
CarID(it is foreign key of Tbl_Car_detail)
Image

像这样在第一个表Tbl_Car_detail中记录

Record in 1st table Tbl_Car_detail like this

CarID                CarBrand
1                       Audi

2                       bwm

我在第二张表tbl_car_Image中插入具有相同CarID的多个图像，就像这样

i am inserting Multiple Image of same CarID in 2nd table tbl_car_Image like this

ImageID                  CarID             Image
1                             1                  Image1

2                             1                  Image2

3                             1                  Image3

4                              2                 Image4

5                              2                 Image5

现在我想在两个表的一个数据列表中显示结果
我正在使用此sql查询

Now i want show result in one datalist from both table
I am Using this sql Query

SELECT     dbo.Tbl_Car_detail.CarID,dbo.tbl_mst_CarDetail.CarBrand , tbl_car_Image.Image
FROM         dbo.Tbl_Car_detail.CarID RIGHT OUTER JOIN
                      tbl_car_Image.Image ON dbo.Tbl_Car_detail.CarID = dbo.tbl_car_Image.CarID

我正在获取这样的记录

I am Geting record like this

CarID               CarBrand                Image

1                      Audi                       Image1

1                      Audi                       Image2

1                      Audi                       Image3

2                      Bmw                        Image4

2                      Bmw                        Image5

但是我只希望每个CarID结果像这样的1张图像

But i want only 1 image with per CarID result like this

CarID               CarBrand                Image

1                      Audi                 Image1

2                     Bmw                   Image4

请提供我的sql查询，您可以在中删除sql查询邮件.[/Edit]

Please Provide me sql query, you can mail sql Query at E-mail removed[/Edit]

推荐答案

如果我了解您的问题，那么您可能只需要添加GROUP BY，然后使用MAX函数来获取图像字段之一.

If I''m understanding your question, then you may just need to add a GROUP BY and then use the MAX function to get one of the image fields.

SELECT dbo.Tbl_Car_detail.CarID, dbo.tbl_mst_CarDetail.CarBrand, MAX(tbl_car_Image.Image)
FROM dbo.Tbl_Car_detail
RIGHT OUTER JOIN tbl_car_Image.Image
ON dbo.Tbl_Car_detail.CarID = dbo.tbl_car_Image.CarID
GROUP BY dbo.Tbl_Car_detail.CarID, dbo.tbl_mst_CarDetail.CarBrand

或者，如果您需要确保获得每个组的顶部图像(顺序很重要)，那么也许可以使用一个通用的表表达式，如下所示:

Or if you need to ensure that you''re getting the top image for each group (order matters), then perhaps you can use a common table expression like this:

WITH cte AS 
(
SELECT dbo.Tbl_Car_detail.CarID, 
       dbo.tbl_mst_CarDetail.CarBrand, 
       tbl_car_Image.Image, 
       ROW_NUMBER() OVER (PARTITION BY dbo.Tbl_Car_detail.CarID, dbo.tbl_mst_CarDetail.CarBrand ORDER BY tbl_car_Image.Image DESC) AS rownum
FROM dbo.Tbl_Car_detail
RIGHT OUTER JOIN tbl_car_Image.Image
ON dbo.Tbl_Car_detail.CarID = dbo.tbl_car_Image.CarID
)
SELECT CarID, CarBrand, Image
FROM cte
WHERE rownum = 1

我会承认实际上并没有创建表并运行任何一个查询，因此您可能需要在使用它们之前对其进行调整.

I''ll admit to not actually creating tables and running either of these queries, so you may need to tweak them before use.

这篇关于在2表中显示数据列表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！