table1
cellphone state
13100001231 1
13100005201 1
13166635210 1
13119500101 1
13100956328 1
table2
ID Number NumberName Price
1 520 谐音 10
2 0000 中4零 20
3 1950 日期 30
4 123 顺序 40
5 666 连续 50
要实现的功能是 表1 中的手机号若包含 表2 中的Number 字段的值就显示出来没有就只显示号码！
最终需要的结果
cellphone NumberName price NumberName Price
13100001231 中4零 20 顺序 40
13100005201 中4零 20 谐音 10
13166635210 连续 30
13119500101 日期 10
13100956328
------解决思路----------------------
IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test
CREATE TABLE #test
(
Mobile VARCHAR(11)
,States INT
)
IF OBJECT_ID('tempdb..#test1','U') IS NOT NULL DROP TABLE #test1
CREATE TABLE #test1
(
ID INT
,Number VARCHAR(11)
,NumberName NVARCHAR(200)
,Price DECIMAL(9,2)
)
INSERT INTO #test
SELECT '13100001231', 1 UNION ALL
SELECT '13100005201', 1 UNION ALL
SELECT '13166635210', 1 UNION ALL
SELECT '13119500101', 1 UNION ALL
SELECT '13100956328', 1
INSERT INTO #test1
SELECT 1, '520', '谐音', 10 UNION ALL
SELECT 2, '0000', '中4零', 20 UNION ALL
SELECT 3, '1950', '日期', 30 UNION ALL
SELECT 4, '123', '顺序', 40 UNION ALL
SELECT 5, '666', '连续', 50
SELECT A.Mobile
,ISNULL(T.NumberName,'') AS NumberName
,ISNULL(T.Price,0) AS Price
INTO #test3
FROM #test AS A
OUTER APPLY (SELECT NumberName,Price FROM #test1 AS B WHERE CHARINDEX(b.Number,A.Mobile,0)>0) T
DECLARE @total INT,@Sql NVARCHAR(MAX)
SELECT @total= MAX(cnt) FROM (SELECT COUNT(1) AS cnt FROM #test3 GROUP BY Mobile)AS A
SELECT @Sql=ISNULL(@Sql+',','')
+'MAX(CASE WHEN rn='+RTRIM(a.number)+' THEN NumberName ELSE '''' END) AS NumberName,'
+'MAX(CASE WHEN rn='+RTRIM(a.number)+' THEN Price ELSE 0 END) AS Price'
FROM master.dbo.spt_values AS A
WHERE A.type='P'
AND number BETWEEN 1 AND @total
SELECT @Sql='SELECT T.Mobile ,'+@Sql+'
FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY Mobile ORDER BY GETDATE()) AS rn
FROM #test3 )AS T
GROUP BY T.Mobile'
EXECUTE(@Sql)
------解决思路----------------------
select E.Mobile,E.NumberName,E.Price into table3
from (select T.Mobile,D.NumberName,D.Price from table1 T left join
(SELECT A.mobile,NumberName,Price FROM table1 A ,table2 B
WHERE CHARINDEX(b.Number,A.Mobile,0)>0) D on T.mobile=D.Mobile )E
declare @sql nvarchar(max);
set @sql='';
;with t
as
(
select *,ROW_NUMBER() over(Partition by mobile order by @@servername) rownum
from table3
)
select @sql = @sql +',max(case when rownum ='+cast(rownum as varchar)+' then NumberName else null end) as NumberName'+
',max(case when rownum = '+cast(rownum as varchar)+' then Price else null end) as Price'
from t group by rownum
SELECT @Sql='SELECT Mobile' + @Sql +
' from (select *,ROW_NUMBER() over(Partition by mobile order by @@servername) rownum
from table3
)t' +
' GROUP BY Mobile '
exec(@sql)