sqlite3 ，这个查询如何写？

[自由建客]

代码： 全选

-- 厂家表
CREATE TABLE Vender (
    Id   INTEGER     PRIMARY KEY AUTOINCREMENT,
    Name CHAR( 32 )  NOT NULL
                     UNIQUE,
    Show BOOLEAN     NOT NULL
                     DEFAULT ( TRUE )
);

-- 产品类型表
CREATE TABLE ProductType (
    Id   INTEGER     PRIMARY KEY AUTOINCREMENT,
    Name CHAR( 32 )  NOT NULL
                     UNIQUE
);

-- 产品表
CREATE TABLE Product (
    Id    INTEGER PRIMARY KEY AUTOINCREMENT,
    V_Id  INTEGER NOT NULL
                  REFERENCES Vender ( Id ) ON DELETE NO ACTION
                                           ON UPDATE CASCADE,
    PT_Id INTEGER NOT NULL
                  REFERENCES ProductType ( Id ) ON DELETE NO ACTION
                                                ON UPDATE CASCADE,
    Show BOOLEAN  NOT NULL
                  DEFAULT ( TRUE ),
    UNIQUE ( V_Id, PT_Id )
);

-- 产品价格表
CREATE TABLE ProductValue (
    P_Id  INTEGER NOT NULL
                  REFERENCES Product ( Id ) ON DELETE NO ACTION
                                            ON UPDATE CASCADE,
    Value NUMERIC NOT NULL,
    VDate DATE    NOT NULL,
    UNIQUE ( P_Id, VDate )
);

厂家名，产品类型名，最新报价，定价日期

代码： 全选

        SELECT Vender.Name,
               ProductType.Name,
               ProductValue.Value,
               ProductValue.VDate
          FROM ……此处该如何写？

[自由建客]

代码： 全选

-- 产品视图
CREATE VIEW ProductView AS
       SELECT Tmp.Id,
              Vender.Name AS Vender,
              ProductType.Name AS Type,
              Tmp.Value,
              Tmp.Date,
              Tmp.Show
         FROM (
              SELECT P.Id,
                     P.V_Id,
                     P.PT_Id,
                     V.Value,
                     max( V.Date ) AS Date,
                     P.Show
                FROM Product AS P,
                     ProductValue AS V
               WHERE P.Id = V.P_Id
               GROUP BY P.Id
        )
           AS Tmp,
              Vender,
              ProductType
        WHERE Tmp.V_Id = Vender.Id
              AND
              Tmp.PT_Id = ProductType.Id;