如何只连接第一行(存储过程)

Hi i have following stored procedure which returns multiple photosnames. i want to return just first row. i tried TOP 1 but i'm sure i am making some syntax error. pls help

你好，我有以下存储过程，返回多个photosname。我要返回第一行。我尝试了TOP 1，但我确信我犯了一些语法错误。请帮助

CREATE PROCEDURE getProductsBySubCategoryName
    @SubCategoryName nvarchar(50)
AS
BEGIN
SELECT Products.productName, subCategory.subcategoryName, Photos.photoName
    FROM subCategory INNER JOIN Products ON
    subCategory.subcategory_id = Products.subcategory_id
    INNER JOIN Photos ON
    Products.product_id = Photos.product_id
    WHERE (subcategoryName = @SubCategoryName) 
END

One more question. how can i select when photoName is null or select a specified record for null photo –

一个问题。如何选择photoName为null或为null photo -选择指定的记录

2 个解决方案

#1

If you need first photo for each product, try following:

如果您需要每个产品的第一张照片，请尝试如下:

CREATE PROCEDURE getProductsBySubCategoryName
    @SubCategoryName nvarchar(50)
AS
BEGIN
    set nocount on;

    ;with ph as (
        select product_id, photoName, row_number() over (partition by product_id order by photoName) pNum
        from Photos
    )   
    SELECT p.productName, sc.subcategoryName, ph.photoName
    FROM subCategory sc
        INNER JOIN Products p ON sc.subcategory_id = p.subcategory_id
        INNER JOIN ph ON p.product_id = ph.product_id and ph.pNum = 1
        WHERE (sc.subcategoryName = @SubCategoryName);
END

#2

You should be able to access the top record simply by adding "TOP 1" after SELECT. Take a look at this url for clarification: http://www.w3schools.com/sql/sql_top.asp. Also can you please specify the exact error you are getting?

您应该能够通过在选择后添加“top 1”来访问top记录。请查看此url以进行说明:http://www.w3schools.com/sql/sql_top.asp。你能不能详细说明你得到的准确错误?