[My]需要foreach处理的SQL查询语法 [英] [My]SQL Query Syntax which requires foreach processing
问题描述
我有一个 SQL 查询任务,我可以在 C# 或 linqpad 中完成,但更喜欢它在 SQL 中,以便标准报告工具可以完成.
End DB 是 MySQL 下的 bugzilla
<块引用><块引用>问题是我需要遍历 bug_activity 以查找特定更改以考虑父记录有效",如何?例如像
这样的伪逻辑 如果 bug_status 进入 IN BACKLOG然后 bug_status 转到 ASSIGNED这发生在 2016-03-01 到 206-03-31然后考虑有效记录
<块引用><块引用>
我不确定如何执行此操作,因为 Web 示例仅显示 DECLARE 和 LOOP,但循环如何适合选择、从、何处"代码.
set @BugID = 64252;选择bugs_activity.bug_id,--profiles.realname,--profiles.login_name,bugs_activity.bug_when,fielddefs.name,bugs_activity.已添加-- bugs_activity.removed从bugs_activity,个人资料,字段定义-- 现实世界中的Where xx"将有更多的逻辑并产生大量的 bugzilla 记录-- 每个 bugzilla 记录都有自己的bugs_activity"-- 逻辑需要查看每个 buzilla 记录历史来过滤结果-- 想要得到过滤记录集和记录总数其中 bug_id = @BugID AND bugs_activity.who = profiles.userid AND bugs_activity.fieldid = fielddefs.id
bug_activity 示例
bug_id bug_when 名称添加64252 26/01/2016 6:51:30 AM status_whiteboard ID:10313857464252 26/01/2016 上午 6:52:10 抄送 xxx@abc.com64252 28/01/2016 上午 9:49:10 待办事项中的 bug_status64252 28/01/2016 9:49:10 AM cf_escalation_notes 努力:2支持转载64252 28/01/2016 上午 9:49:10 分配_to def@abc.com64252 2/05/2016 4:33:05 PM bug_status ASSIGNED
SELECTbug_id,SUM(CASE WHEN bug_status='IN BACKLOG' THEN 1 ELSE 0 END) 作为 backlogCount,SUM(CASE WHEN bug_status='ASSIGNED' THEN 1 ELSE 0 END) asassignedCountFROM bugs_activityWHERE action_date BETWEEN '2016-03-01' 和 '206-03-31'GROUP BY bug_idHAVING backlogCount>0 ANDassignedCount>0
选择返回在此期间处于IN BACKLOG"和ASSIGNED"状态的 bug_ids.您可以在 FROM 部分中使用上面的查询而不是 bugs_activity
更新:
在 SELECT 部分添加
MAX(CASE WHEN bug_status='IN BACKLOG' THEN action_date ELSE NULL END) 作为 backlogDate,MAX(CASE WHEN bug_status='ASSIGNED' THEN action_date ELSE NULL END) asassignedDate
然后在 HAVING 部分 AND backlogDate
I have an SQL query task which I can do OK in C# or linqpad but would prefer it is in SQL so standard reporting tools can do it.
End DB is a bugzilla under MySQL
The problem is I need to loop through the bug_activity looking for particular changes to consider the parent record "valid", how ? e.g. pseudo logic like
if bug_status went to IN BACKLOG
then bug_status went to ASSIGNED
and this happened 2016-03-01 to 206-03-31
then consider valid record
I am unsure how to do this as web examples only show DECLARE and LOOPs but how a loop fits into the "select, from, where" code.
set @BugID = 64252;
select
bugs_activity.bug_id,
-- profiles.realname,
-- profiles.login_name,
bugs_activity.bug_when,
fielddefs.name,
bugs_activity.added
-- bugs_activity.removed
from
bugs_activity,
profiles,
fielddefs
-- Real world 'Where xx' will have more logic and result in a number of bugzilla records
-- Each bugzilla record has its own 'bugs_activity'
-- Logic needs to look at each buzilla records historyto filter results
-- Want to end up with a filtered record set and a total number of records
Where bug_id = @BugID AND bugs_activity.who = profiles.userid AND bugs_activity.fieldid = fielddefs.id
Example of bug_activity
bug_id bug_when name added
64252 26/01/2016 6:51:30 AM status_whiteboard ID:103138574
64252 26/01/2016 6:52:10 AM cc xxx@abc.com
64252 28/01/2016 9:49:10 AM bug_status IN BACKLOG
64252 28/01/2016 9:49:10 AM cf_escalation_notes Effort: 2
Reproduced by support
64252 28/01/2016 9:49:10 AM assigned_to def@abc.com
64252 2/05/2016 4:33:05 PM bug_status ASSIGNED
SELECT
bug_id,
SUM(CASE WHEN bug_status='IN BACKLOG' THEN 1 ELSE 0 END) as backlogCount,
SUM(CASE WHEN bug_status='ASSIGNED' THEN 1 ELSE 0 END) as assignedCount
FROM bugs_activity
WHERE action_date BETWEEN '2016-03-01' AND '206-03-31'
GROUP BY bug_id
HAVING backlogCount>0 AND assignedCount>0
The select returns bug_ids which were in 'IN BACKLOG' and in 'ASSIGNED' statuses during the period. You can use the query above in your FROM section instead of bugs_activity
UPDATE:
Add in the SELECT section
MAX(CASE WHEN bug_status='IN BACKLOG' THEN action_date ELSE NULL END) as backlogDate,
MAX(CASE WHEN bug_status='ASSIGNED' THEN action_date ELSE NULL END) as assignedDate
and then in HAVING section AND backlogDate<assignedDate
这篇关于[My]需要foreach处理的SQL查询语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!