php - 如何使用别名从两个表中获取计数值

Question

我有两个表，一个 export_log ，另一个是 client_log 表，首先我尝试从单个表中获取 count 值(export_log) 工作正常。然后我尝试从两个表(export_log 和 client log)获取 count 值，现在出现如下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) Total_login, cl.count(*) Total_access DATE_FORMAT( max( el.lastmodified ) ' at line 1

我需要通过单个查询从两个表中获取count值，我已经尝试了很多次但没有得到结果。以及如何在 max() 函数中使用别名？

请更正此查询..

 SELECT el.count( * ) Total_login, cl.count(*) Total_access DATE_FORMAT( max( `el.lastmodified` ) , '%d %M %y ,%H:%i' ) last_logged
    FROM export_log el, client_log cl
    WHERE (el.unique_id = 'n110r24dan756j5vnv5v0016r31ad6jg' and el.unique_id != 'nn10r24daj756j5hnv5v0016r31ad6ja') AND (cl.unique_id = 'n110r24dan756j5vnv5v0016r31ad6jg' and cl.unique_id != 'n110r24dan756j5vnv5v0016r31ad6jg')
    AND el.user_name
    IN (
    'bala','sathish'
    ) AND cl.user_name IN('bala','sathish')
    AND `el.lastmodified`>=CAST(DATE_FORMAT( NOW( ) , '%Y-%m-01' ) AS DATE)and CAST(DATE_FORMAT(NOW(),'%Y-%m-01') AS DATE)
    GROUP BY el.unique_id,cl.unique_id
    ORDER BY Total_login DESC

Answer 1

因为您的 select 子句语法错误，所以您可以尝试:

SELECT count(el.*) Total_login, count(cl.*) Total_access, DATE_FORMAT( max(`el.lastmodified`) , '%d %M %y ,%H:%i') last_logged
    FROM export_log el, client_log cl
    WHERE (el.unique_id = 'n110r24dan756j5vnv5v0016r31ad6jg' and el.unique_id != 'nn10r24daj756j5hnv5v0016r31ad6ja') AND (cl.unique_id = 'n110r24dan756j5vnv5v0016r31ad6jg' and cl.unique_id != 'n110r24dan756j5vnv5v0016r31ad6jg')
    AND el.user_name
    IN (
    'bala','sathish'
    ) AND cl.user_name IN('bala','sathish')
    AND `el.lastmodified`>=CAST(DATE_FORMAT( NOW( ) , '%Y-%m-01' ) AS DATE)and CAST(DATE_FORMAT(NOW(),'%Y-%m-01') AS DATE)
    GROUP BY el.unique_id,cl.unique_id
    ORDER BY Total_login DESC