有关SQL查询的问题 [英] Question regarding to sql query

问题描述

嗨！

我的问题是关于加入表

我有一个产品和图像表. ProductId是图像表中的外键.
我的意思是说Image表包含单个产品的多个图像.

我需要这样的查询，该查询返回产品的所有数据和每个产品的单个图​​像以显示在首页中.

产品领域如下
============================
ProductId
产品名称
详细信息
CountryId
StateId
CityId
CategoryId
CompanyId
StartingDate
ClosingDate
BidPrice
BidIncrement
型号
注册号
IsAvailable
状态


以下是图像表字段
===============================
ImageId，ProductId，ProductImage，IsActive

Hi!

My question is regarding to join table

I have a Product and Image table. ProductId is a foreign key in Image table.
I mean to say that Image table contain multiple image for single product.

I need such query that return all the data of product and single image of every product to display in homepage.

Product Field are following
==============================
ProductId
ProductName
Detail
CountryId
StateId
CityId
CategoryId
CompanyId
StartingDate
ClosingDate
BidPrice
BidIncrement
Model
RegisterNo
IsAvailable
Status


Following are Image table field
================================
ImageId, ProductId, ProductImage, IsActive

推荐答案

您有一个产品的多个图像，如果显示任何图像就可以了吗?

然后使用imagetable中的前1张图像

You have multiple images for single product and is that fine if you display any images?

Then use Top 1 image from imagetable

select * from product p
inner join image i on p.ProductId=i.productid
where imageid in (select top 1 imageid from image where productid=p.productid)

朋友你好，我已经放了一些虚拟数据并进行查询以从图像"表中选择第一张图像，我认为这可能对您有所帮助.

Hello friend i have put some dummy data and make one query to select the first images from Image table, i thought it might helps you.

CREATE TABLE PRODUCT(
    ProductId INT IDENTITY(1,1)
    ,ProductName NVARCHAR(MAX)
    ,Detail NVARCHAR(MAX)
    ,CountryId INT
    ,StateId INT
    ,CityId INT
    ,CategoryId INT
    ,CompanyId INT
    ,StartingDate DATETIME
    ,ClosingDate DATETIME
    ,BidPrice FLOAT
    ,BidIncrement INT
    ,Model VARCHAR(50)
    ,RegisterNo VARCHAR(50)
    ,IsAvailable BIT
    ,Status BIT
)


CREATE TABLE IMAGES(
    ImageId INT IDENTITY(1,1)
    ,ProductId INT
    ,ProductImage VARCHAR(50)
    ,IsActive BIT
)



INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod1','Test Prod 1',1000)
INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod2','Test Prod 2',1000)
INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod3','Test Prod 3',1000)
INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod4','Test Prod 4',1000)


INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(1,'Prod1 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(1,'Prod1 Image 2',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(1,'Prod1 Image 3',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(2,'Prod2 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(2,'Prod2 Image 2',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(3,'Prod3 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(4,'Prod4 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(4,'Prod4 Image 2',1)



SELECT * FROM PRODUCT
SELECT * FROM IMAGES

SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY ProductImage ) AS RCNT FROM IMAGES) AS DATA
WHERE DATA.RCNT = 1

OUT PUT是这样的

and the OUT PUT is like this

ImageID ProductID ProductIMage IsActive RCNT
1	1	Prod1 Image 1	1	1
4	2	Prod2 Image 1	1	1
6	3	Prod3 Image 1	1	1
7	4	Prod4 Image 1	1	1

并与产品表和图像表联接

And with Product Table And Image Table Join

SELECT PRODUCT.ProductId,PRODUCT.ProductName,PRODUCT.Detail ,PRImage.ProductImage FROM PRODUCT
INNER JOIN (
SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY ProductImage ) AS RCNT FROM IMAGES) AS DATA
WHERE DATA.RCNT = 1) AS PRImage ON PRImage.PRODUCTID = PRODUCT.PRODUCTID

输出

OUT PUT

ProductId ProductName     Detail        ProductImage
1	   Prod1	Test Prod 1	Prod1 Image 1
2	   Prod2	Test Prod 2	Prod2 Image 1
3	   Prod3	Test Prod 3	Prod3 Image 1
4	   Prod4	Test Prod 4	Prod4 Image 1

您可以根据需要修改此查询.

You can modifiy this query as per your requirement.

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