我正在创建一个包含多个子查询的选择,但是我没有找到一种使用子查询值来创建where语句似乎被忽略的方法。
我有1个内容表,2个连接表以及2个其他连接表,而另一个连接表直接第一个。

我尝试了几种解决方案,这是最新的一种

select * from (SELECT a.idcontents, a.title, concat(a.filepath, '/', a.filename) as file,
        a.captured_on, a.starred, a.file_type,
        @PL:=(select GROUP_CONCAT(CONCAT(p.idplaces, '-', p.place) SEPARATOR ', ') from places p where a.idplaces=p.idplaces) as places,
        @AL:=(select GROUP_CONCAT(CONCAT(al.idalbum, '-', al.album) SEPARATOR ', ') from album al, join_album_contents am where am.idalbum=al.idalbum and am.idcontents=a.idcontents) as album,
        @PE:=(select GROUP_CONCAT(CONCAT(an.idpeople, '-', an.person) SEPARATOR ', ') from people an, join_people_contents ao where ao.idpeople=an.idpeople and ao.idcontents=a.idcontents) as people
        FROM contents a) t
        WHERE (@PE is null OR @AL is null OR @PL=1)


但我也尝试过

SELECT a.idcontents, a.title, concat(a.filepath, '/', a.filename) as file,
        a.captured_on, a.starred, CONCAT(p.idplaces, '-', p.place) as places,
        (select GROUP_CONCAT(CONCAT(al.idalbum, '-', al.album) SEPARATOR ', ') from album al, join_album_contents am where am.idalbum=al.idalbum and am.idcontents=a.idcontents) as album,
        (select GROUP_CONCAT(CONCAT(an.idpeople, '-', an.person) SEPARATOR ', ') from people an, join_people_contents ao where ao.idpeople=an.idpeople and ao.idcontents=a.idcontents) as people
        FROM contents a, places p
        WHERE a.idplaces=p.idplaces
        and (@album IS NULL OR @people IS NULL or p.idplaces=1)


没有成功,似乎where子句总是被忽略,并且结果包含其中包含一个或所有where子句的记录。
只有安慰记录才能正确显示。

只是想知道是否有解决此问题的解决方案。

在下面的评论中找到解决方案!!

t.people is null or t.album is null or t.places='1'


谢谢!!

最佳答案

这等效吗?:

SELECT
    a.idcontents, a.title, concat(a.filepath, '/', a.filename) as file,
    a.captured_on, a.starred, a.file_type
FROM contents a
WHERE
        NOT EXISTS (
            select 1
            from people p inner join people_contents pc on pc.idpeople = p.people
            where p.idpeople = a.idcontents /* is this the right column? */
        )
    or  NOT EXISTS (
            select 1
            from album al inner join album_contents ac on ac.idalbum = al.idalbum
            where al.idcontents = a.idcontents
        )
    or  1 = (
            select min(p.idplaces) from places p
            where p.idplaces = a.idplaces
            having count(*) = 1
        )

关于mysql - 使用子查询的MySQL SELECT不能按预期工作,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38161759/

10-13 02:03