数据库2005数据结构:
订单号 分店号 子订单号 到达时间 离开时间
FolioID ChainID ChildFolioID Arrdate Depdate 1 1 2 2009-07-02 2009-07-02 2 1 3 2009-07-02 2009-07-02 3 1 0 2009-07-01 2009-07-01 1 2 0 2009-07-05 2009-07-05
3 2 0 2009-07-04 2009-07-04 4 2 3 2009-07-04 2009-07-04
这种子父关系相信大家都看得懂吧,有一点不同, 0代表该订单是最底层的订单(即到了底的订单),
而大于0的订单代表它至少有一个子订单,跟树形结构反过来了。
举例:
在分店(ChainID)为1的的订单中,下面这条就是最顶层的父订单
1 1 2 2009-07-02 2009-07-02
而 3 1 0 2009-07-01 2009-07-01 是最底层的订单,因为它ChildID为0说明一点:确定唯一的一张订单是用FolioID和ChainID同时来确定, 并不是FolioID,比如上面数据的FolioID为3的。
现在有这样的需求: 1 首先找到一个订单的全部子订单, 然后用它的Arrdate 与 ChildID为0的子订单的Arrdate相比较, 如果在同一天的话, 则找出该订单, 举例:我需要的数据是:
4 2 3 2009-07-04 2009-07-04
因为它和它的Child为0的子订单的Arrdate在同一天 2 找到没有子订单的订单,
举例: 我需要的数据是
1 2 0 2009-07-05 2009-07-05
因为该订单没有子订单, 最终需要的结果:
4 2 3 2009-07-04 2009-07-04
1 2 0 2009-07-05 2009-07-05
订单号 分店号 子订单号 到达时间 离开时间
FolioID ChainID ChildFolioID Arrdate Depdate 1 1 2 2009-07-02 2009-07-02 2 1 3 2009-07-02 2009-07-02 3 1 0 2009-07-01 2009-07-01 1 2 0 2009-07-05 2009-07-05
3 2 0 2009-07-04 2009-07-04 4 2 3 2009-07-04 2009-07-04
这种子父关系相信大家都看得懂吧,有一点不同, 0代表该订单是最底层的订单(即到了底的订单),
而大于0的订单代表它至少有一个子订单,跟树形结构反过来了。
举例:
在分店(ChainID)为1的的订单中,下面这条就是最顶层的父订单
1 1 2 2009-07-02 2009-07-02
而 3 1 0 2009-07-01 2009-07-01 是最底层的订单,因为它ChildID为0说明一点:确定唯一的一张订单是用FolioID和ChainID同时来确定, 并不是FolioID,比如上面数据的FolioID为3的。
现在有这样的需求: 1 首先找到一个订单的全部子订单, 然后用它的Arrdate 与 ChildID为0的子订单的Arrdate相比较, 如果在同一天的话, 则找出该订单, 举例:我需要的数据是:
4 2 3 2009-07-04 2009-07-04
因为它和它的Child为0的子订单的Arrdate在同一天 2 找到没有子订单的订单,
举例: 我需要的数据是
1 2 0 2009-07-05 2009-07-05
因为该订单没有子订单, 最终需要的结果:
4 2 3 2009-07-04 2009-07-04
1 2 0 2009-07-05 2009-07-05
是列出所有的还是按参数查询
GO
CREATE TABLE TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 4, 2, 3 ,'2009-07-04','2009-07-04'SELECT DISTINCT T1.* FROM TB T1
INNER JOIN TB T2
ON T1.CHILDFOLIOID<>0 AND T2.CHILDFOLIOID=0 AND T1.ARRDATE=T2.ARRDATE
比如这条记录:
FolioID ChainID ChildID
1 2 0 2009-07-05 2009-07-05
你在分店ID (ChainID) 为2 里面。 找不到ChildID = 1的订单记录。
4 2 3 2009-07-04 2009-07-04
我在分店ID (ChainID) 为2 里面。 也找不到ChildID = 4的订单记录啊
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 4, 2, 3 ,'2009-07-04','2009-07-04' UNION ALLSELECT 5, 2, 6 ,'2009-07-02','2009-07-02' UNION ALLSELECT 6, 2, 0 ,'2009-07-01','2009-07-01'
SELECT DISTINCT T1.* FROM TB T1
INNER JOIN TB T2
ON T1.CHILDFOLIOID<>0 AND T2.CHILDFOLIOID=0 AND T1.ARRDATE=T2.ARRDATE
这样出来的还是一条。
试试对不对
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 4, 2, 3 ,'2009-07-04','2009-07-04'SELECT DISTINCT T1.* FROM TB T1
INNER JOIN TB T2
ON T1.CHILDFOLIOID<>0 AND T2.CHILDFOLIOID=0 AND T1.ARRDATE=T2.ARRDATE
UNION ALL
SELECT * FROM TB T1
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T2.ChildFolioID=T1.FolioID )
AND T1.CHILDFOLIOID=0/*
4 2 3 2009-07-04 00:00:00.000 2009-07-04 00:00:00.000
1 2 0 2009-07-05 00:00:00.000 2009-07-05 00:00:00.000
*/
确定一条唯一的订单是需要用到FolioID 和ChainID。 只有这两个数字是唯一的, 才能证明这条订单是唯一的。
麻烦你测一下下面这条数据,
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-01','2009-07-01' UNION ALLSELECT 4, 2, 3 ,'2009-07-01','2009-07-01' UNION ALLSELECT 5, 2, 6 ,'2009-07-02','2009-07-02' UNION ALLSELECT 6, 2, 0 ,'2009-07-02','2009-07-02'
IF OBJECT_ID('tempdb..#TB') IS NOT NULL DROP TABLE #TB
GO
CREATE TABLE #TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO #TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 4, 2, 3 ,'2009-07-04','2009-07-04'
declare @chainid int
declare @folioid int
declare @i int
select @i = count(1) from #tb
select
a.*
from #tb a
inner join #tb b on b.ChainID = a.ChainID and b.ChildFolioID = 0
where not exists (select 1 from #tb where chainid = a.chainid and ChildFolioID = a.FolioID)
and a.Arrdate = b.Arrdate
FolioID ChainID ChildFolioID Arrdate Depdate
----------- ----------- ------------ ------------------------------------------------------ ------------------------------------------------------
1 2 0 2009-07-05 00:00:00.000 2009-07-05 00:00:00.000
4 2 3 2009-07-04 00:00:00.000 2009-07-04 00:00:00.000(所影响的行数为 2 行)鸭子出答案了 小弟也蹭个分
SELECT DISTINCT T1.* FROM TB T1
INNER JOIN TB T2
ON T1.CHILDFOLIOID<>0 AND T2.CHILDFOLIOID=0 AND T1.ARRDATE=T2.ARRDATE
我觉得你这里写的有点问题, 因为没用上Folioid 和 ChainID 只用了T1.ARRDATE=T2.ARRDATE当多条数据有相同的ARRDATE时, 这样数据就不对了。
IF OBJECT_ID('tempdb..#TB') IS NOT NULL DROP TABLE #TB
GO
CREATE TABLE #TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO #TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 4, 2, 3 ,'2009-07-04','2009-07-04'select
a.*
from #tb a
inner join #tb b on b.ChainID = a.ChainID and b.ChildFolioID = 0
where not exists (select 1 from #tb where chainid = a.chainid and ChildFolioID = a.FolioID)
and a.Arrdate = b.Arrdate
FolioID ChainID ChildFolioID Arrdate Depdate
----------- ----------- ------------ ------------------------------------------------------ ------------------------------------------------------
1 2 0 2009-07-05 00:00:00.000 2009-07-05 00:00:00.000
4 2 3 2009-07-04 00:00:00.000 2009-07-04 00:00:00.000(所影响的行数为 2 行)
多了点东西,呵呵不好意思
IF OBJECT_ID('tempdb..#TB') IS NOT NULL DROP TABLE #TB
GO
CREATE TABLE #TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO #TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALL
SELECT 3, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 4, 2, 3 ,'2009-07-04','2009-07-04' UNION ALLSELECT 5, 2, 6 ,'2009-07-05','2009-07-02' UNION ALLSELECT 6, 2, 0 ,'2009-07-02','2009-07-02'
IF OBJECT_ID('tempdb..#TB') IS NOT NULL DROP TABLE #TB
GO
CREATE TABLE #TB(FolioID INT, ChainID INT, ChildFolioID INT, Arrdate DATETIME, Depdate DATETIME)
INSERT INTO #TB
SELECT 1, 1, 2 ,'2009-07-02','2009-07-02' UNION ALLSELECT 2, 1, 3 ,'2009-07-02','2009-07-02' UNION ALL SELECT 3, 1, 0 ,'2009-07-01','2009-07-01' UNION ALL
SELECT 4, 1, 0 ,'2009-07-01','2009-07-01' UNION ALLSELECT 1, 2, 0 ,'2009-07-05','2009-07-05' UNION ALLSELECT 5, 2, 0 ,'2009-07-04','2009-07-04' UNION ALLSELECT 3, 2, 5 ,'2009-07-04','2009-07-04' UNION ALL
SELECT 4, 2, 3 ,'2009-07-04','2009-07-04'
alter table #tb add flag int
go
declare @i int
select @i = count(1) from #tbwhile @i > 0
begin update a set
flag = case when ChildFolioID <> 0 then
(select top 1 (case when ChildFolioID = 0 then FolioID else ChildFolioID end) from #tb where a.ChildFolioID = FolioID and ChainID = a.ChainID)
else ChildFolioID end
from #tb a
set @i = @i-1
endselect * from #tb
select
a.*
from #tb a
inner join #tb b on b.ChainID = a.ChainID and ((a.ChildFolioID = 0 and a.FolioID = b.FolioID) or a.flag = b.FolioID)
where not exists (select 1 from #tb where chainid = a.chainid and ChildFolioID = a.FolioID)
and a.Arrdate = b.Arrdate
FolioID ChainID ChildFolioID Arrdate Depdate flag
----------- ----------- ------------ ------------------------------------------------------ ------------------------------------------------------ -----------
1 2 0 2009-07-05 00:00:00.000 2009-07-05 00:00:00.000 0
4 1 0 2009-07-01 00:00:00.000 2009-07-01 00:00:00.000 0
4 2 3 2009-07-04 00:00:00.000 2009-07-04 00:00:00.000 5(所影响的行数为 3 行) 数据稍微改了一下