mysql相关子查询总是出错，找不到原因

下面是5个应用程序数据库表

CREATE TABLE IF NOT EXISTS `Cars` (
  `car_id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `insertionDate` datetime NOT NULL,
  PRIMARY KEY (`car_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

 CREATE TABLE IF NOT EXISTS `newCars` (
  `car_id` int(11) NOT NULL, //Only new cars have a dealer (Cars.type = 1)
  `dealer_id` int(11) NOT NULL,
  PRIMARY KEY (`car_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `CarsPhotos` (
  `photo_id` int(11) NOT NULL AUTO_INCREMENT,
  `car_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `insertionDate` datetime NOT NULL,
  PRIMARY KEY (`photo_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `Dealers` (
  `dealer_id` int(11) NOT NULL AUTO_INCREMENT,
  `dealerName` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  `insertionDate` datetime NOT NULL,
  PRIMARY KEY (`dealer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `dealersPhotos` (
  `photo_id` int(11) NOT NULL AUTO_INCREMENT,
  `dealer_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `insertionDate` datetime NOT NULL,
  PRIMARY KEY (`photo_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

我正在尝试查找至少有一张相关照片可供查看的汽车（汽车照片或经销商照片），如下所示：
搜索操作必须在一个查询中完成
检查汽车是否至少有一个照相车计数。
如果carphotocount=0，则检查车辆是否是新的（cars.type=1）
如果是新车，则从新车表中获取经销商id
检查经销商是否至少有一个照片经销商照片计数。
为此，我用两种方法编写了sql查询（mysql），它们都会产生一个相关错误。

SELECT car_id ,
    FROM Cars c
    WHERE
      CASE
        WHEN
          (SELECT count(*) as carPhotosCount FROM CarsPhotos WHERE CarsPhotos.car_id = c.car_id HAVING carPhotosCount > 0) THEN 1
        ELSE
          CASE
            WHEN type = 1 THEN
              CASE
                WHEN
                  (SELECT count(*) as dealerPhotosCount FROM dealersPhotos
                  JOIN  newCars ON  newCars.car_id = c.car_id
                  WHERE dealersPhotos.dealer_id = newCars.dealer_id HAVING dealerPhotosCount > 0) THEN 1
                ELSE 0
              END
            ELSE 0
          END
      END

问题2：

SELECT car_id ,
      CASE
        WHEN type = 1 THEN (SELECT newCars.dealer_id FROM newCars WHERE newCars.car_id = c.car_id)
        ELSE null
      END deal_id
    FROM Cars c
    WHERE
      CASE
        WHEN (SELECT count(*) as dealerPhotosCount FROM dealersPhotos WHERE dealersPhotos.dealer_id = deal_id HAVING dealerPhotosCount > 0) THEN 1
        ELSE
          CASE
            WHEN (SELECT count(*) as carPhotosCount FROM CarsPhotos WHERE CarsPhotos.car_id = c.car_id HAVING carPhotosCount > 0) THEN 1
            ELSE 0
          END
      END

我想你只是想 exists 以及 limit :

SELECT car_id ,
       (CASE WHEN type = 1
            THEN (SELECT nc.dealer_id FROM newCars nc WHERE nc.car_id = c.car_id LIMIT 1)
        END) as deal_id
FROM Cars c
WHERE EXISTS (SELECT 1
              FROM dealersPhotos dp
              WHERE dp.dealer_id = c.deal_id
             ) OR
      EXISTS (SELECT 1 
              FROM CarsPhotos cp
              WHERE cp.car_id = c.car_id
             ) ;

查询中没有明显的重大错误。但是，中的子查询 SELECT 可能返回多行，这通常会导致错误。
编辑：
您似乎希望在外部查询中加入经销商，然后在 WHERE 条款。一种方法是在 WHERE 条款：

SELECT c.car_id,
       (CASE WHEN c.type = 1 THEN nc.dealer_id END) as deal_id
FROM Cars c LEFT JOIN
     newCars nc
     ON nc.car_id = c.car_id 
WHERE EXISTS (SELECT 1
              FROM dealersPhotos dp
              WHERE dp.dealer_id = (CASE WHEN c.type = 1 THEN nc.dealer_id END)
             ) OR
      EXISTS (SELECT 1 
              FROM CarsPhotos cp
              WHERE cp.car_id = c.car_id
             ) ;

mysql相关子查询总是出错，找不到原因

1条答案

相关问题

热门标签

最新问答