所以,
我不知道发生了什么事。这两个查询都可以很好地单独运行。但由于某些原因,当我尝试将它们合并在一起时,我只获取顶部查询的数据。
我真的很想得到一些指导。第二个查询基本上返回第一个查询中缺少的数据的结果。我只是不知道发生了什么。
这可能是我搞不懂的一件简单的事情,但我试过很多不同的方法,但似乎没有什么办法。
(SELECT
date(dg.date) as 'Date',
(pd.nid) as 'NID',
if((bl.nid ) is not null,1,0) as 'Blocks',
pd.special_weekday as 'Weekday Price',
pd.special_weekend as 'Weekend Price',
pd.special_weekly as 'Weekly Price',
pd.special_minstay as 'Minimum Nights'
from (
select DATE_ADD(curdate(), INTERVAL (@i:=@i+1)-1 DAY) as `Date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where (DATE_ADD(curdate(),INTERVAL @i DAY) BETWEEN curdate() AND date_add(curdate(), INTERVAL 1 DAY))
) dg
LEFT JOIN
(SELECT
n.nid as `nid`,
ifnull(p.special_from,p2.special_from) as `special_from`,
ifnull(p.special_to,p2.special_to) as 'special_to',
ifnull(p.special_weekdayprice,p2.special_weekdayprice) as `special_weekday`,
ifnull(p.special_weekendprice,p2.special_weekendprice) as `special_weekend`,
ifnull(p.special_weeklyprice,p2.special_weeklyprice) as `special_weekly`,
ifnull(p.special_minstay,p2.special_minstay) as `special_minstay`,
n.state_name as `state_name`
FROM
radb.property_state_machine n
LEFT JOIN radb.rep_property rep on n.nid=rep.parent
LEFT JOIN radb.rep_property rep1 on n.nid=rep1.nid
LEFT JOIN radb.price_dates p on n.nid=p.nid
LEFT JOIN radb.price_dates p2 on rep1.parent=p2.nid
WHERE n.state_name='Live' AND ( (rep.nid = 0 OR isnull(rep.nid)))) pd on ((pd.special_to IS NULL OR date(pd.special_to) >= dg.date)) AND (date(pd.special_from) <= dg.date )
LEFT JOIN
radb.node n on pd.nid=n.nid
LEFT JOIN
radb.profile_value pv on n.uid=pv.uid
LEFT JOIN
radb.admin_book bl on ((date(bl.book_end) IS NULL OR date(bl.book_end) >= dg.date) and pd.nid=bl.nid) AND (date(bl.book_start) <= dg.date AND pd.nid=bl.nid)
GROUP BY 1,2)
UNION ALL
(SELECT
date(dt.date) as 'Date',
(tm.nid) as 'NID',
if((tm.nid) is not null,1,0) as 'Blocks',
null,
null,
null,
null
FROM (
select DATE_ADD(curdate(), INTERVAL (@i:=@i+1)-1 DAY) as `Date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where (DATE_ADD(curdate(),INTERVAL @i DAY) BETWEEN curdate() AND date_add(curdate(), INTERVAL 1 DAY))
) dt
LEFT JOIN
(SELECT
n1.nid as `nid`,
ifnull(m.book_start,m2.book_start) as `book_start`,
ifnull(m.book_end,m2.book_end) as `book_end`,
n1.state_name as `state_name`
FROM
radb.property_state_machine n1
LEFT JOIN radb.rep_property rp on n1.nid=rp.parent
LEFT JOIN radb.rep_property rp1 on n1.nid=rp1.nid
LEFT JOIN radb.admin_book_temp m on n1.nid=m.nid
LEFT JOIN radb.admin_book_temp m2 on rp1.parent=m2.nid
WHERE n1.state_name='Live' AND ( (rp.nid = 0 OR isnull(rp.nid)))) tm on ((date(tm.book_end) IS NULL OR date(tm.book_end) >= dt.date) ) AND (date(tm.book_start) <= dt.date )
LEFT JOIN
radb.node n2 on tm.nid=n2.nid
LEFT JOIN
radb.profile_value pv on n2.uid=pv.uid
GROUP BY 1,2)
版本2-仍然是相同的问题,即使我 Package 在一个子选择工会
SELECT
x.date as 'Date',
x.NID as 'NID',
x.blocks as 'Blocks',
x.weekday as 'Weekday',
x.weekend as 'Weekend',
x.weekly as 'Weekly',
x.min as 'Nights'
FROM
(SELECT date(dg.date) as 'Date',
(pd.nid) as 'NID',
if((bl.nid ) is not null,1,0) as 'Blocks',
pd.special_weekday as 'Weekday',
pd.special_weekend as 'Weekend',
pd.special_weekly as 'Weekly',
pd.special_minstay as 'min'
from (
select DATE_ADD(curdate(), INTERVAL (@i:=@i+1)-1 DAY) as `Date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where (DATE_ADD(curdate(),INTERVAL @i DAY) BETWEEN curdate() AND date_add(curdate(), INTERVAL 1 DAY))
) dg
LEFT JOIN
(SELECT
n.nid as `nid`,
ifnull(p.special_from,p2.special_from) as `special_from`,
ifnull(p.special_to,p2.special_to) as 'special_to',
ifnull(p.special_weekdayprice,p2.special_weekdayprice) as `special_weekday`,
ifnull(p.special_weekendprice,p2.special_weekendprice) as `special_weekend`,
ifnull(p.special_weeklyprice,p2.special_weeklyprice) as `special_weekly`,
ifnull(p.special_minstay,p2.special_minstay) as `special_minstay`,
n.state_name as `state_name`
FROM
radb.property_state_machine n
LEFT JOIN radb.rep_property rep on n.nid=rep.parent
LEFT JOIN radb.rep_property rep1 on n.nid=rep1.nid
LEFT JOIN radb.price_dates p on n.nid=p.nid
LEFT JOIN radb.price_dates p2 on rep1.parent=p2.nid
WHERE n.state_name='Live' AND ( (rep.nid = 0 OR isnull(rep.nid)))) pd on ((pd.special_to IS NULL OR date(pd.special_to) >= dg.date)) AND (date(pd.special_from) <= dg.date )
LEFT JOIN
radb.admin_book bl on ((date(bl.book_end) IS NULL OR date(bl.book_end) >= dg.date) and pd.nid=bl.nid) AND (date(bl.book_start) <= dg.date AND pd.nid=bl.nid)
UNION ALL
SELECT
date(dt.date) as 'Date',
(tm.nid) as 'NID',
if((tm.nid) is not null,1,0) as 'Blocks',
null as'Weekday',
null as 'Weekend',
null as 'Weekly',
null as 'Nights'
FROM (
select DATE_ADD(curdate(), INTERVAL (@i:=@i+1)-1 DAY) as `Date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where (DATE_ADD(curdate(),INTERVAL @i DAY) BETWEEN curdate() AND date_add(curdate(), INTERVAL 1 DAY))
) dt
LEFT JOIN
(SELECT
n.nid as `nid`,
ifnull(p.book_start,p2.book_start) as `book_start`,
ifnull(p.book_end,p2.book_end) as `book_end`,
n.state_name as `state_name`
FROM
radb.property_state_machine n
LEFT JOIN radb.rep_property rep on n.nid=rep.parent
LEFT JOIN radb.rep_property rep1 on n.nid=rep1.nid
LEFT JOIN radb.admin_book_temp p on n.nid=p.nid
LEFT JOIN radb.admin_book_temp p2 on rep1.parent=p2.nid
WHERE n.state_name='Live' AND ( (rep.nid = 0 OR isnull(rep.nid)))) tm on ((date(tm.book_end) IS NULL OR date(tm.book_end) >= dt.date) ) AND (date(tm.book_start) <= dt.date )
) x
LEFT JOIN
radb.node n on x.nid=n.nid
LEFT JOIN
radb.profile_value pv on n.uid=pv.uid
GROUP BY x.date,x.nid,x.blocks,x.weekday,x.weekend,x.weekly,x.min
示例结果
DATA SET #1
# Date, NID, Blocks, Weekday Price, Weekend Price, Weekly Price, Minimum Nights
'2018-10-05', '190668', '1', '165', '165', '165', '3'
'2018-10-05', '190669', '1', '235', '235', '231', '2'
'2018-10-05', '190671', '0', '176', '133', '142', '2'
'2018-10-05', '190672', '1', '661', '661', '658', '4'
'2018-10-05', '190673', '1', '203', '203', '138', '2'
'2018-10-05', '190675', '1', '219', '219', '158', '4'
'2018-10-05', '190676', '1', '204', '204', '197', '3'
'2018-10-05', '190677', '1', '235', '235', '217', '3'
DATA SET #2
# Date, NID, Blocks, Weekday Price, Weekend Price, Weekly Price, Minimum Nights
'2018-10-05', '190670', '1', NULL, NULL, NULL, NULL
'2018-10-06', '190670', '1', NULL, NULL, NULL, NULL
'2018-10-05', '190680', '1', NULL, NULL, NULL, NULL
'2018-10-05', '190885', '1', NULL, NULL, NULL, NULL
'2018-10-06', '190885', '1', NULL, NULL, NULL, NULL
'2018-10-05', '190933', '1', NULL, NULL, NULL, NULL
'2018-10-05', '191063', '1', NULL, NULL, NULL, NULL
'2018-10-06', '191063', '1', NULL, NULL, NULL, NULL
基本上,这两个数据集应该被追加,因为第二个数据集填补了数据集1缺失的空白。第二个数据集没有最后4列的数据。
2条答案
按热度按时间u3r8eeie1#
所以,
在对这个逻辑反复思考了几个小时并做了一些尝试和错误之后,我意识到它无法合并,因为我生成了date子查询。因此,我重新定义了查询运行方式背后的逻辑。
下面是我的工作sql。谢谢大家的帮助。
0sgqnhkj2#
试试这个。我认为两个表中的列名应该相同。