Re行号查询

Question

我有如下需求:

学生和家长登录信息需要合并邮件发送信件

在我们的数据库中，大多数学生是单亲家长，少数是单亲家长

我正在写一个查询(下面)，以提供学生和家长的详细信息以及用户名- studententid_1或studententid_2(取决于父母的数量)自动生成的密码。

我们要做的是，如果一个学生是单亲家长，那么只有他们的联系方式需要显示在我的查询中，而不是额外的id

我正在努力在我的查询中实现这一点，所以任何帮助都非常感谢。

SELECT StudID, StudName, RTRIM(StudID) + '_1' AS Parent_Username, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(1))
+ CHAR(ASCII('a') + ABS(CHECKSUM(NEWID())) % 25) + CHAR(ASCII('A') + ABS(CHECKSUM(NEWID())) % 25) + LEFT(NEWID(), 5) AS Parent_Password
FROM
Student
StudId   StudName          Gender Parent Username  Parent Password
09054828 Alexander  Lewis    M     09054828_1       0iCCA086
09054828 Alexander  Lewis    M     09054828_1       6jI247CA
09054828 Alexander  Lewis    M     09054828_1       7xA1075E
09054828 Alexander  Lewis    M     09054828_1       3dFCBCCF
09056750 Daniel O'Sullivan   M     09056750_1       6tC76C96
09057033 Daniel Geggus       M     09057033_1       9gXDB414
And we have Parents Portal table which has below data
StudentID    Name               ParentUsername   ParentPassword 
9054828 Lewis   Alexander          09054828_1       l5oXYQo
9054828 Lewis   Alexander          09054828_2       70AYvbi
9056750 O'Sullivan  Daniel         09056750_1       1OnVMn0
9056750 O'Sullivan  Daniel         09056750_2       rT47Sx5
9057033 Geggus  Daniel             09057033_1       Ap6EV3v
9057033 Geggus  Daniel             09057033_2       Ar4AQ22

因此，一旦我将原始查询连接到Parents Portal表，我看到如下

   StudentID    Name               ParentUsername   ParentPassword 
09054828 Alexander  Lewis    M     09054828_1       0iCCA086
09054828 Alexander  Lewis    M     09054828_1       6jI247CA
09054828 Alexander  Lewis    M     09054828_1       7xA1075E
09054828 Alexander  Lewis    M     09054828_1       3dFCBCCF
9056750      Daniel O'Sullivan     09056750_1        1OnVMn0
9057033      Daniel Geggus         09057033_1        Ap6EV3v

。e即使一个学生有两个父母，结果也会翻倍(4行而不是2行)

任何想法或建议我如何限制重复行?

谢谢基于"增大化现实"技术的

Answer 1

您可以在普通表表达式或子查询中使用RANK() OVER (PARTITION By StudID order By)，然后只过滤RANK =1。

with query as (
SELECT 
RANK() OVER (PARTITION By StudID  order by <column parent related>)  as rank,
StudID, 
StudName, 
RTRIM(StudID) + '_1' AS Parent_Username, 
CAST(
     ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(1)) 
    + CHAR(ASCII('a') + ABS(CHECKSUM(NEWID())) % 25) 
    + CHAR(ASCII('A') + ABS(CHECKSUM(NEWID())) % 25) 
    + LEFT(NEWID(), 5) AS Parent_Password
FROM Student)
select StudID,StudName,Parent_Username, Parent_Password from query where rank = 1

详细信息请参见windows文档