如何获得比较价值 [英] How to get comparing value

问题描述

TblChatMaster

 --------------------- 
 ChatId CreatedDate 
 
 4 15/05/2013 
 
 5 16/05/2013 

TblChatDesc

 ------------------- --- 
 id ChatId ReceiverId 
 
 1 4 2 
 
 2 4 3 
 
 3 5 2 

TblMessageMaster

  - -------------------------------- 
 
 id ChatId MsgText MessengerId 
 
 14'H嗨'1 
 
 2 5'Hello'1 

我有ReceiverId和MessengerId。



我们有，



MessengerId = 1

ReceiverId = 2,3



现在我想得到那个ChatId其中messengerId = 1且ReceiverId = 2& 3

通过以上结论，chatId应该是4



-----我正在按照以下方式行事，但它不起作用

 选择 cm.ChatId 来自 TblChatMaster cm 
  join  TblChatDesc cd  on  cm.ChatId = cd.ChatId 
  join  TblMessageMaster mm   cm.ChatId = mm.ChatId 其中​​ ReceiverId  （选择 * 来自 dbo.Split （'  2,3'））和 MessengerId = 1 

解决方案

确保

从dbo中选择* .Split（'2,3'）

返回正确的结果



或tr这个

 其中（ReceiverId =  2  或 ReceiverId =  3 ）和。 .. 

快乐编码！

：）

替换

其中ReceiverId in（select * from dbo.Split（''2,3''））

 其中ReceiverId in（2,3） 

   where  cm.ChatId  in （选择 ChatId 来自 tblChatDesc 其中​​ ReceiverId =  2 ）
 和 cm.ChatId  （选择 ChatId 来自 tblChatDesc 其中 ReceiverId =  3 ） 

[尝试3 !!]

我无法从SQL版本的存储过程返回临时表我我目前正在使用，所以您可能需要调整此代码，以使其适用于您的版本和si简化它

基本上，我在ChatId和ReceiverId上使用分组来获得格式为
的临时表格b $ b

 ChatId ReceiverId c 
 4 2 1 
 4 3 1 
 5 2 1 

然后我''正在使用外部选择，也有一个分组...但这次我只想要ChatId并且只有它在临时表中出现两次...

 选择 ChatId 来自 
（
 选择 cm.ChatId，ReceiverId，count（*） as  c 
 来自 #ChatMaster cm 
  join  #ChatDesc cd  on  cm.ChatId = cd。 ChatId 
  join  #MessageMaster mm  on  cd.ChatId = mm.ChatId 
 < span class =code-keyword>其中 MessengerI d = 1 
 和 cm.ChatId  in （从中选择 ChatId  #ChatDesc 其中 ReceiverId  in （ 2 ， 3 ））
 组 按 cm.ChatId，ReceiverId 
）tmp 
  group   by  ChatId 
   count（*）=  2    -   将此2替换为拆分函数返回的项目数 

TblChatMaster

---------------------
ChatId    CreatedDate

 4           15/05/2013   

 5           16/05/2013   

TblChatDesc

----------------------
id   ChatId   ReceiverId

 1     4           2

 2     4           3

 3     5           2

TblMessageMaster

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

 id   ChatId  MsgText    MessengerId

  1     4      'Hi'         1

  2     5      'Hello'      1

I have ReceiverId and MessengerId.

Let we have,

MessengerId = 1
ReceiverId = 2,3

Now I want to get that ChatId which have messengerId=1 and ReceiverId=2 & 3
By above conclusion chatId should be 4

-----I am doing following way but it is not work

select cm.ChatId from TblChatMaster cm
 join TblChatDesc cd on cm.ChatId=cd.ChatId
 join TblMessageMaster mm on cm.ChatId=mm.ChatId where ReceiverId in(select * from dbo.Split('2,3')) and MessengerId=1

解决方案

make sure

select * from dbo.Split('2,3')

returns proper result

or try this

where (ReceiverId  = 2 or ReceiverId = 3)  and ...

Happy Coding!
:)

Replace

where ReceiverId in(select * from dbo.Split(''2,3''))

with

where ReceiverId in (2,3)

where cm.ChatId in (Select ChatId from tblChatDesc where ReceiverId = 2)
and cm.ChatId in (Select ChatId from tblChatDesc where ReceiverId = 3)

[Attempt 3!!]
I can''t return temporary tables from a stored procedure in the version of SQL I''m currently using so you may need to tweak this code to make it work on your version and to simplify it
Basically, I''m using group by on both ChatId and ReceiverId to get a temporary table in the format

ChatId	ReceiverId	c
4	2			1
4	3			1
5	2			1

Then I''m using an outer select, also with a group by ... but this time I just want the ChatId and only if it appears in the temporary table exactly twice ...

select ChatId from
(
    select cm.ChatId, ReceiverId, count(*) as c
    from #ChatMaster cm
    join #ChatDesc cd on cm.ChatId=cd.ChatId
    join #MessageMaster mm on cd.ChatId=mm.ChatId
    where MessengerId=1
    and cm.ChatId in (Select ChatId from #ChatDesc where ReceiverId in (2,3))
    group by cm.ChatId, ReceiverId
) tmp
group by ChatId
having count(*) = 2 -- replace this 2 with the count of items returned by the split function

这篇关于如何获得比较价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！