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

zaq34kh6  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(314)

下面是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
r8xiu3jd

r8xiu3jd1#

我想你只是想 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
             ) ;

相关问题