对于那里的SQL大师，一个问题 [英] For the SQL Gurus out there, a question

问题描述

我不擅长SQL - 因此这里是


考虑这个2个表X和Y的场景，其中包含多对多的
关系


表X（名称，重量）

X1 2

X2 1

X3 5
X4 1


表Y（姓名，属性）

Y1 attrib1

Y2 attrib2

Y3 attrib3

Y4 attrib4


关系表Z（id，id）

X1 Y1

X1 Y2

X2 Y1

X3 Y1

X3 Y2

X3 Y4

X4 Y4

所以基于Z的分布（Y /总Y的数量）就像是

Y1 = 100 * 3/7 Y2 = 100 * 2 / 7 Y3 = 100 * 0/7 Y4 = 100 * 2/7

现在我想要考虑X中的weighatge列


所以

X1 Y1 2 * 1

X1 Y2 2 * 1

X2 Y1 1 * 1

X3 Y1 5 * 1

X3 Y2 5 * 1

X3 Y4 5 * 1

X4 Y4 1 * 1


总和：21


鉴于这些表格，我将如何考虑权重并在单个SQL查询中获得

分布。


SQL查询的期望输出


Id Attrib Distrib

--------------------------------

Y1 attrib1 100 * 8/21

Y2 attrib2 100 * 7/21

Y3 attrib3 0

Y4 attrib4 100 * 6/21

Thx

JP

I am not good at SQL - hence here goes

Consider this scenario of 2 tables X and Y with a many to many
relationship

Table X (name,weightage)
X1 2
X2 1
X3 5
X4 1

Table Y (name ,attrib)
Y1 attrib1
Y2 attrib2
Y3 attrib3
Y4 attrib4

Relationship table Z (id,id)
X1 Y1
X1 Y2
X2 Y1
X3 Y1
X3 Y2
X3 Y4
X4 Y4
So based on Z the distribution (count of Y/ Total Y ) is like
Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7
Now I want to factor in the weighatge column in X also

So
X1 Y1 2*1
X1 Y2 2*1
X2 Y1 1*1
X3 Y1 5*1
X3 Y2 5*1
X3 Y4 5*1
X4 Y4 1*1

Sum: 21

Given these tables ,How would I factor in the weights and get the
distribution in a single SQL query.

Desired Output of SQL Query

Id Attrib Distrib
--------------------------------
Y1 attrib1 100*8/21
Y2 attrib2 100*7/21
Y3 attrib3 0
Y4 attrib4 100*6/21
Thx
JP

推荐答案

Oracle还是SQLServer？这是在SQLServer 2000上测试的，但是是ANSI-92所以我认为它在Oracle 9上应该没问题。


CREATE TABLE TableX（xcol CHAR（2） ）PRIMARY KEY，权重INTEGER NOT NULL）


INSERT INTO TableX（xcol，weightage）VALUES（''X1''，2）

INSERT INTO TableX （xcol，weightage）VALUES（''X2''，1）

INSERT INTO TableX（xcol，weightage）VALUES（''X3''，5）

INSERT INTO TableX（xcol，权重）VALUES（''X4''，1）


CREATE TABLE TableY（ycol CHAR（2）PRIMARY KEY，attrib VARCHAR（7）NOT NULL）


INSERT INTO TableY（ycol，attrib）VALUES（''Y1''，''attrib1''）

INSERT INTO TableY（ycol，attrib）VALUES （''Y2''，''attrib2''）

INSERT INTO TableY（ycol，attrib）VALUES（''Y3''，''attrib3''）

INSERT INTO TableY（ycol，attrib）VALUES（''Y4''，''attrib4''）


CREATE TABLE TableZ（xcol CHAR） （2）参考文献TableX（xcol），ycol CHAR（2）

参考表格（ycol），PRIMARY KEY（xcol，ycol））


INSERT INTO TableZ（xcol，ycol）VALUES（''X1''，''Y1''）

INSERT INTO TableZ（xcol，ycol）VALUES（''X1''，''Y2''）

INSERT INTO TableZ（xcol，ycol）VALUES（''X2''，''Y1''）

INSERT INTO TableZ（xcol，ycol）VALUES（'' X3''，''Y1''）

INSERT INTO TableZ（xcol，ycol）VALUES（''X3''，''Y2''）

INSERT INTO TableZ（xcol，ycol）VALUES（''X3''，''Y4''）

INSERT INTO TableZ（xcol，ycol）VALUES（''X4''，''Y4''）


SELECT Y.ycol，Y.attrib，

COALESCE（100 * SUM（X.weightage）/

（SELECT CAST（SUM（X.weightage）AS REAL）

来自TableX AS X

JOIN TableZ AS Z

ON X.xcol = Z. xcol），0）

来自TableY AS Y

LEFT JOIN TableZ AS Z

ON Y.ycol = Z.yco l

LEFT JOIN TableX AS X

ON Z.xcol = X.xcol

GROUP BY Y.ycol，Y.attrib


-

David Portas

SQL Server MVP

-

Oracle or SQLServer? This was tested on SQLServer 2000 but is ANSI-92 so I
think it should be OK on Oracle 9.

CREATE TABLE TableX (xcol CHAR(2) PRIMARY KEY, weightage INTEGER NOT NULL)

INSERT INTO TableX (xcol, weightage) VALUES (''X1'',2)
INSERT INTO TableX (xcol, weightage) VALUES (''X2'',1)
INSERT INTO TableX (xcol, weightage) VALUES (''X3'',5)
INSERT INTO TableX (xcol, weightage) VALUES (''X4'',1)

CREATE TABLE TableY (ycol CHAR(2) PRIMARY KEY, attrib VARCHAR(7) NOT NULL)

INSERT INTO TableY (ycol, attrib) VALUES (''Y1'',''attrib1'')
INSERT INTO TableY (ycol, attrib) VALUES (''Y2'',''attrib2'')
INSERT INTO TableY (ycol, attrib) VALUES (''Y3'',''attrib3'')
INSERT INTO TableY (ycol, attrib) VALUES (''Y4'',''attrib4'')

CREATE TABLE TableZ (xcol CHAR(2) REFERENCES TableX (xcol), ycol CHAR(2)
REFERENCES TableY (ycol), PRIMARY KEY (xcol,ycol))

INSERT INTO TableZ (xcol, ycol) VALUES (''X1'',''Y1'')
INSERT INTO TableZ (xcol, ycol) VALUES (''X1'',''Y2'')
INSERT INTO TableZ (xcol, ycol) VALUES (''X2'',''Y1'')
INSERT INTO TableZ (xcol, ycol) VALUES (''X3'',''Y1'')
INSERT INTO TableZ (xcol, ycol) VALUES (''X3'',''Y2'')
INSERT INTO TableZ (xcol, ycol) VALUES (''X3'',''Y4'')
INSERT INTO TableZ (xcol, ycol) VALUES (''X4'',''Y4'')

SELECT Y.ycol, Y.attrib,
COALESCE(100*SUM(X.weightage)/
(SELECT CAST(SUM(X.weightage) AS REAL)
FROM TableX AS X
JOIN TableZ AS Z
ON X.xcol = Z.xcol),0)
FROM TableY AS Y
LEFT JOIN TableZ AS Z
ON Y.ycol = Z.ycol
LEFT JOIN TableX AS X
ON Z.xcol = X.xcol
GROUP BY Y.ycol, Y.attrib

--
David Portas
SQL Server MVP
--

John Pifer写道：

John Pifer wrote:

我不擅长SQL - 因此这里有用

考虑2个表格X和Y的情景有多对多的关系

I am not good at SQL - hence here goes

Consider this scenario of 2 tables X and Y with a many to many
relationship

让我们就在这里停下来！


这个例子是定义的糟糕设计的例子。从逻辑设计转向

物理设计时，必须解决所有多对多
关系。


修复设计问题！


这是唯一可接受的解决方案。


-

Daniel Morgan http://www.outreach.washington.edu /e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da ****** @ x.washington.edu

（将''x''替换为''u''来回复）

Lets just stop right here!

The example is by definition an example of bad design. All many-to-many
relationships must be resolved when moving from a logical design to a
physical design.

Fix the design problem!

That is the only acceptable solution.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace ''x'' with a ''u'' to reply)

Daniel，


重新阅读John的帖子或查看我发布的DDL。 John为多对多关系设有加盟表

。


-

David Portas

SQL Server MVP

-

Daniel,

Re-read John''s post or check out the DDL I posted. John has a joining table
for the many-to-many relationship.

--
David Portas
SQL Server MVP
--

这篇关于对于那里的SQL大师，一个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！