我正在使用Microsoft SQL服务器。以下查询生成一个与以下内容非常相似的表:
idnumber | checkID | taskID | status
1001 | 9000 | 410 | Approved
1001 | 9001 | 410 | Approved
1001 | 9002 | 410 | Approved
1201 | 9100 | 430 | Approved
1201 | 9101 | 430 | Approved
。我需要的是查询只返回其中一个 idnumber,而不考虑其他数据(因此只有一个 idnumber 被"批准"而不是其他 idnumber(。
我想要的结果类似于:
身份证号码 |检查标识 |任务标识 |地位
1001 |9000 |410 |批准
1201 |9100 |430 |批准
到目前为止我的查询:
SELECT DISTINCT viewTable.idnumber
, viewTable.checkID
, viewTable.taskID
, 'Approved' AS status
FROM viewTable
INNER JOIN anotherTable
ON viewTable.idnumber = anotherTable.idnumber
INNER JOIN someOtherTable
ON someOtherTable.idnumber = anotherTable.idnumber
WHERE anotherTable.CODE = 'DesiredCode'
有关此问题的更多信息:我在另一个Table.CODE中寻找一个特定的值,并且只想"批准"该特定代码。上面的查询批准返回 ID 号的任何内容。
您可以使用聚合函数(如 max
或 min
(只对一列中的唯一值感兴趣:
SELECT viewTable.idnumber
, MAX(viewTable.checkID) AS checkID
, MAX(viewTable.taskID) AS taskID
, 'Approved' AS status
FROM viewTable
INNER JOIN anotherTable
ON viewTable.idnumber = anotherTable.idnumber
INNER JOIN someOtherTable
ON someOtherTable.idnumber = anotherTable.idnumber
WHERE anotherTable.CODE = 'DesiredCode'
GROUP BY viewTable.idnumber
一个简单的方法是使用 TOP 1 获取第一行
SELECT TOP 1 DISTINCT viewTable.idnumber
, viewTable.checkID
, viewTable.taskID
, 'Approved' AS status
FROM viewTable
INNER JOIN anotherTable
ON viewTable.idnumber = anotherTable.idnumber
INNER JOIN someOtherTable
ON someOtherTable.idnumber = anotherTable.idnumber
WHERE anotherTable.CODE = 'DesiredCode'
最终通过适当的顺序