sql – 正确使用Count函数

如果Enrollment_Changes没有后续更改,则订户更改为TO的电话将列在P_Enrollment表中

例如,订户12345678在2011年1月5日注册了RAZR. 2011年11月1日,他改变了RAZR.您可以通过2012年5月19日的Enrollment_Changes上的下一个事务查看他更改了TO的内容.

您如何找到首次使用iPhone 3注册的子计数？

这是我创建表的代码

创建表：TBL 1

USE [Test2]
GO

/****** Object:  Table [dbo].[P_ENROLLMENT]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[P_ENROLLMENT](
    [Subid ] [float] NULL,[Enrollment_Date] [datetime] NULL,[Channel] [nvarchar](255) NULL,[Region] [nvarchar](255) NULL,[Active_Status] [float] NULL,[Drop_Date] [datetime] NULL,[Phone_Model] [nvarchar](255) NULL
) ON [PRIMARY]

GO

TBL 2

USE [Test2]
GO

/****** Object:  Table [dbo].[ENROLLMENT_CHANGES]     ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ENROLLMENT_CHANGES](
    [Subid] [float] NULL,[Cdate] [datetime] NULL,[Phone_Model] [nvarchar](255) NULL
) ON [PRIMARY]

GO

插入TBL1

INSERT INTO [P_ENROLLMENT]([Subid ],[Enrollment_Date],[Channel],[Region],[Active_Status],[Drop_Date],[Phone_Model]) 
    VALUES(12345678,'2011-01-05 00:00:00','Retail','Southeast',1,NULL,'iPhone 4');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12346178,'2011-03-13 00:00:00','Indirect Dealers','West','HTC Hero');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12346679,'2011-05-19 00:00:00','2012-03-15 00:00:00','Droid 2');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12347190,'2011-07-25 00:00:00','Northeast','2012-05-21 00:00:00',[Phone_Model]) 
    VALUES(12347701,'2011-08-14 00:00:00',[Phone_Model]) 
    VALUES(12348212,'2011-09-30 00:00:00',[Phone_Model]) 
    VALUES(12348723,'2011-10-20 00:00:00','Southeast');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12349234,'2012-01-06 00:00:00','2012-02-14 00:00:00','West');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12349745,'2012-01-26 00:00:00','2012-04-15 00:00:00',[Phone_Model]) 
    VALUES(12350256,'2012-02-11 00:00:00',[Phone_Model]) 
    VALUES(12350767,'2012-03-02 00:00:00','Sidekick');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12351278,'2012-04-18 00:00:00','Midwest','iPhone 3');

INSERT INTO [P_ENROLLMENT]([Subid ],[Phone_Model]) 
    VALUES(12351789,'2012-05-08 00:00:00','2012-07-04 00:00:00',[Phone_Model]) 
    VALUES(12352300,'2012-06-24 00:00:00',[Phone_Model]) 
    VALUES(12352811,'2012-06-25 00:00:00','Sidekick');

插入TBL2

INSERT INTO [ENROLLMENT_CHANGES]([Subid],[Cdate],'2011-11-01 00:00:00','RAZR');

INSERT INTO [ENROLLMENT_CHANGES]([Subid],'2012-01-07 00:00:00','HTC Hero');

INSERT INTO [ENROLLMENT_CHANGES]([Subid],'2012-01-28 00:00:00','2012-02-21 00:00:00','Blackberry Bold');

INSERT INTO [ENROLLMENT_CHANGES]([Subid],'2012-05-05 00:00:00','2012-05-19 00:00:00','Palm Pre');

INSERT INTO [ENROLLMENT_CHANGES]([Subid],'2012-05-20 00:00:00','2012-06-04 00:00:00','2012-06-05 00:00:00','iPhone 3');

INSERT INTO [ENROLLMENT_CHANGES]([Subid],'2012-06-10 00:00:00','iPhone 3');

解决方法

select COUNT(*) Total
from
(
    select e.*,rn = row_number() over (partition by e.subid order by c.cdate desc),first_model = coalesce(c.phone_model,e.phone_model)
    from [P_ENROLLMENT] e
    left join [ENROLLMENT_CHANGES] c on c.subid = e.subid
) x
where rn=1 and first_model = 'iPhone 3'

对于所有记录

select *
from
(
    select e.*,e.phone_model)
    from [P_ENROLLMENT] e
    left join [ENROLLMENT_CHANGES] c on c.subid = e.subid
) x
where rn=1 and first_model = 'iPhone 3'
order by subid

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!