请教这样的查询语句怎么写

Question

假设有三个表他们之间通过FK关联  Site表  SiteHistory表  Customer表    
我只介绍一下表中重要的column  Site表中有 iSiteID, iSiteName, iSiteUrl,datCreateTime(创建时间) 
SiteHistory结构和Site完全一样  只不过多了一个datTimeStamp的column用来记录每一次对site表的更新 
Customer表中有 iCustomerID,iSiteID,datSignUpDate 
具体关系是 
iSiteID在Site表中是unique的用来做Primary Key 
iSiteHistory表中iSiteID和datTimeStamp用来做Primary Key,  同一个iSiteID在此表中可能对应多个记录 \ 
iCustomerID在Customer表中是Unique的 现在发生如下情况 
一开始在没有引用SiteHistory表的情况下 当查询Customer的时候很容易通过iSiteID把Customer和Site表关联查询得到相关的结果 
但是根据需要引入SiteHistory表后    需要得到以下查询 
假设Site和SiteHistory表中原先有三个站点记录 
iSiteID  iSiteName  iSiteUrl  datCreateTime 
1            a      a.com    06/01/2006 
2            b      a.com    06/01/2007 
3            c      c.com    06/01/2008 
iSiteID  iSiteName  iSiteUrl  datCreateTime  datTimeStamp 
1            a      a.com    06/01/2006      06/01/2006 
2            b      a.com    06/01/2007      06/01/2007 
3            c      c.com    06/01/2008      06/01/2008 Customer表 
iCustomerID  iSiteID  datSignUpDate 
1              1        07/01/2006 
2              2        07/01/2007 
3              2        07/01/2007 
4              2        07/01/2007 
5              3        07/01/2008 
如果对Site表中b更新名字成d那么SiteHistory表中会增加一条记录 
2            d      d.com    06/01/2008      08/01/2008 在此后原来的b站点也就是现在d站点又注册了两个新用户现在Customer表如下 
iCustomerID  iSiteID  datSignUpDate 
1              1        07/01/2006 
2              2        07/01/2007 
3              2        07/01/2007 
4              2        07/01/2007 
5              3        07/01/2008 
6              2        09/01/2008 
7              2        09/01/2008 
    
现在就有如下问题  假设在用户界面上有若干查询选择下拉列表框中显示SiteHistory中所有的不重复的站点(iSiteID可能相同)  
用户选择b 需要显示的结果应该是 
iCustomerID    SiteName  datSignUpDate 
2              b        07/01/2007 
3              b        07/01/2007 
4              b        07/01/2007 如果选择 d 需要显示的结果是  因为以前的三个用户仍然存在他们也属于新的站点 
iCustomerID    SiteName  datSignUpDate 
2              d        07/01/2007 
3              d        07/01/2007 
4              d        07/01/2007 
6              d        09/01/2008 
7              d        09/01/2008 如果选择全部那么应该显示 iCustomerID    SiteName  datSignUpDate 
1              a        07/01/2006 
2              d(b)    07/01/2007 
3              d(b)    07/01/2007 
4              d(b)    07/01/2007 
5              c        07/01/2008 
6              d        09/01/2008 
7              d        09/01/2008 
总的来说就是  显示结果的时候  选以前的显示以前的  选现在的显示现在的  选全部的话就用相关的名字区分出来 我写的办法比较复杂逻辑性不好,所以想请大家帮忙看看有什么好办法 

Answer 1

iSiteHistory--应用有效时间段StartDate,EndDate

Answer 2

对  我是用Store Procudures但是 写的Logic不清楚  所以想请教大家一下  谢谢

Answer 3

iCustomerID    SiteName  datSignUpDate 
1              a        07/01/2006 
2              d(b)    07/01/2007 
3              d(b)    07/01/2007 
4              d(b)    07/01/2007 
5              c        07/01/2008 
6              d        09/01/2008 
7              d        09/01/2008 为2000/2005?
这里有个合并的条件在

Answer 4

--> --> (Roy)生成測試數據
 set nocount on ;
