Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Последние записи с группировкой как в сообщениях у смартфонов  [new]
RomanCv
Guest
Здравствуйте. Следующая задача: вывести список последних сообщений (диалогов):

см скрин

Делаю так, но получается вывести только последнее сообщение собеседника, если я отправлял ему сообщение, то собеседник не прыгает на верх, а нужно, чтобы перемещался и показывалось МОЕ сообщение и заголовок уже получателя.

SELECT messages.id, messages.message, messages.senderId, messages.recieverId, users.name
FROM messages INNER JOIN
(select top 1000 senderId, max(id) as mid from messages where (senderId = @userId or recieverId = @userId) group by senderId order by mid desc ) as Tb
on Tb.mid = messages.id
inner join
users on users.id = messages.senderId

where
messages.recieverId = @userId
order by messages.id desc
5 мар 13, 05:00    [14011650]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
Добрый Э - Эх
Guest
стало быть сортировать нужно не просто по ID, а по бОльшему из двух ID (входящее - исходящее).
5 мар 13, 06:09    [14011669]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
В подзапросе слить (CASE-ом) "от кого" и "кому", отобрать последние десять... Ну а к ним прикпутить всё остальное.. Чо, нет?
5 мар 13, 09:16    [14012029]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
и делать не INNER JOIN, а UNION... Ээээ... Ну, типа так:
select top(10)
   u.id, u.message, isnull(u.type_flag*u.recieverId, u.senderId)sender_receiver_id, users.name
from(
select 1 type_flag,* from messages where messages.recieverId = @userId
union all
select null type_flag,* from messages where messages.senderId = @userId
)u
order by u.id desc 
5 мар 13, 10:28    [14012470]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
RomanCv
Guest
Вот так решил это:
SELECT     messages.id, messages.message, sender_receiver_id, users.name + ' ' + users.fam + ' ' + users.otch as userFullName
FROM            messages INNER JOIN
                         (
						 select top (922337203685477580) 1 type_flag, senderId as sender_receiver_id, max(id) as maxid from messages where messages.recieverId = @userid group by  senderId
union all
select top (922337203685477580) null type_flag, recieverId as sender_receiver_id, max(id) as maxid from messages where messages.senderId = @userid group by  recieverId
order by maxid desc
) as Tb
						  on Tb.maxid = messages.id 
						left outer join users
						on sender_receiver_id = users.id
						
						
					 where 
					 messages.senderId = @userId or
					  messages.recieverId = @userId
						  order by messages.id desc
5 мар 13, 20:06    [14016534]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
RomanCv
Guest
Извиняюсь, в таком случае диалоги дублируются :( Вопрос актуален.
5 мар 13, 23:58    [14017092]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
а может все проще?
SELECT     
   messages.id, messages.message, isnull(nullif(senderId,@userid),receiverId)sender_receiver_id, 
   users.name + ' ' + users.fam + ' ' + users.otch as userFullName
FROM messages 
left join users on users.id=isnull(nullif(senderId,@userid),receiverId)
where @userid in (senderId,receiverId)
order by messages.id desc
6 мар 13, 00:45    [14017180]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
RomanCv
Guest
Cygapb-007, это выводит список последних сообщений, без группировки по диалогам.
6 мар 13, 10:55    [14018244]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
RomanCv,

Дайте уже, что ли, тестовые данные. Рекомендации по оформлению сообщений в форуме, п.6.
6 мар 13, 11:28    [14018405]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
vadim-tmn
Member

Откуда:
Сообщений: 52
как я понял)), type - нужен, если нужен, чисто для определения потом отправлял или принимал сообщение
select
max(maxid), man , type
from
(
select MAX(id) as maxid, recieverId as man, 'r' as type from
from messages
where messages.senderId = @userid
group by recieverId
union all
select MAX(id) as maxid, senderId as man , 's' as type from
from messages
where messages.recieverId = @userid
group by senderId
)as tt
group by man , type
6 мар 13, 11:46    [14018497]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
vadim-tmn
Member

Откуда:
Сообщений: 52
ой ступил))
6 мар 13, 11:47    [14018508]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
vadim-tmn
Member

Откуда:
Сообщений: 52
vadim-tmn
ой ступил))

хотя....)) надо проверять на данных)
6 мар 13, 11:49    [14018513]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
RomanCv
Guest
vadim-tmn, то же самое
Ок, давайте тестовые данные попробуем
1) таблица сообщений:
CREATE TABLE [dbo].[messages](
[id] [int] IDENTITY(1,1) NOT NULL,
[message] [text] NOT NULL,
[senderId] [int] NOT NULL,
[recieverId] [int] NOT NULL
)

2) Таблица юзеров
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](150) NOT NULL)

