求带主从结构仅取3条明细的SQL语句及现有SQL问题咨询

Alright, let's work through your two SQL needs and fix that incomplete query together—here's a practical approach tailored to your scenario:

1. Complete & Optimize the Incomplete Query #

First, let's finish the JOIN conditions and add logic to limit each product to only 3 supplier price records (since you mentioned a master-detail pair with only 3 details). I noticed you're referencing a supplier table (r.Nombre, r.Calificacion) but haven't joined it yet, so I'll include that too.

Here's the polished query:

WITH RankedSupplierPrices AS (
    SELECT 
        p.id_empresa, 
        p.codigo, 
        p.descripcion, 
        p.piezas_comprar,
        -- Replace IIF with CASE if you need cross-database compatibility
        IIF(o.Costo IS NULL, 0, o.Costo) AS Costo,
        IIF(r.Nombre IS NULL, 'S/P', r.Nombre) AS Proveedor,
        IIF(r.Calificacion IS NULL, 0, r.Calificacion) AS Calificacion,
        -- Rank prices per product (adjust ORDER BY to prioritize records as needed)
        ROW_NUMBER() OVER (PARTITION BY p.codigo ORDER BY o.Costo ASC) AS PriceRank
    FROM vw_productos_compras p
    -- Join to supplier price table (fix the ON condition to match your actual schema!)
    LEFT JOIN access_pyme.dbo.Productos_Proveedores o 
        ON p.codigo = o.Codigo_Producto  -- Assumed product code match; update if your column names differ
    -- Join to supplier details table
    LEFT JOIN access_pyme.dbo.Proveedores r 
        ON o.Id_Proveedor = r.Id_Proveedor  -- Assumed supplier ID match; adjust if needed
)
-- Filter to only keep top 3 price records per product
SELECT 
    id_empresa, 
    codigo, 
    descripcion, 
    piezas_comprar,
    Costo,
    Proveedor,
    Calificacion
FROM RankedSupplierPrices
WHERE PriceRank <= 3;

Key Details to Adjust: #

• Join Conditions: Double-check o.Codigo_Producto and r.Id_Proveedor—these are guesses based on typical schema design. Replace them with your actual column names that link the product view to the supplier price table, and the price table to the supplier table.
• Sort Priority: The ORDER BY o.Costo ASC in the window function ranks cheapest prices first. If you want to prioritize higher-rated suppliers instead, change it to ORDER BY r.Calificacion DESC.
• Cross-Database Compatibility: If you're not using SQL Server/Azure SQL, swap IIF with CASE statements (e.g., CASE WHEN o.Costo IS NULL THEN 0 ELSE o.Costo END AS Costo).

2. Simplified Query for Total 3 Detail Records #

If you meant returning only 3 total detail records (not 3 per product), here's a stripped-down version:

SELECT TOP 3
    p.id_empresa, 
    p.codigo, 
    p.descripcion, 
    p.piezas_comprar,
    IIF(o.Costo IS NULL, 0, o.Costo) AS Costo,
    IIF(r.Nombre IS NULL, 'S/P', r.Nombre) AS Proveedor,
    IIF(r.Calificacion IS NULL, 0, r.Calificacion) AS Calificacion
FROM vw_productos_compras p
LEFT JOIN access_pyme.dbo.Productos_Proveedores o 
    ON p.codigo = o.Codigo_Producto
LEFT JOIN access_pyme.dbo.Proveedores r 
    ON o.Id_Proveedor = r.Id_Proveedor
-- Add an ORDER BY to ensure consistent results
ORDER BY p.codigo, o.Costo;

内容的提问来源于stack exchange，提问作者Oscar