MySql完全联接(联合)和对多个日期列的排序 [英] MySql full join (union) and ordering on multiple date columns
问题描述
一个相当复杂的sql查询,可能会使我变得更加困难: 我有两个桌子:
A rather complicated sql query that I might be making much more difficult that it should be: I have two tables:
新闻: 新闻ID,日期时间,新闻文本
News: newsid, datetime, newstext
图片: pictureid,日期时间,imgPath
Picture: pictureid, datetime, imgPath
两者没有关系,我只是在新闻/图片创建的日期之前加入
The two are not related, I am only joining by the date that the news/picture was created on
SQL:
SELECT * FROM news as n LEFT OUTER JOIN (SELECT count(pictureid), datetime
FROM picture GROUP BY DATE(datetime)) as p ON DATE(n.datetime) = DATE(p.datetime)
UNION
SELECT * FROM news as n RIGHT OUTER JOIN (SELECT count(pictureid),
datetime FROM picture GROUP BY DATE(datetime)) as p ON
DATE(n.datetime) = DATE(p.datetime)
我必须使用union来模拟MySQL中的完整外部联接. 结果:
I have to use union to simulate a full outer join in MySQL. The results:
newsid text datetime count() datetime
1 sometext 2011-01-16 1 2011-01-16
2 moo2 2011-01-19 NULL NULL
3 mooo3 2011-01-19 NULL NULL
NULL NULL NULL 4 2011-01-14
问题是,我显然以两个日期列结尾-一个来自新闻,一个来自图片,这意味着我无法按日期排序,并按正确的顺序排序!有任何想法吗?即使这意味着要重组数据库!我需要将日期放在一栏中.
The problem being that I obviously end up with two date columns- one from news and one from pictures, this means i cannot order by date and have it be in the correct order! Any ideas? Even if it means restructuring the database! I need date to be in a single column.
答案来自SeRPRo 完整的工作代码为:
The answer came from SeRPRo The completed working code is:
SELECT `newsid`, `text`,
CASE
WHEN `datetime` IS NULL
THEN `pdate`
ELSE `datetime`
END
as `datetime`,
`pcount` FROM
(
(SELECT * FROM news as n LEFT OUTER JOIN
(SELECT count(pictureid) as pcount, datetime as pdate FROM picture GROUP BY DATE(datetime)) as p
ON DATE(n.datetime) = DATE(p.pdate) ORDER BY datetime
)
UNION
(SELECT * FROM news as n RIGHT OUTER JOIN
(SELECT count(pictureid) as pcount, datetime as pdate FROM picture GROUP BY DATE(datetime)) as p
ON DATE(n.datetime) = DATE(p.pdate) ORDER BY datetime
)
) as x
ORDER BY datetime
推荐答案
仅使用您的数据库结构和查询,并且由于FULL OUTER JOIN在MySQL中不可用,所以我认为一个解决方案可能是:
just using your database structure and your query, and since FULL OUTER JOIN is not available in MySQL, I think a solution could be this:
SELECT
`newsid`,
`text`,
CASE
WHEN `datetime` IS NULL THEN `pdate`
ELSE `datetime`
END as `datetime,
`pcount`
(
SELECT *
FROM `news` as `n`
LEFT OUTER JOIN (
SELECT count(pictureid) as `pcount`, datetime as `pdate`
FROM picture GROUP BY DATE(datetime)
) as p ON DATE(n.datetime) = DATE(p.datetime)
UNION
SELECT *
FROM `news` as `n`
RIGHT OUTER JOIN (
SELECT count(pictureid) as `pcount`, datetime as `pdate`
FROM picture GROUP BY DATE(datetime)
) as p ON DATE(n.datetime) = DATE(p.datetime)
)
这篇关于MySql完全联接(联合)和对多个日期列的排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!