在 trac 报告查询中检测未设置的 $VARIABLE

发布于 2024-11-08 17:03:18 字数 936 浏览 0 评论 0原文

我整理了一份 trac 报告，其功能如下：

SELECT status, 
   id AS ticket, summary, priority, keywords,  
   datetime(changetime/1000000, 'unixepoch') AS last_updated, 
   milestone AS __group__,
   reporter, owner
  FROM ticket t
  WHERE status NOT IN ('closed', 'resolved') AND milestone in ('$MILESTONE')
  ORDER by t.milestone ASC, p.value

但是，如果用户没有为 $MILESTONE 指定值，我想显示我们三个团队中每一个团队的结果，按团队分组：

SELECT status, 
   id AS ticket, summary, priority, keywords,  
   datetime(changetime/1000000, 'unixepoch') AS last_updated, 
   milestone AS __group__,
   reporter, owner
  FROM ticket t
  WHERE status NOT IN ('closed', 'resolved') AND milestone in ('project1', 'project2', 'project3')
  ORDER by t.milestone ASC, p.value

如何检测 sql 查询中是否设置了变量？除此之外，是否有一种简单的方法可以为用户首次单击报表时显示的 $TEAM 设置默认值？

（注意：如果没有一种在 sql 实现中通用的方法，请将其取消标记为“sql”或发布对此效果的评论，以便我可以做到这一点。）

原文

I've kludged a trac report that does something like this:

SELECT status, 
   id AS ticket, summary, priority, keywords,  
   datetime(changetime/1000000, 'unixepoch') AS last_updated, 
   milestone AS __group__,
   reporter, owner
  FROM ticket t
  WHERE status NOT IN ('closed', 'resolved') AND milestone in ('$MILESTONE')
  ORDER by t.milestone ASC, p.value

However, if the user hasn't specified a value for $MILESTONE, I'd like to display the results for each of our three teams, grouped by team:

SELECT status, 
   id AS ticket, summary, priority, keywords,  
   datetime(changetime/1000000, 'unixepoch') AS last_updated, 
   milestone AS __group__,
   reporter, owner
  FROM ticket t
  WHERE status NOT IN ('closed', 'resolved') AND milestone in ('project1', 'project2', 'project3')
  ORDER by t.milestone ASC, p.value

How can I detect whether a variable is set or not in a sql query? Barring that, is there an easy way to set a default value for $TEAM to be displayed when the user first clicks to the report?

(Note: if there's not a way that's general across sql implementations, please untag this as "sql" or post a comment to that effect so I can do it.)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遗失的美好 2024-11-15 17:03:18

您应该能够使用 CASE WHEN ELSE 语句在 WHERE 子句中，如下所示：

SELECT status, 
id AS ticket, summary, priority, keywords,  
datetime(changetime/1000000, 'unixepoch') AS last_updated, 
milestone AS __group__,
reporter, owner
FROM ticket t
WHERE status NOT IN ('closed', 'resolved') 
AND (CASE WHEN '$MILESTONE' != '' THEN (milestone='$MILESTONE') 
     ELSE (milestone in ('project1', 'project2', 'project3')) END)
ORDER by t.milestone ASC, p.value

You should be able to use a CASE WHEN ELSE statement in the WHERE clause like this:

SELECT status, 
id AS ticket, summary, priority, keywords,  
datetime(changetime/1000000, 'unixepoch') AS last_updated, 
milestone AS __group__,
reporter, owner
FROM ticket t
WHERE status NOT IN ('closed', 'resolved') 
AND (CASE WHEN '$MILESTONE' != '' THEN (milestone='$MILESTONE') 
     ELSE (milestone in ('project1', 'project2', 'project3')) END)
ORDER by t.milestone ASC, p.value

回复收藏 0 原文

~没有更多了~