MySQL UNION在同一个表上，带有两个WHERE子句

Question

我有一个表alerts

date | alert | status | sentDate | id 

在该表中，在我发送警报后，我将状态设置为"sent"并设置发送日期。默认情况下，状态为"not"(表示尚未发送(。

现在，我想获得最后一个sentDate，我可以使用以下查询来获得它

SELECT sentDate FROM alerts WHERE id = someid AND status = 'sent' Order by date desc limit 1;

此外，我想获得尚未发送的最新警报，我正在使用此查询

SELECT date, alert FROM alerts WHERE id = someid AND status = 'not' order by date asc limit 1;

是否可以在一个查询中完成此操作？也许使用UNION。我曾尝试对这两个查询应用UNION/UNIONALL，但它给了我错误。

有人能帮忙吗？

提前谢谢。

Answer 1

SELECT 
max(CASE WHEN status = 'sent' THEN DATE END) AS sentDate,
max(CASE WHEN status = 'not' THEN DATE END) AS notSentDate 
FROM alerts WHERE id = someid 

您可以尝试上述查询。

它会帮助你的。

Answer 2

你可以试试这个：

SELECT MAX(t.sentDate) AS sentDate, MAX(t.date) AS date, MAX(alert) AS alert
FROM
(
SELECT MAX(sentDate) AS  sentDate, "" AS date, "" AS alert
FROM alerts 
WHERE id = someid 
AND status = 'sent'
UNION 
SELECT "" AS sentDate, date, alert 
FROM alerts 
WHERE id = someid 
AND status = 'not'
) t

Answer 3

你可以试试这个

SELECT 
(Select date from alerts where id= someid AND status='not' 
ORDER by date DESC limit 1) latestNotSentDate, 
(Select date from alerts where id= someid AND status='sent' 
ORDER BY date DESC limit 1) latestSentDate;

Answer 4

查询中的列数必须相同，请尝试以下操作：

SELECT sentDate,'' FROM alerts WHERE id = 'id' AND status = 'sent' Order by date desc limit 1;
UNION
SELECT date, alert FROM alerts WHERE id = 'id' AND status = 'not' order by date asc limit 1;

此外，我认为状态是无关紧要的，这些信息是由sentDate的存在提供的。

我想你可以写：

SELECT sentDate,'' FROM alerts WHERE id = 'id' AND sentDate is not null Order by date desc limit 1;
UNION
SELECT date, alert FROM alerts WHERE id = 'id' AND sentDate is NULL order by date asc limit 1;