时间戳

首页 > 解决方案 > 如何在mysql中联合的两个部分中使用where条件

php - 如何在mysql中联合的两个部分中使用where条件

问题描述

我正在使用 php 和 mysql 来制作聊天应用程序。我想在联合的两个部分都使用 where 条件，但这不起作用我的代码是：

$Query = "(SELECT u.userid, u.photo, u.username FROM `last_activity` AS la INNER JOIN user AS u ON la.userid = u.userid LEFT join private_chat pc ON u.userid = pc.sender_id WHERE la.last_activity_date BETWEEN '2020-09-21 10:20:00' AND '2020-09-21 10:30:00' AND u.username like '%amit%' ORDER BY pc.created_date DESC) UNION (SELECT userid, photo, username FROM user u LEFT join private_chat pc ON u.userid = pc.sender_id WHERE u.u_type = '2')";

我想要喜欢，但这不起作用

$Query = "(SELECT u.userid, u.photo, u.username FROM `last_activity` AS la INNER JOIN user AS u ON la.userid = u.userid LEFT join private_chat pc ON u.userid = pc.sender_id) UNION (SELECT userid, photo, username FROM user u LEFT join private_chat pc ON u.userid = pc.sender_id ) WHERE la.last_activity_date BETWEEN '2020-09-21 10:20:00' AND '2020-09-21 10:30:00' AND u.u_type = '2' AND u.username like '%amit%' ORDER BY pc.created_date DESC";

标签： phpmysql

解决方案

SELECT *
FROM 
(
    SELECT u.userid, u.photo, u.username, pc.created_date
    FROM `last_activity` AS la
    INNER JOIN `user` AS u ON la.userid = u.userid
    LEFT JOIN private_chat pc ON u.userid = pc.sender_id
    WHERE la.last_activity_date BETWEEN '2020-09-21 10:20:00' AND '2020-09-21 10:30:00'
        AND u.username LIKE '%amit%' 
        
    UNION ALL
    
    SELECT userid, photo, username, pc.created_date
    FROM `user` u
    LEFT JOIN private_chat pc ON u.userid = pc.sender_id
    WHERE u.u_type = '2'
) a
ORDER BY a.created_date DESC;

推荐阅读