如何使用sql server从表格中查找数据？

问题描述：

嗨，我为数据创建一个存储过程，并发送推送notification.and我想这些数据marge并获得一个原始。但我怎么能这样做，我不知道。我需要帮助，请帮助我，让我知道该怎么做that.this是我在这里查询下面我有写：如何使用sql server从表格中查找数据？

这是查询=>

SELECT N.NotificationId, 
    N.UserId, 
    N.ActionUserId, 
    (CASE WHEN N.NotificationTypeId = 1 THEN 1 
       WHEN N.NotificationTypeId = 7 THEN 3 
      ELSE  
       2 
     END) AS TypeId,       
    AU.ProfileImage, 
    AU.UserName, 
    N.IsRead, 
    (CASE WHEN N.NotificationTypeId = 1 THEN 1 
      WHEN N.NotificationTypeId = 7 THEN 3 
     ELSE  
      2 
    END) AS TypeId,   
    N.NotificationTypeId, 
    N.InsertDateTime 
    FROM Notifications N 
    INNER JOIN Users U ON N.UserId = U.UserId 
    INNER JOIN Users AU ON N.ActionUserId = AU.UserId  
    ORDER BY N.InsertDateTime DESC

这是我目前的O/P =>

NotificationId | UserId | ActionUserId | UserName | NotificationTypeId | InsertDateTime   | ProfileImage 
    6    20  15    hbc  1     2017-06-22 17:14:16.803 20170416032403869.jpeg 
    5    20  16    tyu  1     2017-06-22 17:12:12.297  20170416031522534.jpeg 
    4    20  17    opl  1     2017-06-22 17:11:58.060  20170416031250102.jpeg 
    3    10  11    abc  1     2017-06-22 16:14:16.803 20170416032403867.jpeg 
    2    10  12    xyz  1     2017-06-22 16:14:12.297  20170416031522533.jpeg 
    1    10  13    rty  1     2017-06-22 16:13:58.060  20170416031250101.jpeg

这是我预期的O/p =>

NotificationId | UserId | ActionUserId | UserName | NotificationTypeId | InsertDateTime   | ProfileImage   | NotificationText 
    6    20  15    hbc  1     2017-06-22 17:14:16.803 20170416032403869.jpeg hbc,tyu and 1 other users followed you 
    3    10  11    abc  1     2017-06-22 16:14:16.803 20170416032403867.jpeg abc,xyz and 1 other users followed you

我想喜欢这种玛吉这个数据的任何一个知道如何能做到这一点认罪让我知道。

您的输出结果与您的查询不匹配... – iamdave

我知道NotificationText列未在查询中提及，但它是我的预期o/p – coderwill

Where Insert DateTime'来自例如？ – iamdave

答

你可以用派生表和一些窗口函数来做到这一点。我曾经也有点逻辑的补充，以确保Notification Text有正确的英文取决于这些用户的数字包括：

-- Create test data 
declare @Notifications table(NotificationID int, UserID int, ActionUserID int, NotificationTypeID int, InsertDateTime datetime); 
declare @Users table(UserID int, UserName nvarchar(10), ProfileImage nvarchar(50)) 
insert into @Notifications values (6,20,15,1,'2017-06-22 17:14:16.803'),(5,20,16,1,'2017-06-22 17:12:12.297'),(4,20,17,1,'2017-06-22 17:11:58.060'),(3,10,11,1,'2017-06-22 16:14:16.803'),(2,10,12,1,'2017-06-22 16:14:12.297'),(1,10,13,1,'2017-06-22 16:13:58.060'); 
insert into @Users values (15,'hbc','20170416032403869.jpeg'),(16,'tyu','20170416031522534.jpeg'),(17,'opl','20170416031250102.jpeg'),(10,'aaa',''),(11,'abc','20170416032403867.jpeg'),(12,'xyz','20170416031522533.jpeg'),(13,'rty','20170416031250101.jpeg'); 


-- Specify UserID 
declare @UserID int = 10; 

