我有两个表，一个是列表表，这是表的数据类型
create table List(
CustNo Int,
NoOverDue money,
OverDue1 money,
OverDue2 money,
OverDue3 money,
OverDue4 money
)
和一个表调用tablecost
create table tablecost(
custno int,
cost money,
DueDate datetime,
ListDate datetime
)
我的表格中的数据太多，因此我为您创建了一些数据列，以更快地解决我的问题。
insert into tablecost values (1,100,'20130812','20130712')
insert into tablecost values (2,100,'20130612','20130712')
insert into tablecost values (3,100,'20130512','20130712')
insert into tablecost values (4,100,'20130412','20130712')
insert into tablecost values (5,100,'20130312','20130712')
insert into tablecost values (6,100,'20130112','20130712')
目的是在我的List表中插入：
如果我的到期日尚未结束，它将插入到过期。
如果在1到30天之间过期，它将插入过期1。
如果在31到60天之间过期，它将插入过期2。
如果在61到90天之间过期，它将插入过期3。
如果在91到120天之间过期，它将插入过期4。
这是我在查询上尝试的方法，它工作正常：
insert into List (CustNo, NoOverDue)
select CustNo,Cost from TableCost
where DueDate > GetDate()
and convert(varchar(8),ListDate,112) between '20130711' and '20130713'
insert into List (CustNo, OverDue1)
select CustNo,Cost from TableCost
where DueDate between DateAdd(Day,-30,GetDate()) and DateAdd(Day,-1,GetDate())
and convert(varchar(8),ListDate,112) between '20130711' and '20130713'
insert into List (CustNo, OverDue2)
select CustNo,Cost from TableCost
where DueDate between DateAdd(Day,-60,GetDate()) and DateAdd(Day,-31,GetDate())
and convert(varchar(8),ListDate,112) between '20130711' and '20130713'
insert into List (CustNo, OverDue3)
select CustNo,Cost from TableCost
where DueDate between DateAdd(Day,-90,GetDate()) and DateAdd(Day,-61,GetDate())
and convert(varchar(8),ListDate,112) between '20130711' and '20130713'
insert into List (CustNo, OverDue4)
select CustNo,Cost from TableCost
where DueDate between DateAdd(Day,-120,GetDate()) and DateAdd(Day,-91,GetDate())
and convert(varchar(8),ListDate,112) between '20130711' and '20130713'
这是我的问题，是否有任何可能的方式来缩短或改善查询代码？使此查询仅插入该表似乎很长时间
我同意这样的意见，即您最好将数据的存储和表示需求分开。但是，如果您仍然需要实现当前目标，则可能的解决方案是
INSERT INTO List (CustNo, NoOverDue, OverDue1, OverDue2, OverDue3, OverDue4)
SELECT CustNo
,CASE WHEN ddiff < 1 THEN Cost END NoOverDue
,CASE WHEN ddiff BETWEEN 1 AND 30 THEN Cost END OverDue1
,CASE WHEN ddiff BETWEEN 31 AND 60 THEN Cost END OverDue2
,CASE WHEN ddiff BETWEEN 61 AND 90 THEN Cost END OverDue3
,CASE WHEN ddiff BETWEEN 91 AND 120 THEN Cost END OverDue4
FROM
(
SELECT t.*, DATEDIFF(DAY, duedate, GETDATE()) ddiff
FROM TableCost t
WHERE CONVERT(VARCHAR(8), ListDate, 112) BETWEEN '20130711' AND '20130713'
AND DATEDIFF(DAY, duedate, GETDATE()) <= 120
) q
这是SQLFiddle演示
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句