我有一个数据库,其表格的内容如下:
message_number message_type message_chat
0 IN Hi
1 OB Hello
2 IN Help
3 IN Want to find this thing
4 OB Sure
5 OB Please let me know
我已经写了5行,因为我想在显示的示例表中将我想要的所有可能的情况都包含在我的查询中。
现在在我的查询输出中,我想要类似的东西:
message_in message_out
Hi Hello
Help NULL
Want to find this string Sure
NULL Please let me know
所以我要考虑的情况是:
假设Message_Number = 0和Message_number = 1两个都具有messages_type值,如在然后将message_chat_in as Message_Chat(at Message_number = 0)放置为null和message_chat null and the Message_number = 1
如果message_number = 0有messages_type = in and message_number = 1 headmess_type = ob,然后show message_chat(在message_number = 0)为message_chat_in and message_chat(message_number = 1),则不eats_number = 1)1;
希望我已经澄清了这种情况,尽管我在预期的输出中包括了所有三个条件。我的sqlquery应该看起来像?
编辑:我正在使用MySQL版本5.5.8
尝试以下查询
SELECT
q1.message_number in_num,
q1.message_chat in_chat,
q2.message_number out_num,
q2.message_chat out_chat
FROM
(
SELECT *,@i1:=IFNULL(@i1,0)+1 num
FROM Chat
ORDER BY message_number
) q1
LEFT JOIN
(
SELECT *,@i2:=IFNULL(@i2,0)+1 num
FROM Chat
ORDER BY message_number
) q2
ON q2.num=q1.num+1 AND q2.message_type<>q1.message_type
WHERE q1.message_type='IN'
UNION ALL
SELECT
q1.message_number in_num,
q1.message_chat in_chat,
q2.message_number out_num,
q2.message_chat out_chat
FROM
(
SELECT *,@i3:=IFNULL(@i3,0)+1 num
FROM Chat
ORDER BY message_number
) q1
RIGHT JOIN
(
SELECT *,@i4:=IFNULL(@i4,0)+1 num
FROM Chat
ORDER BY message_number
) q2
ON q2.num=q1.num+1 AND q2.message_type<>q1.message_type
WHERE q2.message_type='OB'
AND q1.message_type IS NULL
ORDER BY IFNULL(in_num,out_num)
SQL小提琴-http://sqlfiddle.com/#!9/95A515/1
第二个变体
SET @i1 = 0;
SET @i2 = 0;
SET @i3 = 0;
SET @i4 = 0;
-- the same query
sql小提琴-http://sqlfiddle.com/#!9/95A515/2
或
SELECT 0,0,0,0 INTO @i1,@i2,@i3,@i4;
-- the same query
sql小提琴-http://sqlfiddle.com/#!9/95A515/5
为什么不在此处使用分析函数?我会用铅()这样做:
with inc as (
--Do the incorporation in this block. could be subquery too
--but its easier to read this way.
select
case when message_type = 'IN'
then message_chat
end as message_in
,case when LEAD(message_type) OVER (Order by message_number) = 'OB' --get the next message by number if it is type OB
then LEAD(message_chat) OVER (order by message_number)
end as message_out
from input
)
select *
from inc
where coalesce(message_in, message_out) is not null --filter out rows where with in & out is null
好吧,由于MySQL中没有分析功能小于8,因此代码可能不容易遵循:
with data_rn as
(
-- this isolate consecutive rows with the same message_type
select d1.*, count(d2.message_number) rn
from data d1
left join data d2 on d1.message_number > d2.message_number and d1.message_type != d2.message_type
group by d1.message_number
),
data_rn2 as
(
-- this marks the rows where new rows has to be added (i.e. when rn2 != 0)
select d1.*, count(d2.message_number) rn2
from data_rn d1
left join data_rn d2 on d1.rn = d2.rn and d1.message_type = d2.message_type and d1.message_number > d2.message_number
group by d1.message_number
),
data_added as
(
-- this add new rows
select message_number, message_type, message_chat
from data_rn2
union all
select message_number - 0.5, 'OB', NULL from data_rn2 where message_type = 'IN' and rn2 != 0
union all
select message_number - 0.5, 'IN', NULL from data_rn2 where message_type = 'OB' and rn2 != 0
order by message_number
), data_added_rn as
(
-- this compute new row numbering
select d1.*, ceil((count(d2.message_number)+1)/2) rn
from data_added d1
left join data_added d2 on d1.message_number > d2.message_number
group by d1.message_number
)
-- this will do the final formating
select max(case when message_type = 'IN' then message_chat end) message_in,
max(case when message_type = 'OB' then message_chat end) message_out
from data_added_rn
group by rn
演示
我试图适当评论每个部分。