-- Create Notification 
with d as 
(
    select n.NotificationID 
      ,n.UserID 
      ,n.ActionUserID 
      ,au.UserName 
      ,n.NotificationTypeID 
      ,n.InsertDateTime 
      ,au.ProfileImage 
      ,row_number() over (partition by n.UserID order by n.InsertDateTime desc) as rn 
      ,count(*) over (partition by n.UserID) as c 
    from @Notifications n 
     join @Users au 
      on(n.ActionUserID = au.UserID) 
) 
select d.NotificationID 
     ,d.UserID 
     ,d.ActionUserID 
     ,d.UserName 
     ,d.NotificationTypeID 
     ,d.InsertDateTime 
     ,d.ProfileImage 
     ,d.UserName 
     + isnull(case when d2.c = 2 
         then ' and ' 
         else ', ' 
         end 
        + d2.UserName 
        ,'') 
     + case when d2.c > 2 
       then ' and ' + cast(d2.c-2 as nvarchar(10)) + ' other users' 
       else '' 
       end 
     + ' followed you' as NotificationText 
from d 
    left join d as d2 
     on(d.UserID = d2.UserID 
      and d2.rn = 2 
      ) 
where d.rn = 1;

输出：

+----------------+--------+--------------+----------+--------------------+-------------------------+------------------------+-----------------------------------------+ 
| NotificationID | UserID | ActionUserID | UserName | NotificationTypeID |  InsertDateTime  |  ProfileImage  |   NotificationText    | 
+----------------+--------+--------------+----------+--------------------+-------------------------+------------------------+-----------------------------------------+ 
|    3 |  10 |   11 | abc  |     1 | 2017-06-22 16:14:16.803 | 20170416032403867.jpeg | abc, xyz and 1 other users followed you | 
|    6 |  20 |   15 | hbc  |     1 | 2017-06-22 17:14:16.803 | 20170416032403869.jpeg | hbc, tyu and 1 other users followed you | 
+----------------+--------+--------------+----------+--------------------+-------------------------+------------------------+-----------------------------------------+

条件或与任何userid在哪里条件，那么它给更多的用户？ – coderwill

你好，你可以请给我暗示与条件我需要2用户为用户ID 10和用户ID 20所以请给我提示，并帮助我 – coderwill

@coderwill我已经更新了我的答案。请注意，堆栈溢出不是免费的代码写入服务，我们不为你工作。请不要继续追踪更新。如果我们要帮助你，我们将在我们自己的时间内这样做。 – iamdave

答

你可以试试这个：

SELECT Q.NotificationId, 
    Q.UserId, 
    Q.ActionUserId, 
    (CASE WHEN Q.NotificationTypeId = 1 THEN 1 
       WHEN Q.NotificationTypeId = 7 THEN 3 
      ELSE  
       2 
     END) AS TypeId,       
    Q.ProfileImage, 
    Q.UserName, 
    Q.IsRead, 
    (CASE WHEN Q.NotificationTypeId = 1 THEN 1 
      WHEN Q.NotificationTypeId = 7 THEN 3 
     ELSE  
      2 
    END) AS TypeId2,   
    Q.NotificationTypeId, 
    Q.InsertDateTime 
    FROM (SELECT N.UserId, N.ActionUserId, N.NotificationTypeId, 
     AU.ProfileImage, AU.UserName, N.IsRead, N.InsertDateTime,   N.NotificationID 
    FROM Notifications N 
     INNER JOIN Users U ON N.UserId = U.UserId 
     INNER JOIN Users AU ON N.ActionUserId = AU.UserId 
    WHERE N.UserId = @UserId ) Q         
    INNER JOIN (SELECT MAX(NotificationID) AS MaxNotifID, UserID FROM   
dbo.Notifications 
     WHERE UserID = @userID GROUP BY UserID) R ON 
      Q.NotificationID = R.MaxNotifID AND Q.UserID = R.USerID 
    ORDER BY Q.InsertDateTime DESC

如何使用sql server从表格中查找数据？

相关推荐