3) Заполняем юзеров:
INSERT INTO USERS (name) VALUES ('ROMA')
INSERT INTO USERS (name) VALUES ('DZIGURDA')
INSERT INTO USERS (name) VALUES ('ALFRED')

4) Шлем сообщения:
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HELLO DZIGURDA FROM ROMAN', 1, 2)
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HELLO ROMAN FROM DZIGURDA', 2, 1)
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HOW ARE YOU DZIGURDA?', 1, 2)

INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HELLO DZIGURDA FROM ALFRED', 3, 2)
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HELLO ALFRED FROM DZIGURDA', 2, 3)
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HOW ARE YOU DZIGURDA?', 3, 2)

INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HELLO ROMA FROM ALFRED', 3, 1)
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HELLO ALFRED FROM ROMA', 1, 3)
INSERT INTO MESSAGE (message, senderId, recieverId) VALUES ('HOW ARE YOU ROMA?', 3, 1)
6 мар 13, 15:42    [14020329]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
RomanCv,

Немного дополнил тестовые данные двумя диалогами из одного сообщения:
+
-- 1) таблица сообщений:
CREATE TABLE [dbo].[messages](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [message] [text] NOT NULL,
 [senderId] [int] NOT NULL,
 [recieverId] [int] NOT NULL 
) 

-- 2) Таблица юзеров
CREATE TABLE [dbo].[users](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](150) NOT NULL)

-- 3) Заполняем юзеров:
INSERT INTO USERS (name) VALUES ('ROMA')
INSERT INTO USERS (name) VALUES ('DZIGURDA')
INSERT INTO USERS (name) VALUES ('ALFRED')
INSERT INTO USERS (name) VALUES ('PETER')
INSERT INTO USERS (name) VALUES ('SHLEMENZON')

-- 4) Шлем сообщения:
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO DZIGURDA FROM ROMAN', 1, 2)
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO ROMAN FROM DZIGURDA', 2, 1)
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HOW ARE YOU DZIGURDA?', 1, 2)

INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO DZIGURDA FROM ALFRED', 3, 2)
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO ALFRED FROM DZIGURDA', 2, 3)
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HOW ARE YOU DZIGURDA?', 3, 2)

INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO ROMA FROM ALFRED', 3, 1)
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO ALFRED FROM ROMA', 1, 3)
INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HOW ARE YOU ROMA?', 3, 1) 

INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO ROMA FROM PETER', 4, 1) 

INSERT INTO MESSAGEs (message, senderId, recieverId) VALUES ('HELLO SHLEMENZON FROM ROMA', 1, 5) 
Пишем запрос (да простят меня владельцы case-sensitive БД):
declare @userid int;

set @userid = 1; -- ROMA

select m.id, m.message, m.senderId, m.recieverId, u.name
from messages m
     inner join users u on u.id = isnull(nullif(senderId,@userid),recieverId)
where m.id in (  select max(m1.id)
                 from messages m1
                 where m1.senderId = @userId or m1.recieverId = @userId
                 group by case when m1.senderId < m1.recieverId then m1.senderId else m1.recieverId end,
                          case when m1.senderId < m1.recieverId then m1.recieverId else m1.senderId end
              )
order by m.id desc;

set @userid = 2; -- DZIGURDA

select m.id, m.message, m.senderId, m.recieverId, u.name
from messages m
     inner join users u on u.id = isnull(nullif(senderId,@userid),recieverId)
where m.id in (  select max(m1.id)
                 from messages m1
                 where m1.senderId = @userId or m1.recieverId = @userId
                 group by case when m1.senderId < m1.recieverId then m1.senderId else m1.recieverId end,
                          case when m1.senderId < m1.recieverId then m1.recieverId else m1.senderId end
              )
order by m.id desc;
6 мар 13, 16:16    [14020593]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
RomanCv
Guest
Гость333,

Спасибо большое! Работает :)
6 мар 13, 16:47    [14020834]     Ответить | Цитировать Сообщить модератору
 Re: Последние записи с группировкой как в сообщениях у смартфонов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
как вариант:)
declare @userid int=1

SELECT     
   m.id, m.message, ABS(SIGN(m.senderId-@userid))income, id_sr, u.name
FROM messages m
cross apply (select isnull(nullif(m.senderId,@userid),m.recieverId) id_sr)c
left join users u on u.id=id_sr
where @userid in (m.senderId,m.recieverId)
and not exists(
   select NULL
   from messages p 
   where @userid in (p.senderId,p.recieverId) 
      and isnull(nullif(p.senderId,@userid),p.recieverId)=id_sr 
      and p.id>m.id
   )
order by m.id desc
6 мар 13, 22:55    [14022324]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить