求SQL語句

2009-03-26 03:43:52 107 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

基于SQL求集合的交、并、补对数据库查询结果集求补（差）、交、并集，可利用SQL的EXCEPT（补集）、INTERSECT（交集）和UNION（并集）关键词，其中Mysql仅支持UNION，但可以利用SQL实现EXCEPT、INTERSECT的功能。阅读详情

person ttime Iotype
001 8:00 1
001 12:00 2
001 13:00 1
001 17:00 2
002 8:00 1
002 12:00 2

转换成表
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
如何實現

...全文

融合ZILN损失与深度学习的LTV预测：从概率分布到精准营销决策本文探讨了融合ZILN损失与深度学习的LTV预测方法，通过深度概率模型精准预测用户生命周期价值（LTV），优化营销决策。文章详细解析了ZILN损失函数的优势、模型架构设计要点及工程化落地实践，帮助企业在预算分配、用户分层等场景实现数据驱动的精准营销。阅读详情

builderwfy

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

dawugui 2009-03-26

打赏
举报

create table tb(person varchar(10) , ttime varchar(10) , Iotype int)

insert into tb values('001', '08:00', 1 )

insert into tb values('001', '12:00', 2 )

insert into tb values('001', '13:00', 1 )

insert into tb values('001', '17:00', 2 )

insert into tb values('002', '08:00', 1 )

insert into tb values('002', '12:00', 2 )

go



select person , 

  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],

  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]

from

(

  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t

) m

group by person , (px-1)/2

order by person  , [in]



/*

person     in         out        

---------- ---------- ---------- 

001        08:00      12:00

001        13:00      17:00

002        08:00      12:00



（所影响的行数为 3 行）



*/







select Wpid = (select count(1) from 

(

select person , 

  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],

  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]

from

(

  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t

) m

group by person , (px-1)/2

) t1 where t1.person < t2.person or (t1.person = t2.person and t1.[in] < t2.[in])

) + 1 , t2.* from

(

select person , 

  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],

  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]

from

(

  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t

) m

group by person , (px-1)/2

) t2

order by wpid

/*

Wpid        person     in         out        

----------- ---------- ---------- ---------- 

1           001        08:00      12:00

2           001        13:00      17:00

3           002        08:00      12:00



（所影响的行数为 3 行）

*/



drop table tb

dawugui 2009-03-26

打赏
举报

create table tb(person varchar(10) , ttime varchar(10) , Iotype int)

insert into tb values('001', '08:00', 1 )

insert into tb values('001', '12:00', 2 )

insert into tb values('001', '13:00', 1 )

insert into tb values('001', '17:00', 2 )

insert into tb values('002', '08:00', 1 )

insert into tb values('002', '12:00', 2 )

go



select person , 

  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],

  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]

from

(

  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t

) m

group by person , (px-1)/2

order by person  , [in]



drop table tb



/*

person     in         out        

---------- ---------- ---------- 

001        08:00      12:00

001        13:00      17:00

002        08:00      12:00



（所影响的行数为 3 行）



*/

求SQL語句

相关推荐