时间戳

首页 > 解决方案 > 如何使用存储在表中的表/列名来选择数据？动态构建 SQL？

sql - 如何使用存储在表中的表/列名来选择数据？动态构建 SQL？

问题描述

我有一个表格，显示与这些事件相关的事件和车辆：

id | model | colour     | cargo
--------------------------------------------
1 | vw       | red      | tomatoes
2 | bmw      | red      | golf clubs

我想要一张可以用来管理警报的表格。有一列表示要检查的字符串，另一列显示它应用于哪个列/表，以及一些描述警报的文本：

id | column_name    | check_string     | alert_string
--------------------------------------------
1 | my_table.colour | red              | 'Vehicle is red'
2 | my_table.cargo  | [sport,club]     | 'Vehicle is carrying sports goods'

或者可能：

id | column_name    | check_string     | alert_string
--------------------------------------------
1 | my_table.colour | red              | 'Vehicle is red'
2 | my_table.cargo  | sport            | 'Vehicle is carrying sports goods'
3 | my_table.cargo  | club             | 'Vehicle is carrying sports goods'

我想要一个可以运行的查询，该查询将返回适用于该行的所有警报：

id | alert_text
--------------------------------------------
1 | ['Vehicle is red']
2 | ['Vehicle is red', 'Vehicle is carrying sports goods']

我这样做的方式是在 Python 中构建 SQL 命令并针对数据库运行它们，但是随着规则数量的增加或变量需要更改/更新，这成为一种负担（突然我们不关心红色汽车，而是非常关心蓝色汽车）。

SELECT id, 'Vehicle is red' as alert_text FROM my_table 
WHERE my_table.colour = 'red';

SELECT id, 'Sports goods' as alert_text FROM my_table 
WHERE my_table.cargo in ['sport', 'club'];

SELECT <many other rules>;

有一个更好的方法吗？是否值得构建一个可以动态指向一列的数据库表，检查该列的字符串，然后与规则关联的警报文本？我什至应该使用 SQL 来解决这个问题吗？

我有一种感觉，SQL 可能不是这项工作的正确工具，但我不知道我不知道什么......

标签： sqldatabasepostgresql

解决方案

此 SQL 代码将允许您为事件和警报拥有任意大小的数据库表

Declare @vSQL nvarchar(Max)
Declare @vColumnName nvarchar(25)
Declare @vCheckString nvarchar(25)
Declare @vAlertString nvarchar(50)

Declare vCursor CURSOR For
  Select [column_name], [check_string], [alert_string] From vehicle_alerts

  Open vCursor;
  Fetch Next From vCursor Into @vColumnName, @vCheckString, @vAlertString

  --Make global temp table from source then delete contents.  
  --Ensures field lengths are as large as the largest value in the temp table (Not the most efficient but it works)
  Select id, alert_string INTO ##alerts From vehicle_alerts
  Delete From ##alerts  

  While @@FETCH_STATUS = 0  --Loop through alerts and interrogate your events using the LIKE operator
  Begin
     Set @vSQL = 'INSERT INTO ##alerts Select id, '''+@vAlertString+''' As [Alert] From vehicle_events Where ' + @vColumnName + ' Like ''%' + @vCheckString + '%'''
     Execute (@vSQL)
     Fetch Next From vCursor Into @vColumnName, @vCheckString, @vAlertString
  End;

  --Use STUFF to combine the alert strings by id
  Select id,STUFF((Select ',' + [alert_string] From ##alerts Where id = a.id FOR XML PATH('')),1,1,'') AS Tmp 
  From ##alerts AS a
  Group By id

  Drop Table ##alerts
  Close vCursor
  Deallocate vCursor

推荐阅读