if not object_id('Site') is null
drop table Site
Go
Create table Site([iSiteID] int,[iSiteName] nvarchar(1),[iSiteUrl] nvarchar(5),[datCreateTime] Datetime)
Insert Site
select 1,N'a',N'a.com','06/01/2006' union all
select 2,N'b',N'a.com','06/01/2007' union all
select 3,N'c',N'c.com','06/01/2008'
Go
--> --> (Roy)生成測試數據
 
if not object_id('SiteHistory') is null
drop table SiteHistory
Go
Create table SiteHistory([iSiteID] int,[iSiteName] nvarchar(1),[iSiteUrl] nvarchar(5),[datCreateTime] Datetime,[datTimeStamp] Datetime unique([iSiteID],[datTimeStamp]))
Insert SiteHistory
select 1,N'a',N'a.com','06/01/2006','06/01/2006' union all
select 2,N'b',N'a.com','06/01/2007','06/01/2007' union all
select 3,N'c',N'c.com','06/01/2008','06/01/2008'
Go
--> --> (Roy)生成測試數據
 
if not object_id('Customer') is null
drop table Customer
Go
Create table Customer([iCustomerID] int,[iSiteID] int,[datSignUpDate] Datetime)
Insert Customer
select 1,1,'07/01/2006' union all
select 2,2,'07/01/2007' union all
select 3,2,'07/01/2007' union all
select 4,2,'07/01/2007' union all
select 5,3,'07/01/2008'
Go--生成测试update Site
set [iSiteName]='d'
where [iSiteID]=2go
Insert SiteHistory
select 2,N'd',N'd.com','06/01/2006','08/01/2008' 
go
insert  Customer
select 6,2,'09/01/2008' union all
select 7,2,'09/01/2008'go
if object_id('p1') is not null
drop proc p1
go
create proc p1(@iSiteName nvarchar(1)=null)
asif @iSiteName is null
select 
    b.iCustomerID,[iSiteName]=a.[iSiteName]+(select isnull(b.Col.value('/R[1]','nvarchar(100)'),'')
from 
(select COl=(select  N'('+[iSiteName]+')' from SiteHistory where iSiteID=a.iSiteID  and datTimeStamp>b.datSignUpDate 
order by datTimeStamp  desc For XML PATH(''), ROOT('R'), TYPE))b)
,b.datSignUpDate 
from 
    Site a
join
Customer b on a.iSiteID=b.iSiteID
order by b.iCustomerID
else 
select 
*
from 
Customer a
where
exists(select 1 from SiteHistory b where [iSiteID]=a.[iSiteID] 
and [iSiteName]=@iSiteName and [datSignUpDate]<
(select  isnull(min([datTimeStamp]),[datSignUpDate]+1) from SiteHistory where [iSiteID]=b.[iSiteID] and [datTimeStamp]>b.[datTimeStamp]))
go测试1:
exec p1 b测试2:
exec p1 d测试3:
exec p1/*
iCustomerID iSiteID     datSignUpDate
----------- ----------- -----------------------
2           2           2007-07-01 00:00:00.000
3           2           2007-07-01 00:00:00.000
4           2           2007-07-01 00:00:00.000iCustomerID iSiteID     datSignUpDate
----------- ----------- -----------------------
2           2           2007-07-01 00:00:00.000
3           2           2007-07-01 00:00:00.000
4           2           2007-07-01 00:00:00.000
6           2           2008-09-01 00:00:00.000
7           2           2008-09-01 00:00:00.000iCustomerID iSiteName                                                                                             datSignUpDate
----------- ----------------------------------------------------------------------------------------------------- -----------------------
1           a                                                                                                     2006-07-01 00:00:00.000
2           d(d)                                                                                                  2007-07-01 00:00:00.000
3           d(d)                                                                                                  2007-07-01 00:00:00.000
4           d(d)                                                                                                  2007-07-01 00:00:00.000
5           c                                                                                                     2008-07-01 00:00:00.000
6           d                                                                                                     2008-09-01 00:00:00.000
7           d                                                                                                     2008-09-01 00:00:00.000
*/

Answer 5

--> --> (Roy)生成測試數據
 set nocount on ;
if not object_id('Site') is null
drop table Site
Go
Create table Site([iSiteID] int,[iSiteName] nvarchar(1),[iSiteUrl] nvarchar(5),[datCreateTime] Datetime)
Insert Site
select 1,N'a',N'a.com','06/01/2006' union all
select 2,N'b',N'a.com','06/01/2007' union all
select 3,N'c',N'c.com','06/01/2008'
Go
--> --> (Roy)生成測試數據
 
