USE DP6_Siemens
GOALTER PROCEDURE [dbo].[Siemens_Confirm_XS]
@BEGINTIME DATETIME ,
@ENDTIME DATETIME,
@CorpId INT
AS
BEGIN
--要发出的信息内容
INSERT INTO DP_SMSOUTQUEUE(CORPID , MOBILE , CONTENT , SendChannel , AttemptTimes , Operator)
SELECT CORPID=@CorpId ,
dbo.GetMobileByMemberId(a.MemberId) AS MOBILE ,
+'西门子dp平台核实:你好,你昨日共上报'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店的月进货额,总进货额'+CAST(ISNULL(a.BB,0)AS VARCHAR(10))+'元,已上报门店为:'+CAST(ISNULL(a.CC,0)AS VARCHAR(10))
AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),1) AS SENDCHANNEL ,
0 AS ATTEMPTTIMES ,
'Siemens_Confirm_XS' AS OPERATOR
--内容的值
FROM ( SELECT dce.MemberId ,
COUNT(DISTINCT CASE dce.Instruction
WHEN 'XS' THEN dce.StoreId
END) AA,
SUM(CASE dce.Instruction
WHEN 'XS' THEN CAST(dce.Param1 AS INT)
END) BB,
(SELECT NIMEI FROM (
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
(SELECT StoreId+' ' FROM dbo.DP_MemberStore WHERE MemberId=AA.MemberId FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B)L) CC
FROM dbo.DP_CommonSmsEntries dce
LEFT JOIN dbo.DP_SmsInProcess SIP ON dce.NewSmsId = SIP.NewSmsId
AND dce.CorpId = SIP.CorpId
LEFT JOIN dbo.DP_Members c ON c.CorpId=dce.CorpId AND SIP.MemberId = c.MemberId
AND c.Status=2 AND c.RoleId=7
WHERE dce.CorpId=@CorpId
AND dce.RecvTime >= @BEGINTIME
AND dce.RecvTime < @ENDTIME
AND dce.Instruction ='XS'
GROUP BY dce.MemberId
) a
ENDGO运行存储过程EXEC [dbo].[Siemens_Confirm_XS]
@BEGINTIME = N'2013-02-03',
@ENDTIME = N'2013-02-07',
@CorpId = 1弹出提示:消息 512,级别 16,状态 1,过程 Siemens_Confirm_XS,第 10 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。 主要是FOR XML PATH('')输出东西,我想一个MenberId输出一列StorsId,可是貌似值的个数对不上,求大神帮助xml
GOALTER PROCEDURE [dbo].[Siemens_Confirm_XS]
@BEGINTIME DATETIME ,
@ENDTIME DATETIME,
@CorpId INT
AS
BEGIN
--要发出的信息内容
INSERT INTO DP_SMSOUTQUEUE(CORPID , MOBILE , CONTENT , SendChannel , AttemptTimes , Operator)
SELECT CORPID=@CorpId ,
dbo.GetMobileByMemberId(a.MemberId) AS MOBILE ,
+'西门子dp平台核实:你好,你昨日共上报'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店的月进货额,总进货额'+CAST(ISNULL(a.BB,0)AS VARCHAR(10))+'元,已上报门店为:'+CAST(ISNULL(a.CC,0)AS VARCHAR(10))
AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),1) AS SENDCHANNEL ,
0 AS ATTEMPTTIMES ,
'Siemens_Confirm_XS' AS OPERATOR
--内容的值
FROM ( SELECT dce.MemberId ,
COUNT(DISTINCT CASE dce.Instruction
WHEN 'XS' THEN dce.StoreId
END) AA,
SUM(CASE dce.Instruction
WHEN 'XS' THEN CAST(dce.Param1 AS INT)
END) BB,
(SELECT NIMEI FROM (
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
(SELECT StoreId+' ' FROM dbo.DP_MemberStore WHERE MemberId=AA.MemberId FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B)L) CC
FROM dbo.DP_CommonSmsEntries dce
LEFT JOIN dbo.DP_SmsInProcess SIP ON dce.NewSmsId = SIP.NewSmsId
AND dce.CorpId = SIP.CorpId
LEFT JOIN dbo.DP_Members c ON c.CorpId=dce.CorpId AND SIP.MemberId = c.MemberId
AND c.Status=2 AND c.RoleId=7
WHERE dce.CorpId=@CorpId
AND dce.RecvTime >= @BEGINTIME
AND dce.RecvTime < @ENDTIME
AND dce.Instruction ='XS'
GROUP BY dce.MemberId
) a
ENDGO运行存储过程EXEC [dbo].[Siemens_Confirm_XS]
@BEGINTIME = N'2013-02-03',
@ENDTIME = N'2013-02-07',
@CorpId = 1弹出提示:消息 512,级别 16,状态 1,过程 Siemens_Confirm_XS,第 10 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。 主要是FOR XML PATH('')输出东西,我想一个MenberId输出一列StorsId,可是貌似值的个数对不上,求大神帮助xml
解决方案 »
- ================= TSQL XML列实现相关新闻的功能 =================
- 根据当前主表的ID获取下面子表信息的问题
- SQL统计
- 急求一存储过程写法!
- 一个统计报表的问题,有点难度,请高手解决
- 急!急!在线等 --SQL2005 设置种子标示 但在插入数据时发生跳跃
- 为什么在 VS.NET2003 的示例与快速入门教程中无法访问数据库?
- 请教zjcxc(邹建) ( )和zhangzs8896(小二) 等高手~~~~
- 用ASP判断表中字段是否存在并增加
- 如何改变数据库实例的名称???
- 明细表统计出错了,详情请点击....
- ---------------数据库排序规则修改------------------------------
(SELECT NIMEI FROM
(SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI
FROM (SELECT AA.MemberId,(SELECT StoreId+' ' FROM dbo.DP_MemberStore WHERE MemberId=AA.MemberId FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B
)L
) CC
这一大句,换成一句即可
stuff((SELECT ' '+StoreId FROM dbo.DP_MemberStore WHERE MemberId=dce.MemberId FOR XML PATH('')),1,1,'') as NIMEI
如果出现在分组中,应该加个聚合max(stuff())
GO/****** Object: StoredProcedure [dbo].[Siemens_Confirm_XS] Script Date: 02/05/2013 09:42:08 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[Siemens_Confirm_XS]
@BEGINTIME DATETIME ,
@ENDTIME DATETIME,
@CorpId INT
AS
BEGIN
CREATE TABLE #aa(memberId INT ,stordid VARCHAR(50))
INSERT INTO #aa
SELECT B.MemberId,UserList AS NIMEI FROM (
SELECT AA.MemberId,
(SELECT StoreId+'店,' FROM dbo.DP_MemberStore WHERE MemberId=AA.MemberId FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B
INSERT INTO DP_SMSOUTQUEUE(CORPID , MOBILE , CONTENT , SendChannel , AttemptTimes , Operator)
SELECT CORPID=@CorpId ,
dbo.GetMobileByMemberId(a.MemberId) AS MOBILE ,
+'西门子dp平台核实:你好,你昨日共上报'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店的月进货额,总进货额'+CAST(ISNULL(a.BB,0)AS VARCHAR(10))+'元,已上报门店为:'+CAST(ISNULL(a.CC,0)AS VARCHAR(max))
AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),1) AS SENDCHANNEL ,
0 AS ATTEMPTTIMES ,
'Siemens_Confirm_XS' AS OPERATOR
FROM ( SELECT dce.MemberId ,
COUNT(DISTINCT CASE dce.Instruction
WHEN 'XS' THEN dce.StoreId
END) AA,
SUM(CASE dce.Instruction
WHEN 'XS' THEN CAST(dce.Param1 AS INT)
END) BB,
#aa.stordid AS CC
FROM dbo.DP_CommonSmsEntries dce
LEFT JOIN dbo.DP_SmsInProcess SIP ON dce.NewSmsId = SIP.NewSmsId
AND dce.CorpId = SIP.CorpId
LEFT JOIN #aa ON SIP.MemberId = #aa.memberId
LEFT JOIN dbo.DP_Members c ON c.CorpId=dce.CorpId AND SIP.MemberId = c.MemberId
AND c.Status=2 AND c.RoleId=7
WHERE dce.CorpId=@CorpId
AND dce.RecvTime >= @BEGINTIME
AND dce.RecvTime < @ENDTIME
AND dce.Instruction ='XS'
GROUP BY dce.MemberId,#aa.stordid
) a
DROP TABLE #aa
ENDGO
运行存储过程就出提示 消息 245,级别 16,状态 1,过程 Siemens_Confirm_XS,第 11 行
在将 varchar 值 '店,' 转换成数据类型 int 时失败。
求帮助,就差这个问题了,搞定回家好好过年
stuff((SELECT '店,'+cast(StoreId as nvarchar(50)) FROM dbo.DP_MemberStore WHERE MemberId=dce.MemberId FOR XML PATH('')),1,2,'') as NIMEI
SELECT CAST(StoreId AS NVARCHAR(10))+N'店,'
USE DP6_Siemens
GOALTER PROCEDURE [dbo].[Siemens_ReRemind_XS] @BeginTime VARCHAR(10) ,
@EndTime VARCHAR(10) ,
@Corpid INT
AS
BEGIN
CREATE TABLE #aa(memberId INT ,stordid VARCHAR(500))
INSERT INTO #aa
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
(SELECT CAST(StoreId AS NVARCHAR(max))+N'店,' FROM dbo.DP_MemberStore WHERE MemberId=AA.MemberId FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B
--要发出的信息
INSERT INTO DP_SmsOutQueue(CorpId , Mobile , [Content] , SendChannel , AttemptTimes , Operator)
SELECT @Corpid AS Corpid ,
dbo.GetMobileByMemberId(a.MemberId) AS Mobile ,
'西门子dp平台提醒:你好,你还有'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店未上报上月的进货额,请在今天内上报,多谢合作!未上报门店为:'+CAST(ISNULL(a.CC,0)AS VARCHAR(max))+ '。' AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),@Corpid) AS SendChannel ,
0 AS AttemptTimes ,
'Siemens_ReRemind_XS' AS Operator
--发出信息的参数
--发出信息对应的会员ID
FROM (SELECT tt.MemberId,
--未上报XS会员对应为上报的门店数
COUNT (DISTINCT CASE tt.MemberId WHEN '%1%' THEN tt.storeId end ) AS AA,
--列出未上报的门店ID
#aa.stordid AS CC
FROM dbo.DP_MemberStore tt
LEFT JOIN dbo.DP_CommonSmsEntries kk ON tt.CorpId = kk.CorpId AND tt.MemberId = kk.MemberId
LEFT JOIN #aa ON tt.MemberId = #aa.memberId
LEFT JOIN dbo.DP_SmsInProcess ii ON kk.NewSmsId = ii.NewSmsId AND kk.CorpId = ii.CorpId
LEFT JOIN dbo.DP_Members pp ON ii.CorpId = pp.CorpId AND ii.MemberId = pp.MemberId AND pp.Status=2 AND pp.RoleId=7
WHERE tt.CorpId=@Corpid
AND kk.RecvTime>=@BeginTime
AND kk.RecvTime<@EndTime
--排除上报了XS任务的会员
AND NOT EXISTS ( SELECT TOP 1
1
FROM dbo.DP_CommonSmsEntries DCSE
WHERE DCSE.RecvTime >= @BeginTime
AND DCSE.RecvTime < @EndTime
AND DCSE.CorpId = @Corpid
AND DCSE.Instruction = 'XS'
AND DCSE.MemberId = tt.MemberId )
--排除休假会员
AND NOT EXISTS ( SELECT TOP 1
1
FROM DP_SysAttendanceEntries dsae
WHERE dsae.AttendDate >= @BeginTime
AND dsae.AttendDate < @EndTime
AND dsae.CorpId = @Corpid
AND tt.MemberId = dsae.MemberId
AND dsae.TypeId = 2 )
GROUP BY tt.MemberId ,#aa.stordid
)a
DROP TABLE #aa
ENDGO运行之后
消息 245,级别 16,状态 1,过程 Siemens_ReRemind_XS,第 18 行
在将 varchar 值 '%1%' 转换成数据类型 int 时失败。
是求storeId的个数的,但是case when条件怎么选好啊?MemberId是1100004,1100002之类的,以排除了Instruction=‘XS’的