求查询统计检查部位个数的一点思路

[一品梅]

检查部位费用表结构

CREATE TABLE [dbo].[T_BODYPART](
	[BodyPartIdentity] [bigint] NOT NULL,
	[BodyPartName] [nvarchar](20) NOT NULL,
	[BodyPartMemo] [nvarchar](50) NULL,
	[DepartmentIdentity] [bigint] NULL,
	[PriceIdentity] [int] NULL,
	[DisplayOrder] [int] NULL,
	[ReportNode] [nvarchar](255) NULL,
	[ModalityTypeIdentity] [int] NULL,
	[Protocolcode] [nvarchar](256) NULL,
	[BodyPartCount] [int] NULL,
	[EXPOSALCOUNT] [int] NULL,
	[DisplayOrder2] [int] NULL,
	[Price] [numeric](18, 2) NULL,
	[BodyPartStoped] [smallint] NULL,
	[BodyPartCode] [nvarchar](20) NULL,
 CONSTRAINT [PK_T_BODYPART] PRIMARY KEY CLUSTERED 
(
	[BodyPartIdentity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

这是病人报告表结构

SELECT TOP 1000 [F_STU_GUID]
      ,[F_STU_NUM]
      ,[F_STU_NAME]
      ,[F_STU_NAME_EN]
      ,[F_STU_SEX]
      ,[F_STU_AGE]
      ,[F_STU_BRITH]
      ,[F_STU_TELEPHONE]
      ,[F_STU_ADDRESS]
      ,[F_STU_PROFESSION]
      ,[F_STU_COMPANY]
      ,[F_STU_ID_CARD]
      ,[F_STU_NATION]
      ,[F_STU_MARRY]
      ,[F_STU_HEIGHT]
      ,[F_STU_WEIGHT]
      ,[F_STU_FEE_TYPE]
      ,[F_STU_IN_NUM]
      ,[F_STU_OUT_NUM]
      ,[F_STU_BUNK_NUM]
      ,[F_STU_SICKROOM_NUM]
      ,[F_STU_SICKROOM_AREA]
      ,[F_STU_FROM]
      ,[F_STU_MODEL]
      ,[F_STU_BODY_PART]
      ,[F_STU_METHOD]
      ,[F_STU_RESULT]
      ,[F_STU_SUSPICION]
      ,[F_STU_DISEASE]
      ,[F_STU_REFER_DOCTOR]
      ,[F_STU_REFER_OFFICE]
      ,[F_STU_REFER_DATE]
      ,[F_STU_PERFORM_DOCTOR]
      ,[F_STU_PERFORM_OFFICE]
      ,[F_STU_PERFORM_DATE]
      ,[F_STU_OPT_DOCTOR]
      ,[F_STU_OPT_DATE]
      ,[F_STU_WRITER]
      ,[F_STU_WRITE_DATE]
      ,[F_STU_OBSERVATION]
      ,[F_STU_DIAGNOSE]
      ,[F_STU_ADVICE]
      ,[F_STU_DETAIL]
  FROM [PACS].[dbo].[T_STUDY_REPORT]

数据表[PACS].[dbo].[T_STUDY_REPORT]的检查部位字段[F_STU_BODY_PART]可以是多部位的。
比如：腹部+小器官+心脏，
然后我想做个统计，统计每天的每个检查部位和对应的操作次数和费用，请问怎么实现，请给个思路，贴上代码更好。

[一品梅]

比如： 表1 body_part price 小器官 100 心脏 150 头颅 200 四肢 60 颈动脉 30 桡骨 20 膝关节 50 表2 F_stu_body_part 小器官+心脏 四肢 头颅+四肢 统计结果： 2020年9月24日统计数据： 小器官 1次 100 心脏 1次 150 四肢 2次 120 头颅 1次 200 要求：不需要函数，只用单纯的SQL语句。（接口限制）

[lich2005]

不需要函数是指不使用自定义函数吗？ 如果使用的是 SQL Server 2016 及更高版本 的话，可以用 STRING_SPLIT 来分隔字符串再进行处理。 b1 = 表1, b2 = 表2 大致代码 -- SELECT value as item, cnt, (cnt * price) total_price FROM ( SELECT value,COUNT(*) AS CNT FROM b2 CROSS APPLY STRING_SPLIT(F_stu_body_part, '+') GROUP BY value ) tb join b1 on tb.value = b1.body_part

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T1') IS NOT NULL
DROP TABLE #T1
GO

CREATE TABLE #T1
(ID INT IDENTITY(1,1),
 F_STU_BODY_PART VARCHAR(100))

INSERT INTO #T1
SELECT 'A+B+E' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D+C+A+E'

GO

IF OBJECT_ID(N'TEMPDB.DBO.#T2') IS NOT NULL
DROP TABLE #T2
GO

CREATE TABLE #T2
(BODY_PART VARCHAR(5),
 PRICE INT)

INSERT INTO #T2
SELECT 'A',100 UNION ALL
SELECT 'B',200 UNION ALL
SELECT 'C',300 UNION ALL
SELECT 'D',320 UNION ALL
SELECT 'E',360 UNION ALL
SELECT 'F',420
  
GO

SELECT B.BODY_PART,COUNT(*) AS AMOUNT,SUM(PRICE) AS TOTAL_VALUE 
FROM
(SELECT A.*,SUBSTRING(A.F_STU_BODY_PART,B.NUMBER,CHARINDEX('+',A.F_STU_BODY_PART+'+',NUMBER)-NUMBER) AS SINGLE_PART
FROM #T1 A
JOIN MASTER.DBO.SPT_VALUES B ON CHARINDEX('+','+'+A.F_STU_BODY_PART,NUMBER)=B.NUMBER
WHERE B.TYPE='P') AS A
JOIN #T2 AS B ON A.SINGLE_PART=B.BODY_PART
GROUP BY B.BODY_PART

[一品梅]

用的是SQL 2008,以前给客户写过，后来U盘丢了，都没了。 现在想想没有思路了。

[雨夹雪]

--3楼的数据,谢谢
SELECT a.BODY_PART,COUNT(1),SUM(a.PRICE)  
FROM 
#T2 a INNER JOIN #T1 b ON CHARINDEX('+'+a.BODY_PART+'+','+'+b.F_STU_BODY_PART+'+')>0
GROUP BY a.BODY_PART

[雨夹雪]

--sql2016
SELECT C.BODY_PART,COUNT(1),SUM(C.PRICE)  FROM #T1 A OUTER APPLY string_split(F_STU_BODY_PART,'+') B
INNER JOIN #T2 C ON B.value = C.BODY_PART
GROUP BY C.BODY_PART

[一品梅]

好的，搞定了，结贴，谢谢大家