oracle SQL根据另一个表的列值显示列

qyswt5oh 于 2023-04-20 发布在 Oracle

关注(0)|答案(2)|浏览(122)

ORACLE SQL与我的半初学者的经验。
我有一个表Credits，每个ID一行：

ID, ArtCred, BizCred, HumCred, NatCred, TekCred, GenCred
5001, 12, 7.5, 12, 14, 11, 9
5002, 10.5, 6, 5, 4, 6, 3

每个学生的ID和每个课程组的学分。
学生可以从GrpChoice表中选择一个课程组，该表显示了他们的选择和做出选择的日期。

ID, GroupChoice, DateChoice
5001, ART, 3/3/2010
5001, BIZ, 5/5/2015
5001, NAT, 6/23/2022
5002, ART, 6/23/2023

即使没有选择，我也可以查询每个学生的每个组的学分）：

SELECT
    ID,
    GroupChoice, 
    artcred,
    bizcred,
    humcred,
    natcred,
    tekcred,
    gencred
FROM 
    Grpchoice
INNER JOIN 
    Credits ON GrpChoice.ID = Credits.ID
ORDER BY 
    Grpchoice.datechoice Desc

ID   Group   Art     Biz     Hum     Nat    Tek     Gen
5001  TEK   12  7.5 12  14  11  9
5001  HHS   12  7.5 12  14  11  9
5001  BIZ   12  7.5 12  14  11  9
5001  ART   12  7.5 12  14  11  9
5002  ART       10.5     6       5       4       6      3

但我需要显示每个ID和GrpChoice获得的学分（按日期desc排序）：
我只想查看ID 5001：

ART 12
 BIZ 7.5
 NAT 14

对于ID 5002：

ART 10.5

我试过一个枢轴，但由于GrpChoice值可以更改，我不认为我可以使用它。除非我以不同的方式设置我的数据，以ID为枢轴...我只读过关于枢轴的文章。
CASE GRPCHOICE不起作用，因为它不会根据SELECT的值修改SELECT（我不能“SELECT CASE When GrpChoice =“Art”then SELECT artCHr，WHEN GrpChoice=“BIZ”then SELECT bizCHr...”）。
我看过OVER（PARTITION BY），但我不知道它是如何工作的。
欢迎提出建议，谢谢。

oracle

来源：https://stackoverflow.com/questions/76009542/sql-show-a-column-based-on-another-tables-column-values

2条答案

按热度按时间

efzxgjgh1#

使用UNPIVOT，而不是PIVOT，然后连接两个表：

SELECT c.id, subject, credit
FROM   credits
       UNPIVOT (
         credit FOR subject IN (
           artcred As 'ART',
           bizcred AS 'BIZ',
           humcred AS 'HUM',
           natcred AS 'NAT',
           tekcred AS 'TEK',
           gencred AS 'GEN'
         )
       ) c
       INNER JOIN grpchoice g
       ON c.id = g.id AND c.subject = g.groupchoice

其中，对于样本数据：

CREATE TABLE credits (ID, ArtCred, BizCred, HumCred, NatCred, TekCred, GenCred) AS
SELECT 5001, 12, 7.5, 12, 14, 11, 9 FROM DUAL UNION ALL
SELECT 5002, 10.5, 6, 5, 4, 6, 3 FROM DUAL;

CREATE TABLE GrpChoice (ID, GroupChoice, DateChoice) AS
SELECT 5001, 'ART', DATE '2010-03-03' FROM DUAL UNION ALL
SELECT 5001, 'BIZ', DATE '2015-05-05' FROM DUAL UNION ALL
SELECT 5001, 'NAT', DATE '2022-06-23' FROM DUAL UNION ALL
SELECT 5002, 'ART', DATE '2023-06-23' FROM DUAL;

输出：
| ID|主题|荣誉资质|
| --------------|--------------|--------------|
| 五零一|艺术|十二岁|
| 五零一|BIZ|七点五|
| 五零一|NAT|十四岁|
| 五零零二|艺术|十点五|
fiddle

赞(0）回复(0）举报 2023-04-20

mwg9r5ms2#

实际上，提供了示例数据，您可以使用CASE表达式来获得预期结果：

WITH        --  Sample Data
    credits AS
        (
            Select 5001 "ID", 12 "ARTCRED", 7.5 "BIZCRED", 12 "HUMCRED", 14 "NATCRED", 11 "TEKCRED", 9 "GENCRED" From Dual Union All
            Select 5002 "ID", 10.5 "ARTCRED", 6 "BIZCRED",  5 "HUMCRED",  4 "NATCRED",  6 "TEKCRED", 3 "GENCRED" From Dual 
        ),
    grp_choices AS
        (
            Select 5001 "ID", 'ART' "GROUPCHOICE", To_Date('03.03.2010', 'dd.mm.yyyy') "DATECHOICE" From Dual Union All
            Select 5001 "ID", 'BIZ' "GROUPCHOICE", To_Date('05.05.2015', 'dd.mm.yyyy') "DATECHOICE" From Dual Union All
            Select 5001 "ID", 'NAT' "GROUPCHOICE", To_Date('23.06.2022', 'dd.mm.yyyy') "DATECHOICE" From Dual Union All
            Select 5002 "ID", 'ART' "GROUPCHOICE", To_Date('23.06.2023', 'dd.mm.yyyy') "DATECHOICE" From Dual
        )
--
--  M a i n   S Q L
Select  c.ID, 
        g.GROUPCHOICE, 
        CASE g.GROUPCHOICE
            WHEN 'ART' THEN c.ARTCRED
            WHEN 'BIZ' THEN c.BIZCRED
            WHEN 'HUM' THEN c.HUMCRED
            WHEN 'NAT' THEN c.NATCRED
            WHEN 'TEK' THEN c.TEKCRED
            WHEN 'GEN' THEN c.GENCRED
        ELSE 0
        END "CREDIT"
From    credits c
Inner Join grp_choices g ON(g.ID = c.ID)
--
--  R e s u l t :
        ID GROUP     CREDIT
---------- ----- ----------
      5001 ART           12
      5001 BIZ          7.5
      5001 NAT           14
      5002 ART         10.5

赞(0）回复(0）举报 2023-04-20

我来回答

oracle SQL根据另一个表的列值显示列

2条答案

相关问题

热门标签

最新问答