通过从两个表中选择类别从表中选择数据 [英] Select Data From a Table By Selecting Category From Two Tables

问题描述

大家好，



我有桌子喜欢



1）持有主数据的用户喜欢

Hello All,

I have Tables Like

1) User Which Holds The Master Data Like

User ID   Name    OtherColumns
1         Banshi   Nothing
2         Banshi2  Nothing 
3         Banshi3  Nothing 

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

2）第二个表朋友持有数据

--------------------------------------
2)Second Table "Friends" Holds Data Like

ID    UserID     FriendID    FriendCategory
1       1          2             2
2       2          1             2
3       4          1             2
4       1          4             2

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



3）第三表订阅者持有数据

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

3)Third Table "Subscribers" Holds Data Like

ID    SubscribersID  SubscribToID    FriendCategory
1       1              3                  3
2       1              4                  3

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

4）第四张表FriendCat 赞

---------------------------------------
4)Fourth Table "FriendCategory" Like

ID      Name
2       Friends
3       Subscribers

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



5）第五表帖子包含数据类似

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

5)Fifth Table "Posts" Contains Data Like

PostID   UserID  Post           
1         2      Hello Dear Post By Banshi2
2         3      Hello Dear  Post By Banshi3
3         4     Hello Dear  Post By Banshi4 

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

现在成为用户1我需要数据赞

A）我需要数据如果由我的朋友发布

B）我需要数据如果发布我已被订阅

c）如果用户是我的朋友订阅了那么它必须显示朋友没有我订阅了



我的数据类似于

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

---------------------------------------
Now Being User 1 i Need Data Like
A) I need Data if Posted By My Friends
B) I need Data if Posted By i am Subscribed to
c) If User is My Friend Subscribed as Well Then it Must Show Friend not i am Subscribed

My Data Something Like
----------------------------

UserID      Name        Post                          Category
2          Banshi2    Hello Dear Post By Banshi2     Friends
3          Banshi3    Hello Dear Post By Banshi3     I Am Subcriber 
4          Banshi4    Hello Dear Post By Banshi4     Friends

请尽情享受我如何在Sql Server 2008中获得这样的优势





提前付款

Banshi

Please Suugest Me How to Get Such a Out put in Sql Server 2008


Thanks in Advance
Banshi

推荐答案

您好，



请尝试这样：

Hi,

Try like this:

select m.ID, m.Name, p.Post, fc.Name from Master Data m
  inner join Friends f on m.ID = f.UserID
  inner join FriendCategory fc on fc.ID = f.FriendCategory

谢谢

Thanks

A）如果我朋友发布我需要数据

A) I need Data if Posted By My Friends

select f.userid,f.friendids,md.Name,post.Post,fc.Category
from post
left join friends f on friendids = post.userids
left join MasterData md on md.userid = f.friendids 
left join FriendCategory fc on f.FriendCategory = f.FriendCategory
where f.userid = 1

B）我需要数据如果我已经订阅了

B) I need Data if Posted By i am Subscribed to

select f.SubscribersID as userid,f.SubscribToID,md.Name,post.Post,fc.Category
from post
left join Subscribers f on SubscribToID  = post.userids
left join MasterData md on md.userid = f.SubscribToID    
left join FriendCategory fc on f.FriendCategory = f.FriendCategory
where f.SubscribersID  = 1

c）如果用户是我的朋友订阅了那么它必须显示朋友而不是我订阅

c) If User is My Friend Subscribed as Well Then it Must Show Friend not i am Subscribed

select userid,friendids,name,post,case when sum(flag)=3 then 'I m subscribed to' else 'Friend' end as Category
from
(
    select f.userid,f.friendids,md.Name,post.Post,fc.Category, 1 as flag
    from post
    left join friends f on friendids = post.userids
    left join MasterData md on md.userid = f.friendids
    left join FriendCategory fc on f.FriendCategory = f.FriendCategory
    where f.userid = 1
    
    select f.SubscribersID as userid,f.SubscribToID,md.Name,post.Post,fc.Category , 3 as flag
    from post
    left join Subscribers f on SubscribToID  = post.userids
    left join MasterData md on md.userid = f.SubscribToID
    left join FriendCategory fc on f.FriendCategory = f.FriendCategory
    where f.SubscribersID  = 1
)
as a
group by userid,friendids,name,post

Happy Coding！

：）

Happy Coding!
:)

这篇关于通过从两个表中选择类别从表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！