if not object_id('SiteHistory') is null
drop table SiteHistory
Go
Create table SiteHistory([iSiteID] int,[iSiteName] nvarchar(1),[iSiteUrl] nvarchar(5),[datCreateTime] Datetime,[datTimeStamp] Datetime unique([iSiteID],[datTimeStamp]))
Insert SiteHistory
select 1,N'a',N'a.com','06/01/2006','06/01/2006' union all
select 2,N'b',N'a.com','06/01/2007','06/01/2007' union all
select 3,N'c',N'c.com','06/01/2008','06/01/2008'
Go
--> --> (Roy)生成測試數據
 
if not object_id('Customer') is null
drop table Customer
Go
Create table Customer([iCustomerID] int,[iSiteID] int,[datSignUpDate] Datetime)
Insert Customer
select 1,1,'07/01/2006' union all
select 2,2,'07/01/2007' union all
select 3,2,'07/01/2007' union all
select 4,2,'07/01/2007' union all
select 5,3,'07/01/2008'
Go--生成测试update Site
set [iSiteName]='d'
where [iSiteID]=2go
Insert SiteHistory
select 2,N'd',N'd.com','06/01/2006','08/01/2008' 
go
insert  Customer
select 6,2,'09/01/2008' union all
select 7,2,'09/01/2008'go
if object_id('p1') is not null
drop proc p1
go
create proc p1(@iSiteName nvarchar(1)=null)
asif @iSiteName is null
select 
    b.iCustomerID,[iSiteName]=a.[iSiteName]+(select isnull(b.Col.value('/R[1]','nvarchar(100)'),'')
from 
(select COl=(select  N'('+[iSiteName]+')' from SiteHistory where iSiteID=a.iSiteID  and datTimeStamp>b.datSignUpDate 
order by datTimeStamp  desc For XML PATH(''), ROOT('R'), TYPE))b)
,b.datSignUpDate 
from 
    Site a
join
Customer b on a.iSiteID=b.iSiteID
order by b.iCustomerID
else 
select 
a.iCustomerID,[iSiteName]=@iSiteName,a.datSignUpDate --这里改改显示@iSiteName
from 
Customer a
where
exists(select 1 from SiteHistory b where [iSiteID]=a.[iSiteID] 
and [iSiteName]=@iSiteName and [datSignUpDate]<
(select  isnull(min([datTimeStamp]),[datSignUpDate]+1) from SiteHistory where [iSiteID]=b.[iSiteID] and [datTimeStamp]>b.[datTimeStamp]))
go测试1:
exec p1 b测试2:
exec p1 d测试3:
exec p1/*
iCustomerID iSiteID     datSignUpDate
----------- ----------- -----------------------
2           2           2007-07-01 00:00:00.000
3           2           2007-07-01 00:00:00.000
4           2           2007-07-01 00:00:00.000iCustomerID iSiteID     datSignUpDate
----------- ----------- -----------------------
2           2           2007-07-01 00:00:00.000
3           2           2007-07-01 00:00:00.000
4           2           2007-07-01 00:00:00.000
6           2           2008-09-01 00:00:00.000
7           2           2008-09-01 00:00:00.000iCustomerID iSiteName                                                                                             datSignUpDate
----------- ----------------------------------------------------------------------------------------------------- -----------------------
1           a                                                                                                     2006-07-01 00:00:00.000
2           d(d)                                                                                                  2007-07-01 00:00:00.000
3           d(d)                                                                                                  2007-07-01 00:00:00.000
4           d(d)                                                                                                  2007-07-01 00:00:00.000
5           c                                                                                                     2008-07-01 00:00:00.000
6           d                                                                                                     2008-09-01 00:00:00.000
7           d                                                                                                     2008-09-01 00:00:00.000
*/

Answer 6

上面结果集贴错了;--> --> (Roy)生成測試數據
 set nocount on ;
