среда, 27 марта 2013 г.

Список дат, когда была отгрузка товара. Решения с подзапросами

-- Список дат (ShipDate), когда была отгрузка товара 777 (ProductID)-- Решение с подзапросами


-- Предикат EXISTS

DECLARE @PID AS INT = 777;
SELECT
    DISTINCT CAST (OH.ShipDate AS DATE) AS ShipDate
FROM AdventureWorks2012.Sales.SalesOrderHeader AS OH
WHERE
    EXISTS
    (
         SELECT *
         FROM AdventureWorks2012.Sales.SalesOrderDetail AS OD
         WHERE OD.SalesOrderID = OH.SalesOrderID
                 AND OD.ProductID = @PID
    );
GO

-- Предикат IN

DECLARE @PID AS INT = 777;
SELECT
    DISTINCT CAST (OH.ShipDate AS DATE) AS ShipDate
FROM AdventureWorks2012.Sales.SalesOrderHeader AS OH
WHERE
    @PID IN
    (
         SELECT OD.ProductID
         FROM AdventureWorks2012.Sales.SalesOrderDetail AS OD
         WHERE OD.SalesOrderID = OH.SalesOrderID
    )
GO

-- Предикат SOME

DECLARE @PID AS INT = 777;

SELECT
    DISTINCT CAST (OH.ShipDate AS DATE) AS ShipDate
FROM AdventureWorks2012.Sales.SalesOrderHeader AS OH
WHERE
    @PID = SOME
    (
         SELECT OD.ProductID
         FROM AdventureWorks2012.Sales.SalesOrderDetail AS OD
         WHERE OD.SalesOrderID = OH.SalesOrderID
    )