n*6
2 楼
The original question is for SAS developers. However, I think the problem
can be solved by using SQL in database. I am working on that now for fun.
can be solved by using SQL in database. I am working on that now for fun.
l*9
3 楼
omg。。。。这也太快了.我才申请不到2分钟
h*l
4 楼
first of all you will need an unique ID for each row added to make this
possible. For example:
Table: TEST1
id cn charge
possible. For example:
Table: TEST1
id cn charge
b*e
5 楼
欢迎、欢迎
有好瓷器，发上来，让大家学习、学习。
有好瓷器，发上来，让大家学习、学习。
j*n
6 楼
It is a good solution.
btw, try CTE with ROW_NUMBER() in 2k5 and 2k8. Same way to solve problem but
looks fancier.
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
btw, try CTE with ROW_NUMBER() in 2k5 and 2k8. Same way to solve problem but
looks fancier.
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
l*9
7 楼
国内看过一些~美国这边资源有限阿~
主要过来向大家学习的
主要过来向大家学习的
B*g
8 楼
1. 显然要pk
2. 显然要timestamp，要是先负后正的不能删，比如
Card_Number Charge Date
A123 -14.56 2008/01/01
A123 15.23 2008/01/02
A123 14.56 2008/01/03
那个A123都不能取消
3. 如果负的不多应该用loop一个一个找
4. 不过大家还是喜欢拽sql，咱也写一个，没测试，oracle。
2. 显然要timestamp，要是先负后正的不能删，比如
Card_Number Charge Date
A123 -14.56 2008/01/01
A123 15.23 2008/01/02
A123 14.56 2008/01/03
那个A123都不能取消
3. 如果负的不多应该用loop一个一个找
4. 不过大家还是喜欢拽sql，咱也写一个，没测试，oracle。
B*g
9 楼
这套数据上面的sql work吗？
Card_Number Charge
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
but
【在 j*****n 的大作中提到】
: It is a good solution.
: btw, try CTE with ROW_NUMBER() in 2k5 and 2k8. Same way to solve problem but
: looks fancier.
Card_Number Charge
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
but
【在 j*****n 的大作中提到】
: It is a good solution.
: btw, try CTE with ROW_NUMBER() in 2k5 and 2k8. Same way to solve problem but
: looks fancier.
j*n
10 楼
Yes, it works. I also tried adding more cases and seems fine.
btw, his code is added into my knowledge base, so I wouldn't have headache
in the future... just like your RANK()... hehe...
【在 B*****g 的大作中提到】
: 这套数据上面的sql work吗？
: Card_Number Charge
: A123 15.23
: A123 -14.56
: A234 11.12
: A234 3.87
: A234 11.12
: A234 -11.12
: A234 4.86
:
btw, his code is added into my knowledge base, so I wouldn't have headache
in the future... just like your RANK()... hehe...
【在 B*****g 的大作中提到】
: 这套数据上面的sql work吗？
: Card_Number Charge
: A123 15.23
: A123 -14.56
: A234 11.12
: A234 3.87
: A234 11.12
: A234 -11.12
: A234 4.86
:
B*g
11 楼
我看错了。你为啥不存我的？哈哈
【在 j*****n 的大作中提到】
: Yes, it works. I also tried adding more cases and seems fine.
: btw, his code is added into my knowledge base, so I wouldn't have headache
: in the future... just like your RANK()... hehe...
【在 j*****n 的大作中提到】
: Yes, it works. I also tried adding more cases and seems fine.
: btw, his code is added into my knowledge base, so I wouldn't have headache
: in the future... just like your RANK()... hehe...
n*6
12 楼
I worked out a SAS version where SQL is extensively used and can be easily
converted into database. Next step is to see the difference in SAS solution
and database solution.
/* LOAD IN DATA */
DATA CARD;
INPUT Card_Number $ Charge;
CARDS;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
;
RUN;
/* CREATE TEMP TABLE FOR DISTINCT CARD_NUMBER, ADD NEW COLUMN AS ID */
PROC SQL;
CREATE TABLE CARD_NUM_STAGE AS
SELECT Card_Number, Count(Card_Number) AS Card_PerNum_
converted into database. Next step is to see the difference in SAS solution
and database solution.
/* LOAD IN DATA */
DATA CARD;
INPUT Card_Number $ Charge;
CARDS;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
;
RUN;
/* CREATE TEMP TABLE FOR DISTINCT CARD_NUMBER, ADD NEW COLUMN AS ID */
PROC SQL;
CREATE TABLE CARD_NUM_STAGE AS
SELECT Card_Number, Count(Card_Number) AS Card_PerNum_
n*6
13 楼
厉害啊。我一开始就想用join来做，因为join速度最快。可是没搞出来。
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
n*6
14 楼
能解释一下吗？
1，2：z的where clause写在select clause里面，起什么作用呢？
3：t alias 派什么作用？
select * from @TEST1 y
where id not in
(select id
from (select
(select top 1 a.id from TEST1
left outer join @TEST1 b on a.cn = b.cn
and a.charge = (b.charge * -1)
and a.charge > 0
where z.cn = a.cn /*1*/
and a.charge = -z.charge /*2*/
) as id
from @TEST1 z
)t /*3*/
where id i
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
1，2：z的where clause写在select clause里面，起什么作用呢？
3：t alias 派什么作用？
select * from @TEST1 y
where id not in
(select id
from (select
(select top 1 a.id from TEST1
left outer join @TEST1 b on a.cn = b.cn
and a.charge = (b.charge * -1)
and a.charge > 0
where z.cn = a.cn /*1*/
and a.charge = -z.charge /*2*/
) as id
from @TEST1 z
)t /*3*/
where id i
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
n*6
15 楼
SOLUTION 2
PROC SQL;
CREATE TABLE CARD_OUTPUT AS
SELECT * FROM (SELECT * FROM CARD A WHERE A.CHARGE>0)
EXCEPT ALL (SELECT CARD_NUMBER, ABS(CHARGE) FROM CARD B WHERE B.CHARGE < 0);
QUIT;
PROC SQL;
CREATE TABLE CARD_OUTPUT AS
SELECT * FROM (SELECT * FROM CARD A WHERE A.CHARGE>0)
EXCEPT ALL (SELECT CARD_NUMBER, ABS(CHARGE) FROM CARD B WHERE B.CHARGE < 0);
QUIT;
h*l
16 楼
z在这里起到partition by的作用, 如果你用 RANK()的话.
t alias的作用是给temp table的.
【在 n********6 的大作中提到】
: 能解释一下吗？
: 1，2：z的where clause写在select clause里面，起什么作用呢？
: 3：t alias 派什么作用？
: select * from @TEST1 y
: where id not in
: (select id
: from (select
: (select top 1 a.id from TEST1
: left outer join @TEST1 b on a.cn = b.cn
: and a.charge = (b.charge * -1)
t alias的作用是给temp table的.
【在 n********6 的大作中提到】
: 能解释一下吗？
: 1，2：z的where clause写在select clause里面，起什么作用呢？
: 3：t alias 派什么作用？
: select * from @TEST1 y
: where id not in
: (select id
: from (select
: (select top 1 a.id from TEST1
: left outer join @TEST1 b on a.cn = b.cn
: and a.charge = (b.charge * -1)
h*l
17 楼
SQL SERVER没有except all :(
这样很简单.
);
【在 n********6 的大作中提到】
: SOLUTION 2
: PROC SQL;
: CREATE TABLE CARD_OUTPUT AS
: SELECT * FROM (SELECT * FROM CARD A WHERE A.CHARGE>0)
: EXCEPT ALL (SELECT CARD_NUMBER, ABS(CHARGE) FROM CARD B WHERE B.CHARGE < 0);
: QUIT;
这样很简单.
);
【在 n********6 的大作中提到】
: SOLUTION 2
: PROC SQL;
: CREATE TABLE CARD_OUTPUT AS
: SELECT * FROM (SELECT * FROM CARD A WHERE A.CHARGE>0)
: EXCEPT ALL (SELECT CARD_NUMBER, ABS(CHARGE) FROM CARD B WHERE B.CHARGE < 0);
: QUIT;
n*6
18 楼
sql server and oracle都有实现类似功能的语句，似乎是except, minus, 或其他。
【在 h******l 的大作中提到】
: SQL SERVER没有except all :(
: 这样很简单.
:
: );
【在 h******l 的大作中提到】
: SQL SERVER没有except all :(
: 这样很简单.
:
: );
h*l
19 楼
有EXCEPT但是没有EXCEPT ALL. 这样会受到很多的限制.
【在 n********6 的大作中提到】
: sql server and oracle都有实现类似功能的语句，似乎是except, minus, 或其他。
【在 n********6 的大作中提到】
: sql server and oracle都有实现类似功能的语句，似乎是except, minus, 或其他。
n*6
20 楼
刚查sql server 2005 books online,
有except/intersect
没有all option
【在 h******l 的大作中提到】
: 有EXCEPT但是没有EXCEPT ALL. 这样会受到很多的限制.
有except/intersect
没有all option
【在 h******l 的大作中提到】
: 有EXCEPT但是没有EXCEPT ALL. 这样会受到很多的限制.
相关阅读
Question on Analysis managerJava调用Oracle存储过程的问题汉字储存问题请教[转载] 问一个弱问题insert疑问summary data by category in ArcGis问一个excel的弱问题Organic chemistry WebBasic oracle questions.Q on adding new column弱问：如何用access打开并修改远程服务器上的数据库？请教一个sql问题Where to find log files in SQL server?mysql or sqlserver?Database Interview Questionsmssql的查询器无法正确显示日期.Re: 请问怎么在QUERY出来的结果里第一列加上自己的INCREMENTALAccess application form 转成html怎么写这个Query，谢谢Strange VB/ADO problem (转载)