サブクエリなしで2つのテーブルの結合から最大IDの行を取得する

debugcn 投稿 Dev

user606669：

これら2つのテーブルにJOINクエリを実装するのに問題があります。

製品表には製品が含まれています。ProductInstanceテーブルには、時間の経過とともに価格が変更された製品のバージョンが含まれています。

nullであっても、storeIdごとに1つの行があるはずですが、最も高いproductInstanceIdが含まれている必要があります。

私のクエリは各製品の最大IDを持つ行を取得できますが、StoreIdを無視します。

Select P.id as productId, PI.id as productInstanceId, P.name, PI.storeId, PI.price from products as P

LEFT JOIN productInstances AS PI
ON PI.productId = P.id

LEFT JOIN productInstances AS PI2
ON (PI2.productId = P.id and (PI.id < PI2.id )  )
WHERE PI2.id IS NULL
AND PI.id IS NOT NULL

テーブルを作成します。

CREATE SCHEMA IF NOT EXISTS `TestDB` DEFAULT CHARACTER SET utf8 ;
USE `TestDB` ;

-- -----------------------------------------------------
-- Table `TestDB`.`products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TestDB`.`products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TestDB`.`productInstances`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TestDB`.`productInstances` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `storeId` INT NULL,
  `productId` INT NOT NULL,
  `price` INT NOT NULL,
  PRIMARY KEY (`id`, `productId`),
  INDEX `fk_productInstances_products_idx` (`productId` ASC),
  CONSTRAINT `fk_productInstances_products`
    FOREIGN KEY (`productId`)
    REFERENCES `TestDB`.`products` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

このクエリを使用してテストデータを挿入する

INSERT INTO `TestDB`.`products` (`name`) VALUES ('Product 1');
INSERT INTO `TestDB`.`products` (`name`) VALUES ('Product 2');

INSERT INTO `TestDB`.`productInstances` (`storeId`, `productId`, `price`) VALUES ('1', '1', '111');
INSERT INTO `TestDB`.`productInstances` (`productId`, `price`) VALUES ('1', '122');
INSERT INTO `TestDB`.`productInstances` (`productId`, `price`) VALUES ('1', '133');
INSERT INTO `TestDB`.`productInstances` (`storeId`, `productId`, `price`) VALUES ('1', '1', '115');
INSERT INTO `TestDB`.`productInstances` (`storeId`, `productId`, `price`) VALUES ('2', '1', '155');

逝去：

MySQLの場合8.0+を使用できROW_NUMBER()、それぞれの最大のIDを取得するには、ウィンドウ関数をproductidとstoreid：

select p.id productId, 
       pi.id productInstanceId, 
       p.name, 
       pi.storeId, 
       pi.price 
from products p 
left join (
  select *, row_number() over (partition by storeId, productId order by id desc) rn
  from productInstances
) pi on pi.productId = p.id and pi.rn = 1

以前のバージョンでは、相関サブクエリで同じことができます。

select p.id as productId, 
       pi.id as productInstanceId, 
       p.name, 
       pi.storeId, 
       pi.price 
from products p 
left join (
  select pi.* from productInstances pi
  where pi.id = (
    select max(id) 
    from productInstances 
    where productId = pi.productId and storeId <=> pi.storeId 
  )
) pi on pi.productId = p.id

デモをご覧ください。
結果：

> productId | productInstanceId | name      | storeId | price
> --------: | ----------------: | :-------- | ------: | ----:
>         1 |                 3 | Product 1 |    null |   133
>         1 |                 4 | Product 1 |       1 |   115
>         1 |                 5 | Product 1 |       2 |   155
>         2 |              null | Product 2 |    null |  null

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]