sql - 强制查询根据 WHERE 值传递重复结果

Question

有人要求我根据帐号向报告中添加几列。

问题是多个帐户在此报告中被多次列出。

报表有500多行，但是只有450个账号。

有没有办法强制我的结果给我重复的数据？

例子:

SELECT
a.AccountNumber,
a.ProgramFlag,
a.ApplicationStatus

FROM
accountInfo as A

WHERE
a.AccountNumber in ('100','101','101','101','102','102','103')

ORDER BY
a.AccountNumber asc

结果:

AccountNumber | Program Flag | ApplicationStatus
------------------------------------------------
100           |      1       |     INSTALLED
101           |      0       |     CLOSED
102           |      1       |     INSTALLED
103           |      0       |     INSTALLED

期望的结果:

AccountNumber | Program Flag | ApplicationStatus
------------------------------------------------
100           |      1       |     INSTALLED
101           |      0       |     CLOSED
101           |      0       |     CLOSED
101           |      0       |     CLOSED
102           |      1       |     INSTALLED
102           |      1       |     INSTALLED
103           |      0       |     INSTALLED

通过重复多次出现的帐户的所有信息，可以更轻松地将所需结果添加到自定义报告中。尤其是 500 多条记录。

Answer 1

您不能在 where 子句中添加行，但您可以在派生表中使用您的数字并对其进行联接。

SELECT
  a.AccountNumber,
  a.ProgramFlag,
  a.ApplicationStatus
FROM
  dbo.accountInfo as A
INNER JOIN 
  (VALUES('100'),('101'),('101'),('101'),('102'),('102'),('103')) as T(AccountNumber)
ON
  A.AccountNumber = T.AccountNumber
ORDER BY
  a.AccountNumber asc;

SQL Fiddle