mysql数据库查询将两列数据一列去重，一列去重后合并

[banerpang]

goodsno color
A 红，黄
B 红，蓝，绿
B 红，蓝
C 红，黄
C 红，绿
D 绿，黄，蓝
D 红，绿，蓝
D 红，黄，绿

查询结果
goodsno color
A 红，黄
B 红，蓝，绿
C 红，黄，绿
D 红，绿，黄，蓝

[clark_kidd]

表设计的有问题，一条sql处理不了，要用程序或者存储过程+游标处理吧

[繁花尽流年]

引用 楼主 banerpang 的回复:

goodsno color A 红，黄 B 红，蓝，绿 B 红，蓝 C 红，黄 C 红，绿 D 绿，黄，蓝 D 红，绿，蓝 D 红，黄，绿 查询结果 goodsno color A 红，黄 B 红，蓝，绿 C 红，黄，绿 D 红，绿，黄，蓝

IF OBJECT_ID('tempdb..#tmp_1') IS NOT NULL DROP TABLE #tmp_1
CREATE TABLE #tmp_1 (goodsno VARCHAR(1),color VARCHAR(100))
INSERT INTO #tmp_1
select     'A','红,黄' union all
select     'B','红,蓝,绿'union all
select     'B','红,蓝' union all
select     'C','红,黄' union all
select     'C','红,绿' union all
select     'D','绿,黄,蓝' union all
select     'D','红,绿,蓝' union all
select     'D','红,黄,绿' 


;WITH aa AS (
	SELECT DISTINCT  a.goodsno,SUBSTRING(a.color, b.number, CHARINDEX(',', a.color+',', b.number)-b.number) AS color
	FROM   #tmp_1 a
		   JOIN MASTER.dbo.spt_values b
				ON CHARINDEX(',', ','+a.color, b.number) = b.number
	WHERE  b.[type]= 'P'
)


SELECT b.goodsno,LEFT(color,LEN(color)-1) as color FROM (
SELECT goodsno,
(SELECT color+',' FROM aa 
  WHERE goodsno=a.goodsno 
  FOR XML PATH('')) AS color
FROM aa a 
GROUP BY goodsno
) b

LZ试试

[砸死牛顿的苹果]

楼上的思路 每行打散再合并 不错~

[砸死牛顿的苹果]

不过人家是mysql 数据库~

[年后入职国企]

流年真是骚 完美满足我的需求