树型 SQL 查询

[wuxiaoqqqq]

现有一个历史表如下

InvoiceID InvoiceHistoryID ItemID OriginalItemID Description Quantity UnitPrice
1 1 1 0 Test1 10 10
1 1 2 0 Test2 20 20
1 1 3 0 Test3 30 30
1 2 4 1 Test1 5 10
1 2 5 2 Test2 20 20
1 2 6 3 Test3 30 30
1 3 7 4 Test1 5 5
1 3 8 5 Test2 20 20
1 3 9 6 Test3 25 30

我要求这里面某次历史记录前这个Item的Description,Quantity,UnitPrice到这次都没有变化的。
比如求InvoiceHistoryID = 2 得到的结果是
Test2
Test3
求InvoiceHistoryID = 3 得到的结果是
Test2


谢谢.

[wuxiaoqqqq]

我晕，空格怎么都没了，不过应该看得出来意思吧，大家帮忙看看啊。

[等不到来世]

if object_id('[pb]') is not null drop table [pb]
go
create table [pb]([InvoiceID] int,[InvoiceHistoryID] int,[ItemID] int,[OriginalItemID] int,[Description] varchar(5),[Quantity] int,[UnitPrice] int)
insert [pb]
select 1,1,1,0,'Test1',10,10 union all
select 1,1,2,0,'Test2',20,20 union all
select 1,1,3,0,'Test3',30,30 union all
select 1,2,4,1,'Test1',5,10 union all
select 1,2,5,2,'Test2',20,20 union all
select 1,2,6,3,'Test3',30,30 union all
select 1,3,7,4,'Test1',5,5 union all
select 1,3,8,5,'Test2',20,20 union all
select 1,3,9,6,'Test3',25,30
go
select * from [pb]

declare @HID int
set @HID=2
--set @HID=3
select Description from pb t
where InvoiceHistoryID=@HID
and not exists (select 1 from pb where InvoiceID=t.InvoiceID and Description=t.Description and InvoiceHistoryID<@HID and (Quantity<>t.Quantity or UnitPrice<>t.UnitPrice))
--@HID=2
/*
Description
-----------
Test2
Test3

(2 行受影响)
*/

--@HID=3
/*
Description
-----------
Test2

(1 行受影响)
*/

[子陌红尘]

declare @t table(InvoiceID int,InvoiceHistoryID int,ItemID int,OriginalItemID int,Description varchar(10),Quantity int,UnitPrice int)
insert into @t select 1,1,1,0,'Test1',10, 10 
insert into @t select 1,1,2,0,'Test2',20, 20 
insert into @t select 1,1,3,0,'Test3',30, 30 
insert into @t select 1,2,4,1,'Test1',5 ,10 
insert into @t select 1,2,5,2,'Test2',20, 20 
insert into @t select 1,2,6,3,'Test3',30, 30 
insert into @t select 1,3,7,4,'Test1',5 ,  5 
insert into @t select 1,3,8,5,'Test2',20, 20 
insert into @t select 1,3,9,6,'Test3',25, 30 

declare @InvoiceHistoryID int
set @InvoiceHistoryID=2

select  
    a.Description
from
    @t a,@t b 
where 
    a.OriginalItemID=b.ItemID
    and
    a.Description=b.Description and a.Quantity=b.Quantity and a.UnitPrice=b.UnitPrice
    and
    a.InvoiceHistoryID=@InvoiceHistoryID

/*
Description 
----------- 
Test2
Test3
*/

[子陌红尘]

declare @t table(InvoiceID int,InvoiceHistoryID int,ItemID int,OriginalItemID int,Description varchar(10),Quantity int,UnitPrice int)
insert into @t select 1,1,1,0,'Test1',10, 10 
insert into @t select 1,1,2,0,'Test2',20, 20 
insert into @t select 1,1,3,0,'Test3',30, 30 
insert into @t select 1,2,4,1,'Test1',5 ,10 
insert into @t select 1,2,5,2,'Test2',20, 20 
insert into @t select 1,2,6,3,'Test3',30, 30 
insert into @t select 1,3,7,4,'Test1',5 ,  5 
insert into @t select 1,3,8,5,'Test2',20, 20 
insert into @t select 1,3,9,6,'Test3',25, 30 

declare @InvoiceHistoryID int
set @InvoiceHistoryID=3

select  
    a.Description
from
    @t a,@t b 
where 
    a.OriginalItemID=b.ItemID
    and
    a.Description=b.Description and a.Quantity=b.Quantity and a.UnitPrice=b.UnitPrice
    and
    a.InvoiceHistoryID=@InvoiceHistoryID

