同一个伙伴的两个记录在一起 [英] Two records of same Partner together

问题描述

大家好，


这是表和DML的声明

CREATE TABLE [jatpartnerMst]（

[rowid] [int]，

[partnerid] [int] NULL，

[mcstat] [int] DEFAULT（1）， - 1待定，2批准

[sf] [varchar]（20）

）


INSERT INTO [jatpartnerMst]

（[rowid] ]，[partnerid]，[mcstat]，[sf]）VALUES（1,1,2，''active''）

INSERT INTO [jatpartnerMst]

（ [rowid]，[partnerid]，[mcstat]，[sf]）VALUES（2,1,2，''active''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（3,1,2，''active''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（4,1,2，''active''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（5,1,1，''active''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（6,1,2，''inactive''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（7,1,2，''inactive''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（8,1,2，''inactive'' ）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（9,2,2，''有效''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（10,2,1，' '活跃''）

INSERT INTO [jatpartnerMst]

（[rowid]，[partnerid]，[mcstat]，[sf]）VALUES（11,1,2 ，''活跃''）


我希望找到的是最新的记录，它是其他

记录

例如如果partnerID 1被更改，则会转到表格的底部，

任何给定时间我只对max（rowid）感兴趣的合作伙伴

stat 1或2


我正在使用此查询

select * from jatpartnerMst where rowid in（select max（rowid）f rom

jatpartnermst其中mcstat in（1,2）group by partnerid，mcstat）

这个查询不给我最新的。


使用此查询

从jatpartnerMst中选择*其中rowid in（选择max（rowid）来自

jatpartnermst其中mcstat in（1,2）group by partnerid， mcstat）

由rowid desc订购


合作伙伴的两条记录分开。我想告诉他们

彼此。

所以输出应该是


11 1 2活跃

5 1 1活跃

9 2 2活跃

10 2 1活跃


11& 5个rowid是相互跟随的，因为它们是相同的

合作伙伴的行，11是最近的行[因为新的行插入了

结尾]


是否可以使用单个查询执行上述操作

我正在使用光标执行相同操作。


热烈问候

Jatinder

Hi all,

Here is the table and DML statments
CREATE TABLE [jatpartnerMst] (
[rowid] [int] ,
[partnerid] [int] NULL ,
[mcstat] [int] DEFAULT (1), -- 1 Pending ,2 Approved
[sf] [varchar] (20)
)

INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(1,1,2,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(2,1,2,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(3,1,2,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(4,1,2,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(5,1,1,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(6,1,2,''inactive'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(7,1,2,''inactive'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(8,1,2,''inactive'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(9,2,2,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(10,2,1,''active'')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],[sf])VALUES(11,1,2,''active'')

What I wish to find is the latest record on the top and it''s other
records
e.g If partnerID 1 is changed it goes to the bottom of the table , at
any given time I am interested only in max(rowid) for a partner with
stat 1 or 2

I am using this query
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
This query does not give me the latest.

On using this query
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
order by rowid desc

The partner''s two records get seperated . I wish to show them following
one another.
So the output should be

11 1 2 active
5 1 1 active
9 2 2 active
10 2 1 active

11 & 5 rowids are following each other because they are rows of same
partner and 11 is the most recent row [ because new rows are inserted
at the end]

Is it possible to do the above using single query
I am using cursor to do the same.

With Warm regards
Jatinder

推荐答案

您好

为此您可能需要看到

"扩展层次结构"在BOL


这里是一个例子


CREATE PROCEDURE扩展（@current char（20））as

SET NOCOUNT ON

DECLARE @level int，@ line char（20）

CREATE TABLE #stack（item char（20），level int）

INSERT INTO #stack VALUES（@current，1）

SELECT @level = 1


WHILE @level> 0

BEGIN

IF EXISTS（SELECT * FROM #stack WHERE level = @level）

BEGIN

SELECT @current = item

FROM #stack

WHERE level = @level

SELECT @line = space（@level - 1）+ @current

打印@line

从#stack删除

WHERE level = @level

AND item = @current

INSERT #stack

SELECT child，@ level + 1

FROM hierarchy

WHERE parent = @current

IF @@ ROWCOUNT> 0

SELECT @level = @level + 1

END

ELSE

SELECT @level = @level - 1

结束 - 什么


请告诉我它是否解决了这个问题..

最好的问候，
Chandra
http://groups.msn.com/SQLResource /
http://chanduas.blogspot.com/

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


***通过开发人员指南 http://www.developersdex.com发送 ***

Hi
For this you might require to see
"Expanding Hierarchies" in BOL

here is the example

CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE

Please let me know if it solves the purpose..
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

您好钱德拉，

感谢您的回复和时间。但我正在寻找一套基于套装

解决方案


热烈的问候

Jatinder

Hi Chandra,
Thanks for your reply and time . But I am looking for a "Set Based
Solution"

With warm regards
Jatinder

jsfromynr（ja************@clovertechnologies.com）写道：

jsfromynr (ja************@clovertechnologies.com) writes:

我希望找到的是顶部的最新记录和它的其他
记录
例如，如果partnerID 1被更改，它会转到表格底部，
任何给定时间我只对max感兴趣（ rowid）对于
stat 1或2的合作伙伴

What I wish to find is the latest record on the top and it''s other
records
e.g If partnerID 1 is changed it goes to the bottom of the table , at
any given time I am interested only in max(rowid) for a partner with
stat 1 or 2

最新？我没有看到任何日期时间列，那么你的定义是什么？

最新？

-

Erland Sommarskog，SQL Server MVP ， es****@sommarskog.se


SQL Server SP3联机丛书在
http://www.microsoft .com / sql / techinf ... 2000 / books.asp

这篇关于同一个伙伴的两个记录在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！