if not object_id('Site') is null
drop table Site
Go
Create table Site([iSiteID] int,[iSiteName] nvarchar(1),[iSiteUrl] nvarchar(5),[datCreateTime] Datetime)
Insert Site
select 1,N'a',N'a.com','06/01/2006' union all
select 2,N'b',N'a.com','06/01/2007' union all
select 3,N'c',N'c.com','06/01/2008'
Go
--> --> (Roy)生成測試數據
 
if not object_id('SiteHistory') is null
drop table SiteHistory
Go
Create table SiteHistory([iSiteID] int,[iSiteName] nvarchar(1),[iSiteUrl] nvarchar(5),[datCreateTime] Datetime,[datTimeStamp] Datetime unique([iSiteID],[datTimeStamp]))
Insert SiteHistory
select 1,N'a',N'a.com','06/01/2006','06/01/2006' union all
select 2,N'b',N'a.com','06/01/2007','06/01/2007' union all
select 3,N'c',N'c.com','06/01/2008','06/01/2008'
Go
--> --> (Roy)生成測試數據
 
if not object_id('Customer') is null
drop table Customer
Go
Create table Customer([iCustomerID] int,[iSiteID] int,[datSignUpDate] Datetime)
Insert Customer
select 1,1,'07/01/2006' union all
select 2,2,'07/01/2007' union all
select 3,2,'07/01/2007' union all
select 4,2,'07/01/2007' union all
select 5,3,'07/01/2008'
Go--生成测试update Site
set [iSiteName]='d'
where [iSiteID]=2go
Insert SiteHistory
select 2,N'd',N'd.com','06/01/2006','08/01/2008' 
go
insert  Customer
select 6,2,'09/01/2008' union all
select 7,2,'09/01/2008'go
if object_id('p1') is not null
drop proc p1
go
create proc p1(@iSiteName nvarchar(1)=null)
asif @iSiteName is null
select 
    b.iCustomerID,[iSiteName]=a.[iSiteName]+(select isnull(b.Col.value('/R[1]','nvarchar(100)'),'')
from 
(select COl=(select  N'('+[iSiteName]+')' from SiteHistory where iSiteID=a.iSiteID  and datTimeStamp>b.datSignUpDate 
order by datTimeStamp  desc For XML PATH(''), ROOT('R'), TYPE))b)
,b.datSignUpDate 
from 
    Site a
join
Customer b on a.iSiteID=b.iSiteID
order by b.iCustomerID
else 
select 
a.iCustomerID,[iSiteName]=@iSiteName,a.datSignUpDate --这里改改显示@iSiteName
from 
Customer a
where
exists(select 1 from SiteHistory b where [iSiteID]=a.[iSiteID] 
and [iSiteName]=@iSiteName and [datSignUpDate]<
(select  isnull(min([datTimeStamp]),[datSignUpDate]+1) from SiteHistory where [iSiteID]=b.[iSiteID] and [datTimeStamp]>b.[datTimeStamp]))
go测试1:
exec p1 b测试2:
exec p1 d测试3:
exec p1/*
iCustomerID iSiteName datSignUpDate
----------- --------- -----------------------
2           b         2007-07-01 00:00:00.000
3           b         2007-07-01 00:00:00.000
4           b         2007-07-01 00:00:00.000iCustomerID iSiteName datSignUpDate
----------- --------- -----------------------
2           d         2007-07-01 00:00:00.000
3           d         2007-07-01 00:00:00.000
4           d         2007-07-01 00:00:00.000
6           d         2008-09-01 00:00:00.000
7           d         2008-09-01 00:00:00.000iCustomerID iSiteName                                                                                             datSignUpDate
----------- ----------------------------------------------------------------------------------------------------- -----------------------
1           a                                                                                                     2006-07-01 00:00:00.000
2           d(d)                                                                                                  2007-07-01 00:00:00.000
3           d(d)                                                                                                  2007-07-01 00:00:00.000
4           d(d)                                                                                                  2007-07-01 00:00:00.000
5           c                                                                                                     2008-07-01 00:00:00.000
6           d                                                                                                     2008-09-01 00:00:00.000
7           d                                                                                                     2008-09-01 00:00:00.000*/

Answer 7

