在项目的数据库设计中，可能会碰到不确定列的情况。 使用JSON 格式来保存这种不确定列的数据， 是一种比较灵活且方便的做法。 

 

比如一张表记录学生的成绩。

 

 

如果列上的学科是不确定的， 比如有的学生有物理成绩， 有的学生有生物成绩，那么我们可能会考虑将表设计成：

 

 

但这样，可能就会在查询的时候，遇到麻烦。比如我想知道数学成绩是4分的有哪些学生？

类似的问题， 我在项目中使用的做法是写了一个自定义的函数: fun_scale_matchJsonPattern 

 1 /*
 2  *  Author : Harvey Hu
 3  *  Created At :2020-6-5
 4  *  Description : 按指定单个模式判断匹配JSON是否满足（>= 条件即为匹配）
 5  *  Return =1 匹配 ； =0 不匹配
 6  */
 7 CREATE FUNCTION [dbo].[fun_scale_matchJsonPattern]
 8 (
 9      @pattern nvarchar(1000),  -- 单个模式
10      @input nvarchar(1000)
11  ) returns int
12  begin
13      Declare @targetScore int = 0; 
14      Declare @actualScore int = 0; 
15      Declare @result int = 0; 
16       
17      select @targetScore= count(*) from OPENJSON(@pattern) a
18 
19      select @actualScore = count(1)
20         from OPENJSON(@pattern) a
21            inner join OPENJSON(@input) b on a.[key]= b.[key] and a.[value] = b.[value]
22 
23      IF @targetScore = @actualScore
24         SET @result = 1
25      
26      return @result
27 
28  end

 

 使用的示例如下

1 select  
2    *
3 from dbo.[学生成绩表]
4 where 
5    dbo.fun_scale_matchJsonPattern([成绩],'{"数学":"4"}')=1

 

简单的来说，就是比较条件的数量和匹配的条件数量是否一致，如果一致则认为满足。

这种方式的好处是， 可以使用 'and' 逻辑去匹配多个条件；不好的地方在于只能判断‘相等逻辑’，不适用于‘大于’‘小于’等其他的二元逻辑。 不过由于工作上够用了， 所以没有做更多的扩展。如果有需要可以在评论中留言。

 

基于上述内容， 下面这个是‘多个条件取其一即可满足’的实现

 1  /*
 2  *  Author : Harvey Hu
 3  *  Created At :2020-6-5
 4  *  Description : 按指定多个模式判断匹配JSON是否满足（只要有其中之一满足即为匹配）
 5  *  Return =1 匹配 ； =0 不匹配
 6  */
 7 CREATE FUNCTION [dbo].[fun_scale_matchJsonPatterns]
 8 (
 9     @patterns nvarchar(1000),  -- 数组JSON格式
10     @input nvarchar(1000)
11  ) returns int  
12  as
13  begin
14     DECLARE @key nvarchar(50)
15     DECLARE @value nvarchar(1000)
16     DECLARE @type int
17     DECLARE @result int =0
18 
19    DECLARE jsonCursor CURSOR for 
20    select * from OPENJSON(@patterns)
21 
22    OPEN  jsonCursor
23    fetch next from jsonCursor into @key,@value ,@type
24 
25    while @@fetch_status=0           --判断是否成功获取数据   进入循环
26    begin
27          if @type = 5  and  dbo.[fun_scale_matchJsonPattern](@value,@input)=1  -- object
28               set @result = 1
29    fetch next from jsonCursor into @key,@value ,@type
30    end
31 
32 
33    CLOSE jsonCursor
34 
35    return @result
36  end

 

使用了游标， 所以性能上可能会有影响。 期待改进！