Question

我有以下三点询问:所有数据,最后7天,最后30天。

SELECT AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount
FROM dbo.Products
WHERE Id = @id

SELECT AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount
FROM dbo.Products
WHERE Id = @id AND DATEDIFF(day, UpdatedDatetime, getdate()) < 7

SELECT AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount
FROM dbo.Products
WHERE Id = @id AND DATEDIFF(day, UpdatedDatetime, getdate()) < 30

这三个问题给我提供了正确的数据,但并非我所希望的形式。是否有办法将这三个问题合并成一个问题。我的最终目标是,把所有数据放在一行。

此外,在我看来,7天的平均计算结果可以重新使用30天和全部清单。我是否能够做到最佳?

Answer 1

与此类似。确保适当测试

SELECT
      AVG(Price) AS AggregatedPrice
    , COUNT(*) AS PCount
    , AVG(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 7 THEN Price ELSE Null End) AS AggregatedPrice7Days
    , SUM(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 7 THEN 1 ELSE 0 End) AS AggregatedPrice7Days
    , AVG(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 30 THEN Price ELSE Null End) AS AggregatedPrice30Days
    , SUM(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 30 THEN 1 ELSE 0 End) AS AggregatedPrice30Days
FROM
    dbo.Products
WHERE
    Id = @id

Answer 2

SELECT @Id
       , t1.AggregatedPrice AggregatedPrice_ALL, t1.PCount PCount_ALL
       , t2.AggregatedPrice AggregatedPrice_7, t2.PCount PCount_7
       , t3.AggregatedPrice AggregatedPrice_30, t3.PCount PCount_30
FROM
  (SELECT Id, AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount
     FROM dbo.Products
    WHERE Id = @id) t1
  JOIN 
  (SELECT Id, AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount
     FROM dbo.ApplicationPrice
    WHERE Id = @id AND DATEDIFF(day, UpdatedDatetime, getdate()) < 7) t2 
  ON t1.Id = t2.id
  JOIN
  (SELECT Id, AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount
     FROM dbo.ApplicationPrice
    WHERE Id = @id AND DATEDIFF(day, UpdatedDatetime, getdate()) < 30) t3
  ON t1.Id = t3.id

Answer 3

我没有T-SQL手,来测试yn子,但应该这样:

SELECT t1.AggregatedPrice, t1.PCount, t2.AggregatedPrice, t2.PCount, t3.AggregatedPrice, t3.PCount 
FROM
  (SELECT AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount  FROM dbo.Products  WHERE Id = @id) t1,
  (SELECT AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount  FROM dbo.Products  WHERE Id = @id AND DATEDIFF(day, UpdatedDatetime, getdate()) < 7) t2,
  (SELECT AVG(Price) AS AggregatedPrice, COUNT(*) AS PCount  FROM dbo.Products  WHERE Id = @id AND DATEDIFF(day, UpdatedDatetime, getdate()) < 30) t3

Answer 4

为此:

SELECT  AVG(Price) AS AggregatedPrice, 
        COUNT(*) AS PCount,
        AVG(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 7 THEN Price ELSE NULL END) AS AggregatedWeekPrice, 
        COUNT(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 7 THEN 1 ELSE NULL END) AS PWeekCount,
        AVG(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 30 THEN Price ELSE NULL END) AS AggregatedMonthPrice, 
        COUNT(CASE WHEN DATEDIFF(day, UpdatedDatetime, getdate()) < 30 THEN 1 ELSE NULL END) AS PMonthCount,
FROM dbo.Products
WHERE Id = @id

友情链接