我在原来的代码基础上怎么改写成兄台的Select 呢?set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description: customer search
-- =============================================
ALTER PROCEDURE [sams_admin].[SPUICustomerSearch] 
@vchFirstName varchar(50) = null,
@vchLastName varchar(50) = null, 
@sdatSignup1 datetime = null,
@sdatSignup2 datetime = null,
@iSiteID int = null,
@bInAffiliate bit=null,
@vchStatus varchar(20) = null,
@iUserID int = null,
@vchUserName varchar(50) = null, 
@iCompanyID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
if @iSiteID < 1
set @iSiteID = null
if @iUserID < 1
set @iUserID = null
if @iCompanyID < 1 
set @iCompanyID = null
if @vchStatus is null
set @vchStatus = 'All' SET NOCOUNT ON;SELECT 
a.iCustomerID,a.vchUserName,a.vchFirstName,a.vchLastName,a.bInAffiliate,a.bSuspended,a.bRemoved,a.sdatSignUp,a.sdatSuspendRemove,
case when a.bRemoved=1 then 'Inactive' when a.bSuspended=1 then 'Suspended' else 'Active' end vchStatus,
b.vchName as vchSiteName, b.vchURL as vchSiteURL, 
sams_storefront.FN_CustomerHasServices(a.iCustomerID,null,null,0,1) vchCurrentPlan  这个Function不用管是得到另外一个需要的值的
FROM
sams_storefront.aCustomer a 
inner join sams_admin.FuncMyStores(@iUserID) b on a.iSiteID = b.iSiteID (这里是个Function拿到相应的属于一个管理员的site列表得) 代码见下面
WHERE
(a.vchLastName like @vchLastName + '%' or @vchLastName is null)
 AND (a.vchFirstName like @vchFirstName + '%' or @vchFirstName is null)
 AND (a.vchUserName = @vchUserName or @vchUserName is null)
 AND (a.bInAffiliate = @bInAffiliate or @bInAffiliate is null)
 AND (a.iSiteID = @iSiteID or @iSiteID is null)
 AND (a.iCompanyID = @iCompanyID or @iCompanyID is null)
and (@sdatSignup1 is null or @sdatSignup1<=a.sdatSignUp)
and (@sdatSignup2 is null or datediff(day,a.sdatSignUp,@sdatSignup2)>=0)
and (@vchStatus = 'All'
or (@vchStatus='Active' and a.bRemoved=0 and a.bSuspended=0)
or (@vchStatus='Suspended' and a.bRemoved=0 and a.bSuspended=1)
or (@vchStatus='Inactive' and a.bRemoved=1))
ORDER BY a.sdatSignup descENDsams_admin.FuncMyStores代码  set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description: get all stores a user can see
-- =============================================
ALTER FUNCTION [sams_admin].[FuncMyStores]
(
@iUserID int = null
)
RETURNS 
@stores TABLE 
(
iSiteID int,
vchName varchar(50),
vchURL varchar(50),
    sdatCreateDate datetime)
AS
BEGIN
-- Fill the table variable with the rows for your result set

insert @stores
        select Distinct iSiteID, vchName, vchURL,sdatCreateDate
from sams_storefront.aSiteHistory 
where (@iUserID is null) or (sams_admin.UserHasSecurityGroup(@iUserID,'Administrator') = 1
or sams_admin.UserHasSecurityGroup(@iUserID,'Accounting Personnel') = 1
or sams_admin.UserHasSecurityGroup(@iUserID,'Customer Service Manager') = 1
or sams_admin.UserHasSecurityGroup(@iUserID,'Customer Service Representative') = 1
or @iUserID = iSFMPUserID
or @iUserID = iSDAUserID or @iUserID = iSDA2UserID or @iUserID = iSDA3UserID or @iUserID = iSDA4UserID
or @iUserID = iSDUseriD or @iUserID = iSD2UserID) RETURN 
END

Answer 8

inner join sams_admin.FuncMyStores(@iUserID) b on a.iSiteID = b.iSiteID (这里是个Function拿到相应的属于一个管理员的site列表德) 这个实际上就是一个管理员登陆能看到他权限下能看到的site列表  你可以假设显示全部站点  

Answer 9

现在引入两个新的columns 在SiteHistory表  一个是StartTime,  一个是EndTime   当更新一个site名字的时候  StartTime就是更新的时间  以前的site的endtime就是新的site的starttime