SQL 逻辑配置表连接业务表时用到的技巧。(报表拆分,*号处理,排斥处理)
业务背景:
在财务领域或者营销领域,常常存在多种费用项,这些费用项的判断标准可能就就是那两个字段,但一个字段里面的值可能存在全部,某个,多个的情况。因此在开发相对应的费用项报表的时候,需要开发逻辑配置表然后关联业务表。
这样后期维护只需要维护逻辑配置表就可以了。
逻辑配置表:
业务表:
最终处理结果:
涉及代码
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[逻辑配置表]') AND type IN ('U')) DROP TABLE [dbo].[逻辑配置表] GO CREATE TABLE [dbo].[逻辑配置表] ( [费用项] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [品牌] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [国家] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL ) GO ALTER TABLE [dbo].[逻辑配置表] SET (LOCK_ESCALATION = TABLE) GO -- ---------------------------- -- Records of 逻辑配置表 -- ---------------------------- INSERT INTO [dbo].[逻辑配置表] ([费用项], [品牌], [国家]) VALUES (N'001', N'A', N'P') GO INSERT INTO [dbo].[逻辑配置表] ([费用项], [品牌], [国家]) VALUES (N'002', N'A,B', N'P') GO INSERT INTO [dbo].[逻辑配置表] ([费用项], [品牌], [国家]) VALUES (N'003', N'*', N'P,X') GO INSERT INTO [dbo].[逻辑配置表] ([费用项], [品牌], [国家]) VALUES (N'004', N'-A,-B', N'*') GO
业务表
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[业务表]') AND type IN ('U')) DROP TABLE [dbo].[业务表] GO CREATE TABLE [dbo].[业务表] ( [品牌] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [国家] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [销售额] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL ) GO ALTER TABLE [dbo].[业务表] SET (LOCK_ESCALATION = TABLE) GO -- ---------------------------- -- Records of 业务表 -- ---------------------------- INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'A', N'P', N'100') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'B', N'P', N'101') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'C', N'P', N'102') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'D', N'P', N'103') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'A', N'X', N'104') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'C', N'X', N'105') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'D', N'X', N'106') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'B', N'y', N'107') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'C', N'X', N'108') GO INSERT INTO [dbo].[业务表] ([品牌], [国家], [销售额]) VALUES (N'D', N'X', N'109') GO
运行逻辑参考
with t1 as ( select * from 业务表 ) , t11 as (select distinct 国家 ,'*'国家连接符 from 业务表) , t12 as (select distinct 品牌,'*'品牌连接符 from 业务表 union all select distinct b.品牌,'-'+a.品牌 品牌连接符 from 业务表 a , 业务表 b where a.品牌 <> b.品牌 ) , t2 as ( select t.费用项 ,v.value 国家 ,w.value 品牌 from 逻辑配置表 t cross apply String_split(t.国家 , ',') v cross apply String_split(t.品牌 , ',') W ) , t3 as ( select distinct 费用项,isnull(b.国家,a.国家) 国家 , isnull(c.品牌 , a.品牌) 品牌 from t2 a left join t11 b on a.国家 = b.国家连接符 left join t12 c on a.品牌 = c.品牌连接符 ) select a.费用项,sum(b.销售额*1) 销售额 from t3 a left join t1 b on a.国家= b.国家 and a.品牌 = b.品牌 group by 费用项