sql语句

2018 年 3 月 10 日2018 年 4 月 2 日 by 沧海之树 In SQLSERVER, 工作

select cbi.Name,cbi.ID,ii.ID+'-'+ii.Name as ProjectName,ccd.ExcutionNumber from CustomerConsumptionDetails ccd
inner join CustomerConsumptionInfo cci on cci.ExcutionNumber=ccd.ExcutionNumber
inner join CustomerBaseInfo cbi on cbi.ID=cci.CustomerID
inner join ItemInfo ii on ii.ID=ccd.ItemID where 1=1
where ccd.ExcutePercent=1 and cbi.ID='CTXW3N07F4'

select cbi.Name,cbi.ID,ii.ID+'-'+ii.Name as ProjectName,ccd.ExcutionNumber from CustomerConsumptionDetails ccd
inner join CustomerConsumptionInfo cci on cci.ExcutionNumber=ccd.ExcutionNumber
inner join CustomerBaseInfo cbi on cbi.ID=cci.CustomerID
inner join ItemInfo ii on ii.ID=ccd.ItemID
inner join SatisfactionSurvey ss on ss.CustomerID=cbi.ID
where 1=1 and ccd.ExcutePercent=1 group by ccd.ExcutionNumber,cbi.Name,cbi.ID,ii.ID,ii.Name

select * from CustomerConsumptionInfo
select * from CustomerConsumptionDetails
select * from

select * from SatisfactionSurvey

select * from CustomerConsumptionDetails

select ss.SurveyID, cbi.Name,cbi.ID,ii.ID ProjectID,ii.ID+'-'+ii.Name as ProjectName,ccd.ExcutionNumber,ss.zlis from CustomerConsumptionDetails ccd
inner join CustomerConsumptionInfo cci on cci.ExcutionNumber=ccd.ExcutionNumber
inner join CustomerBaseInfo cbi on cbi.ID=cci.CustomerID
inner join ItemInfo ii on ii.ID=ccd.ItemID
inner join SatisfactionSurvey ss on ss.CustomerID=cbi.ID
where 1=1 and ccd.ExcutePercent=1
group by ccd.ExcutionNumber,cbi.Name,cbi.ID,ii.ID,ii.Name,ss.SurveyID,ss.zlis

select ss.SurveyID, cbi.Name,cbi.ID,ii.ID ProjectID,ii.ID+'-'+ii.Name as ProjectName,ccd.ExcutionNumber,ss.zlis from SatisfactionSurvey ss
inner join CustomerBaseInfo cbi on cbi.ID=ss.CustomerID

inner join ItemInfo ii on ii.ID=
inner join CustomerConsumptionDetails ccd on ss.CustomerID=cbi.ID
inner join ItemInfo ii on ii.ID=ccd.ItemID
inner join CustomerConsumptionInfo cci on cci.ExcutionNumber=ccd.ExcutionNumber
where 1=1 and ccd.ExcutePercent=1
group by ccd.ExcutionNumber,cbi.Name,cbi.ID,ii.ID,ii.Name,ss.SurveyID,ss.zlis

select * from CustomerConsumptionDetails

select * from CustomerBaseInfo

select ID,CategoryID,TopCategoryID from ItemInfo

select * from CustomerConsumptionInfo

select * from SatisfactionSurvey

select * from ItemCategory;

select ss.CustomerID,ss.CustomerName,ii.CategoryID,ss.SurveyDate,
ii.ID ProjectID,ii.ID+'-'+ii.Name as ProjectName,ccd.ExcutionNumber,ss.zlis from SatisfactionSurvey ss
inner join CustomerConsumptionDetails ccd on ccd.ExcutionNumber=ss.ExecuteNumber
inner join ItemInfo ii on ii.ID=ccd.ItemID
inner join ItemCategory ic on ii.CategoryID=ic.ID
where 1=1 and ccd.ExcutePercent=1
group by ii.ID,ii.Name,ccd.ExcutionNumber,ss.zlis,ss.CustomerID,ss.CustomerName,ii.CategoryID,ss.SurveyDate

select
case ParentID
when '00001' then '整形科'
when '00016' then '皮肤科'
else '其他'
end as 科室,ID as ProjectID, Name as 咨询项目,ID,
0 咨询, 0 回访人数, 0 满意人数, 0 一般人数,0 不满意人数,0 好评率, 0 差评率
from ItemCategory ic
where ParentID<>'00034' order by ID

select * from ItemCategory

=================================================

USE [bh]
GO

