SALE表
ID PRODUCTID CREATER KIND CREATTIME
1 001 A 销售 2010-02-01
2 001 B 调整 2010-02-03
3 001 C 调整 2010-02-05
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06要求输入A ,查询结果为
1 001 A 销售 2010-02-01
2 001 B 调整 2010-02-03
3 001 C 调整 2010-02-05
//001第一次被A销售,后面001的记录全部为A所有要求输入B ,查询结果为
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
//045第一次被B销售,后面045的记录全部为B所有
ID PRODUCTID CREATER KIND CREATTIME
1 001 A 销售 2010-02-01
2 001 B 调整 2010-02-03
3 001 C 调整 2010-02-05
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06要求输入A ,查询结果为
1 001 A 销售 2010-02-01
2 001 B 调整 2010-02-03
3 001 C 调整 2010-02-05
//001第一次被A销售,后面001的记录全部为A所有要求输入B ,查询结果为
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
//045第一次被B销售,后面045的记录全部为B所有
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
//045第一次被B销售,后面045的记录全部为B所有晕,那001还输入过B呢
要求输入B ,查询结果为
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
//045第一次被B销售,后面045的记录全部为B所有晕,那001还输入过B呢
B做的是调整,不计算在B的里面 .
-- Author :SQL77(只为思齐老)
-- Date :2010-03-17 16:50:59
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#SALE
if object_id('tempdb.dbo.#SALE') is not null drop table #SALE
go
create table #SALE([ID] int,[PRODUCTID] varchar(3),[CREATER] varchar(1),[KIND] varchar(4),[CREATTIME] datetime)
insert #SALE
select 1,'001','A','销售','2010-02-01' union all
select 2,'001','B','调整','2010-02-03' union all
select 3,'001','C','调整','2010-02-05' union all
select 4,'045','B','销售','2010-02-05' union all
select 4,'045','E','调整','2010-02-06'
--------------开始查询--------------------------
DECLARE @CREATER VARCHAR(1)
SET @CREATER='A'
select * from #SALE
WHERE PRODUCTID IN(SELECT PRODUCTID FROM #SALE WHERE CREATER=@CREATER AND KIND='销售')----------------结果----------------------------
/* (所影响的行数为 5 行)ID PRODUCTID CREATER KIND CREATTIME
----------- --------- ------- ---- ------------------------------------------------------
1 001 A 销售 2010-02-01 00:00:00.000
2 001 B 调整 2010-02-03 00:00:00.000
3 001 C 调整 2010-02-05 00:00:00.000(所影响的行数为 3 行)
*/
-- Author :SQL77(只为思齐老)
-- Date :2010-03-17 16:50:59
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#SALE
if object_id('tempdb.dbo.#SALE') is not null drop table #SALE
go
create table #SALE([ID] int,[PRODUCTID] varchar(3),[CREATER] varchar(1),[KIND] varchar(4),[CREATTIME] datetime)
insert #SALE
select 1,'001','A','销售','2010-02-01' union all
select 2,'001','B','调整','2010-02-03' union all
select 3,'001','C','调整','2010-02-05' union all
select 4,'045','B','销售','2010-02-05' union all
select 4,'045','E','调整','2010-02-06'
--------------开始查询--------------------------
DECLARE @CREATER VARCHAR(1)
SET @CREATER='B'
select * from #SALE
WHERE PRODUCTID IN(SELECT PRODUCTID FROM #SALE WHERE CREATER=@CREATER AND KIND='销售')----------------结果----------------------------
/*
(所影响的行数为 5 行)ID PRODUCTID CREATER KIND CREATTIME
----------- --------- ------- ---- ------------------------------------------------------
4 045 B 销售 2010-02-05 00:00:00.000
4 045 E 调整 2010-02-06 00:00:00.000(所影响的行数为 2 行)*/楼主自己改成过程或函数吧
if object_id('[SALE]') is not null drop table [SALE]
create table [SALE]([ID] int,[PRODUCTID] varchar(3),[CREATER] varchar(1),[KIND] varchar(4),[CREATTIME] datetime)
go
insert [SALE]
select 1,'001','A','销售','2010-02-01' union all
select 2,'001','B','调整','2010-02-03' union all
select 3,'001','C','调整','2010-02-05' union all
select 4,'045','B','销售','2010-02-05' union all
select 4,'045','E','调整','2010-02-06'if object_id('proc_trans_search') is not null drop proc proc_trans_search
go
create proc proc_trans_search @CREATER varchar(20)
as
select t.* from [SALE] t join [SALE] r
on t.[PRODUCTID] = r.[PRODUCTID]
where r.[CREATER] = @CREATER and r.[KIND] = '销售'
goexec proc_trans_search 'A'
ID PRODUCTID CREATER KIND CREATTIME
----------- --------- ------- ---- -----------------------
1 001 A 销售 2010-02-01 00:00:00.000
2 001 B 调整 2010-02-03 00:00:00.000
3 001 C 调整 2010-02-05 00:00:00.000(3 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-17 16:55:43
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[SALE]
if object_id('[SALE]') is not null drop table [SALE]
go
create table [SALE]([ID] int,[PRODUCTID] varchar(3),[CREATER] varchar(1),[KIND] varchar(4),[CREATTIME] datetime)
insert [SALE]
select 1,'001','A','销售','2010-02-01' union all
select 2,'001','B','调整','2010-02-03' union all
select 3,'001','C','调整','2010-02-05' union all
select 4,'045','B','销售','2010-02-05' union all
select 4,'045','E','调整','2010-02-06'
--------------开始查询--------------------------
declare @s varchar(10)
set @s='a'
select * from sale s where exists(select 1 from sale where creater=@s and kind='销售' and productid=s.productid)declare @s1 varchar(10)
set @s1='b'
select * from sale s where exists(select 1 from sale where creater=@s1 and kind='销售' and productid=s.productid)
----------------结果----------------------------
/*
ID PRODUCTID CREATER KIND CREATTIME
----------- --------- ------- ---- ------------------------------------------------------
1 001 A 销售 2010-02-01 00:00:00.000
2 001 B 调整 2010-02-03 00:00:00.000
3 001 C 调整 2010-02-05 00:00:00.000(所影响的行数为 3 行)ID PRODUCTID CREATER KIND CREATTIME
----------- --------- ------- ---- ------------------------------------------------------
4 045 B 销售 2010-02-05 00:00:00.000
4 045 E 调整 2010-02-06 00:00:00.000(所影响的行数为 2 行)*/
SALE表
ID PRODUCTID CREATER KIND CREATTIME
1 001 A 销售 2010-02-01
2 001 B 销售 2010-02-03
3 001 C 调整 2010-02-05
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
要求输入A ,查询结果为
1 001 A 销售 2010-02-01
2 001 B 调整 2010-02-03
3 001 C 调整 2010-02-05
//001第一次被A销售,后面001的记录全部为A所有要求输入B ,查询结果为
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
//045第一次被B销售,后面045的记录全部为B所有
SALE表
ID PRODUCTID CREATER KIND CREATTIME
1 001 A 销售 2010-02-01
2 001 B 销售 2010-02-03
3 001 C 调整 2010-02-05
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
要求输入A ,查询结果为
1 001 A 销售 2010-02-01
2 001 B 调整 2010-02-03
3 001 C 调整 2010-02-05
//001第一次被A销售,后面001的记录全部为A所有要求输入B ,查询结果为
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
//045第一次被B销售,后面045的记录全部为B所有第一次是谁销售的,那么以后的都是谁的,大家大概没有理解我的意思。
也可能描述的不是很清楚。
if object_id('[sale]') is not null drop table [sale]
go
create table [sale]([ID] int,[PRODUCTID] varchar(3),[CREATER] varchar(1),[KIND] varchar(4),[CREATTIME] datetime)
insert [sale]
select 1,'001','A','销售','2010-02-01' union all
select 2,'001','B','销售','2010-02-03' union all
select 3,'001','C','调整','2010-02-05' union all
select 4,'045','B','销售','2010-02-05' union all
select 4,'045','E','调整','2010-02-06'
---查询---
declare @creater varchar(10)
set @creater='A'
select a.ID,a.PRODUCTID,a.CREATER,
case when not exists(select 1 from sale where PRODUCTID=a.PRODUCTID and CREATTIME<a.CREATTIME) then '销售'
else '调整'
end,
a.CREATTIME
from sale a,
(select * from sale t where not exists(select 1 from sale where PRODUCTID=t.PRODUCTID and CREATTIME<t.CREATTIME)) b
where a.PRODUCTID=b.PRODUCTID
and b.CREATER=@creater---结果---
ID PRODUCTID CREATER CREATTIME
----------- --------- ------- ---- -----------------------
1 001 A 销售 2010-02-01 00:00:00.000
2 001 B 调整 2010-02-03 00:00:00.000
3 001 C 调整 2010-02-05 00:00:00.000(3 行受影响)
第二条数据如果销售,查询出来的结果就不对了ID PRODUCTID CREATER KIND CREATTIME
1 001 A 销售 2010-02-01
2 001 B 销售 2010-02-03
3 001 C 调整 2010-02-05
4 045 B 销售 2010-02-05
4 045 E 调整 2010-02-06
go
create table [SALE](
[ID] int
,[PRODUCTID] varchar(10)
,[CREATER] varchar(10)
,[KIND] varchar(20)
,[CREATTIME] datetime
)
insert [SALE]
select 1,'001','A','销售','2010-02-01' union all
select 2,'001','B','销售','2010-02-03' union all
select 3,'001','C','调整','2010-02-05' union all
select 4,'045','B','销售','2010-02-05' union all
select 4,'045','E','调整','2010-02-06'drop table #t
select *,
CASE WHEN
NOT EXISTS
(SELECT 1 FROM SALE WHERE PRODUCTID=S.PRODUCTID AND CREATTIME<S.CREATTIME AND KIND='销售') THEN 1 ELSE 0 END STATE INTO #T
from SALE S
DECLARE @CREATER VARCHAR(1)
SET @CREATER='A'select * from #T
WHERE PRODUCTID IN(SELECT PRODUCTID
FROM #T WHERE CREATER=@CREATER AND KIND='销售' AND STATE=1)
77哥不是做出来了不?无论销售还是调整