SQL高手请进,如能解决给高手!
数据库有数据
id beginDate endDate
1 2013-08-01 2013-08-04
查询出结果、可用函数等等
id Date
1 2013-08-01
1 2013-08-02
1 2013-08-03
1 2013-08-04
请高手多多指教。 SQL
[解决办法]
CREATE TABLE #temp(id INT, beginDate DATETIME, endDate DATETIME)
INSERT #temp SELECT 1, '2013-08-01', '2013-08-04'
SELECT
a.id,
[Date] = CONVERT(CHAR(10), DATEADD(DAY, number, beginDate), 120)
FROM #temp a
CROSS APPLY
(
SELECT TOP(DATEDIFF(DAY, A.beginDate, A.endDate)+1) number
FROM MASTER..spt_values
WHERE type ='p'
ORDER BY number
) b
/*
idDate
12013-08-01
12013-08-02
12013-08-03
12013-08-04
*/
select
a.id,convert(varchar(10),dateadd(d,b.number,a.beginDate),120) as Date
from 你的表名 a
left join master..spt_values b on b.type='p'
where dateadd(d,b.number,a.beginDate)<=a.[endDate]
SELECT COUNT(*) FROM master..spt_values b WHERE b.TYPE='p'
--2048
declare @t TABLE
([id] int,[beginDate] datetime,[endDate] datetime)
insert @t
select 1,'1900-01-01','2013-08-06'
select
a.id,
CONVERT(VARCHAR(10),DATEADD(d,b.number,a.beginDate),120) AS Date
from @t a
LEFT JOIN master..spt_values b ON 1=1 AND b.TYPE='p'
WHERE DATEADD(d,b.number,a.beginDate)<=a.[endDate]
--此例最多只能找到 1905-08-10
declare @tab table(id int,beginDate datetime,enddate datetime)
insert into @tab values(1,'20130801','20130804')
select id,dateadd(day,number,begindate) as [date]
from @tab join master..spt_values sp
on dateadd(day,number,begindate)<=enddate
and sp.type='p' and number>=0
--不好意思,上面發錯了,應該是以下代碼:
WITH a1 (id,beginDate,endDate) AS
(SELECT 1,'2013-08-01','2013-08-04')
SELECT a.id,CONVERT(CHAR(10),DATEADD(dd,b.number-1,beginDate),20) date
FROM a1 a,master..spt_values b
WHERE b.type='P' AND b.number BETWEEN 1 AND DATEDIFF(dd,beginDate,endDate)+1