代码之家 › 专栏 › 技术社区 › Kyle Rosendo

SQL查询帮助

tsql sql

Kyle Rosendo · 技术社区 · 15 年前

select 
   TableB.Name, 
   TableA.Charge, 
   ( 
     select Charge 
     from TableA 
     where (DateAdded >= '13/10/2009' and DateAdded < '14/10/2009')
   ) 
     from TableA

inner join 
   TableB on TableB.ID = TableA.ID

where 
   TableA.DateAdded >= '10/14/2009'

order by 
   Name asc

CHARGE

最后,我想对两个返回的电荷场做一个计算,如果它更容易显示,那也很好。

提前谢谢

凯尔

编辑1:

昨天,我们向MachineA收取了500英镑的费用今天我们向MachineA收取300英镑的费用

我们运行查询,我需要的结果如下:

Name = MachineA
Charge = 300
YesterdayCharge = 500

4 回复 | 直到 15 年前

van 15 年前

如果您确实需要以前的日期(包括周末等),那么下面的查询应该可以完成这项工作。否则,请发布数据样本和预期结果:

SELECT      TableB.Name,
            TableA.Charge,
            prev.Charge AS PrevCharge
FROM        TableA
INNER JOIN  TableB 
        ON  TableA.ID = TableB.ID
LEFT JOIN   TableA prev
        ON  TableA.ID = prev.ID
        --// use this if DateAdded contains only date
        --AND TableA.DateAdded = DATEADD(day, +1, prev.dateAdded)
        --// use this if DateAdded contains also time component
        AND CONVERT(DATETIME, CONVERT(CHAR(8), TableA.DateAdded, 112), 112) = DATEADD(day, +1, CONVERT(DATETIME, CONVERT(CHAR(8), prev.dateAdded, 112), 112))

编辑-1: 当DateAdded也包含时间时,在JOIN中添加了选项

balexandre 15 年前

SELECT 
   B.Name, 
   A.Charge,
   DATEPART(day, A.DateAdded) as day
FROM
   TableA A, Table B
WHERE
   B.ID = A.ID AND
   A.DateAdded BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()
GROUP BY
   B.Name, 
   A.Charge,
   A.DateAdded

Maksym Gontar 15 年前

试试这个:

DECLARE @ValuesTable TABLE(Name VARCHAR(20), Charge INT, DateAdded DATETIME)

INSERT INTO @ValuesTable
SELECT 'Name1', 10, DATEADD(dd, 2, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 20, DATEADD(dd, 2, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 30, DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 40, DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 50, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 60, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 70, DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name3', 80, DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 90, DATEADD(dd, -2, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 100, DATEADD(dd, -2, DATEDIFF(dd, 0, GETDATE()))


SELECT 
ISNULL(T.Name,Y.Name) AS Name, 
SUM(ISNULL(Y.Charge,0)) AS Yesterday, SUM(ISNULL(T.Charge,0)) AS Today,
SUM(ISNULL(T.Charge,0)) - SUM(ISNULL(Y.Charge,0)) AS Diff
FROM(
    SELECT Name, Charge 
    FROM @ValuesTable 
    WHERE DateAdded BETWEEN DATEADD(day, -2, GETDATE()) 
        AND DATEADD(day, -1, GETDATE())
) AS Y
FULL JOIN(
    SELECT Name, Charge 
    FROM @ValuesTable 
    WHERE DateAdded BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()
) AS T ON ISNULL(T.Name,Y.Name) = ISNULL(Y.Name,T.Name)
GROUP BY ISNULL(T.Name,Y.Name) , ISNULL(Y.Name,T.Name)

MLT 15 年前

这可能只是SO中的一个输入错误,但如果在同一个查询中使用日期字符串“14/10/2009”和“10/14/2009”,它将永远不会起作用。无论您使用哪种日期格式,其中一种格式中的月份太多。