/*
Description 
----------- 
Test2
*/

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([InvoiceID] int,[InvoiceHistoryID] int,[ItemID] int,[OriginalItemID] int,[Description] varchar(5),[Quantity] int,[UnitPrice] int)
insert [tb]
select 1,1,1,0,'Test1',10,10 union all
select 1,1,2,0,'Test2',20,20 union all
select 1,1,3,0,'Test3',30,30 union all
select 1,2,4,1,'Test1',5,10 union all
select 1,2,5,2,'Test2',20,20 union all
select 1,2,6,3,'Test3',30,30 union all
select 1,3,7,4,'Test1',5,5 union all
select 1,3,8,5,'Test2',20,20 union all
select 1,3,9,6,'Test3',25,30
 
---查询---
select 
  a.* 
from 
  tb a 
inner join 
  tb b 
on 
  a.OriginalItemID=b.ItemID and b.Description=a.Description and  b.Quantity=a.Quantity and b.UnitPrice=a.UnitPrice
where 
  a.InvoiceHistoryID=2

---结果---
InvoiceID   InvoiceHistoryID ItemID      OriginalItemID Description Quantity    UnitPrice   
----------- ---------------- ----------- -------------- ----------- ----------- ----------- 
1           2                5           2              Test2       20          20
1           2                6           3              Test3       30          30

（所影响的行数为 2 行）

[百年树人]

---查询---
select 
  a.* 
from 
  tb a 
inner join 
  tb b 
on 
  a.OriginalItemID=b.ItemID and b.Description=a.Description and  b.Quantity=a.Quantity and b.UnitPrice=a.UnitPrice
where 
  a.InvoiceHistoryID=3

---结果---
InvoiceID   InvoiceHistoryID ItemID      OriginalItemID Description Quantity    UnitPrice   
----------- ---------------- ----------- -------------- ----------- ----------- ----------- 
1           3                8           5              Test2       20          20

（所影响的行数为 1 行）

[-狙击手-]

哦，是前一次，我还当是历史所有的呢

[-狙击手-]

[Quote=引用 7 楼 happyflystone 的回复:]
哦，是前一次，我还当是历史所有的呢
[/Quote]

楼主说是树我以为遍历所有的呢，看来理解错了，
把我理解错的贴上来

------------------------------------------------------------------------
-- Author:  happyflystone  
-- Date  :  2009-05-06 17:30:12
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--      	Apr 14 2006 01:12:25 
--      	Copyright (c) 1988-2005 Microsoft Corporation
--      	Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------

-- Test Data: T1
IF OBJECT_ID('T1') IS NOT NULL 
    DROP TABLE T1
Go
CREATE TABLE T1(InvoiceID INT,InvoiceHistoryID INT,
ItemID INT,OriginalItemID NVARCHAR(1),Description NVARCHAR(5),
Quantity INT,UnitPrice INT)
Go
INSERT INTO T1
SELECT 1,1,1,'0','Test1',10,10 UNION ALL
SELECT 1,1,2,'0','Test2',20,20 UNION ALL
SELECT 1,1,3,'0','Test3',30,30 UNION ALL
SELECT 1,2,4,'1','Test1',5,10 UNION ALL
SELECT 1,2,5,'2','Test2',20,20 UNION ALL
SELECT 1,2,6,'3','Test3',30,30 UNION ALL
SELECT 1,3,7,'4','Test1',5,5 UNION ALL
SELECT 1,3,8,'5','Test2',20,20 UNION ALL
SELECT 1,3,9,'6','Test3',25,30 
GO
--Start
;with t
as
(select * from t1 where InvoiceHistoryID = 3
 union all
 select t1.* from t,t1 where t1.itemid = t.OriginalItemID)

SELECT 
	a.description 
FROM
	t a
group by a.description,a.quantity,a.unitprice
having count(1) = (select count(1) from t where  description = a.description group by description )

--Result:
/*

description
-----------
Test2


*/
--End 

[等不到来世]

[Quote=引用 7 楼 happyflystone 的回复:]
哦，是前一次，我还当是历史所有的呢
[/Quote]
应该是历史所有的。

[zimu312500]

请问楼上各位的发帖形式时怎么搞出来的，我看树人大哥每次回答都是这种模式，难道每次都会拿楼主的数据进行测试吗，学习！

[wuxiaoqqqq]

我的意思是历史所有的啊。

[百年树人]

[Quote=引用 7 楼 happyflystone 的回复:]
哦，是前一次，我还当是历史所有的呢
[/Quote]

刚好2到3的时候只有一个没有变化，给撞上了，看来还是石头兄厉害...

[wuxiaoqqqq]

[Quote=引用 7 楼 happyflystone 的回复:]
哦，是前一次，我还当是历史所有的呢
[/Quote]

我打错了，2到3的时候Test1的值应该是不变的。

[-狙击手-]

[Quote=引用 9 楼 szx1999 的回复:]
引用 7 楼 happyflystone 的回复:
哦，是前一次，我还当是历史所有的呢

应该是历史所有的。
[/Quote]

狡猾，注意到你这个了

InvoiceHistoryID<@HID