现在工作上遇到特殊要求,举例子如下
Date User_Name
2007-01-15 John
2007-01-15 Andy
2007-01-15 Jenny
2007-01-16 Steve
2007-01-16 John
2007-01-18 Andrew
2007-01-19 Andy
2007-01-21 Bryan
...
2007-03-02 Andy
2007-03-05 John
2007-03-06 Andy
...现在添加一个colomn,要求在每个月内,当一个用户出现第一次的时候,这个colomn就添加为1,否则为0。Date User_Name First_Count
2007-01-15 John 1
2007-01-15 Andy 1
2007-01-15 Jenny 1
2007-01-16 Steve 1
2007-01-16 John 0
2007-01-18 Andrew 1
2007-01-19 Andy 0
2007-01-21 Bryan 1
...
2007-03-02 Andy 1
2007-03-05 John 1
2007-03-06 Andy 0
...
请问如何设计select 语句条件自动生成这些数据?请高手指教。
Date User_Name
2007-01-15 John
2007-01-15 Andy
2007-01-15 Jenny
2007-01-16 Steve
2007-01-16 John
2007-01-18 Andrew
2007-01-19 Andy
2007-01-21 Bryan
...
2007-03-02 Andy
2007-03-05 John
2007-03-06 Andy
...现在添加一个colomn,要求在每个月内,当一个用户出现第一次的时候,这个colomn就添加为1,否则为0。Date User_Name First_Count
2007-01-15 John 1
2007-01-15 Andy 1
2007-01-15 Jenny 1
2007-01-16 Steve 1
2007-01-16 John 0
2007-01-18 Andrew 1
2007-01-19 Andy 0
2007-01-21 Bryan 1
...
2007-03-02 Andy 1
2007-03-05 John 1
2007-03-06 Andy 0
...
请问如何设计select 语句条件自动生成这些数据?请高手指教。
cnt integer;begin select count(*)
into cnt
from youtable
where User_Name = v_User_Name
and to_char(sysdate, 'yyyy-mm') = to_char(sysdate, 'Date ')
if cnt > 0 then colomn := 0;
else
colomn := 1;
end if;end;具体代码还得根据你的实际情况修改
==============================
现情况为:
表M
Id name
------------
a aName
b bName
c cName表Am(m_id对应表M的id)
Id m_id
-------------
1 a
2 c表Ad(为Am的子表)
Id am_id qty
--------------------
1 1 10
2 1 10
3 2 10表Bm(m_id对应表M的id)
Id m_id
-------------
1 a
2 b表Bd(为Bm的子表)
Id bm_id qty
--------------------
1 1 10
2 2 10
3 2 10当可能还有Cm,Cd;Dm,Dd...多个关联子集,
现在要得到的结果:m.id m.name ad.qty bd.qty
------------------------------
a aName 20 10
b bName 20 -->ad.qty为空
c bName 10 -->bd.qty为空
========================================
二楼兄弟,不对啊,我要的-->ad.qty为空,-->bd.qty为空,这两个不能有值。