我认为案例陈述将是解决此问题的最佳方法,但对任何其他建议持开放态度。我想做的是这个。查询是基于用户输入的关于他们正在寻找的类型报告生成的。事情是所有可能的表,除了一个包含 message_id 列。在奇数表上,我可以通过另一个字段 time_sent 获取数据。以下是用户输入后生成的查询示例。
SELECT
ps.account_id,
ba.account_name,
NULL AS total_users,
COUNT(DISTINCT(ps.uid)) AS unique_users,
COUNT(DISTINCT r.msg_timestamp) AS messages_received,
NULL AS total_clicks,
NULL AS unique_clicks,
COUNT(DISTINCT s.message_id) AS messages_sent, // THIS IS WHERE I NEED TO USE A CASE OR SOME OTHER MEANS TO DETERMINE IF THE message_id COLUMN EXISTS
COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
FROM conversation_facebook_page_subscribers ps
LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
WHERE
(ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
AND
(r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
GROUP BY ps.account_id, ba.account_name
我尝试使用 case 语句来检查表的名称,并根据表名称选择适当的字段,但这不起作用。我已经搜索了很多关于 SO 的问题,看看我是否能找到可行的方法,但我没有找到任何可行的方法。有人可以指出我正确的方向吗?提前感谢您的任何帮助。
您可以使用INFORMATION_SCHEMA COLUMNS 表
但是不能在普通的sql语句中使用,因为mysql会解析所有部分。
我仍然有问题要理解,为什么你首先缺少列。