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

请高手多多指教。

[Shawn]

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

/*
id	Date
1	2013-08-01
1	2013-08-02
1	2013-08-03
1	2013-08-04
*/

[叶子]

declare @t TABLE
([id] int,[beginDate] datetime,[endDate] datetime)
insert @t
select 1,'2013-08-01','2013-08-04'

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]
/*
id          Date
----------- ----------
1           2013-08-01
1           2013-08-02
1           2013-08-03
1           2013-08-04
*/

[Shawn]

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), B.[Date], 120)
FROM #temp a
CROSS APPLY
(
    SELECT [Date]=DATEADD(DAY, number ,A.beginDate) 
    FROM MASTER..spt_values 
    WHERE type ='p'
		AND DATEADD(DAY, number ,A.beginDate) <= A.endDate
) b
 
/*
id    Date
1    2013-08-01
1    2013-08-02
1    2013-08-03
1    2013-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]

[天二天]

我试了一个，1楼 和4楼的方法都可以用。各给一半分。谢谢你们。

[---涛声依旧---]

楼主，注意了：楼上的方法都有局限性了，两个日期之间最好不要相隔2048天了，否则就出现BUG了

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

[kobemadi]

动态语句啊，学习了。

[习惯性蹭分]

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

[lzw_0736]

create table cs (id char (2),name char (10))

insert into cs 
select'1'   ,  'aa'
union all 
select'1'   ,  'a'
union all 
select'1'   ,  'aa'
union all 
select'2'   ,  'aq'
union all 
select'2'   ,  'aq'
union all 
select'3'   ,  'a'
union all 
select'3'   ,  'a'
union all 
select'3'   ,  'v'
union all 
select'3'   ,  'd'
union all 
select'4'   ,  'c'
1 	3
2 	2
3 	4
4 	1

;
with a1 as
(
select id,count(*) as qty from cs group by id 
)
,a2 as
(
select *,3-case when qty%3=0 then 3 else qty%3 end add_qty from a1
)
,a3 as
(
select *,id id2 from cs
union all
select null,null,a.id
from a2 a
inner join master..spt_values b on b.number between 1 and a.add_qty
where b.type='P'
)
select id,name
from a3
order by id2,id desc

[天二天]

引用 4 楼 maco_wang 的回复:

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]

不知道oracle这样有没有用也！

[叶子]

引用 13 楼 yanghaic2009 的回复:

[quote=引用 4 楼 maco_wang 的回复:]

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]

不知道oracle这样有没有用也！[/quote] oracle没有master..spt_values这个系统表。

[小孩快跑]

都是大神来的！

[天二天]

引用 13 楼 yanghaic2009 的回复:

[quote=引用 4 楼 maco_wang 的回复:]

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]

不知道oracle这样有没有用也！[/quote] 那怎么办呢

[ljr_aa]

可以自己建一个序列表，再按以上方法

[lzw_0736]

--不好意思，上面發錯了，應該是以下代碼：
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

[service490229980]

可以不用sql语句的，不过用sql语句生成，第一次看到，学习了

[看破世界]

收藏