SELECT CASE
WHEN name LIKE '%,%' THEN LEFT(name, Charindex(' ', products) - 1)
ELSE name
END,
CASE
WHEN name LIKE '%,%' THEN RIGHT(name, Charindex(' ', Reverse(products)) - 1)
END
FROM YourTable
然后你需要把这个和同一个表合并。。。最后的代码看起来像。。。
select count( distinct abc), abc from
(
SELECT CASE
WHEN PA_NAME LIKE '% %' THEN LEFT(PA_NAME, Charindex(' ', PA_NAME) - 1)
ELSE PA_NAME
END [abc]
FROM phparty
union all
SELECT CASE
WHEN PA_NAME LIKE '% %' THEN RIGHT(PA_NAME, Charindex(' ', Reverse(PA_NAME)) -1)
END [abc]
FROM phparty
) t group by abc
SELECT Products, COUNT(*) AS count
FROM
(
SELECT SUBSTRING_INDEX(Products, ',', 1) AS Products FROM yourTable
UNION ALL
SELECT SUBSTRING_INDEX(Products, ',', -1) FROM yourTable
) t
GROUP BY Products
ORDER BY
count DESC, Products;
2条答案
按热度按时间yiytaume1#
首先,您需要将数据分成两列,如
然后你需要把这个和同一个表合并。。。最后的代码看起来像。。。
在这里,您可以用您的\u列\u名称替换您的\u名称
xiozqbni2#
您正在sql表中存储csv数据,这不是一件好事。但看起来你正试图摆脱这一点,这是一件好事。这里有一个选项,使用
SUBSTRING_INDEX
:演示