w********r
发帖数: 727 | 1 I have two tables:
Table A:
Name Project Score
Jack A 100
Jack B 50
Jack C 50
Susan A 50
Susan B 50
Table B:
Name Project Score
Brian A 70
Brian B 50
Brian D 30
I want to compare the table difference sorted by name. Eg. compare Jack in
table A with table B and then Susan in table A with table B. The result need
to be:
Name Project Score
Jack A 30 (30 is from 100-70)
Jack C 50 (50 is from 50-null)
Jack D -30 (-30is from null -30)
Susan A -20 (20 is from 50-70)
Suan D -30 (-30 is from null -30)
I used full outer join and group by
But for project D, it is not in table A, so I can only get:
Null D -30
I need Null to be the name of current group by column, anyway to do it? | s**********o
发帖数: 14359 | 2 A LEFT JOIN B
A.score - IsNull( B.score, 0) | w********r
发帖数: 727 | 3 left join will miss project D case
The key is for D case, name need to be from Table A, not Table B. | B*****g
发帖数: 34098 | 4 看不懂踢
【在 w********r 的大作中提到】
: I have two tables:
: Table A:
: Name Project Score
: Jack A 100
: Jack B 50
: Jack C 50
: Susan A 50
: Susan B 50
: Table B:
: Name Project Score
| s**********o
发帖数: 14359 | 5 Susan D TABLE A里没有，怎么会出来个 -30呢，自己默认是0，那就得补RECORD，没
有其他办法，不过你的问题是不太清楚
【在 w********r 的大作中提到】
: left join will miss project D case
: The key is for D case, name need to be from Table A, not Table B.
| w********r
发帖数: 727 | 6
insert data into table的时候是没有project/score就不insert，所以有这个麻烦
就是Table B里的Brian是标准table
其他人都放在table A里和 Brian比较区别
A里Jack有的project，B里没有，要找出来
A里Jack没有的project，B里有，也要找出来
这个区别是Jack和B里Brian的区别，所以第一列都要是Jack(even in project D)
这个好像MS SQL是搞不定
我最后是excel/vba里面先把A里人列出来，然后循环一个一个和B比较
但是这样很慢，要比较和调用SQL很多次
【在 s**********o 的大作中提到】
: Susan D TABLE A里没有，怎么会出来个 -30呢，自己默认是0，那就得补RECORD，没
: 有其他办法，不过你的问题是不太清楚
| y****9
发帖数: 144 | 7 in princial: create a view C from A union all B
create view C (name, project, score, i) as
select a.*, 1 from A a
union all
select b.*, 2 form B b
select name, project,
sum(case when i=1 then score else -1 * score end)
from C
group by name, project;
Just demonstrate idea, ignore syntax error.
【在 w********r 的大作中提到】
:
: insert data into table的时候是没有project/score就不insert，所以有这个麻烦
: 就是Table B里的Brian是标准table
: 其他人都放在table A里和 Brian比较区别
: A里Jack有的project，B里没有，要找出来
: A里Jack没有的project，B里有，也要找出来
: 这个区别是Jack和B里Brian的区别，所以第一列都要是Jack(even in project D)
: 这个好像MS SQL是搞不定
: 我最后是excel/vba里面先把A里人列出来，然后循环一个一个和B比较
: 但是这样很慢，要比较和调用SQL很多次
| y****9
发帖数: 144 | 8 read the original questin again, maybe I did not understand the question
correctly. Anyway,just 灌点水 哈哈！
【在 y****9 的大作中提到】
: in princial: create a view C from A union all B
: create view C (name, project, score, i) as
: select a.*, 1 from A a
: union all
: select b.*, 2 form B b
: select name, project,
: sum(case when i=1 then score else -1 * score end)
: from C
: group by name, project;
: Just demonstrate idea, ignore syntax error.
| w********r
发帖数: 727 | 9 这个问题很绕嘛？怎么这么多人都看不懂。
再举例解释一次，就是有很多人，做很多项目，每人做每个项目的结果有个分数。
不是每个人都做所有项目，所以有些项目只有一部分人参与了。
现在要把所有人和其中一个人（BRIAN）做比较。当然BRIAN也不是所有项目都做了，而
且BRIAN做的项目其他人可能也没做。
要得出一个比较的结果表格显示每个人和BRIAN做的每个项目分数区别。这里假设如果
那个人没有做BRIAN的项目，他的分数就是0。BRIAN没做的项目BRIAN的分数也是0。
主要问题是，如果这个人（假设JACK）没有做BRIAN的项目，比较的时候表格这行必须
要归在JACK
名下，因为这个是JACK和BRIAN比较的差别。但是在MSSQL里面因为这行TABLE里面没有
，full join后第一列值就是NULL，怎么把这个NULL变成JACK一直搞不定。 | B*****g
发帖数: 34098 | 10 Oracle code，SQL server可能需要改一下
SELECT CASE WHEN D.name IS NULL THEN A.name ELSE D.name END,
CASE WHEN A.score IS NULL THEN 0 ELSE A.score END -CASE WHEN D.
score IS NULL THEN 0 ELSE D.score END
FROM A FULL OUTER JOIN
(
SELECT C.name, B.Project, B.score
FROM B CROSS JOIN (SELECT DISTINCT A.name FROM A) C
) D
ON A.name = D.name AND A.Project = D.Project
【在 w********r 的大作中提到】
: 这个问题很绕嘛？怎么这么多人都看不懂。
: 再举例解释一次，就是有很多人，做很多项目，每人做每个项目的结果有个分数。
: 不是每个人都做所有项目，所以有些项目只有一部分人参与了。
: 现在要把所有人和其中一个人（BRIAN）做比较。当然BRIAN也不是所有项目都做了，而
: 且BRIAN做的项目其他人可能也没做。
: 要得出一个比较的结果表格显示每个人和BRIAN做的每个项目分数区别。这里假设如果
: 那个人没有做BRIAN的项目，他的分数就是0。BRIAN没做的项目BRIAN的分数也是0。
: 主要问题是，如果这个人（假设JACK）没有做BRIAN的项目，比较的时候表格这行必须
: 要归在JACK
: 名下，因为这个是JACK和BRIAN比较的差别。但是在MSSQL里面因为这行TABLE里面没有
| | | s**********o
发帖数: 14359 | 11 先把没参与就是0的先找出来union table a 然后在left join b, 这样比较保险 | i*****w
发帖数: 75 | 12 The following code is not optimized for performance, I just would like to
show the steps to solve the problem.
HTH
-- Data Preparation for Table A
declare @TblA table(name varchar(20), project varchar(10), score int)
insert into @TblA(name, project, score)
select 'Jack', 'A', 100
union all
select 'Jack', 'B', 50
union all
select 'Jack', 'C', 50
union all
select 'Susan', 'A' , 50
union all
select 'Susan', 'B' , 50
-- Data Preparation for Table B
declare @TblB table(name varchar(20), project varchar(10), score int)
insert into @TblB(name, project, score)
select 'Brian', 'A', 70
union all
select 'Brian', 'B', 50
union all
select 'Brian', 'D', 30
-- Get All projects available
declare @tblC table (projectName varchar(10))
insert into @tblC (projectName)
select distinct project from @TblA
union
select distinct project from @TblB
-- Get All Names avaialble
declare @tblD table (Name varchar(10))
insert into @tblD (Name)
select distinct Name from @TblA
-- Get all combination of names and projects
declare @TblE table(name varchar(20), project varchar(10), score int)
insert into @TblE(name, project, score)
select Name, projectName, 0 as score
from @tblC cross join @tblD
-- Get current data, leave not applied projects with 0 score
Update a
set a.score = b.score
from @TblE a
Inner join @TblA b ON a.name = b.name and a.project = b.project
-- Get records that the score difference is not equal to zero
select * from
(
select a.name, a.project, a.score - isnull(b.score,0) score from
@TblE a
left join @TblB b on a.project = b.project
)b where b.score <> 0
【在 w********r 的大作中提到】
: 这个问题很绕嘛？怎么这么多人都看不懂。
: 再举例解释一次，就是有很多人，做很多项目，每人做每个项目的结果有个分数。
: 不是每个人都做所有项目，所以有些项目只有一部分人参与了。
: 现在要把所有人和其中一个人（BRIAN）做比较。当然BRIAN也不是所有项目都做了，而
: 且BRIAN做的项目其他人可能也没做。
: 要得出一个比较的结果表格显示每个人和BRIAN做的每个项目分数区别。这里假设如果
: 那个人没有做BRIAN的项目，他的分数就是0。BRIAN没做的项目BRIAN的分数也是0。
: 主要问题是，如果这个人（假设JACK）没有做BRIAN的项目，比较的时候表格这行必须
: 要归在JACK
: 名下，因为这个是JACK和BRIAN比较的差别。但是在MSSQL里面因为这行TABLE里面没有
| y****9
发帖数: 144 | 13
I think Beijin's above idea really wonderful and do solve the problem. I
tested as follows:
SQL> select * from a;
NAME PROJECT SCORE
------------------------------ ---------- ----------
Jack A 100
Jack B 50
Jack C 50
Susan A 50
Susan B 50
SQL> select * from b;
NAME PROJECT SCORE
------------------------------ ---------- ----------
Brian B 50
Brian A 70
Brian D 30
-- a little bit modification from Being's code for clarity
-- added ordr by and project col
SQL> SELECT CASE WHEN D.name IS NULL THEN A.name ELSE D.name END name,
2 nvl(A.project, D.project ) project,
3 CASE WHEN A.score IS NULL THEN 0 ELSE A.score END
4 - CASE WHEN D.score IS NULL THEN 0 ELSE D.score END score
5 FROM A FULL OUTER JOIN
6 (
7 SELECT C.name, B.Project, B.score
8 FROM B CROSS JOIN (SELECT DISTINCT A.name FROM A) C
9 ) D
10 ON A.name = D.name AND A.Project = D.Project
11 order by 1, 2;
NAME PROJECT SCORE
------------------------------ ---------- ----------
Jack A 30
Jack B 0
Jack C 50
Jack D -30
Susan A -20
Susan B 0
Susan D -30
-- my code using group by
SQL>
SQL> select name, project, sum(score)
2 from (
3 select A.name, A.project, A.score
4 from A
5 union all
6 select D.name, D.project, D.score
7 from
8 (
9 SELECT C.name, B.Project, -1 *B.score score
10 FROM B CROSS JOIN (SELECT DISTINCT A.name FROM A) C
11 ) D
12 )
13 group by name, project
14 order by 1, 2
15 ;
NAME PROJECT SUM(SCORE)
------------------------------ ---------- ----------
Jack A 30
Jack B 0
Jack C 50
Jack D -30
Susan A -20
Susan B 0
Susan D -30
【在 B*****g 的大作中提到】
: Oracle code，SQL server可能需要改一下
: SELECT CASE WHEN D.name IS NULL THEN A.name ELSE D.name END,
: CASE WHEN A.score IS NULL THEN 0 ELSE A.score END -CASE WHEN D.
: score IS NULL THEN 0 ELSE D.score END
: FROM A FULL OUTER JOIN
: (
: SELECT C.name, B.Project, B.score
: FROM B CROSS JOIN (SELECT DISTINCT A.name FROM A) C
: ) D
: ON A.name = D.name AND A.Project = D.Project
| w********r
发帖数: 727 | 14 Thanks!
Cross Join solves this problem
【在 B*****g 的大作中提到】
: Oracle code，SQL server可能需要改一下
: SELECT CASE WHEN D.name IS NULL THEN A.name ELSE D.name END,
: CASE WHEN A.score IS NULL THEN 0 ELSE A.score END -CASE WHEN D.
: score IS NULL THEN 0 ELSE D.score END
: FROM A FULL OUTER JOIN
: (
: SELECT C.name, B.Project, B.score
: FROM B CROSS JOIN (SELECT DISTINCT A.name FROM A) C
: ) D
: ON A.name = D.name AND A.Project = D.Project
| w********r
发帖数: 727 | 15 关键就是cross join
cross join以后，所有人所有项目的组合都出来了，没有参与就是0
之前没有参与的是空行，所以才有这么多麻烦。这个解决以后，其他的都是现成的了
【在 y****9 的大作中提到】
:
: I think Beijin's above idea really wonderful and do solve the problem. I
: tested as follows:
: SQL> select * from a;
: NAME PROJECT SCORE
: ------------------------------ ---------- ----------
: Jack A 100
: Jack B 50
: Jack C 50
: Susan A 50
| B*****g
发帖数: 34098 | 16 这个没包子不合适吧，嘿嘿
【在 w********r 的大作中提到】
: Thanks!
: Cross Join solves this problem
| y****w
发帖数: 3747 | 17 本版从不发包子实在是败笔。是不是感觉像给人做作业，哈哈。
【在 B*****g 的大作中提到】
: 这个没包子不合适吧，嘿嘿
| B*****g
发帖数: 34098 | 18 不行，以后再问sql题，必须先给包子
【在 y****w 的大作中提到】
: 本版从不发包子实在是败笔。是不是感觉像给人做作业，哈哈。
| y****w
发帖数: 3747 | 19 你能要来funding么，有的话咱可以host sql chanllenge。或者交1个包子参加，才能
看到别人的答案，最终最佳解法等。or等2个星期。
【在 B*****g 的大作中提到】
: 不行，以后再问sql题，必须先给包子
| w********r
发帖数: 727 | 20 不好意思来晚了
一共26个WB全转给beijing大美女啦
watercolor，您好：
您转给 Beijing,现金(伪币):26,收取手续费:0.26
站务
【在 B*****g 的大作中提到】
: 这个没包子不合适吧，嘿嘿
| B*****g
发帖数: 34098 | 21 你这属于倾家荡产
【在 w********r 的大作中提到】
: 不好意思来晚了
: 一共26个WB全转给beijing大美女啦
: watercolor，您好：
: 您转给 Beijing,现金(伪币):26,收取手续费:0.26
: 站务
| w********r
发帖数: 727 | 22 也是啊，应该留点下次再问大拿的时候用
【在 B*****g 的大作中提到】
: 你这属于倾家荡产
|
|
