如何在SQL查询结果中动态添加行(s)

Question

这是一个查询。

SELECT T.TestTypeId, F.FlockCode, AMT, CV, S.SampleDate 
FROM [dbo].[Tests] AS T 
JOIN [Samples] AS S ON T.SampleId = S.Id 
JOIN [TestTypes] AS TT ON T.TestTypeId = TT.Id 
JOIN [Flocks] AS F ON T.FlockId = F.Id 
WHERE T.TestTypeId = 7 AND T.IsDeleted = 0 
AND S.SampleDate BETWEEN '2017-12-17' AND '2018-01-08' 
AND T.FlockId IN (7,13)
ORDER BY S.SampleDate, F.FlockCode

返回以下结果

TestTypeId  FlockCode   AMT     CV      SampleDate
7           R1.33       8.93    9.28    2017-12-17
7           R1.33       9.05    10.76   2018-01-01
7           R1.37       8.95    8.99    2018-01-01

对于现有的数据而言，S.SampleDate 2017-12-17 只有一个 FlockCode R1.33 的行是准确的。

现在我需要在这个结果中为 FlockCode R1.37 添加另一行数据，日期为 2017-12-17，AMT 和 CV 的值为 0 或 null。

因此，预期的结果如下：

TestTypeId  FlockCode   AMT     CV      SampleDate
7           R1.33       8.93    9.28    2017-12-17
7           R1.37       0       0       2017-12-17
7           R1.33       9.05    10.76   2018-01-01
7           R1.37       8.95    8.99    2018-01-01

任何 SQL、LINQ 或 C# 的解决方案都将非常有帮助。提前感谢。

- ramzan ali

Answer 1

基本的结构是使用交叉连接（cross join）和左连接（left join）进行操作。由于您的查询有些复杂，建议使用以下方法：

with t as (
      SELECT T.TestTypeId, F.FlockCode, AMT, CV, S.SampleDate 
      FROM [dbo].[Tests] T JOIN
           [Samples] S
           ON T.SampleId = S.Id JOIN
           [TestTypes] TT
           ON T.TestTypeId = TT.Id  JOIN
           [Flocks] F
           ON T.FlockId = F.Id 
      WHERE T.TestTypeId = 7 AND T.IsDeleted = 0 AND
            S.SampleDate BETWEEN '2017-12-17' AND '2018-01-08' AND
            T.FlockId IN (7, 13)
     )
SELECT f.TestTypeId, f.FlockCode, COALESCE(t.AMT, 0) as AMT,
       COALESCE(t.CV, 0) as CV, d.SampleDate
FROM (SELECT DISTINCT TestTypeId, FlockCode FROM t) f CROSS JOIN
     (SELECT DISTINCT SampleDate FROM t) d LEFT JOIN
     t
     ON f.FlockCode = t.FlockCode AND f.TestTypeId = t.TestTypeId,
        d.SampleDate = t.SampleDate
ORDER BY d.SampleDate, f.FlockCode;

- Gordon Linoff

---

啊，这就是我在寻找的答案。 - ramzan ali

Answer 2

在SQL中，您可以使用UNION运算符。

SELECT T.TestTypeId, F.FlockCode, AMT, CV, S.SampleDate 
FROM [dbo].[Tests] AS T 
JOIN [Samples] AS S ON T.SampleId = S.Id 
JOIN [TestTypes] AS TT ON T.TestTypeId = TT.Id 
JOIN [Flocks] AS F ON T.FlockId = F.Id 
WHERE T.TestTypeId = 7 AND T.IsDeleted = 0 
AND S.SampleDate BETWEEN '2017-12-17' AND '2018-01-08' 
AND T.FlockId IN (7,13)
UNION 
SELECT 7 AS TestTypeId, 'R1.37' AS FlockCode, 0 AS AMT, 0 AS CV, '2017-12-17' AS SampleDate
ORDER BY SampleDate, FlockCode

- Yogesh Sharma

---

谢谢您的回答，这符合要求，但是这里的 7 或 R1.37 将会动态更改。 - ramzan ali

Answer 3

只需使用UNION！

SELECT T.TestTypeId, F.FlockCode, AMT, CV, S.SampleDate 
FROM [dbo].[Tests] AS T 
JOIN [Samples] AS S ON T.SampleId = S.Id 
JOIN [TestTypes] AS TT ON T.TestTypeId = TT.Id 
JOIN [Flocks] AS F ON T.FlockId = F.Id 
WHERE T.TestTypeId = 7 AND T.IsDeleted = 0 
AND S.SampleDate BETWEEN '2017-12-17' AND '2018-01-08' 
AND T.FlockId IN (7,13)
UNION
SELECT 7 AS TestTypeId, 'R1.37' as FlockCode, 0 as AMT, 0 as CV, '2017-12-17' as SampleDate
ORDER BY SampleDate, FlockCode

- Mark E

---

谢谢您的回答，这符合要求，但是这里的 7 或 R1.37 将会动态更改。 - ramzan ali