Say you have 2 columns you are grouping by and getting a count:
假设您有2个列,您正在分组并获得计数:
select col1, col2, count(*)
from yourtable
group by col1, col2
Your col2 will be unique, but you will get repeated col1's. To avoid this, you can:
您的col2将是唯一的,但您将重复col1。为避免这种情况,您可以:
select decode(lag(col1,1,0) over (order by col1),col1,' ',col1) col1, col2, cnt
from
(select col1, col2, count(*) cnt
from yourtable
group by col1, col2)
This will compare the prior value -- lag(col1,1,0) over (order by col1) -- with the current value. If it is the same, it returns a blank instead.
这将比较先前的值 - 滞后(col1,1,0)超过(按col1排序) - 与当前值。如果它相同,则返回空白。
My question
I am trying to write a SELECT as below to display data in the given format at the end of the question. Its not working,please help
我正在尝试编写一个SELECT,如下所示,在问题的最后以给定的格式显示数据。它不工作,请帮助
SELECT DECODE (LAG (firstname, 1, 0) OVER (ORDER BY firstname),
firstname, ' ',
firstname
) firstname,
DECODE (LAG (emplid, 1, 0) OVER (ORDER BY emplid),
emplid, ' ',
emplid
) emplid,
DECODE (LAG (tplan_name, 1, 0) OVER (ORDER BY tplan_name),
tplan_name, ' ',
tplan_name
) tplan_name,
completed_cdt_act, ----->This is 1 more calculated field corresponding to each TPLAN_NAME
DECODE (LAG (t_objective_id, 1, 0) OVER (ORDER BY t_objective_id),
t_objective_id, ' ',
t_objective_id
) t_objective_id,
completed_activities
FROM (SELECT test_cp.firstname, test_op.emplid,tp.tplan_name,
fn_tp_get_cmpltd_act_cnt (tp.tplan_id,
test_cp.person_id,
test_o.org_id
) completed_cdt_act,
test_tpo.t_objective_id,
( fn_tp_obj_comp_req_act_cdt (test_lp.lp_person_id,
tp.tplan_id,
test_tpo.t_objective_id,
tp.is_credit_based
)
+ fn_tp_obj_comp_opt_act_cdt (test_lp.lp_person_id,
tp.tplan_id,
test_tpo.t_objective_id,
tp.is_credit_based
)
) completed_activities,
tpobjact.activity_id activity_id, -----> Again ,each objective_id we select CAN have multiple activity_ids.The remaining fields gives the activity_name,their status
lr.catalog_item_name,
fn_tp_get_act_type (tpobjact.is_preferred,
tpobjact.is_required) status,
lr.catalog_item_type activity_type, lr.delivery_method, lr.status status1,
FROM test_learning_plan test_lp,
test_training_plan tp,
test_person test_cp,
test_org_person test_op,
test_org test_o
test_tp_objective test_tpo,
test_train_obj_activity tpobjact,
test_learning_record lr,
test_training_objective obj,
WHERE test_lp.lp_person_id = '1'
AND test_cp.person_id = test_lp.lp_person_id
AND tp.tplan_id = test_lp.lp_catalog_hist_id
AND test_op.o_person_id = test_cp.person_id
and test_o.org_id = test_op.O_ORG_ID
AND test_tpo.tplan_id = tp.tplan_id
and lr.tp_ref_lp_id = test_lp.learning_plan_id
AND lr.lr_catalog_history_id = tpobjact.activity_id
AND tpobjact.t_objective_id = test_tpo.t_objective_id);
******************************************************************************************************
The display format is something like this.
firstname emplid Tplan name completed(for TP) Objective Name Credits (for objective) Number of activities completed(for objective) activity name activity completion status
U1 U1 TP1 5
OBJ1 4 3 C1 PASSED
C2 PASSED
C3 WAIVED
T1 ENROLLED
T2 ENROLLED
OBJ2 3 2
S1 ENROLLED
S2 PASSED
T3 WAIVED
U1 U1 TP2 C4 INPROGRESS
50 OBJ11 50 30 C11 PASSED
C22 PASSED
C33 WAIVED
T11 ENROLLED
T22 ENROLLED
OBJ22 40 20
S11 ENROLLED
S22 PASSED
1 个解决方案
#1
I guess the easiest way to approach this is to simplify things.
我想最简单的方法是简化事情。
So start like this.
所以就这样开始吧。
1) What do you want to display - i.e. what is the list of
1)你想要显示什么 - 即什么是列表
If you want a list of employees with "some stuff" against each employee, then write a script that just gets the list of employees.
如果您想要一个员工列表,其中包含针对每个员工的“某些内容”,那么请编写一个只获取员工列表的脚本。
2) Add a bit of complexity
2)增加一点复杂性
If you need to know the "number of something" an employee has done, add that in. You have the option to a) Join the tables and use grouping b) performing a sub-select statement to just get the value. Your choice will depend on performance considerations.
如果您需要知道员工所做的“事情数量”,请将其添加进去。您可以选择a)加入表并使用分组b)执行子选择语句以获取值。您的选择取决于性能考虑因素。
3) Got that working? Add a bit more complexity - what's the next thing you need for each employee that you need to obtain... (i.e. iterate around to add each complex item, knowing that it works at each stage).
3)有工作吗?增加一点复杂性 - 您需要为每个员工获得的下一件事是什么......(即迭代添加每个复杂项目,知道它在每个阶段都有效)。
#1
I guess the easiest way to approach this is to simplify things.
我想最简单的方法是简化事情。
So start like this.
所以就这样开始吧。
1) What do you want to display - i.e. what is the list of
1)你想要显示什么 - 即什么是列表
If you want a list of employees with "some stuff" against each employee, then write a script that just gets the list of employees.
如果您想要一个员工列表,其中包含针对每个员工的“某些内容”,那么请编写一个只获取员工列表的脚本。
2) Add a bit of complexity
2)增加一点复杂性
If you need to know the "number of something" an employee has done, add that in. You have the option to a) Join the tables and use grouping b) performing a sub-select statement to just get the value. Your choice will depend on performance considerations.
如果您需要知道员工所做的“事情数量”,请将其添加进去。您可以选择a)加入表并使用分组b)执行子选择语句以获取值。您的选择取决于性能考虑因素。
3) Got that working? Add a bit more complexity - what's the next thing you need for each employee that you need to obtain... (i.e. iterate around to add each complex item, knowing that it works at each stage).
3)有工作吗?增加一点复杂性 - 您需要为每个员工获得的下一件事是什么......(即迭代添加每个复杂项目,知道它在每个阶段都有效)。