22,209
社区成员
发帖
与我相关
我的任务
分享
select SINGLE_CHAR,cast(round((销售额*1.0)/QTY,2) as decimal(12,2))
from
(select A.*,LEN(品名)-LEN(REPLACE(品名,',',''))+1 AS QTY,
SUBSTRING(品名,number,CHARINDEX(',',品名+',',number)-number) AS SINGLE_CHAR
from table A
join master.dbo.spt_values B on CHARINDEX(',',','+A.品名,B.number)=B.number
where type='p' ) as A
USE tempdb
GO
IF OBJECT_ID('AA') IS NOT NULL DROP TABLE AA
GO
CREATE TABLE AA(
单号 INT,
品名 VARCHAR(20),
销售额 INT
)
GO
INSERT INTO AA
SELECT 1,'A',100
UNION ALL SELECT 2,'A,B',300
UNION ALL SELECT 3,'B,C,D',400
UNION ALL SELECT 4,'A,B,D',500
GO
--------- 以上为测试表及测试数据 -----------
--1. 增加 字符串分割 表值函数
IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_String2ToStringArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE ([item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
--2. 查询
SELECT fs.item AS [品名]
,CAST( (AA.销售额*1.0)/(SELECT COUNT(1) FROM [dbo].[Fun_String2ToStringArray](AA.[品名],',')) AS DECIMAL(10,2)) AS [销售额]
FROM AA CROSS APPLY [dbo].[Fun_String2ToStringArray](AA.[品名],',') AS fs