求助:sql查询

[wangf1978]

表一
id name fruit_id
1 猴大 1,2
2 猴二 1,3,5
表二
id name
1 苹果
2 香蕉
3 西瓜
4 水蜜桃
5 梨
表三
id name fruits
1 猴大 2
2 猴二 3
表一第三列字符串与表二列id关联,能查询出表三水果种类数量?

[wangf1978]

表一fruit_id列是字符串,与表二中id列(int)关联,希望通过表一表二查询,统计出表三数据,即猴大猴二各有几种水果。

[顺势而为1]

按楼主的意思, 直接算表1 中Fruit_ID的 , 号数量就可以了

[顺势而为1]

if not object_id(N'Tempdb..#Tmp_Data') is null
    drop table #Tmp_Data
Go

CREATE TABLE #Tmp_Data(
      ID int,
	  Name nvarchar(10),
	  fruit_id varchar(20))

INSERT INTO #Tmp_Data
Select 1, N'猴大','1,2' union 
Select 2, N'猴二','1,3,5'

Select ID,Name,(Len(Fruit_id)-Len(Replace(Fruit_ID,',',''))+1) as Fruits
From #Tmp_Data

[wangf1978]

但是,表一fruit_id列中如果存在不在表二范围的数据,计算,号结果就不准确了

[wangf1978]

我的目的是希望字符串与int关联查询

[听雨停了]

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id VARCHAR(20),
NAME VARCHAR(20),
fruit_id VARCHAR(20)	
)
INSERT INTO #tab 
SELECT '1','猴大',N'1,2'
UNION ALL
SELECT '2','猴二',N'1,3,5'
--测试数据结束
SELECT a.id,a.name,b.fruits
FROM   (
           SELECT id,NAME,[value] = CONVERT(XML, '<v>' + REPLACE(fruit_id, ',', '</v><v>') + '</v>') FROM #tab
       ) A
       OUTER APPLY (
    SELECT fruits = N.v.value('.', 'varchar(100)')
    FROM   A.[value].nodes('/v') N(v)
) B

id                   name                 fruits
-------------------- -------------------- ----------------------------------------------------------------------------------------------------
1                    猴大                   1
1                    猴大                   2
2                    猴二                   1
2                    猴二                   3
2                    猴二                   5

(5 行受影响)

[繁花尽流年]

引用 6 楼 wangf1978 的回复:

我的目的是希望字符串与int关联查询

if not object_id(N'Tempdb..#Tmp_Data') is null
    drop table #Tmp_Data
Go
 
CREATE TABLE #Tmp_Data(
      ID int,
      Name nvarchar(10),
      fruit_id varchar(20))
 
INSERT INTO #Tmp_Data
Select 1, N'猴大','1,2' union 
Select 2, N'猴二','1,3,5'



SELECT t.Name,COUNT(*) AS qty FROM (
	SELECT a.ID,a.Name,SUBSTRING(a.fruit_id, b.number, CHARINDEX(',', a.fruit_id+',', b.number)-b.number) AS fruit_id
	FROM   #Tmp_Data a
		   JOIN MASTER.dbo.spt_values b
				ON CHARINDEX(',', ','+a.fruit_id, b.number) = b.number
	WHERE  b.[type]= 'P'  
) t
WHERE EXISTS (SELECT 1 FROM 表二 WHERE t.fruit_id=cast(id as vaVARCHAR
GROUP BY t.Name

借下5#数据

[听雨停了]

引用 4 楼 wangf1978 的回复:

但是,表一fruit_id列中如果存在不在表二范围的数据,计算,号结果就不准确了

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id VARCHAR(20),
NAME VARCHAR(20),
fruit_id VARCHAR(20)	
)
INSERT INTO #tab 
SELECT '1','猴大',N'1,2,7'
UNION ALL
SELECT '2','猴二',N'1,3,5,8,9'

IF OBJECT_ID('tempdb..#tab2') IS NOT NULL
DROP TABLE #tab2
CREATE TABLE #tab2(
id VARCHAR(20),
NAME VARCHAR(20)	
)
INSERT INTO #tab2 
SELECT '1','苹果'
UNION ALL
SELECT '2','香蕉'
UNION ALL
SELECT '3','西瓜'
UNION ALL
SELECT '4','水蜜桃'
UNION ALL
SELECT '5','李子'

--测试数据结束


SELECT a.id,a.name,b.fruits,c.name
FROM   (
           SELECT id,NAME,[value] = CONVERT(XML, '<v>' + REPLACE(fruit_id, ',', '</v><v>') + '</v>') FROM #tab
       ) A
       OUTER APPLY (
    SELECT fruits = N.v.value('.', 'varchar(100)')
    FROM   A.[value].nodes('/v') N(v)
) B 
INNER JOIN #tab2 c ON b.fruits=c.id

id                   name                 fruits                                                                                               name
-------------------- -------------------- ---------------------------------------------------------------------------------------------------- --------------------
1                    猴大                   1                                                                                                    苹果
1                    猴大                   2                                                                                                    香蕉
2                    猴二                   1                                                                                                    苹果
2                    猴二                   3                                                                                                    西瓜
2                    猴二                   5                                                                                                    李子

(5 行受影响)

这样的话就可以计算不在表2中的数据了，跟我上面写的那个差不多，只不过连了表2而已

[二月十六]

如果能保证fruits_id都在表2中存在，试试这个：

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22),[fruit_id] nvarchar(25))
Insert #T1
select 1,N'猴大',N'1,2' union all
select 2,N'猴二',N'1,3,5'
GO
--测试数据结束
SELECT  id ,
        name ,
        LEN(fruit_id) - LEN(REPLACE(fruit_id, ',', ''))+1 AS fruits
FROM    #T1

[wangf1978]

表二也是查询的结果,也即是说表一的fruid_id 可能存在不在表二中的数据。

[顺势而为1]

引用 6 楼 wangf1978 的回复:

我的目的是希望字符串与int关联查询

Select ID,Name,count(fruit_ID) as Fruits
From (
			Select a.ID,fruit_ID,a.Name
			From (
						Select ID,Name,t.c.value('.','int') AS fruit_ID  
						From  (SELECT ID,Name,CAST('<x>'+REPLACE(fruit_id,',','</x><x>')+'</x>' AS XML ).query('.') AS name1 From #Tmp_Data) a
						CROSS APPLY a.name1.nodes('/x') T(c)
				 ) a
				 Join #Tmp_Data1 b on a.fruit_ID=b.ID
	  ) a
Group By ID,Name

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22),[fruit_id] nvarchar(25))
Insert #T1
select 1,N'猴大',N'1,2' union all
select 2,N'猴二',N'1,3,5'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[name] nvarchar(23))
Insert #T2
select 1,N'苹果' union all
select 2,N'香蕉' union all
select 3,N'西瓜' union all
select 4,N'水蜜桃' union all
select 5,N'梨'
Go
--测试数据结束
SELECT  #T1.id ,
        #T1.name ,
        SUM(CASE WHEN #T2.name IS NOT NULL THEN 1
                 ELSE 0
            END) AS fruits
FROM    #T1
        LEFT JOIN #T2 ON CHARINDEX(',' + RTRIM(#T2.id) + ',',
                                   ',' + #T1.fruit_id + ',') > 0
GROUP BY #T1.id ,
        #T1.name

[wangf1978]

8楼测试通过.再分组应该可以了。撒分。

[wangf1978]

靠,高手这么多,就一丁点分怎么给。

[顺势而为1]

[wangf1978]

剩下十分,追加撒分。

[wangf1978]

版主屌炸天了,膜拜。