/****** Object: View [dbo].[V_CustomerStatus] Script Date: 2018-04-01 16:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[V_CustomerStatus]
as
select
base.ID CustomerID, base.Name CustomerName,
sta.Status StatusID,
(select DetailName from DictDetail where MasterID=2 and InnerID=sta.Status) StatusName,
suc.Success SuccessID,
(select DetailName from DictDetail where MasterID=3 and InnerID=suc.Success) SuccessName,
base.IsCome,
(select DetailName from DictDetail where MasterID=1 and InnerID=base.IsCome) ComeStatus
from CustomerBaseInfo base
left join StatusOfCustomer sta on sta.CustomerID=base.ID
left join SuccessOfCustomer suc on suc.CustomerID=base.ID
where base.Remove<>1
GO

select * from DictDetail where MasterID=1

select * from FinanceTotal

exec sp_executesql N'SELECT [t2].[ChargeDate] AS [ChargeDate], [t0].[ID] AS [Cpid], [t4].[MemberCardNum] AS [CustomerMemberCardNum], [t3].[Name] AS [CustomerName], [t0].[ExecuteStatus] AS [cs],
(CASE
WHEN (CONVERT(Int,[t0].[HandselItemModeID])) IS NOT NULL THEN [t5].[Mode]
ELSE CONVERT(NVarChar(20),@p6)
END) AS [HandselMode], [t1].[Name] AS [ItemName], [t1].[Standard] AS [ItemStandard], [t6].[ItemTypeName] AS [ItemType], [t1].[Unit] AS [ItemUnit], [t0].[Quantity], [t0].[Quantity2] AS [Quantity1], [t0].[Remark], [t0].[Version]
FROM [dbo].[CustomerConsumptionDetails] AS [t0]
INNER JOIN [dbo].[ItemInfo] AS [t1] ON [t1].[ID] = [t0].[ItemID]
INNER JOIN [dbo].[CustomerConsumptionInfo] AS [t2] ON [t2].[ExcutionNumber] = [t0].[ExcutionNumber]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t3] ON [t3].[ID] = [t2].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t4] ON [t4].[CustomerID] = [t3].[ID]
LEFT OUTER JOIN [dbo].[HandselItemMode] AS [t5] ON [t5].[ID] = [t0].[HandselItemModeID]
INNER JOIN [dbo].[ItemTypeInfo] AS [t6] ON [t6].[ID] = [t0].[ItemTypeID]
WHERE ([t0].[CanExecute] = 1) AND ([t0].[ChargeStatus] = @p0) AND (([t1].[MustExcute]) = 1) AND ((
(CASE
WHEN @p1 = 1 THEN
(CASE
WHEN ([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2) THEN 1
WHEN NOT (([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2)) THEN 0
ELSE NULL
END)
WHEN [t4].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t4].[MemberCardNum] LIKE @p3 THEN 1
WHEN NOT ([t4].[MemberCardNum] LIKE @p3) THEN 0
ELSE NULL
END))
ELSE @p4
END)) = 1) AND ([t3].[Name] LIKE @p5) AND ([t2].[ChargeDate] IS NOT NULL)',N'@p0 int,@p1 int,@p2 varchar(8000),@p3 varchar(8000),@p4 int,@p5 nvarchar(4000),@p6 nvarchar(4000)',@p0=1,@p1=0,@p2='%%',@p3='%%',@p4=1,@p5=N'%%',@p6=N''

select cbi.Name,cbi.ID,ii.ID+'-'+ii.Name as ProjectName,ccd.ExcutionNumber from CustomerConsumptionDetails ccd
inner join CustomerConsumptionInfo cci on cci.ExcutionNumber=ccd.ExcutionNumber
inner join CustomerBaseInfo cbi on cbi.ID=cci.CustomerID
inner join ItemInfo ii on ii.ID=ccd.ItemID
inner join SatisfactionSurvey ss on ss.CustomerID=cbi.ID
where 1=1 and ccd.ExcutePercent=1 group by ccd.ExcutionNumber,cbi.Name,cbi.ID,ii.ID,ii.Name

select * from CustomerConsumptionInfo
select * from CustomerConsumptionDetails
select * from exe

select * from SatisfactionSurvey

================================================

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 项目,CountSettingsID,
0 回访人数, 0 满意, 0 一般,0 不满意,0 好评率, 0 差评率, 0 state
from CountSettingsForSubCategory cs where CountSettingsID<>'003'
union all
select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 项目,CountSettingsID,
0 回访人数, 0 满意, 0 一般,0 不满意,0 好评率, 0 差评率, 1 state
from CountSettingsForSubCategory cs
where CountSettingsID<>'003' order by state,CountSettingsID

select ss.CustomerID,ss.CustomerName,ss.CountSettingsSubCategoryID,ss.SurveyDate,suc.Success from SatisfactionSurvey ss
inner join CountSettingsForSubCategory csf on ss.CountSettingsSubCategoryID=csf.ID
inner join CustomerComeLog ccl on ccl.CountSettingsSubCategoryID=ss.CountSettingsSubCategoryID
inner join CustomerBaseInfo cbi on cbi.ID=ss.CustomerID
inner join SuccessOfCustomer suc on suc.CustomerID=ss.CustomerID
where suc.Success=2 group by ss.CountSettingsSubCategoryID,ss.CustomerID,ss.CustomerName,ss.SurveyDate,suc.Success,ss.dyis

select * from CustomerBaseInfo where ID='CTXW3N07F4'

select * from CustomerComeLog where CustomerID='CTXW3N07F4'

select * from DictDetail where MasterID=3

" and SurveyDate> '2018-01-31 00:00:00' and SurveyDate<'2018-04-01 00:00:00'"

select ss.CustomerID,ss.CustomerName,ss.CountSettingsSubCategoryID,ss.SurveyDate,suc.Success,ss.dyis from SatisfactionSurvey ss
inner join CountSettingsForSubCategory csf on ss.CountSettingsSubCategoryID=csf.ID
inner join CustomerComeLog ccl on ccl.CountSettingsSubCategoryID=ss.CountSettingsSubCategoryID
inner join CustomerBaseInfo cbi on cbi.ID=ss.CustomerID
inner join SuccessOfCustomer suc on suc.CustomerID=ss.CustomerID
and SurveyDate> '2018-01-31 00:00:00' and SurveyDate<'2018-04-01 00:00:00'
group by ss.CountSettingsSubCategoryID,ss.CustomerID,ss.CustomerName,ss.SurveyDate,suc.Success,ss.dyis

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 项目,CountSettingsID,
0 前台导诊, 0 回访人数, 0 满意, 0 一般,0 不满意,0 好评率, 0 差评率, 0 state
from CountSettingsForSubCategory cs where CountSettingsID<>'003'
union all
select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 项目,CountSettingsID,
0 前台导诊, 0 回访人数, 0 满意, 0 一般,0 不满意,0 好评率, 0 差评率, 1 state
from CountSettingsForSubCategory cs
where CountSettingsID<>'003' order by state,CountSettingsID

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 咨询项目,CountSettingsID,
0 前台导诊, 0 回访人数, 0 满意人数, 0 一般人数,0 不满意人数,0 好评率, 0 差评率, '未成交' state
from CountSettingsForSubCategory cs where CountSettingsID<>'003'
union all
select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 咨询项目,CountSettingsID,
0 前台导诊, 0 回访人数, 0 满意人数, 0 一般人数,0 不满意人数,0 好评率, 0 差评率, '成交' state
from CountSettingsForSubCategory cs
where CountSettingsID<>'003' order by state,CountSettingsID

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 咨询项目,CountSettingsID,
0 前台导诊, 0 回访人数, 0 满意人数, 0 一般人数,0 不满意人数,0 好评率, 0 差评率, '未成交' 类型
from CountSettingsForSubCategory cs where CountSettingsID<>'003'
union all
select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 咨询项目,CountSettingsID,
0 前台导诊, 0 回访人数, 0 满意人数, 0 一般人数,0 不满意人数,0 好评率, 0 差评率, '成交' 类型
from CountSettingsForSubCategory cs
where CountSettingsID<>'003' order by 类型,CountSettingsID

select ss.CustomerID,ss.CustomerName,ss.CountSettingsSubCategoryID,ss.SurveyDate,suc.Success,ss.dyis from SatisfactionSurvey ss
inner join CountSettingsForSubCategory csf on ss.CountSettingsSubCategoryID=csf.ID
inner join CustomerComeLog ccl on ccl.CountSettingsSubCategoryID=ss.CountSettingsSubCategoryID
inner join CustomerBaseInfo cbi on cbi.ID=ss.CustomerID
inner join SuccessOfCustomer suc on suc.CustomerID=ss.CustomerID where 1=1 and dyis=1 and suc.Success=1 and SurveyDate> '2012-03-31 00:00:00' and SurveyDate<'2018-04-01 00:00:00' and ss.CountSettingsSubCategoryID=5
group by ss.CountSettingsSubCategoryID,ss.CustomerID,ss.CustomerName,ss.SurveyDate,suc.Success,ss.dyis

select * from SatisfactionSurvey

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 咨询项目,CountSettingsID,
0 咨询, 0 回访人数, 0 满意人数, 0 一般人数,0 不满意人数,0 好评率, 0 差评率
from CountSettingsForSubCategory cs
where CountSettingsID<>'003' order by CountSettingsID

=========================================

union all

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 项目,CountSettingsID,
0 执行医生, 0 回访人数, 0 满意, 0 一般,0 不满意,0 好评率, 0 差评率, 1 state
from CountSettingsForSubCategory cs
where CountSettingsID<>'003' order by state,CountSettingsID

select * from CountSettingsForSubCategory

delete from [SatisfactionSurvey]

drop table [SatisfactionSurvey];

select * from CustomerComeLog

select csf.ID,csf.CountSettingsID+','+csf.Name as Name,csf.CountSettingsID from CustomerComeLog ccl
inner join CountSettingsForSubCategory csf on csf.ID=ccl.CountSettingsSubCategoryID where ccl.CustomerID='CTXW3N07F4'

select * from SatisfactionSurvey;

select * from SuccessOfCustomer where CustomerID='CTXW3N07F4';

select ss.SurveyID,suc.Success from SatisfactionSurvey ss
inner join CustomerComeLog cc on cc.CustomerID=ss.CustomerID
inner join SuccessOfCustomer suc on suc.CustomerID=ss.CustomerID where ss.CustomerID='CTXW3N07F4' group by ss.SurveyID,suc.Success

USE [bh]
GO

/****** Object: Table [dbo].[SatisfactionSurvey] Script Date: 2018-03-30 14:10:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SatisfactionSurvey](
[SurveyID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [varchar](20) NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[HandleBy] [varchar](5) NOT NULL,
[SurveyDate] [datetime] NULL,
[dyis] [tinyint] NOT NULL,
[Remarkdy] [nvarchar](200) NULL,
[zxis] [tinyint] NOT NULL,
[Remarkzx] [nvarchar](200) NULL,
[zlis] [tinyint] NOT NULL,
[Remarkzl] [nvarchar](200) NULL,
[shis] [tinyint] NOT NULL,
[Remarksh] [nvarchar](200) NULL,
[MobileNo] [varchar](20) NOT NULL,
[CountSettingsID] [varchar](3) NOT NULL,
[CountSettingsSubCategoryID] [int] NOT NULL,
CONSTRAINT [PK_SatisfactionSurvey] PRIMARY KEY CLUSTERED
(
[SurveyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SatisfactionSurvey] WITH CHECK ADD CONSTRAINT [FK_SatisfactionSurvey_CustomerBaseInfo] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[CustomerBaseInfo] ([ID])
GO

ALTER TABLE [dbo].[SatisfactionSurvey] CHECK CONSTRAINT [FK_SatisfactionSurvey_CustomerBaseInfo]
GO

ALTER TABLE [dbo].[SatisfactionSurvey] WITH CHECK ADD CONSTRAINT [FK_SatisfactionSurvey_Employee] FOREIGN KEY([HandleBy])
REFERENCES [dbo].[Employee] ([ID])

ALTER TABLE [dbo].[SatisfactionSurvey] WITH CHECK ADD CONSTRAINT [FK_SatisfactionSurvey_CountSettingsForSubCategory] FOREIGN KEY([CountSettingsSubCategoryID])
REFERENCES [dbo].[CountSettingsForSubCategory] ([ID])
GO

ALTER TABLE [dbo].[SatisfactionSurvey] CHECK CONSTRAINT [FK_SatisfactionSurvey_Employee]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调查编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'SurveyID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'CustomerName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'经手人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'HandleBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调查时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'SurveyDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对导医是否满意,0-不满意,1-一般,2-满意' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'dyis'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对导医满意度情况加备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'Remarkdy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对咨询是否满意,0-不满意,1-一般,2-满意' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'zxis'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对咨询满意度情况加备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'Remarkzx'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对手术是否满意,0-不满意,1-一般,2-满意' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'zlis'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对手术满意度情况加备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'Remarkzl'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对术后是否满意,0-不满意,1-一般,2-满意' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'shis'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对术后满意度情况加备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'Remarksh'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手术所属二级分类栏目' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SatisfactionSurvey', @level2type=N'COLUMN',@level2name=N'CountSettingsID'
GO

==================================================

2018-03-22

DROP PROCEDURE testproc;

create procedure testproc
AS
DECLARE @u_id VARCHAR (25);
DECLARE @u_name VARCHAR (255) ;
DECLARE @age int;
--声明游标
--select中的参数，必须要和游标取出来的变量名相同
DECLARE mycursor CURSOR
FOR
SELECT ID,Name,Age FROM CustomerBaseInfo where RegisterDate>'2018-03-01 00:00:00' ;
--打开游标
OPEN mycursor ;
--从游标中取出数据，赋值到刚才定义的3个变量中
FETCH NEXT FROM mycursor INTO @u_id ,@u_name ,@age ;
--判断游标的状态
--0 fetch语句成功
--1 fetch语句失败或者此行不在结果集中
--2 提取的行不存在
WHILE (@@fetch_status = 0)
BEGIN
--print控制输出时，一定要转成相同的类型
PRINT('编号：'+convert(varchar(11),@u_id)+', 姓名：'+@u_name+', 年龄：'+convert(varchar(11),@age));
FETCH NEXT FROM mycursor INTO @u_id ,@u_name ,@age;
END;
--关闭游标
CLOSE mycursor;
--释放游标
DEALLOCATE mycursor;

exec bh.dbo.testproc

SELECT * FROM CustomerBaseInfo where RegisterDate>'2018-03-01 00:00:00' ;

=====================================================

alter database yuanchuang1 modify name=yuanchuang

USE master;
GO
ALTER DATABASE yuanchuang
SET SINGLE_USER --变成单用户模式，获取独占访问
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE yuanchuang
SET READ_ONLY;
GO

ALTER DATABASE yuanchuang
SET READ_WRITE;
GO

ALTER DATABASE yuanchuang
SET MULTI_USER;
GO

alter database yuanchuang3 modify name=yuanchuang

alter database yuanchuang modify name=yuanchuang3

alter database yuanchuang2 modify name=yuanchuang

USE master;
GO
ALTER DATABASE yuanchuang2
SET SINGLE_USER --变成单用户模式，获取独占访问
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE yuanchuang
SET READ_WRITE;
GO

ALTER DATABASE yuanchuang
SET MULTI_USER;
GO

ALTER DATABASE yuanchuang3
SET READ_WRITE;
GO

ALTER DATABASE yuanchuang3
SET MULTI_USER;
GO

USE master;
GO
ALTER DATABASE yuanchuang
SET SINGLE_USER --变成单用户模式，获取独占访问
WITH ROLLBACK IMMEDIATE;
GO
alter database yuanchuang modify name=yuanchuang7
GO
ALTER DATABASE yuanchuang7
SET READ_ONLY;
GO

alter database yuanchuang modify name=yuanchuang5
GO

USE master;
GO
ALTER DATABASE yuanchuang6
SET SINGLE_USER --变成单用户模式，获取独占访问
WITH ROLLBACK IMMEDIATE;
GO
alter database yuanchuang6 modify name=yuanchuang
GO

ALTER DATABASE yuanchuang
SET READ_WRITE;
GO

ALTER DATABASE yuanchuang
SET MULTI_USER;
GO

select name from master..sysdatabases

=================================================================

select fin.TMKEmployee, count(fin.AllTotal) money, sta.Status, suc.Success

select count(case when fin.AllTotal > 100000 then 1 end) as bigdeal1,
count(case when fin.AllTotal between 50000 and 100000 then 1 end) as bigdeal2,
count(case when fin.AllTotal between 30000 and 50000 then 1 end) as bigdeal3,
count(case when fin.AllTotal between 10000 and 30000 then 1 end) as bigdeal4
from FinanceTotal fin
inner join CustomerBaseInfo base on base.ID = fin.CustomerID
inner join Employee emp on emp.ID = base.SceneEmployee
inner join StatusOfCustomer sta on sta.CustomerID = base.ID
inner join SuccessOfCustomer suc on suc.CustomerID = base.ID
where fin.SceneEmployee = '00006' and Date > '2012-03-08 00:00:00' and Date < '2018-03-09 00:00:00'

select count(case when fin.AllTotal > 100000 then 1 else 0 end) as bigdeal1,
count(case when fin.AllTotal between 50000 and 100000 then 1 else 0 end) as bigdeal2,
count(case when fin.AllTotal between 30000 and 50000 then 1 else 0 end) as bigdeal3,
count(case when fin.AllTotal between 10000 and 30000 then 1 else 0 end) as bigdeal4
from FinanceTotal fin
inner join CustomerBaseInfo base on base.ID = fin.CustomerID
inner join Employee emp on emp.ID = base.TMKEmployee
inner join StatusOfCustomer sta on sta.CustomerID = base.ID
inner join SuccessOfCustomer suc on suc.CustomerID = base.ID
where fin.TMKEmployee = '00116' and Date > '2012-03-08 00:00:00' and Date < '2018-03-09 00:00:00'

select sub.ID subID, sub.Name 部位, dept.ID deptID, dept.Name 部门, emp.ID empID,
emp.Name 姓名, sub.ContainsCategories, sub.ContainsConsultingCategories, 0 初诊上门量, 0 初诊成交量,
0 复诊上门量, 0 复诊成交量, 0 老顾客上门量, 0 老顾客成交量
from CountSettingsForSubCategory sub, Employee emp, PositionInfo pos, Department dept
where sub.ID in (4, 5, 6, 7, 11) and emp.IsConsultation = 1 and emp.Valid = 1 and emp.PositionID = pos.ID and pos.DepartmentID
=
dept.ID
order by sub.ID, dept.ID, emp.ID

select (case when cate.ParentID = '0' then cate.ID end) as 一级部门编号, cate.Name 一级部门, -- (case when cate.ParentID<>"0" then cate.ID end) as 二级部门编号, cate.Name 二级部门 ,
0 初诊上门量,
0 初诊成交量, 0 初诊成交金额, 0 初诊成交率,
0 初诊人均消费, 0 复诊上门量, 0 复诊成交量,
0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费,
0 再消费上门量, 0 再消费成交量, 0 再消费成交金额,
0 再消费成交率, 0 再消费人均消费, 0 复查上门量,
0 总成交量, 0 总成交金额, 0 总成交率,
0 总人均消费, 0 其他金额, 0 合计总业绩
from ItemCategory cate,
Department dept
inner join cate ON cate.ID = info.TopCategoryID
where sub.ID in (4, 5, 6, 7, 11)
order by sub.ID, dept.ID, emp.ID

select cate.ParentID as 一级部门,cate.[Name] as 一级部门名称, (case when cate.ParentID<>'0' then cate.ID end) as 二级部门编号, cate.Name 二级部门 ,
0 初诊上门量, 0 初诊成交量, 0 初诊成交金额, 0 初诊成交率, 0 初诊人均消费,0 复诊上门量,
0 复诊成交量, 0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费, 0 再消费上门量,0 再消费成交量,
0 再消费成交金额, 0 再消费成交率, 0 再消费人均消费, 0 复查上门量, 0 总成交量,0 总成交金额,
0 总成交率, 0 总人均消费, 0 其他金额, 0 合计总业绩
from ItemCategory cate
--inner join Department dpt on cate.ParentID=dpt.ID
where cate.ParentID<>'0'

select * from ItemCategory

--group by cate.ParentID,cate.ID,cate.Name

select a.ID sonID,a.Name SonName,a.ParentID,b.ID,b.name,
0 初诊上门量, 0 初诊成交量, 0 初诊成交金额, 0 初诊成交率, 0 初诊人均消费,0 复诊上门量,
0 复诊成交量, 0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费, 0 再消费上门量,0 再消费成交量,
0 再消费成交金额, 0 再消费成交率, 0 再消费人均消费, 0 复查上门量, 0 总成交量,0 总成交金额,
0 总成交率, 0 总人均消费, 0 其他金额, 0 合计总业绩
from
(select * from ItemCategory where ParentID='0') a
left join
(select * from ItemCategory where ParentID<>'0') b
on a.ID=b.ParentID

select come.ID,come.CustomerID ,sta.Status,cc.Name,
suc.Success
from CustomerComeLog come
inner join CountSettingsForSubCategory cc on come.CountSettingsID=cc.CountSettingsID
inner join CustomerBaseInfo base on base.ID = come.CustomerID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID
group by come.ID,come.CustomerID,sta.Status,suc.Success,cc.[Name]
-- CountSettingsSubCategoryID

select * from CustomerComeLog where CountSettingsID
select * from CountSettings
select * from dbo.CountSettingsForSubCategory

select a.ID sonID,a.Name 科室,a.ParentID,b.ID,b.name 项目,
0 初诊上门量, 0 初诊成交量, 0 初诊成交金额, 0 初诊成交率, 0 初诊人均消费,0 复诊上门量,
0 复诊成交量, 0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费, 0 再消费上门量,0 再消费成交量,
0 再消费成交金额, 0 再消费成交率, 0 再消费人均消费, 0 复查上门量, 0 总成交量,0 总成交金额,
0 总成交率, 0 总人均消费, 0 其他金额, 0 合计总业绩
from
(select * from ItemCategory where ParentID='0') a
left join
(select * from ItemCategory where ParentID<>'0') b
on a.ID=b.ParentID

select come.ID,come.CustomerID,sta.Status,suc.Success,base.SceneEmployee,come.CountSettingsID
from CustomerComeLog come
inner join CountSettingsForSubCategory cc on come.CountSettingsID=cc.CountSettingsID
inner join CustomerBaseInfo base on base.ID = come.CustomerID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID
group by come.ID,come.CustomerID,sta.Status,suc.Success,base.SceneEmployee,come.CountSettingsID

select * from PositionInfo
select ID,Name from Employee where PositionID='05' or PositionID='04' and WorkStatus='在职';
select * from Employee
select * from CountSettings
select * from ItemCategory

select a.ID sonID,a.Name SonName,a.ParentID,b.ID,b.name,
0 初诊上门量, 0 初诊成交量, 0 初诊成交金额, 0 初诊成交率, 0 初诊人均消费,0 复诊上门量,
0 复诊成交量, 0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费, 0 再消费上门量,0 再消费成交量,
0 再消费成交金额, 0 再消费成交率, 0 再消费人均消费, 0 复查上门量, 0 总成交量,0 总成交金额,
0 总成交率, 0 总人均消费, 0 其他金额, 0 合计总业绩
from
(select * from CountSettingsForSubCategory where ParentID='0') a
left join
(select * from CountSettingsForSubCategory where ParentID<>'0') b
on a.ID=b.ParentID

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as 项目ID, Name as 项目,
0 初诊上门量, 0 初诊成交量, 0 初诊成交金额, 0 初诊成交率, 0 初诊人均消费,0 复诊上门量,
0 复诊成交量, 0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费, 0 再消费上门量,0 再消费成交量,
0 再消费成交金额, 0 再消费成交率, 0 再消费人均消费, 0 复查上门量, 0 总成交量,0 总成交金额,
0 总成交率, 0 总人均消费, 0 其他金额, 0 合计总业绩
from CountSettingsForSubCategory cs where CountSettingsID<>'003'
order by CountSettingsID

select * from CountSettingsForSubCategory

select come.ID,come.CustomerID ,sta.Status,suc.Success,base.SceneEmployee,come.CountSettingsID
from CustomerComeLog come
inner join CountSettingsForSubCategory cc on come.CountSettingsID=cc.CountSettingsID
inner join CustomerBaseInfo base on base.ID = come.CustomerID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID

select CountSettingsSubCategoryID from CustomerComeLog

select come.ID,come.CustomerID ,sta.Status,suc.Success,base.SceneEmployee,come.CountSettingsID,come.CountSettingsSubCategoryID
from CustomerComeLog come
inner join CountSettingsForSubCategory cc on come.CountSettingsID=cc.CountSettingsID
inner join CustomerBaseInfo base on base.ID = come.CustomerID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID group by come.ID,come.CustomerID,sta.Status,suc.Success,base.SceneEmployee,come.CountSettingsID,come.CountSettingsSubCategoryID

select sub.ID subID, sub.Name 部位, dept.ID deptID, dept.Name 部门, emp.ID empID, emp.Name 姓名,
sub.ContainsCategories, sub.ContainsConsultingCategories,
0 初诊上门量, 0 初诊成交量, 0 复诊上门量, 0 复诊成交量, 0 老顾客上门量,0 老顾客成交量
from CountSettingsForSubCategory sub, Employee emp, PositionInfo pos, Department dept
where
sub.ID in(4,5,6,7,11)
and emp.IsConsultation=1
and emp.Valid=1
and emp.PositionID=pos.ID
and pos.DepartmentID=dept.ID
order by sub.ID

select
case CountSettingsID
when '001' then '整形科'
when '002' then '皮肤科'
when '004' then '微整科'
else '其他'
end as 科室,ID as ProjectID, Name as 项目,
0 初诊上门量, 0 初诊成交量, 0 初诊成交金额, 0 初诊成交率, 0 初诊人均消费,0 复诊上门量,
0 复诊成交量, 0 复诊成交金额, 0 复诊成交率, 0 复诊人均消费, 0 再消费上门量,0 再消费成交量,
0 再消费成交金额, 0 再消费成交率, 0 再消费人均消费, 0 复查上门量, 0 总成交量,0 总成交金额,
0 总成交率, 0 总人均消费, 0 其他金额, 0 合计总业绩
from CountSettingsForSubCategory cs where CountSettingsID<>'003'
order by 科室

select sum(fin.AllTotal) money, sta.Status,base.NetChannelID,nc.NetName
from FinanceTotal fin
inner join CustomerBaseInfo base on base.ID = fin.CustomerID
inner join CountSettingsForSubCategory cc on cc.CountSettingsID=fin.ItemID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID

select ft.*,cc.ContainsConsultingCategories from FinanceTotal ft,CountSettingsForSubCategory cc
where charindex(ft.ItemID,cc.ContainsConsultingCategories)>0

select CategoryID from ItemInfo iifo, FinanceTotal ft where iifo.ID=ft.ItemID
select sum(AllTotal) from FinanceTotal where AllTotal>0

select * from ItemInfo where ID='00945'
select * from FinanceTotal where ItemID = (select ContainsConsultingCategories from CountSettingsForSubCategory)

select * from CustomerComeLog
select ContainsConsultingCategories from CountSettingsForSubCategory

select * from CountSettingsForSubCategory

select cc.ID,cc.Name,sum(ft.AllTotal),cc.CountSettingsID,ft.SceneEmployee from ItemInfo info
inner join FinanceTotal ft ON ft.ItemID = info.ID
inner join CountSettingsForSubCategory cc on charindex(info.CategoryID,cc.ContainsCategories)>0
inner join CustomerBaseInfo base on base.ID = ft.CustomerID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID
group by cc.ID, cc.Name, cc.CountSettingsID,ft.SceneEmployee

select * from CountSettingsForSubCategory;

select * from CustomerAppendInfo where CustomerID='CTXW3N07F4';
select * from CustomerBaseInfo where ID='CTXW3N07F4'

select * from CustomerPhoneNumber where CustomerID='46WW3ESXEQ';

select * from CountSettingsForSubCategory

select * from ModelChargedItems

select * from FinanceTotal where CustomerID='CTXW3N07F4'

select * from ItemInfo where ID='00072';

SELECT [t0].[Date], [t1].[Name] AS [HandleBy], [t3].[Name] AS [HandleByDept], [t0].[ID]
FROM [dbo].[CustomerComeLog] AS [t0]
INNER JOIN [dbo].[Employee] AS [t1] ON [t1].[ID] = [t0].[HandleBy]
INNER JOIN [dbo].[PositionInfo] AS [t2] ON [t2].[ID] = [t1].[PositionID]
INNER JOIN [dbo].[Department] AS [t3] ON [t3].[ID] = [t2].[DepartmentID]
WHERE [t0].[CustomerID] = 'JZ180117022'
ORDER BY [t0].[Date]

select * from CustomerComeLog

select * from CustomerComeLog
select * from CustomerBaseInfo
select * from CustomerRegion

select count(*),cr.ID,cr.Region,sta.Status,suc.Success from CustomerRegion cr
inner join CustomerBaseInfo cbi on cbi.RegionID = cr.ID
inner join CustomerComeLog ccl on ccl.CustomerID=cbi.ID
inner join StatusOfCustomer sta on sta.CustomerID=cbi.ID
inner join SuccessOfCustomer suc on suc.CustomerID=cbi.ID
group by cr.ID, cr.Region,sta.Status,suc.Success

select count(*) from CustomerComeLog
select * from FinanceTotal

select ID ,Region as 来源地域,0 客人总数,0 成交总数, 0 初诊咨询数量,0 初诊成交数量,
0 复诊咨询数量,0 复诊成交数量,0 再消费成交数量,0 初诊收入数据,
0 复诊收入数据,0 再消费收入数据,0 其他收入,0 收入合计 from CustomerRegion

from CustomerRegion

select cr.ID,cr.Region,sum(ft.AllTotal) money,sta.Status,suc.Success from CustomerRegion cr

inner join CustomerBaseInfo base on cr.ID = base.RegionID
inner join FinanceTotal ft on ft.CustomerID = base.ID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID
inner join SuccessOfCustomer suc on suc.CustomerID=base.ID
group by cr.ID, cr.Region, sta.Status,suc.Success

select * from CustomerComeLog

select * from CustomerBaseInfo

select * from ConsultingProjects

select ID SourceID ,Region as 来源地域,0 客人总数,0 成交总数, 0 初诊咨询数量,0 初诊成交数量,
0 复诊咨询数量,0 复诊成交数量,0 再消费成交数量,0 初诊收入数据,
0 复诊收入数据,0 再消费收入数据,0 其他收入,0 收入合计 from CustomerRegion

select base.SceneEmployee,cp.ItemCategoryID,count(cp.ItemCategoryID) 数量
from CustomerComeLog ccl
inner join CustomerBaseInfo base on base.ID = ccl.CustomerID
inner join Employee emp on emp.ID = base.SceneEmployee
--inner join StatusOfCustomer sta on sta.CustomerID = base.ID
--inner join SuccessOfCustomer suc on suc.CustomerID = base.ID
inner join ConsultingProjects cp on cp.CustomerID=base.ID
group by base.SceneEmployee,cp.ItemCategoryID

select base.SceneEmployee,cp.ItemCategoryID,count(cp.ItemCategoryID) 数量
from CustomerComeLog ccl
inner join CustomerBaseInfo base on base.ID = ccl.CustomerID
inner join Employee emp on emp.ID = base.SceneEmployee
inner join ConsultingProjects cp on cp.CustomerID=base.ID
group by base.SceneEmployee,cp.ItemCategoryID

select * from CustomerComeLog

select TMKEmployee,ItemID, AllTotal from FinanceTotal where ItemID<>''

CountSettingsID

select * from CountSettingsForSubCategory

select come.CountSettingsID,come.CountSettingsSubCategoryID, csc.Name categoryname, emp.Name consultname, base.SceneEmployee, come.Date, 1 count
from CustomerComeLog come
inner join CustomerBaseInfo base on base.ID = come.CustomerID
inner join Employee emp on emp.ID=base.SceneEmployee
inner join CountSettingsForSubCategory csc on csc.ID=come.CountSettingsSubCategoryID
where 1=1 and Date> '2012-03-14 00:00:00' and Date<'2018-03-15 00:00:00'

order by come.CountSettingsSubCategoryID
--inner join StatusOfCustomer sta on sta.CustomerID=base.ID
--inner join SuccessOfCustomer suc on suc.CustomerID=base.ID