建议选择声明 [英] Suggestion for select statement
问题描述
我在db表上有这样的信息
公司用户MYDATA
______________________________
AA
01 AA01
用户AAUS
01用户AA01US
BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER是逻辑的引用另一个像这样的表:
用户组
____________________
用户组
USER2 GROUP2
USER3 GROUP
我希望根据公司和用户/组检索列(MYDATA)。
例如:
如果我有COMPANY = 01且USSER = USER,我想要检索行
MYDATA值= AA01US
如果我有公司= KK和USSER = USER,我想要检索行
MYDATA值= AAUS
如果我有COMPANY = 01且USSER = USER2,我想要检索行
MYDATA值= AA01
如果我有COMPANY = KK且USSER = USER2,我想要检索行
MYDATA值= AA
如果我有COMPANY = KK和USSER = USER3,我想要检索行
MYDATA值= BBGR(因为USER3是GROUP的一部分)
什么是检索它的最佳方法(出于性能目的)?
也许我也可以改变我的表结构。
我更喜欢避免许多选择语句来检索这些信息。
我在Windows平台上使用db2 8和9 DB2 / 400 on v5r3
谢谢
Roberto
roberto写道:
我在数据库表上有这种信息
COMPANY USER MYDATA >
______________________________
AA
01 AA01
用户AAUS
01用户AA01US
BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER是逻辑的引用另一个像这样的表:
用户组
____________________
用户组
USER2 GROUP2
USER3 GROUP
我希望根据公司和用户/组检索列(MYDATA)。
例如:
如果我有COMPANY = 01且USSER = USER,我想要检索行
MYDATA值= AA01US
如果我有公司= KK和USSER = USER,我想要检索行
MYDATA值= AAUS
如果我有COMPANY = 01且USSER = USER2,我想要检索行
MYDATA值= AA01
如果我有COMPANY = KK且USSER = USER2,我想要检索行
MYDATA值= AA
如果我有COMPANY = KK和USSER = USER3,我想要检索行
MYDATA值= BBGR(因为USER3是GROUP的一部分)
检索它的最佳方法(出于性能目的)是什么?
也许我也可以更改我的表结构u。
我宁愿避免使用许多选择语句来检索这些信息。
我在Windows平台上使用db2 8和9和v5r3上的DB2 / 400
谢谢
Roberto
使用当前设置,我建议使用存储的程序:
DECLARE GLOBAL TEMPORARY TABLE T1(公司VARCHAR(2),MyUser
VARCHAR(6),MyData VARCHAR(6))
INSERT INTO SESSION.T1 VALUES(NULL,NULL,''AA''),(''''',NULL,''AA01''),>
(NULL,''USER'',''AAUS''),(''''',''USER'',''AA01US'')
INSERT INTO SESSION.T1 VALUES(NULL,NULL,''BB''),(''''',NULL,''BB02''),
(NULL,''GROUP'',' 'BBGR''),(''''',''GROUP'',''BB02GR'')
DECLARE GLOBAL TEMPORARY TABLE T2(MyUser VARCHAR(6),Group VARCHAR(6))
INSERT INTO SESSION.T2 VALUES(''USER'',''GROUP''),(''USER2'',''GROUP2''),
(''USER3'',''GROUP '')
DROP PROCEDURE Get_MyData
CREATE PROCEDURE Get_MyData
(
IN IN_Company VARCHAR( 0002),
IN IN_MyUser VARCHAR(0006)
)
SPECIFIC Get_MyData
BEGIN
DECLARE列表游标返回给客户
SELECT
公司,
MyUser,
MyData
FROM
SESSION.T1
WHERE
NULLIF(公司,IN_Company)IS NULL
AND
(
NULLIF(MyUser,IN_MyUser)IS NULL
或MyUser IN
(
SELECT
MyUser
FROM
SESSION.T2
WHERE < br $>
GROUP = IN_MyUser
)
)
订购
公司,
MyUser
FETCH第一行;
OPEN List;
结束
CALL Get_MyData(''01'',''USER'')
CALL Get_MyData (''KK'',''USER'')
CALL Get_MyData(''01'',''USER2'')
CALL Get_MyData(''KK '','''USER2'')
CALL Get_MyData(''KK'',''GROUP'')
但是,设置本身确实如此似乎不符合逻辑。保持一个COLUMN
可以引用两个独立的COLUMN之一本身就是可疑的。
B.
谢谢你Brian,
你建议另一种设置(表结构)吗?
Roberto
roberto写道:
我有这种数据库表上的信息
公司用户MYDATA
______________________________
AA
01 AA01
USER AAUS
01用户AA01US
BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER是逻辑的引用另一个像这样的表:
用户组
____________________
用户组
USER2 GROUP2
USER3 GROUP
我希望根据公司和用户/组检索列(MYDATA)。
例如:
如果我有COMPANY = KK和USSER = USER2,我想要检索行
MYDATA值= AA
为什么未检索到MYDATA值= BB?
MYDATA值= AA和BB都是COMPANY IS NULL且USER IS NULL。
>
我在Windows平台上使用db2 8和9,在v5r3上使用DB2 / 400
Roberto
CREATE TABLE输入数据
(公司VARCHAR(2)
,用户VARCHAR( 6)
);
INSERT INTO InputData
VALUES
('''01'' ,''用户'')
,(''KK'','用户'')
,(''01'',''USER2'')
,('''KK'',''USER2'')
,(''KK'',''USER3'')
;
我假设多行满足条件,采取MIN(MYDATA)。
------------------------------进入的命令
------------------------------
SELECT InD.Company AS In_Company
,InD.User AS In_User
,MIN(MyData)AS MyData
来自InputData InD
, T1
WHERE
(T1.Company = InD.Company
或
T1.Company IS NULL
AND NOT EXISTS
(SELECT *
来自T1 T1N
WHERE T1N.Company = InD.Company
)
)
AND
(T1.User = InD.User
OR
T1.User
=(选择T2.Group
来自T2
WHERE InD.User = T2.User
)
而不是EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.User = InD.User
)
或
T1.User IS NULL
而不是EXISTS
(SELECT *
$ T1 $ b来自T1 T1N
LEFT OUTER JOIN
T2 T2N
ON T1N.User = T2N.Group
WHERE InD.User IN(T1N.User,T2N.User)
)
)
GROUP BY InD.Company,InD.User
订购InD.User,InD.Company;
------------------------------------------------ ------------------------------
IN_COMPANY IN_USER MYDATA
---------- ------- ------
01用户AA01US
KK用户AAUS
01 USER2 AA01
KK USER2 AA
KK USER3 BBGR
5条记录被选中。
I have this kind of information on a db table
COMPANY USER MYDATA
______________________________
AA
01 AA01
USER AAUS
01 USER AA01US
BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER is a "logical" reference to another table like this one:
USER GROUP
____________________
USER GROUP
USER2 GROUP2
USER3 GROUP
I want retrieve column (MYDATA) according company and user/group.
For example:
If I have COMPANY = 01 and USSER = USER, I want retrieve row with
MYDATA value = AA01US
If I have COMPANY = KK and USSER = USER, I want retrieve row with
MYDATA value = AAUS
If I have COMPANY = 01 and USSER = USER2, I want retrieve row with
MYDATA value = AA01
If I have COMPANY = KK and USSER = USER2, I want retrieve row with
MYDATA value = AA
If I have COMPANY = KK and USSER = USER3, I want retrieve row with
MYDATA value = BBGR (because USER3 is part of GROUP)
What is the best way (for performance purpose) to retrieve it?
Maybe I can also change my table structure.
I prefer to avoi many select statements to retrive this information.
I''m using db2 8 and 9 on windows platform and DB2/400 on v5r3
Thank You
Roberto
roberto wrote:I have this kind of information on a db table
COMPANY USER MYDATA
______________________________
AA
01 AA01
USER AAUS
01 USER AA01US
BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER is a "logical" reference to another table like this one:
USER GROUP
____________________
USER GROUP
USER2 GROUP2
USER3 GROUP
I want retrieve column (MYDATA) according company and user/group.
For example:
If I have COMPANY = 01 and USSER = USER, I want retrieve row with
MYDATA value = AA01US
If I have COMPANY = KK and USSER = USER, I want retrieve row with
MYDATA value = AAUS
If I have COMPANY = 01 and USSER = USER2, I want retrieve row with
MYDATA value = AA01
If I have COMPANY = KK and USSER = USER2, I want retrieve row with
MYDATA value = AA
If I have COMPANY = KK and USSER = USER3, I want retrieve row with
MYDATA value = BBGR (because USER3 is part of GROUP)
What is the best way (for performance purpose) to retrieve it?
Maybe I can also change my table structure.
I prefer to avoi many select statements to retrive this information.
I''m using db2 8 and 9 on windows platform and DB2/400 on v5r3
Thank You
RobertoWith the current setup, i''d suggest either a stored PROCEDURE.:
DECLARE GLOBAL TEMPORARY TABLE T1(Company VARCHAR(2), MyUser
VARCHAR(6), MyData VARCHAR(6))
INSERT INTO SESSION.T1 VALUES (NULL, NULL, ''AA''), (''01'', NULL, ''AA01''),
(NULL, ''USER'', ''AAUS''), (''01'', ''USER'', ''AA01US'')
INSERT INTO SESSION.T1 VALUES (NULL, NULL, ''BB''), (''02'', NULL, ''BB02''),
(NULL, ''GROUP'', ''BBGR''), (''02'', ''GROUP'', ''BB02GR'')
DECLARE GLOBAL TEMPORARY TABLE T2(MyUser VARCHAR(6), Group VARCHAR(6))
INSERT INTO SESSION.T2 VALUES (''USER'', ''GROUP''), (''USER2'', ''GROUP2''),
(''USER3'', ''GROUP'')
DROP PROCEDURE Get_MyData
CREATE PROCEDURE Get_MyData
(
IN IN_Company VARCHAR(0002),
IN IN_MyUser VARCHAR(0006)
)
SPECIFIC Get_MyData
BEGIN
DECLARE List CURSOR WITH RETURN TO CLIENT FOR
SELECT
Company,
MyUser,
MyData
FROM
SESSION.T1
WHERE
NULLIF(Company, IN_Company) IS NULL
AND
(
NULLIF(MyUser, IN_MyUser) IS NULL
OR MyUser IN
(
SELECT
MyUser
FROM
SESSION.T2
WHERE
GROUP = IN_MyUser
)
)
ORDER BY
Company,
MyUser
FETCH FIRST ROW ONLY;
OPEN List;
END
CALL Get_MyData(''01'', ''USER'')
CALL Get_MyData(''KK'', ''USER'')
CALL Get_MyData(''01'', ''USER2'')
CALL Get_MyData(''KK'', ''USER2'')
CALL Get_MyData(''KK'', ''GROUP'')
However, the setup itself does not seem logical. Keeping a COLUMN that
can refer to one of two separate COLUMNs is in itself suspect.
B.
Thank You Brian,
Do you suggest another kind of setup (table structure)?
Roberto
roberto wrote:I have this kind of information on a db table
COMPANY USER MYDATA
______________________________
AA
01 AA01
USER AAUS
01 USER AA01US
BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER is a "logical" reference to another table like this one:
USER GROUP
____________________
USER GROUP
USER2 GROUP2
USER3 GROUP
I want retrieve column (MYDATA) according company and user/group.
For example:
If I have COMPANY = KK and USSER = USER2, I want retrieve row with
MYDATA value = AAWhy MYDATA value = BB was not retrieved?
Both of MYDATA value = AA and BB are COMPANY IS NULL and USER IS NULL.
>
I''m using db2 8 and 9 on windows platform and DB2/400 on v5r3
Thank You
Roberto
CREATE TABLE InputData
(Company VARCHAR(2)
,User VARCHAR(6)
);
INSERT INTO InputData
VALUES
(''01'', ''USER'')
,(''KK'', ''USER'')
,(''01'', ''USER2'')
,(''KK'', ''USER2'')
,(''KK'', ''USER3'')
;
I assumed multiple rows satisfy the conditions, take MIN(MYDATA).
------------------------------ Commands Entered
------------------------------
SELECT InD.Company AS In_Company
, InD.User AS In_User
, MIN(MyData) AS MyData
FROM InputData InD
, T1
WHERE
(T1.Company = InD.Company
OR
T1.Company IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.Company = InD.Company
)
)
AND
(T1.User = InD.User
OR
T1.User
= (SELECT T2.Group
FROM T2
WHERE InD.User = T2.User
)
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.User = InD.User
)
OR
T1.User IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
LEFT OUTER JOIN
T2 T2N
ON T1N.User = T2N.Group
WHERE InD.User IN (T1N.User, T2N.User)
)
)
GROUP BY InD.Company, InD.User
ORDER BY InD.User, InD.Company;
------------------------------------------------------------------------------
IN_COMPANY IN_USER MYDATA
---------- ------- ------
01 USER AA01US
KK USER AAUS
01 USER2 AA01
KK USER2 AA
KK USER3 BBGR
5 record(s) selected.
这篇关于建议选择声明的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!