主页 / 问题 / 1251223
Accepted
Elvin
Asked:2022-03-04 00:51:05 +0800 CST2022-03-04 00:51:05 +0800 CST

如何摆脱左连接中的重复项？

  • 772

我有一张桌子 RaspisanieZanyatij：

id  KursNazvanie             VremyaNachala
1   Matematica                1300000000
5   БазоваяИнформатика        1312345678
6   Биология                  1318765432
4   География                 1322222222
2   История                   1300000000
3   Литература                1311111111

我想输出这样的值是这样的：

1300000000  1311111111
1311111111  1312345678
1312345678  1318765432
1318765432  1322222222
1322222222  NULL

也就是我要输出“值-值稍大”

但我的代码是：

SELECT DISTINCT
    --i1.id,
    i1.VremyaNachala AS time,
    i2.VremyaNachala AS greater_time
FROM
    RaspisanieZanyatij AS i1
    LEFT JOIN  RaspisanieZanyatij AS i2 ON   i2.VremyaNachala > i1.VremyaNachala

给出重复项：

1300000000  1311111111
1300000000  1312345678
1300000000  1318765432
1300000000  1322222222
1311111111  1312345678
1311111111  1318765432
1311111111  1322222222
1312345678  1318765432
1312345678  1322222222
1318765432  1322222222
1322222222  NULL

如何修复代码以删除左侧的重复项并仅输出大于右侧的最小项？

sql

1 个回答

  1. Best Answer
    SELECT DISTINCT t1.VremyaNachala, t2.VremyaNachala
FROM RaspisanieZanyatij t1
LEFT JOIN RaspisanieZanyatij t2 ON t1.VremyaNachala < t2.VremyaNachala
WHERE NOT EXISTS ( SELECT NULL 
                   FROM RaspisanieZanyatij t3
                   WHERE t1.VremyaNachala < t3.VremyaNachala
                     AND t3.VremyaNachala < t2.VremyaNachala )

    或者

    SELECT t1.VremyaNachala, MIN(t2.VremyaNachala) VremyaNachala
FROM RaspisanieZanyatij t1
LEFT JOIN RaspisanieZanyatij t2 ON t1.VremyaNachala < t2.VremyaNachala
GROUP BY t1.VremyaNachala

    还有更多选择...

    • 2

相关问题