SQL条件查询

[蓝色花殇]

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

[chen357313771]

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)

[LongRui888]

1楼、2楼都写的不错

[xilanhuanlejia]

实在是太难啦！