行动态sql
我有一个代表书店的数据库。 有一张表包含书籍可以进入的类别。有些类别仅使用另一个包含类别 - 项目关系的表格进行定义。 但也有一些可以通过编程定义的类别 - 可以使用查询来定义特定作者的类别（SELECT item_id FROM items WHERE author =“John Smith”）。 所以我的分类表有一个“查询”列; 如果它不为空，我使用它来获取类别中的项目，否则我使用category_items表格。
目前，我有应用程序（PHP代码）做出这个决定，但是当我们遍历所有类别时，这意味着很多单独的查询。 有没有办法将这个动态SQL合并到一个连接中？ 就像是：
SELECT c.category, IF(c.query IS NULL, count(i.items), count(EXECUTE c.query)
FROM categories c
LEFT OUTER JOIN category_items i
ON c.category = i.category
EXECUTE需要一个准备好的语句，但我需要为每一行准备一个不同的语句。 另外，EXECUTE不能用于表达式，它只是一个顶级语句。 建议？
当您想要按发布商列出书籍时会发生什么？ 国家？ 语言？ 您必须将它们全部放入一个“category_items”表中。 你将如何选择执行哪个动态查询？ 查询内查询方法不起作用。
我认为你的“类别”概念太宽泛，导致SQL过于复杂。 我会用“类别”来代替“类型”（用于书籍）。 流派在他们自己的表格中定义，并且item_genres将它们连接到商品表格。 书籍作者和书籍流派应该只是在应用程序级别进行单独查询，而不是试图在数据库/ SQL级别使用相同（排序）的查询来完成它们。 （如果你有音乐和书籍，他们可能不应该全部存储在一个“项目”表中，因为它们是不同的概念......有不同的流派，作者与艺术家等等）
我知道这并不能以你喜欢的方式真正解决你的问题，但我认为你不会那么做就会更高兴。
以下是我最终如何在PHP客户端中解决这个问题的方法。
我决定只保留category_items表中的成员资格，并在提交期间使用动态查询来更新此表。
这是我的脚本中的函数，用于在提交或更新期间更新项目的类别。 它需要用户选择的类别列表（只能从没有动态查询的类别中选择），使用这个和动态查询可以计算出当前项目所在类别与它应该在，并根据需要插入/删除以使它们同步。 （请注意，我的数据库中的实际表名与我的问题不同，我使用的是一些通用术语。）
function update_item_categories($dbh, $id, $requested_cats) {
$data = mysql_check($dbh, mysqli_query($dbh, "select id, query from t_ld_categories where query is not null"), 'getting dynamic categories');
$clauses = array();
while ($row = mysqli_fetch_object($data))
$clauses[] = sprintf('select %d cat_id, (%d in (%s)) should_be_in',
$row->id, $id, $row->query);
if (!$requested_cats) $requested_cats[] = -1; // Dummy entry that never matches cat_id
$requested_cat_string = implode(', ', $requested_cats);
$clauses[] = "select c.id cat_id, (c.id in ($requested_cat_string)) should_be_in
from t_ld_categories c
where member_type = 'lessons' and query is null";
$subquery = implode("nunion alln", $clauses);
$query = "select c.cat_id cat_id, should_be_in, (member_id is not null) is_in
from ($subquery) c
left outer join t_ld_cat_members m
on c.cat_id = m.cat_id
and m.member_id = $id";
// printf("<pre>$query</pre>");
$data = mysql_check($dbh, mysqli_query($dbh, $query), 'getting current category membership');
$adds = array();
$deletes = array();
while ($row = mysqli_fetch_object($data)) {
if ($row->should_be_in && !$row->is_in) $adds[] = "({$row->cat_id}, $id)";
elseif (!$row->should_be_in && $row->is_in) $deletes[] = "(cat_id = {$row->cat_id} and member_id = $id)";
}
if ($deletes) {
$delete_string = implode(' or ', $deletes);
mysql_check($dbh, mysqli_query($dbh, "delete from t_ld_cat_members where $delete_string"), 'deleting old categories');
}
if ($adds) {
$add_string = implode(', ', $adds);
mysql_check($dbh, mysqli_query($dbh, "insert into t_ld_cat_members (cat_id, member_id) values $add_string"),
"adding new categories");
}
}
上一篇: row dynamic sql