mysql-union第二个数据集不返回结果

hgqdbh6s 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(315)

所以，
我不知道发生了什么事。这两个查询都可以很好地单独运行。但由于某些原因，当我尝试将它们合并在一起时，我只获取顶部查询的数据。
我真的很想得到一些指导。第二个查询基本上返回第一个查询中缺少的数据的结果。我只是不知道发生了什么。
这可能是我搞不懂的一件简单的事情，但我试过很多不同的方法，但似乎没有什么办法。

(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列的数据。

sql mysql union mysql-workbench

来源：https://stackoverflow.com/questions/52656450/mysql-union-second-dataset-not-returning-results

2条答案

按热度按时间

to94eoyn1#

试试这个。我认为两个表中的列名应该相同。

(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 as 'Weekday Price',
   null as 'Weekend Price',
   null as 'Weekly Price',
   null 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)) 
) 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)

赞(0）回复(0）举报 2021-06-20

ni65a41a2#

所以，
在对这个逻辑反复思考了几个小时并做了一些尝试和错误之后，我意识到它无法合并，因为我生成了date子查询。因此，我重新定义了查询运行方式背后的逻辑。
下面是我的工作sql。谢谢大家的帮助。

SELECT 
b.nid as 'NID',
dg.date as 'Date',
if((bl.nid is not null) or isnull(b.weekday), 1,0) as 'Blocks',
b.weekday as 'Weekday',
b.weekend as 'Weekend',
b.weekly as 'Weekly',
b.nights as 'Nights'
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)) 
) dg
LEFT JOIN
(SELECT

   (pd.nid) as 'NID',
   pd.special_from as 'start',
   pd.special_to as 'end',
   pd.special_weekday as 'Weekday',
   pd.special_weekend as 'Weekend',
   pd.special_weekly as 'Weekly',
   pd.special_minstay as 'Nights'
from 

(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 ( (rep.nid = 0 OR isnull(rep.nid)))) pd 

UNION ALL

SELECT

    (tm.nid) as 'NID',
    tm.book_start as 'start',
    tm.book_end as 'end',
   null,
   null,
   null,
   null

FROM 
(SELECT 
nd2.nid as 'nid',
ifnull(t.book_start,t2.book_start) as `book_start`,
ifnull(t.book_end,t2.book_end) as `book_end`
FROM radb.property_state_machine nd2
LEFT JOIN radb.rep_property rp on nd2.nid=rp.parent
LEFT JOIN radb.rep_property rp1 on nd2.nid=rp1.nid
LEFT JOIN radb.admin_book_temp t on nd2.nid=t.nid 
LEFT JOIN radb.admin_book_temp t2 on rp1.parent=t2.nid where ((rp.nid = 0 OR isnull(rp.nid)))
 ) tm  

) b on ((b.end IS NULL OR date(b.end) >= dg.date)) AND (date(b.start) <= dg.date )
LEFT JOIN
radb.admin_book bl on ((date(bl.book_end) IS NULL OR date(bl.book_end) >= dg.date) and bl.nid=b.nid) AND (date(bl.book_start) <= dg.date AND bl.nid=b.nid)
LEFT JOIN
radb.property_state_machine psm on b.nid=psm.nid

where psm.state_name='Live'

GROUP BY 1,2
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

   (pd.nid) as 'NID',
   pd.special_from as 'start',
   pd.special_to as 'end',
   pd.special_weekday as 'Weekday',
   pd.special_weekend as 'Weekend',
   pd.special_weekly as 'Weekly',
   pd.special_minstay as 'Nights'
from 

(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 ( (rep.nid = 0 OR isnull(rep.nid)))) pd 

UNION ALL

SELECT

    (tm.nid) as 'NID',
    tm.book_start as 'start',
    tm.book_end as 'end',
   null,
   null,
   null,
   null

FROM 
(SELECT 
nd2.nid as 'nid',
ifnull(t.book_start,t2.book_start) as `book_start`,
ifnull(t.book_end,t2.book_end) as `book_end`
FROM radb.property_state_machine nd2
LEFT JOIN radb.rep_property rp on nd2.nid=rp.parent
LEFT JOIN radb.rep_property rp1 on nd2.nid=rp1.nid
LEFT JOIN radb.admin_book_temp t on nd2.nid=t.nid 
LEFT JOIN radb.admin_book_temp t2 on rp1.parent=t2.nid where ((rp.nid = 0 OR isnull(rp.nid)))
 ) tm  

) b on ((b.end IS NULL OR date(b.end) >= dg.date)) AND (date(b.start) <= dg.date )
LEFT JOIN
radb.admin_book bl on ((date(bl.book_end) IS NULL OR date(bl.book_end) >= dg.date) and bl.nid=b.nid) AND (date(bl.book_start) <= dg.date AND bl.nid=b.nid)
LEFT JOIN
radb.property_state_machine psm on b.nid=psm.nid

where psm.state_name='Live'

GROUP BY 1,2

赞(0）回复(0）举报 2021-06-20

我来回答

mysql-union第二个数据集不返回结果

2条答案

相关问题

热门标签

最新问答