1、临时表可以多个。首先从一个最原始的表中抽取能抽取的信息,对可代表关键字的信息进行group,可以包括多于最终表的信息,但是该信息必须是作为抽取中的关键信息,不可少的信息。后面的操作都基于该表
-----------------------------对全曲话单歌手作统计
SELECT LEFT([TIMESTATMP],4)+'-'+SUBSTRING([TIMESTATMP],5,2) AS MONTH,--月份
'全曲' AS RINGTYPE,
CONTENTID,--铃音编码
CONVERT(VARCHAR(50),'未知') AS SINGER_NAME,--歌手名
CONVERT(VARCHAR(50),'未知') AS SINGER_NAME_ZM,--歌手首字母
CONVERT(VARCHAR(50),'未知') AS SINGER_TYPE,--歌手类型
CONVERT(VARCHAR(50),'未知') AS SEX,--性别
CONVERT(VARCHAR(50),'未知') AS CP_NAME,--CP商名
COUNT(CONTENTID) AS MYCOUNT,--购买话单量
SUM(CONVERT(NUMERIC(14,2),PRICE)) AS MYPRICE,--购买金额
COUNT(DISTINCT MSISDN) AS USERAMOUNT--购买用户数
INTO #T1
FROM S_QUANGQU_TEMPMONTH
GROUP BY MONTH, RINGTYPE,CONTENTID,SINGER_NAME,SINGER_NAME_ZM,SINGER_TYPE,
SEX,CP_NAME;
SELECT LEFT([TIMESTATMP],4)+'-'+SUBSTRING([TIMESTATMP],5,2) AS MONTH,--月份
'全曲' AS RINGTYPE,
CONTENTID,--铃音编码
CONVERT(VARCHAR(50),'未知') AS SINGER_NAME,--歌手名
CONVERT(VARCHAR(50),'未知') AS SINGER_NAME_ZM,--歌手首字母
CONVERT(VARCHAR(50),'未知') AS SINGER_TYPE,--歌手类型
CONVERT(VARCHAR(50),'未知') AS SEX,--性别
CONVERT(VARCHAR(50),'未知') AS CP_NAME,--CP商名
COUNT(CONTENTID) AS MYCOUNT,--购买话单量
SUM(CONVERT(NUMERIC(14,2),PRICE)) AS MYPRICE,--购买金额
COUNT(DISTINCT MSISDN) AS USERAMOUNT--购买用户数
INTO #T1
FROM S_QUANGQU_TEMPMONTH
GROUP BY MONTH, RINGTYPE,CONTENTID,SINGER_NAME,SINGER_NAME_ZM,SINGER_TYPE,
SEX,CP_NAME;
对其中不知道的先填充